INDEXING THE X-RAY DIFFRACTION PATTERNS OF CUBIC AND HEXAGONAL

STRUCTURES USING SPREADSHEETS

Zuhoor Elahia,b, Wafa Gullb
a.
Department of Physics, University of Karachi, Pakistan.
b.
Department of Physics and Astronomy, University of Southern Mississippi, USA.

ABSTRACT
X-Ray diffractometry is one of the most useful nondestructive techniques used to characterize
crystalline substances. One can gain knowledge of structures and structural parameters, phases
and texture, average grain size, crystalline strain, and defects, from an XRD pattern. In this
study, we develop a macro-enabled Microsoft excel spreadsheet to study x-ray diffraction
patterns. Through this spreadsheet x-ray diffraction patterns of materials can be analyzed to
find the Miller indices and the lattice parameters. One must provide only the 2θ values for all
the peaks, and then a single click on the calculate button gives the ‘hkl’ values for all the peaks.
This spreadsheet can be used to index XRD patterns of cubical and hexagonal structures.

Keywords: crystal structure, Miller indices, lattice planes, spreadsheets.

1
INTRODUCTION

Several researchers have explored the use of spreadsheets as educational tools across various
fields. For instance, Smith et al. [1] demonstrated the effectiveness of integrating spreadsheets
into mathematics education, highlighting their versatility in visualizing mathematical concepts,
and fostering active learning. In the context of science education, Jones et al. [2] investigated
the use of spreadsheets in chemistry classrooms, emphasizing their role in data analysis,
modeling, and simulation exercises. Similarly, Brown and Miller [3] explored the integration
of spreadsheets in physics education, highlighting their utility in conducting virtual
experiments, analyzing experimental data, and reinforcing theoretical concepts. These studies
collectively underscore the potential of spreadsheets as versatile and accessible educational
tools that facilitate hands-on learning, data visualization, and problem-solving skills
development across various disciplines.
Researchers have employed various methods for analyzing X-ray diffraction (XRD) patterns
beyond the utilization of spreadsheets. One prominent approach involves the use of specialized
software packages tailored for XRD data analysis, such as FullProf [4] and MAUD [5], which
offer advanced functionalities for phase identification, peak fitting, and structural refinement.
Additionally, advancements in machine learning techniques have enabled the development of
algorithms for automated phase identification and quantitative analysis of XRD patterns [6].
Furthermore, studies have explored novel methodologies, including synchrotron X-ray
diffraction and neutron diffraction, to elucidate complex material structures and phase
transitions with high precision and accuracy [7,8]. These diverse approaches underscore the
multifaceted nature of XRD analysis and highlight the importance of leveraging advanced
techniques to unravel the structural properties of materials for various scientific and
technological applications.

1 MATHEMATICAL BACKGROUND
The internal arrangement of atoms in a crystal structure determines the qualitative analysis and
distinctive characteristics of a substance. A crystalline substance's crystal structure, which is a
particular configuration of atoms or molecules, is crucial in determining physical properties
including band structure and optical clarity. X-ray crystallography is thought to be the best
method for precisely determining molecule structure at atomic precision. The detailed
structural information provided by the crystallographic analysis paved the door for quick
development in geology, minerals, seismology, nanotechnology, medicine, and other areas.

2
Atomic structure and X-ray diffraction pattern are closely related. Spectral lines from both
atoms and ions make up this pattern. Thus, a sample's components can be determined using x-
ray diffraction. The likelihood of the atomic or molecular transition decides the strength of the
spectral line in the x-ray diffraction pattern. The sample's crystal structure can be determined
using the x-ray diffraction pattern. While the intensity of a diffraction peak defines the atomic
position within the cell and atomic number, its position determines the size and shape of the
unit cell. A wide range of substances, including metals, liquids, polymers, minerals, plastics,
catalysts, medicines, semiconductors, solar cells, ceramics, thin-film coatings, are analyzed
using XRD patterns.
In this study, we have developed an MS Excel VBA program to analyze the x-ray diffraction
patterns. With the help of this program, x-ray diffraction patterns can be examined to find the
type of crystal structure and to find the lattice parameters. Spreadsheets give pupils the
opportunity to practice dealing with actual data. Spreadsheets enable hypothetical analysis and
give real-world examples to make theoretical or sophisticated theories understandable. One
efficient technique to improve teaching effectiveness is for instructors to use Excel to turn any
spreadsheet into a web page and share it with students. [9]

