Md. Abu Sina Ibne Albaruni
In this article, we have explored 2 easy methods of how to make periodic table in Excel. The first one is manual. It involves various functions and features of Excel. The second method involves VBA codes to create an interactive periodic table.
The periodic table helps to understand the properties, characteristics, and reaction capabilities of all the elements found in nature. It is the foundation block for anyone who wants to study chemistry.
Download Practice Workbook
You can download this practice workbook while going through the article.
What is a Periodic Table?
The periodic table is one of the most comprehensive and important tools in chemistry. This table comprises all known chemical elements in a systematic and organized manner. This is a table that represents the fundamental properties of all the elements found in nature. These elements are classified based on their atomic number.
The periodic table consists of rows that are called Periods and columns that are known as Groups or Families. The modern periodic table has 7 periods and 18 groups.
The modern periodic table is a work of Dmitri Mendeleev, a Russian chemist. Mendeleev organized the elements based on their properties leaving gaps for yet to be discovered elements. He successfully predicted the existence and properties of multiple unknown elements, which were discovered later.
The periodic table allows users to understand the relationship between elements, anticipate the properties of unknown elements, and understand the bonding pattern of different elements. It basically serves as the foundation block to study chemistry.
2 Ways to Make Periodic Table in Excel
In this guide, we will show you two different ways to make a periodic table in Excel. The first method is to make a periodic table manually. The second method is to make an interactive periodic table.
1. Manual Periodic Table
In this section, we will make a periodic table manually by using different Excel formulas and features.
1.1. Insert Data in Properties Table
Each element of a periodic table has a unique set of properties. These properties refer to various characteristics, trends, and relationships shown by the elements of the periodic table. The key properties are as follows.
Symbol: Each element has its own symbol. It is a one or two letter abbreviation that is used to represent the chemical element.
Atomic Number: Atomic number basically refers to the number of protons in an element. This is a unique property of any element.
Atomic Mass: Atomic mass refers to the weight or mass of an atom of a specific element. It includes the mass of the protons, neutrons, and electrons in the atom. We measure it in Atomic Mass Unit (amu).
Element Type: Element type refers to the classification of elements into different categories based on their properties and characteristics. These categories help to understand the general behavior of different elements.
For this work, we have collected the properties of 118 elements in a tabular form. This property table is essential to create a periodic table.
1.2. Create Periodic Table Format
Now, you need to create a periodic table format in Excel.
- Label the columns with the group numbers from 1–18.
- Label the rows with the period numbers from 1–7.
- Enter the symbol of each element in appropriate cells depending on their group number and period number.
- You should adjust the formatting of the table as needed. You can adjust the row height and column width. You may change the font size, colors, and cell borders to make the table more visually appealing and easier to understand.
1.3. Automate Periodic Table with Excel Formulas
We will automate the periodic table with different Excel formulas and features.
- Put any element name in cell D5.
- Merge & Center to range D5:E5.
- Now sequentially, Merge & Center to range K4:M4, put font size 16.
- Merge & Center to range K5:M7, put font size 48.
- Merge & Center to range K8:M8, put font size 20.
- Merge & Center to range K9:M9, put font size 16.
- Formula in cell K4:
=IFERROR(VLOOKUP($D$5, Properties!$B$5:$F$123,2,FALSE), "")- Formula in cell K5:
=IFERROR(VLOOKUP($D$5, Properties!$B$5:$F$123,1,FALSE), "")- Formula in cell K8:
=IFERROR(VLOOKUP($D$5, Properties!$B$5:$F$123,3,FALSE), "")- Formula in cell K9:
=IFERROR(VLOOKUP($D$5, Properties!$B$5:$F$123,4,FALSE), "")
Formula Breakdown
- VLOOKUP($D$5, Properties!$B$5:$F$123,2,FALSE)
The VLOOKUP function looks for the value of cell D5 in range B5:F123 from the Properties worksheet. If the value matches, it returns value from the second column (2) of the specified range. Here, False denotes the exact match.
Result: 1
- IFERROR(VLOOKUP($D$5, Properties!$B$5:$F$123,2,FALSE), “”)
The IFERROR function returns the value of the VLOOKUP function. If there is an error, it returns an empty string.
Result: 1
- You will see the Atomic Number in cell K4, Element Symbol in cell K5, Element Name in cell K8, and Atomic Mass in cell K9.
- When you change the element symbol in cell D5, you will see the properties of that element in range K4:K9.
2. Interactive Periodic Table
Let’s talk about the interactive periodic table now. In this periodic table, the user does not need to type anything. If the user selects a cell from the periodic table, he will see the properties of the element of that particular cell in the worksheet. Moreover, the manual periodic table did not indicate the element type. The interactive periodic table will include the element type for each element of the periodic table.
2.1. Create Interactive Table Format
- Create a periodic table format just like the previous method.
- There are total 10 types of elements recorded in the property table of the Properties worksheet. Add the element types in range D5:D14.
2.2. Assign Colors for Different Element Types
Now we will assign respective colors to range E5:E14 based on elements types using VBA code.
- First, you have to launch the VBA Macro Editor from your workbook. Follow this article: How to Write VBA Code in Excel.
- Paste the following code in your VBA Macro Editor.
- Press the Run button or F5 key to run the code.
Sub Property_Color()
'variable declaration
Dim myRng As Range
'set variable
Set myRng = ActiveSheet.Range("F5:F14")
'set different cell colors
myRng.Cells(1).Interior.ColorIndex = 10
myRng.Cells(2).Interior.ColorIndex = 24
myRng.Cells(3).Interior.ColorIndex = 8
myRng.Cells(4).Interior.ColorIndex = 27
myRng.Cells(5).Interior.ColorIndex = 17
myRng.Cells(6).Interior.ColorIndex = 14
myRng.Cells(7).Interior.ColorIndex = 15
myRng.Cells(8).Interior.ColorIndex = 22
myRng.Cells(9).Interior.ColorIndex = 36
myRng.Cells(10).Interior.ColorIndex = 4
End Sub- You will see different colors assigned for different types of elements in range F5:F14.
VBA Breakdown
myRng.Cells(1).Interior.ColorIndex = 10
myRng.Cells(2).Interior.ColorIndex = 24
myRng.Cells(3).Interior.ColorIndex = 8
myRng.Cells(4).Interior.ColorIndex = 27
myRng.Cells(5).Interior.ColorIndex = 17
myRng.Cells(6).Interior.ColorIndex = 14
myRng.Cells(7).Interior.ColorIndex = 15
myRng.Cells(8).Interior.ColorIndex = 22
myRng.Cells(9).Interior.ColorIndex = 36
myRng.Cells(10).Interior.ColorIndex = 4This portion of code takes each cell of the myRng range and assigns different colors to all of the cells in the specified range.
2.3. Assign Colors to Periodic Table Based on Element Type
- Paste the following code into a new module.
- Press the Run button or F5 key to run the code.
Sub Periodic_Table()
'variable declaration
Dim PropertyRng As Range
Dim ElementRng As Range
Dim TableRng As Range
Dim Property As String
Dim ColIndex As Integer
'set variables
Set PropertyRng = Sheets("Properties").Range("B5:F122")
Set ElementRng = ActiveSheet.Range("D5:F14")
Set TableRng = ActiveSheet.Range("D18:U27")
'loop through each cell of property table
For i = 1 To TableRng.Cells.Count
Property = "No Property"
'look for the element property
For j = 1 To PropertyRng.Rows.Count
If TableRng.Cells(i) = PropertyRng.Cells(j, 1) Then
Property = PropertyRng.Cells(j, 5)
End If
Next j
'change cell color according to element property
For k = 1 To ElementRng.Rows.Count
If Property = ElementRng.Cells(k, 1) Then
ColIndex = ElementRng.Cells(k, 3).Interior.ColorIndex
TableRng.Cells(i).Interior.ColorIndex = ColIndex
End If
Next k
Next i
End Sub- You will see the elements of the periodic table getting colored according to the assigned color based on their element types.
VBA Breakdown
For i = 1 To TableRng.Cells.Count
Property = "No Property"
For j = 1 To PropertyRng.Rows.Count
If TableRng.Cells(i) = PropertyRng.Cells(j, 1) Then
Property = PropertyRng.Cells(j, 5)
End If
Next j- In this portion of the code, there are two nested loops that iterate through TableRng and PropertyRng ranges. For each loop in TableRng range, a variable named Property is initiated with a value No Property. The inner loop then checks if the value of the current cell in the TableRng range matches any value in the first column of the PropertyRng range. If a match is found, the Property variable is updated with the corresponding value from the fifth column of the PropertyRng range. The inner loop continues to search for matches, updating Property variable with the value of the last match found. Once the inner loop completes, the outer loop moves to the next cell in the TableRng range and repeats the process. So, the Property variable holds the value of the property associated with the last matching entry in the PropertyRng range for each cell in the TableRng range, or No Property if no matches were found.
For k = 1 To ElementRng.Rows.Count
If Property = ElementRng.Cells(k, 1) Then
ColIndex = ElementRng.Cells(k, 3).Interior.ColorIndex
TableRng.Cells(i).Interior.ColorIndex = ColIndex
End If
Next k
Next i- In this portion of the VBA code, two nested loops are used to match the property associated with each cell in the TableRng range with the element properties in the ElementRng range. If a match is found, the corresponding cell in the TableRng range is colored based on the color index (ColIndex) retrieved from the ElementRng range. This process keeps repeating for all cells in the TableRng range, resulting in colored cells based on their associated element properties.
2.4. Make Interactive Periodic Table
- Paste the following code in the worksheet module of Interactive Periodic Table worksheet.
- Save the code.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
'variable declaration
Dim Atom As Variant
Dim PropertyRng As Range
Dim WS As Worksheet
'set variables
Atom = Selection.Value
Set PropertyRng = Sheets("Properties").Range("B5:F122")
Set WS = ActiveSheet
'ensure only one cell is selected
If Selection.Cells.Count > 1 Then
MsgBox "Please select only one cell from the Periodic Table"
Exit Sub
End If
'look for the properties of the atom
For i = 1 To PropertyRng.Rows.Count
If Atom = PropertyRng.Cells(i, 1) Then
WS.Range("S4") = PropertyRng.Cells(i, 2)
WS.Range("S6") = Atom
WS.Range("S11") = PropertyRng.Cells(i, 3)
WS.Range("S13") = PropertyRng.Cells(i, 4)
WS.Range("S4:S13").Interior.ColorIndex = Selection.Interior.ColorIndex
End If
'if no property is found
If Atom = "" Or IsNumeric(Atom) Then
WS.Range("S4:S13") = ""
WS.Range("S4:S13").Interior.ColorIndex = 2
End If
Next i
End Sub- Now, whenever you click a cell inside the periodic table in the Interactive Periodic Table worksheet, you will see the property of the element of that cell. But if you select multiple cells, a warning dialog-box will appear.
VBA Breakdown
If Selection.Cells.Count > 1 Then
MsgBox "Please select only one cell from the Periodic Table"
Exit Sub
End If- This portion of the VBA code checks the number of currently selected cells. If the number of selected cells is greater than 1, It displays a warning message in a MsgBox to alert the user. Then, the code exits the subroutine using the Exit Sub command, preventing the rest of the code from executing.
For i = 1 To PropertyRng.Rows.Count
If Atom = PropertyRng.Cells(i, 1) Then
WS.Range("S4") = PropertyRng.Cells(i, 2)
WS.Range("S6") = Atom
WS.Range("S11") = PropertyRng.Cells(i, 3)
WS.Range("S13") = PropertyRng.Cells(i, 4)
WS.Range("S4:S13").Interior.ColorIndex = Selection.Interior.ColorIndex
End If- This portion of the VBA code iterates through each row of the PropertyRng range to find a match between the value of the selected cell (Atom) and the values in the first column of each row. When a match is found, some properties of the PropertyRng range are displayed in the worksheet. These properties include data from the second, third and fourth columns of the matched row which are placed within range S4:S13. Moreover, the background color of range S4:S13 is set to match the background color of the selected cell.
If Atom = "" Or IsNumeric(Atom) Then
WS.Range("S4:S13") = ""
WS.Range("S4:S13").Interior.ColorIndex = 2
End If
Next i- When the selected cell is either empty or it contains a numeric value, range S4:S13 is cleared. The background color of the range becomes white.
Things to Remember
There are a few things to remember while making a periodic table in Excel.
- Be careful with the source range of the VLOOKUP function.
- Use different colors for each element property.
- Make sure that the properties in the property table are accurate.
Frequently Asked Questions
1. What are the representative elements in a periodic table?
Representative elements are the elements in groups 1, 2, and 13 to 18 of the periodic table. They display a wide range of chemical properties and participate in numerous chemical reactions.
2. What is the significance of noble gas?
We refer to each element of group 18 as a noble gas. Noble gas is chemically inert. It does not take part in chemical reactions due to its stable electron configurations.
3. What are isotopes?
Isotopes have the same number of protons in their center, but they can have a different number of neutrons. This makes them a bit heavier or lighter versions of the same element.
Conclusion
In this article, we have demonstrated 2 useful methods of how to make periodic table in Excel. If you have any questions regarding this essay, don’t hesitate to let us know in the comments. Also, if you want to see more Excel content like this, please visit our website, and unlock a great resource for Excel-related content.
