# How to Make Periodic Table in Excel (2 Easy Ways)

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.

## 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 118.
• Label the rows with the period numbers from 17.
• 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 = 4``````

This 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.

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.

Get FREE Advanced Excel Exercises with Solutions!
Md. Abu Sina Ibne Albaruni

Md. Abu Sina Ibne Albaruni holds a BSc in Mechanical Engineering from Bangladesh University of Engineering and Technology. He is a dedicated employee of the ExcelDemy project. He has written quite a large number of articles for ExcelDemy. Besides, he has expertise in VBA. He efficiently automates Excel issues using VBA macros and actively engages in the ExcelDemy forum, offering valuable solutions for user interface challenges. His areas of interest in work and study span MATLAB, Machine Learning,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF