Using blanks instead of repeating values is a common approach to express the hierarchy between columns. You may recognize the structure from Pivot Tables from older Excel versions. Although you can use the formulas to populate these empty cells, it may become time-consuming as the data size grows. In this guide, we’re going to show you how to replace blank cells in an Excel Table.
Steps to fill blank cells in an Excel Table
- First step is to locate and select the empty cells at once. Select your entire data set. Since it is a tabular content, you can press Ctrl + A select all. Excel will detect and select the entire table.
- Open the Go To Special dialog by following Home > Find & Select > Go To Special in the Ribbon.
- Select the Blanks option.
- Click OK to apply your selection.
- Once you click the OK button, you will see the blank cells are selected correctly. Do not click elsewhere. Otherwise, you will lose the selection. You will see that one of the blank cells at the top row is NOT shaded like the others. It's the cell C6 in our case. You can confirm it from the reference box as well.
- While the blank cells are still selected type a formula referencing the above cell of the selected cell. Since the cell C6 is selected in our case, our formula will refer the cell C5.
=C5
- Make sure your reference is relative (without $ signs). Once you type the formula press Ctrl + Enter key combination rather than regular Enter. This combination populates the formula in each selected cell. Since the reference is a relative reference Excel updates it based on each cell. Thus, each formula will target the above cell only.
- It looks done. However, there is one more action left to make replacing blank cells in an Excel Table proper. Make sure to convert the cells with formula to constant values to prevent them updated involuntarily. To make values constant, select all data again.
- Copy them either by Ctrl + C or context menu.
- Then perform Paste Special by using the icon in the context menu.
