Skip to main content

Excel Rietveld

Excel Rietveld Refinement

Files needed: excel_rietveld_01.xlsx; tio2_excel.xy; tio2_excel_01.inp

Learning Outcomes: Most of the tutorials on this site use specialised software packages to perform Rietveld refinement. However we’ve seen in the earlier tutorial on data analysis that we can use least-squares fitting in excel to fit peaks, refine unit cells, etc. This tutorial shows how it’s possible to set up a full Rietveld refinement in excel. The example uses the first seven reflections in a pattern of TiO2 and allows you to refine the one free fractional coordinate on O.

If you haven’t already done the tutorial on least-squares fitting of experimental data and simple peak shapes go here.

This tutorial has been published in the Journal of Chemical Education. Read more detail here

Return to this example as you learn more about Rietveld refinement…you may not understand it all on the first run through!

The tutorial builds incrementally to a full Rietveld by:

  • Reviewing the fitting of a single Gaussian peak as in the earlier tutorial.
  • Fitting 4 Lorentzian peaks in a single data set – an example of whole-powder pattern fitting.
  • Fitting peaks with freely refined intensities at 2-theta values determined by unit cell parameters (a Pawley or Le Bail type approach) using a Lorentzian peak shape.
  • Instead of allowing intensities of each reflection to refine freely, calculating them based on a structural model. i.e. a Rietveld fit. The first example uses a “perfect data set” simulated using Cu Kalpha1 radiation.
  • Introducing a Kalpha2 component and fitting a real experimental data set (d5_05005.raw from other tutorials).
  • (Optional) Fitting neutron diffraction data.

There are two main take-home-messages of the tutorial: (1) Rietveld software isn’t a “magic black box” but is doing easily understandable things; (2) It’s far easier to use purpose-written Rietveld software than excel!

1. Save the files listed above in your working directory.

2. Open the excel file excel_rietveld_01.xlsx. This contains several worksheets. You can either look at each in turn or jump straight to sheet tio2_riet depending on how much time you have. Some of the cells in the spreadsheets have red triangles in the corner. Hover over these to get tips and information. In general suggested refinable parameters are shaded yellow, and the (yobs-ycalc)^2 summation which should be minimised shaded blue. If your refinment gets lost at any stage try going to “data//what-if-analysis/scenario manager” and see if there is a saved set of values.

3. Review the sheet fit_gaussian_answer from the earlier tutorial first. This fits a single Gaussian peak shape as in the earlier tutorial. Make sure you understand how it works, and how to use solver for least-squares fitting.

4. Next look at sheet TiO2_pkfit. This performs exactly the same things as the Gaussian peak fitting example. Here it’s done on a simulated powder pattern of TiO2 with four reflections. We just sum up four Lorentzian functions to fit the data and use a simple polynomial to fit the background. This is whole pattern peak fitting (in topas language a “peaks” phase). Peaks are allowed to refine to any values of 2-theta.

5. Change the peak positions and intensities to see how ycalc varies. Set them to values that look like a good starting model. Then minimise the blue box by refining values in the yellow boxes. Watch the minimisation in the bottom left of the screen. Note that this is a computationally wasteful approach as each peak is calculated over the whole 2-theta range of the data. In real software it’s typical to just calculate over a range of 2-theta values close to the peak maximum for computational efficiency.

6. In sheet TiO2_pawley we again work with a simulated powder pattern of TiO2. We again allow the intensity of the peaks to refine freely. However their positions are now determined by the unit cell parameters. This approach is similar to Le Bail or Pawley fitting. In topas language, a hkl_Is phase. Try changing cell constants to a = 4.59 and c = 2.95 and try minimising the blue box by changing values of yellow boxes. As this example is based on simulated experimental data it uses a Lorentzian peak shape function rather than Gaussian.

7. In sheet TiO2_riet we perform a full Rietveld type refinement. Peak positions are determined by the cell constants and a refineable zero point correction. The structure factor (Fhkl) of each reflection is calculated from the atomic coordinates using the expression given in the work sheet. Scattering factors fj are calculated as a function of 2-theta using scattering factor parameters from International Tables. See sheet fj values for these calculations in more detail. Intensities are then scaled by an LP factor, the reflection multiplicity and a refinable overall scale factor. An atomic displacement parameter (temperature factor) can be refined for each atom. A 2nd order polynomial is used to fit the background. The Lorentzian full width at half maximums are described by a simple 2-theta dependent polynomial. The Rietveld plot is given along with a zoom of each peak. The Rwp value is determined and quoted.

8. Try changing the zero point to 0.1 degrees and seeing the magnitude of the effect on peak positions. You should be able to refine back to the minimum using the solver function to minimise the blue box by refining all the yellow boxes. You can watch the refinement’s progress in the bottom left of the excel screen. Make sure Rwp comes back to around 15.5%. You can watch the sum w(yobs-ycalc)^2 changing in the bottom left of the excel sheet. It takes about 15 seconds to converge on a 2014 vintage low powered laptop. If the refinement diverges, try manually resetting yellow boxes to sensible values, then repeat the refinement with fewer parameters refining. If it gets totally lost, try “data/what-if-analysis/scenario manager”.

9. Try changing the a cell parameters to 4.60, note which reflections are affected. Note which way they move. Is this what you expect from Bragg’s law? Change the c cell parameter to 2.94 and see which way reflections move. See if cell parameters will refine back to their correct values.

10. Try changing the a cell parameter to 4.7 and c cell parameter to 3.0. Will the refinement converge (make a backup copy before you refine!)? Would you expect it to? What does happen?

11. Try changing the O x coordinate to 0.40 and seeing if it will refine back to its correct value of ~0.307. Look at how much peak intensities are affected by the O position. Try changing it to 0.0. Does it still refine back to the correct value?

12. Try changing the O x coordinate to 0.65. What does it refine to? Why does it not converge back to 0.307 even though your Rp value is still ~15.4%? You could try making a plot of wRp vs O x value to help understand this.

13. Investigate how sensitive the key structural parameter O x is to other aspects of the model. Does the value of x change when (a) peak widths are fixed to 0.03° (set u = v = 0, w = 0.03) or (b) as the fixed background term r is changed from 0 to 26 to 500? Refine just O x and scale.

14. Investigate how atomic displacement parameters influence the refinement. Try changing B on each site to e.g. +5 then -5 to see the effect on the calculated pattern. Try a refinement with oxygen B fixed at 1.0 and 2.0, are there large changes in Rwp?

15. Try changing the parameters that describe the peak fwhm evolution as a function of 2-theta. How sensitive is the peak shape to these values? How well does the refinement converge when peak shape coefficients are changed from their ideal values? Try using excel constraints to stabilise the refinement against divergence.

16. Possible extra work: try modifying the spreadsheet so it allows refinement of the fractional occupancy of different atoms sites. You could explore how sensitive refinements are to partial occupancy.

17. Possible extra work: Test the sensitivity of Rwp to O x by fixing all parameters except the cell parameter, scale and zero point and performing a series of refinements at fixed x values from 0 to 1 in 0.05 steps. Plot Rwp vs x. How does this curve explain parts 11 and 12 above?

18. Possible extra work: Try changing a copy of the spreadsheet to simulate the neutron powder diffraction pattern of rutile (the appropriate LP correction11 is obtained with 2 mono = 90 °). How do the intensities change relative to the X-ray pattern? How sensitive is the neutron pattern to O x compared to the X-ray?

19. Possible extra work: We can get some idea on one way of solving structures from powder diffraction using this spreadsheet. In part 11 we found that the O x coordinate does not converge to the correct value if it is started at x = 0.0. What if we didn’t know a sensible starting value? Excel solver has a feature to try multiple random starting values. In the solver dialog add constraints that -0.1 < O x < 1.1 and that 0.0 < scale <0.01. In the options box select the choice to “Use Multistart”. Set both “Max Subproblems” and “Max Feasible Solutions” to 10. When you run solver, Excel will try multiple different re-finements with different starting x values and you should rapidly re-find the b est fit answer. If you repeat this several times you will randomly find the x ~ 0.3 and x ~ 0.7 equivalent solutions. Whilst this is a trivial example, remarkably complex structures have been solved with this type of approach.

20. We can perform exactly the same refinement in topas. Open the input file linked here and refine. You’ll need the datafile tio2_excel.xy in the same directory. You’ll appreciate how much simpler the topas file looks than the excel spreadsheet!

21. Finally, have a look at the sheet TiO2_riet_real_data. This performs the same refinement but this time using real experimental data. The dataset is d5_05005.raw that you use in the TiO2 Rietveld tutorial. The data was recorded using a Cu tube and graphite monochromator (26.6 degrees) which gives Ka1 and Ka2 radiation in a ~2:1 ratio. To do the Rietveld fit, the excel spreadsheet sums up two Lorentzian peaks for each reflection, one at the 2-theta value calculated for Cu Kalpha1 (1.5406 A) and one for Cu Kalpha2 (1.54493 A). At low 2-theta these reflections are sufficiently close that you only see one peak. At high 2-theta they are clearly resolved. To keep the spreadsheet relatively compact, the same scattering lengths, LP correction and adp correction are applied to the alpha1 and alpha2 peak components. Compare your O x coordinate with the one from the TiO2 Rietveld tutorial.

22. From the unit cell parameters and O fractional coordinate determine the Ti-O bond length. Alternatively, enter your final refined coordinates, cell parameters and space group into a drawing program like vesta , crystamaker or mercury. View the structure. Measure TiO6 bond distances and angles.

23. If you have access to a diffractometer try recording your own dataset on TiO2 and replacing the experimental data in this spreadsheet with your own. Just make sure that the columns containing the intensity of each calculated hkl reflection are the same length as your experimental data. Make sure the sum w(yobs-ycalc)^2 is done over the correct column length for your data. You’ll need to change the instrumental wavelength, monochromator angle and Ka1:Ka2 ratio to correct values for your instrument.