METHODS OF ANALYZING THE XRD PATTERNS

There are four methods, two for each (i.e., for cubic and hexagonal) structure, to find the Miller
indices, and the lattice parameters. We will discuss all these one by one.

1.1 CUBIC STRUCTURES

1.1.1 Ratios’ Method for Cubical Structures

To figure out the crystal structure of an unknown sample, we must find the Miller indices and
the lattice parameters. The inter-planar spacing d, the distance between adjacent planes in the
set ‘hkl’ of a material with a cubic structure, and lattice parameter a can be obtained from the
equation,
𝟏𝟏 �𝒉𝒉𝟐𝟐 +𝒌𝒌𝟐𝟐 +𝒍𝒍𝟐𝟐 �
𝒅𝒅𝟐𝟐
= 𝒂𝒂𝟐𝟐
(1)

Using Bragg’s law, the above equation becomes,

𝝀𝝀𝟐𝟐
𝐬𝐬𝐬𝐬𝐬𝐬𝟐𝟐 𝜽𝜽 = �𝟒𝟒𝟒𝟒𝟐𝟐 � (𝒉𝒉𝟐𝟐 + 𝒌𝒌𝟐𝟐 + 𝒍𝒍𝟐𝟐 ) (2)

Solving equation (2) for 𝑎𝑎2 gives,

𝝀𝝀𝟐𝟐
𝒂𝒂𝟐𝟐 = �𝟒𝟒𝐬𝐬𝐬𝐬𝐬𝐬𝟐𝟐 𝜽𝜽� (𝒉𝒉𝟐𝟐 + 𝒌𝒌𝟐𝟐 + 𝒍𝒍𝟐𝟐 ) (3)

3
Thus, 𝜆𝜆2⁄4𝑎𝑎2 is different for different patterns, it can be seen that sin2 𝜃𝜃 is proportional to ℎ2 +
𝑘𝑘 2 + 𝑙𝑙 2 i.e., for increasing θ, planes with a higher hkl will diffract.
Writing equation (2) for two different sets of hkl, and dividing we get,
𝐬𝐬𝐬𝐬𝐬𝐬𝟐𝟐 𝜽𝜽𝟏𝟏 ⁄𝐬𝐬𝐬𝐬𝐬𝐬𝟐𝟐 𝜽𝜽𝟐𝟐 = �𝒉𝒉𝟐𝟐𝟏𝟏 + 𝒌𝒌𝟐𝟐𝟏𝟏 + 𝒍𝒍𝟐𝟐𝟏𝟏 ���𝒉𝒉𝟐𝟐𝟐𝟐 + 𝒌𝒌𝟐𝟐𝟐𝟐 + 𝒍𝒍𝟐𝟐𝟐𝟐 � (4)
In a cubic system, the first reflections occur due to diffraction from the planes with the Miller
indices 100 for primitive cubic, 110 for bcc, and 111 for fcc lattices, so ℎ2 + 𝑘𝑘 2 + 𝑙𝑙 2 =
1, 2, or 3, respectively. Since, the ratio of sin2 𝜃𝜃 values for different planes, scales with the ratio
of ℎ2 + 𝑘𝑘 2 + 𝑙𝑙 2 values, according to equation (4), and since, h, k, & l are always integers, the
values of ℎ2 + 𝑘𝑘 2 + 𝑙𝑙 2 can always be found by dividing the sin2 𝜃𝜃 by the smallest (i.e.,
sin2 𝜃𝜃min for 100), and multiplying those ratios thus by a suitable integer to find values close
to integers. Therefore, the values of sin2 𝜃𝜃 computed for all the XRD peaks are divided by the
smallest one (first reflection). These ratios, if are not already, yield integers when multiplied
by 2 or 3, etc. Those resulting numbers represent the values of ℎ2 + 𝑘𝑘 2 + 𝑙𝑙 2 ; hence, the hkl can
easily be identified from the quadratic forms of Table-2. Alternatively, one may also index the
XRD pattern using the ratio of 1⁄𝑑𝑑 2 values. Because 𝜃𝜃 is a directly measurable quantity, we
have chosen to use the sin2 𝜃𝜃 values instead of 1⁄𝑑𝑑2 .
The lattice parameter 𝑎𝑎 can be calculated from equation (3) as:
𝝀𝝀
𝒂𝒂 = 𝟐𝟐𝐬𝐬𝐬𝐬𝐬𝐬𝟐𝟐 𝜽𝜽 √𝒉𝒉𝟐𝟐 + 𝒌𝒌𝟐𝟐 + 𝒍𝒍𝟐𝟐 (5)

Using equation (5) we can completely index the diffraction pattern and find out the Bravais
lattice and lattice parameters of any cubic crystal structure.
Identification of the Bravais Lattice
The identification of the Bravais lattice can be done by observing the systematic occurrence
(or lack) of reflections in a diffraction pattern. The selection rules for cubic lattices are
summarized in Table-1. The values of ℎ2 + 𝑘𝑘 2 + 𝑙𝑙 2 for the different cubic lattices follow
specific sequences as shown below. [10]
Primitive cubic lattice: 1, 2, 3, 4, 5, 6, 8, 9, 10, 11, 12, 13, 14, 16, ...
Body-centered cubic lattice: 2, 4, 6, 8, 10, 12, 14, 16, ...
Face-centered cubic lattice: 3, 4, 8, 11, 12, 16, 19, 20, 24, 27, 32, ...
Table-1: Selection Rules for the Presence or Absence of Reflections
Bravais Lattice Reflections Present for Reflections Absent for
Primitive All None
Body-centered even h+k+l odd h+k+l
Face-centered unmixed h, k, and l (all even, or all odd) mixed h, k, and l

4
 Table-2: Quadratic Forms of Miller Indices for the Cubic System
h2+k2+l2 h k l h2+k2+l2 h k l h2+k2+l2 h k l h2+k2+l2 h k l
1 1 0 0 29 4 3 2 51 5 5 1 75 5 5 5
2 1 1 0 29 5 2 0 51 7 1 1 75 7 5 1
3 1 1 1 30 5 2 1 52 6 4 0 76 6 6 2
4 2 0 0 32 4 4 0 53 6 4 1 77 6 5 4
5 2 1 0 33 4 4 1 53 7 2 0 78 7 5 2
6 2 1 1 33 5 2 2 54 5 5 2 81 6 6 3
8 2 2 0 34 4 3 3 54 6 3 3 81 7 4 4
9 2 2 1 34 5 3 0 54 7 2 1 83 7 5 3
9 3 0 0 35 5 3 1 56 6 4 2 85 7 6 0
10 3 1 0 36 4 4 2 57 5 4 4 86 6 5 5
11 3 1 1 36 6 0 0 57 7 2 2 86 7 6 1
12 2 2 2 37 6 1 0 58 7 3 0 88 6 6 4
13 3 2 0 38 5 3 2 59 5 5 3 89 7 6 2
14 3 2 1 38 6 1 1 59 7 3 1 90 7 5 4
16 4 0 0 40 6 2 0 61 6 4 3 94 7 6 3
17 3 2 2 41 4 4 3 61 6 5 0 97 6 6 5
17 4 1 0 41 5 4 0 62 6 5 1 98 7 7 0
18 3 3 0 41 6 2 1 62 7 3 2 99 7 5 5
18 4 1 1 42 5 4 1 65 6 5 2 99 7 7 1
19 3 3 1 43 5 3 3 65 7 4 0 101 7 6 4
20 4 2 0 44 6 2 2 66 5 5 4 102 7 7 2
21 4 2 1 45 5 4 2 66 7 4 1 107 7 7 3
22 3 3 2 45 6 3 0 67 7 3 3 108 6 6 6
24 4 2 2 46 6 3 1 68 6 4 4 110 7 6 5
25 4 3 0 48 4 4 4 69 7 4 2 114 7 7 4
25 5 0 0 49 6 3 2 70 6 5 3 121 7 6 6
26 4 3 1 49 7 0 0 72 6 6 0 123 7 7 5
26 5 1 0 50 5 4 3 73 6 6 1 134 7 7 6
27 3 3 3 50 5 5 0 74 7 4 3 147 7 7 7
27 5 1 1 50 7 1 0 74 7 5 0

To check if the spreadsheet works properly, we used the XRD pattern of aluminum, taken from
page no. 102 of [10]. We plugged in the 2𝜃𝜃 values only, and by single click on the calculate
button, the whole table has been generated which is shown in the figure-1 below. The resulting
table gives the lattice planes (hkl) and the lattice parameter (a) for cubical structures.

