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.

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.

• Insert the formulas based on the IFERROR & VLOOKUP functions.

• Formula in cell K4:

• Formula in cell K5:

• Formula in cell K8:

• Formula in cell K9:

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

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

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.

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

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

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.

If Selection.Cells.Count > 1 Then
        MsgBox "Please select only one cell from the Periodic Table"
        Exit Sub
    End If

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 Atom = "" Or IsNumeric(Atom) Then
            WS.Range("S4:S13") = ""
            WS.Range("S4:S13").Interior.ColorIndex = 2
        End If
    Next i

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.