Comparing two PXRD patterns in Excel (alternative view with large screen shots)

PXRD Material Index  Previous Page  Next Page

Comparing two PXRD patterns in Excel

Excel can be used to make visual comparisons between multiple PXRD data sets. The data sets may have been save as individual Excel files so the first step is to move the data sets into a single Excel workbook. The example given below shows how to compare a second data set (D04763) measured on a Siemens D500 Bragg-Brentano diffractometer with the previous data set measured in capillary geometry on the Stoe Stadi-P diffractometer. Both were measured with Cu Kα₁ X-rays so the data can be compared directly as measured, i.e. using the 2θ scale.

Firstly, open the workbook file containing the first PXRD data set. Then open the second workbook file. Under the main Home tab, find the Format options, and select Move or Copy Sheet... to bring up a menu for moving or copying worksheets:

The first pull-down menu enables the user to select to which open workbook the worksheet is to be moved or copied. Select the file that contains the first data set (C05075.xlsx in this example):

Then select whereabouts in the file the worksheet should be copied or moved, and tick the box for the worksheet to be copied (i.e. the original worksheet should be left in the file and not moved to the new file). In this example, I have chosen it to be put at the end of the file (so that tabs at the bottom of the worksheets will appear in the order shown in the copy/move worksheet menu):

Click OK and the worksheet will appear in the same workbook as the first data set as shown below:

The next step is to plot the second PXRD data set on top of the existing chart. Select the chart tab (labelled "PXRD Plot" in this example) and click the right mouse-button on the existing plot so as to access the Select Data... menu:

When the Select Data Source menu appears, choose the option Add to plot an additional data series to the existing plot:

Select the data columns to be plotted in the second data set. It is useful to add a name for the series too (in this case I used "My Sample") at this point:

Once the X and Y values for plotting have been selected, click on OK and the second data set will appear in the list of data series to be plotted as shown below:

Finally, click on OK to accept all the data series shown above.

As imported, the second data set is unscaled and so is only just visible at the bottom of the plot. Excel has default the appearance of the data as an over thick dark-red line. Note that the second set was counted with a scintillation point detector, which has a low count rate compared to a solid-state 1-D PSD (= Position Sensitive Detector).

As seen above, plotting each raw data set on the same graph is not a good idea for data collected under different conditions. One solution is to normlise each data set to a common maximum count of, say, 10000 or 100%. Another solution is to scale the secnd data set so that it has similar counts to that of the first. This is easily performed in a spreadsheet program such as Excel.

Firstly, insert an extra column in the spreadsheet for the second data set (column D in the example below). Then create a variable that will be used to scale the data. In the example below the value 10 has been inserted in cell F12 (and has been labelled with the text "Scaling factor" in cell E12). Finally, all the values in column D are set to equal to those in column B multiplied by the value of cell F12. Note the use of the "$" character so that the same cell F12 is used for the calculation of all values in column D (which has been given the column title "Scaled" as shown below:

The next step is to return to the chart and to edit the data series for the second plot so that the scaled data (contents of column D) are used for the plot. Right mouse-button click on the chart to bring up the menu with Select Data ... and return to the data series menu as before. Select the second series and this time choose Edit:

Simply reselect the Y data for plotting (the legend and X values are OK) using the values in column D as shown:

Click on OK to return to the data series menu as shown below. The scaled data should already be visible as shown below:

If all looks as expected, click OK to accept the new plot.

Instead of using an arbitrary scaling factor of, say, 10, it is visually better to scale the second data set so that the maximum intensity matches that of the first data set. The value of the scaling factor is readily calculated to the maximum value in data set C05075 (in cell B1786) divided by the maxiumum value in the current data set D04763 (in cell B438) as shown below:

Note how values in different worksheets are referenced using the tab name in single quote marks and an exclamation mark as a separator from the cell identifier.

Finally, select the second data series with the mouse and format it to the same line thickness as for the first data set shown in black. I also chose to use bright red for the colour at this point too. The final comparison plot now looks like the one below where the second data set has been scaled to that of the first. Note some of the differences between the two PXRD patterns even though the sample was the same identical material for each PXRD data collection.

PXRD Material Index  Previous Page  Next Page