5
Figure-1 X-ray diffraction pattern of aluminum, taken from page number 81 of [10].

Figure-2 Screenshot of the spreadsheet based on the ratios method to find Miller indices of
cubic crystals.

1.1.2 Analytical Method for Cubical Structures

An analytical method is also used to index the diffraction patterns in the same way. Starting
from the first step, which is the same, we have already calculated the sin2 𝜃𝜃 values for all the
major diffracted lines. In this method again, we will use the same equation as we have used
above considering:
𝝀𝝀𝟐𝟐
𝐬𝐬𝐬𝐬𝐬𝐬𝟐𝟐 𝜽𝜽 = �𝟒𝟒𝟒𝟒𝟐𝟐 � (𝒉𝒉𝟐𝟐 + 𝒌𝒌𝟐𝟐 + 𝒍𝒍𝟐𝟐 ) (6)

6
We observe that sin2 𝜃𝜃 is proportional to (ℎ2 + 𝑘𝑘 2 + 𝑙𝑙 2 ) after realizing that 𝜆𝜆2⁄4𝑎𝑎2 is a
constant for any pattern, which we here refer to as A.
𝝀𝝀𝟐𝟐
𝑨𝑨 = �𝟒𝟒𝟒𝟒𝟐𝟐 � (7)

The equation (6) will become,

𝐬𝐬𝐬𝐬𝐬𝐬𝟐𝟐 𝜽𝜽 = 𝑨𝑨(𝒉𝒉𝟐𝟐 + 𝒌𝒌𝟐𝟐 + 𝒍𝒍𝟐𝟐 ) (8)
The possible values for (ℎ2 + 𝑘𝑘 2 + 𝑙𝑙 2 ) in a simple cubic system are (1,2,3,4,5,6,7,8,…). To
determine the required value of A, the observed values for each peak in the diffraction pattern
are divided by (2,3,4,5,6,...) to obtain the common quotient. Dividing the sin2 𝜃𝜃 by the value
of A, we can determine the 'hkl'. Additionally, by simply rearranging the equation (7), one can
find the lattice parameter as,
𝝀𝝀
𝒂𝒂 = 𝟐𝟐√𝑨𝑨 (9)

An analytical method can be used to index the diffraction pattern of any cubic crystal structure;
the results are identical to those from the prior method. We can fully index the diffraction
pattern of an unknown crystal structure using these two techniques, identify the Bravais lattices,
and find the lattice parameters. Also, the nature of cubic crystal structure (whether fcc or bcc)
can be identified.
The 'hkl' values for the cubicle system are checked first; if they do not satisfy, we evaluate the
values for the hexagonal system. The screenshot of the spreadsheet which uses the analytical
method to find Miller indices of cubic crystals is shown in figure-2 below. We plugged in the
values of 2𝜃𝜃 only and the whole table-is generated by clicking one time on the calculate button.
Here, we used XRD pattern of aluminum taken from page no. 102 of reference [10].

Figure-3 Screenshot of the spreadsheet using the analytical method to find Miller indices of
cubic crystals.

1.2 Hexagonal Structures

1.2.1 Direct Method for Hexagonal Structures

Many common materials, which include semiconductor crystalline polymers, and several
metals, have a hexagonal crystal structure. Suppose the diffraction pattern is expected to be the

7
hexagonal crystal system. In that case, the axial ratio 𝑐𝑐 ⁄𝑎𝑎 is given, and again the first step is to
calculate the sin2 𝜃𝜃 values for all the major diffracted lines. To find the Miller indices and
lattice parameters, the plane spacing equation for the hexagonal structure is.
𝟏𝟏 𝟒𝟒 𝒉𝒉𝟐𝟐 +𝒉𝒉𝒉𝒉+𝒌𝒌𝟐𝟐 𝒍𝒍𝟐𝟐
= 𝟑𝟑 � � + 𝒄𝒄𝟐𝟐 (10)
𝒅𝒅𝟐𝟐 𝒂𝒂𝟐𝟐

From Bragg’s law (𝜆𝜆 = 2𝑑𝑑 sin 𝜃𝜃) we have,

𝟏𝟏 𝟒𝟒 𝐬𝐬𝐬𝐬𝐬𝐬𝟐𝟐 𝜽𝜽
= (11)
𝒅𝒅𝟐𝟐 𝝀𝝀𝟐𝟐

Comparing the above two equations we have,

𝟏𝟏 𝟒𝟒 𝒉𝒉𝟐𝟐 +𝒉𝒉𝒉𝒉+𝒌𝒌𝟐𝟐 𝒍𝒍𝟐𝟐 𝟒𝟒 𝐬𝐬𝐬𝐬𝐬𝐬𝟐𝟐 𝜽𝜽
= 𝟑𝟑 � � + 𝒄𝒄𝟐𝟐 = (12)
𝒅𝒅𝟐𝟐 𝒂𝒂𝟐𝟐 𝝀𝝀𝟐𝟐

Rearranging the above equation gives,

𝝀𝝀𝟐𝟐 𝟒𝟒 𝒉𝒉𝟐𝟐 +𝒉𝒉𝒉𝒉+𝒌𝒌𝟐𝟐 𝒍𝒍𝟐𝟐
𝐬𝐬𝐬𝐬𝐬𝐬𝟐𝟐 𝜽𝜽 = �𝟑𝟑 � � + 𝒄𝒄𝟐𝟐 � (13)
𝟒𝟒 𝒂𝒂𝟐𝟐

As the axial ratio (𝑐𝑐⁄𝑎𝑎) for the hexagonal structures is usually given, the above equation after
modification can be expressed as,
𝝀𝝀𝟐𝟐 𝟒𝟒 𝒍𝒍𝟐𝟐
𝐬𝐬𝐬𝐬𝐬𝐬𝟐𝟐 𝛉𝛉 = 𝟒𝟒𝒂𝒂𝟐𝟐 �𝟑𝟑 (𝒉𝒉𝟐𝟐 + 𝒉𝒉𝒉𝒉 + 𝒌𝒌𝟐𝟐 ) + (𝒄𝒄⁄ 𝟐𝟐 � (14)
𝒂𝒂)

From equation (14), the sin2 𝜃𝜃 has two components; the first of which includes the ‘h’ and ‘k’
values and the second of which includes the ‘l’ values. The value of the two components

�3� (ℎ2 + ℎ𝑘𝑘 + 𝑘𝑘 2 ) and 𝑙𝑙 2 ⁄(𝑐𝑐⁄𝑎𝑎)2 for different sets of ‘h’, ‘k’ and ‘l’ are given in the Table-3
4

and Table-4 respectively, taken from pages 126-127 of [10].

Table-3: values of (4⁄3) (ℎ2 + ℎ𝑘𝑘 + 𝑘𝑘 2 )

h k 0 1 2 3
0 0.0000 1.3333 5.3333 12.0000
1 1.3333 4.0000 9.3333 17.3333
2 5.3333 9.3333 16.0000 25.3333
3 12.0000 17.3333 25.3333 36.0000

Table-4: values of 𝑙𝑙 2 ⁄(𝑐𝑐⁄𝑎𝑎)2 for zinc (𝑐𝑐⁄𝑎𝑎 = 1.8563)

𝑙𝑙 0 1 2 3 4 5 6
𝑙𝑙 2 0 2 4 9 16 25 36
𝑙𝑙 2 ⁄(𝑐𝑐⁄𝑎𝑎)2 0.0000 0.2902 1.1608 2.6118 4.6432 7.2550 10.4472

Now, we can add both the components from Table-3 and table-4 and arrange them in the
possible combinations for different ‘hkl’ values in increasing order for all the values. The

8
structure factor of hexagonal structures suggests that reflections are absent when the following
two conditions are satisfied. [10]
1. When h+2k = 3N (where N is an integer).
2. When l is odd.
All the remaining reflections will be present. The values of ‘a’ and ‘c’ in hexagonal structures
have different values, the indices ‘h’ and ‘k’ can be swapped, but the index ‘l’ cannot. In this
manner, the Miller indices for different values of ‘hkl’ can be found. The lattice parameters ‘a’
and ‘c’ can be calculated using equation (14).
The value of ‘a’ can be found using ‘hk0’ reflections. For this, substituting 𝑙𝑙 = 0 in equation
(14) we get,
𝝀𝝀𝟐𝟐 𝟒𝟒
𝐬𝐬𝐬𝐬𝐬𝐬𝟐𝟐 𝜽𝜽 = 𝟒𝟒𝒂𝒂𝟐𝟐 �𝟑𝟑 (𝒉𝒉𝟐𝟐 + 𝒉𝒉𝒉𝒉 + 𝒌𝒌𝟐𝟐 )� (15)

Rearranging equation (15) for ‘a’ gives,

𝝀𝝀𝟐𝟐
𝒂𝒂𝟐𝟐 = 𝟑𝟑 𝐬𝐬𝐬𝐬𝐬𝐬𝟐𝟐 𝜽𝜽 (𝒉𝒉𝟐𝟐 + 𝒉𝒉𝒉𝒉 + 𝒌𝒌𝟐𝟐 ) (16)

Taking square root on both sides to get ‘a’

𝝀𝝀
𝒂𝒂 = �(𝒉𝒉𝟐𝟐 + 𝒉𝒉𝒉𝒉 + 𝒌𝒌𝟐𝟐 ) (17)
√𝟑𝟑 𝐬𝐬𝐬𝐬𝐬𝐬𝟐𝟐 𝜽𝜽

To calculate the value of ‘c’ we use the reflections of ‘00l’ type. Using h=k=0 in the equation
(14) we have,
𝝀𝝀𝟐𝟐 𝒍𝒍𝟐𝟐
𝐬𝐬𝐬𝐬𝐬𝐬𝟐𝟐 𝜽𝜽 = 𝟒𝟒𝒂𝒂𝟐𝟐 �(𝒄𝒄⁄ 𝟐𝟐
� (18)
𝒂𝒂)

Rearranging equation (18) for ‘c’ gives,

𝝀𝝀𝟐𝟐
𝒄𝒄𝟐𝟐 = 𝟒𝟒 𝐬𝐬𝐬𝐬𝐬𝐬𝟐𝟐 𝜽𝜽 𝒍𝒍𝟐𝟐 (19)

Taking square root on both sides to get ‘c’

𝝀𝝀
𝒄𝒄 = 𝟒𝟒 𝐬𝐬𝐬𝐬𝐬𝐬 𝜽𝜽 𝒍𝒍 (20)

The values of ‘a’ and ‘c’ can be calculated in this way, and we can completely index the
diffraction pattern of the hexagonal crystal and find the Bravais lattice.
To check if the spreadsheets work properly, we used the XRD pattern of zinc taken from page
number 130 of [10]. Once again, we plugged in the 2𝜃𝜃 values for the XRD peaks of the zinc.
After clicking on the calculate button we got the lattice planes (hkl), and the lattice parameters
(a and c) as shown in the figure-3 below.

9
Figure-4 X-ray diffraction patter of zinc-taken from page number 130 of [10]

Figure-5 Screenshot of the spreadsheet using the direct method to find Miller indices of
hexagonal crystals.

10
1.2.2 Analytical Method for Hexagonal Structures

Another method is an analytical method used to find the crystal structure of the hexagonal
close-packed structure. An analytical method is effective and holds less error, and less time-
consuming than the earlier used method. The method we have used first can sometimes be
tricky in finding ‘hkl’ values to calculate sin2𝜃𝜃 values. Sometimes, the diffracted lines peak is
very weak, and it is impossible that you have missed a peak while seeing 2𝜃𝜃 values. This will
cause an error in all the ‘hkl’ values and give you the wrong answer for the lattice parameter.
Therefore, for this purpose, an alternate method which is an analytical method, is also used to
index the pattern of hexagonal structure. Since we again use equation (14),
𝝀𝝀𝟐𝟐 𝟒𝟒 𝒍𝒍𝟐𝟐
𝐬𝐬𝐬𝐬𝐬𝐬𝟐𝟐 𝜽𝜽 = 𝟒𝟒𝒂𝒂𝟐𝟐 �𝟑𝟑 (𝒉𝒉𝟐𝟐 + 𝒉𝒉𝒉𝒉 + 𝒌𝒌𝟐𝟐 ) + (𝒄𝒄⁄ 𝟐𝟐 � (21)
𝒂𝒂)

The above equation can be changed to,

𝐬𝐬𝐬𝐬𝐬𝐬𝟐𝟐 𝜽𝜽 = 𝑨𝑨(𝒉𝒉𝟐𝟐 + 𝒉𝒉𝒉𝒉 + 𝒌𝒌𝟐𝟐 ) + 𝑪𝑪𝑪𝑪𝟐𝟐 (22)
where, 𝐴𝐴 = 𝜆𝜆2⁄3𝑎𝑎2 , and 𝐶𝐶 = 𝜆𝜆2⁄4𝑐𝑐 2 (23)
We use hk0 (l=0) type reflections to find the value of A. Therefore, the permitted values of h
and k for (ℎ2 + ℎ𝑘𝑘 + 𝑘𝑘 2 ) are 1, 3, 4, 7, 9, 12, …. as shown in Table-3. In the first step we
divide all the sin2 𝜃𝜃 values by 1, 3, 4, 7, 9, 12, … and find the common quotient. Hence, the
least common recurring value will yield the value of A. Then, we use 𝐴𝐴 = 𝜆𝜆2⁄3𝑎𝑎2 to find ‘a’
for all the sin2 𝜃𝜃 values.
Then we use equation (22), after rearranging, to find the value of C,
𝑪𝑪𝑪𝑪𝟐𝟐 = 𝐬𝐬𝐬𝐬𝐬𝐬𝟐𝟐 𝜽𝜽 − 𝑨𝑨(𝒉𝒉𝟐𝟐 + 𝒉𝒉𝒉𝒉 + 𝒌𝒌𝟐𝟐 ) (24)
For a hexagonal crystal, ‘l’ can only have integer values 1,2,3,…, and for 𝑙𝑙 2 = 1,4,9, …, we can
find C with the help of 00l type reflection as already mentioned in the earlier section. To get
the value of C, we subtract (A,3A,4A,7A,…) from each sin2 𝜃𝜃 to get the remainder of the order
(1,4,9,…) which will be the 00l type reflections. Again, the common repeated value gives the
value of C, and the lattice parameters c can be found using the relation 𝐶𝐶 = 𝜆𝜆2⁄4𝑐𝑐 2 . Using the
proper combination of A and C, by choosing the ‘hkl’ values allowed by structure factor, we
indexed the entire pattern and used the equation (23) to find the value of a and c.
Again, to check if the spreadsheet works properly, we used the XRD pattern of zinc taken from
page number 130 of [10]. Plugging in the 2𝜃𝜃 values for the XRD peaks of the zinc, clicking on
the calculate button, we got the lattice planes (hkl), and the values of A and C as shown in the
figure-3 below.

11
Figure-6 Screenshot of the spreadsheet designed for the analytical method to find Miller indices
of hexagonal crystals.

2 RESULTS AND DISCUSSIONS

2.1 Cubic structures

Figure-2 and Figure-3 show the screenshots of the spreadsheets developed for the identification
of the cubic crystals using the ratios method and the analytical method, respectively. Our
spreadsheet also identifies the Bravais lattice, and gives the value of the lattice constant, as in
the case of aluminum, it is face-centered cubic having the lattice constant value equal to
0.40478 nm, which can be seen in the screenshots of Figure 2 and Figure 3. The values of hkl
found by both methods for the XRD pattern of aluminum are shown in Table-1 below.

Table 1: Miller indices for pure aluminum found by both the ratios method and the
analytical method.
Peak No. 2θ h2+k2+l2 h,k,l a (nm)
1 38.52 3 1,1,1 0.40447
2 44.76 4 2,0,0 0.40461
3 65.14 8 2,2,0 0.40471
4 78.26 11 3,1,1 0.40482
5 82.47 12 2,2,2 0.40481
6 99.11 16 4,0,0 0.40486
7 112.03 19 3,3,1 0.40492
8 116.60 20 4,2,0 0.40488
9 137.47 24 4,2,2 0.40493

2.2 Hexagonal structures

Figure-5 and Figure-6 show the screenshots of the spreadsheets developed for the identification
of the hexagonal crystals using the direct method and the analytical method, respectively. In

12
the direct method (Figure-5) you only need to provide the values of c/a, and λ, and the
spreadsheet will do the rest of the calculations and will give the values of a, c, and the Miller
indices. In the analytical method (Figure-6) you need to provide the values of c/a, either c or
a, and λ, and the spreadsheet will give the value of the other i.e., a or c, and the Miller indices.
The values of a and c for zinc are found to be 2.664 Å and 4.945 Å, respectively.

The values of hkl found by both methods for the XRD pattern of zinc are shown in Table-2
below.

Table 1: Miller indices for pure zinc found by both the methods.
Peak No. 2θ h,k,l
1 36.31 0,0,2
2 38.98 1,0,0
3 43.21 1,0,1
4 54.32 1,0,2
5 70.08 1,0,3
6 70.64 1,1,0
7 77.04 0,0,4
8 82.09 1,1,2
9 83.72 2,0,0
10 86.54 2,0,1
11 89.91 1,0,4
12 94.88 2,0,2
13 109.13 2,0,3
14 115.80 1,0,5
15 116.37 1,1,4
16 124.03 2,1,0
17 127.47 2,1,1
18 131.86 2,0,4
19 138.56 0,0,6
20 139.14 2,1,2

3 CONCLUSION

In this study, we have developed a user-friendly macro-enabled Microsoft Excel spreadsheet

for the analysis of XRD patterns. The spreadsheet allows for the determination of Miller indices
and lattice parameters of cubic and hexagonal crystal structures. The accuracy of the
spreadsheet was demonstrated by comparing the calculated values with literature values for
various samples. The spreadsheet provides a simple and efficient tool for researchers and
students to analyze XRD patterns and gain insights into the crystal structure of materials.

3.1 Future work

There are some other modules given in [10] which can also be programmed in the same way
as we did for the identification of the cubic and hexagonal structures in the current work. In

13
future work, the spreadsheet can be further improved by incorporating additional
functionalities such as peak fitting, background subtraction, and phase identification.
Additionally, the spreadsheet can be extended to analyze XRD patterns of other crystal systems
such as tetragonal, orthorhombic, and monoclinic structures.

4 ACKNOWLEDGMENTS
"We would like to express our sincere gratitude to Professor Dr. Zaheeruddin for his invaluable
guidance, support, and encouragement throughout the course of this research. His expertise and
insights enriched our understanding and significantly contributed to the success of this project.
He was always available to help, answer our queries, and offer constructive feedback,
demonstrating unwavering dedication and commitment. We are truly grateful for his
mentorship and unwavering support, which played a pivotal role in the completion of this
work."

REFERENCES

1
Villarreal, M. E., Esteley, C. B., & Smith, S. (2018). Pre-service teachers’ experiences within
modelling scenarios enriched by digital technologies. ZDM, 50, 327-341.
2
Sarkar, A., Gordon, A. D., Jones, S. P., & Toronto, N. (2018, October). Calculation view:
multiple-representation editing in spreadsheets. In 2018 IEEE Symposium on Visual
Languages and Human-Centric Computing (VL/HCC) (pp. 85-93). IEEE.
3
Christodoulakis, C., Munson, E. B., Gabel, M., Brown, A. D., & Miller, R. J. (2020). Pytheas:
pattern-based table discovery in CSV files. Proceedings of the VLDB Endowment, 13(12),
2075-2089.
4
Rodríguez-Carvajal, J. (2001). FullProf. CEA/Saclay, France.
5
Lutterotti, L., Matthies, S., Wenk, H. R., Schultz, A. S., & Richardson Jr, J. W. (1997).
Combined texture and structure analysis of deformed limestone from time-of-flight neutron
diffraction spectra. Journal of Applied Physics, 81(2), 594-600.
6
Wu, Z., Bukowski, B. C., Li, Z., Milligan, C., Zhou, L., Ma, T., ... & Miller, J. T. (2018).
Changes in catalytic and adsorptive properties of 2 nm Pt3Mn nanoparticles by subsurface
atoms. Journal of the American Chemical Society, 140(44), 14870-14877.
7
Chen, W., Liu, D., & Li, L. (2019). Multiscale characterization of semicrystalline polymeric
materials by synchrotron radiation X‐ray and neutron scattering. Polymer Crystallization, 2(2),
10043.

14
8
Frías, M., Vigil de la Villa, R., Martínez-Ramírez, S., Fernández-Carrasco, L., Villar-Cociña,
E., & García-Giménez, R. (2020). Multi-Technique characterization of a fine fraction of CDW
and assessment of reactivity in a CDW/Lime system. Minerals, 10(7), 590.
9
Elahi, Z., Gull, W., & Uddin, Z. (2023). Weibull Distribution Parameter Estimation Using
Spreadsheets. Spreadsheets in Education, 1-12.
10
Suryanarayana, C., Norton, M. G., Suryanarayana, C., & Norton, M. G. (1998). X-rays and
Diffraction. Springer US.

15