Contents
After filling an Excel table with certain values (most often when adding an array of information), very often there are empty spaces. They will not interfere with the consideration of the working file itself, however, they will complicate the functions of sorting, calculating data, filtering certain numbers, formulas, and functions. In order for the program to work without difficulty, it is necessary to learn how to fill in the voids with values from neighboring cells.
How to highlight empty cells in a worksheet
Before you start considering how to fill empty cells in an Excel worksheet, you need to learn how to select them. This is only easy to do if the table is small. However, if the document includes a huge number of cells, empty spaces can be located in arbitrary places. Manual selection of individual cells will take a long time, while some empty spaces can be skipped. To save time, it is recommended to automate this process through the built-in tools of the program:
- First of all, you need to mark all the cells of the worksheet. To do this, you can use only the mouse or add the SHIFT, CTRL keys for selection.
- After that, press the key combination on the keyboard CTRL + G (another way is F5).
- A small window called Go To should appear on the screen.
- Click on the “Select” button.
In order to mark cells in the table, on the main toolbar, you need to find the “Find and Select” function. After that, a context menu will appear, from which you need to select the selection of certain values - formulas, cells, constants, notes, free cells. Select the function “Select a group of cells. Next, a settings window will open in which you need to check the box next to the “Empty cells” parameter. To save the settings, you need to click the “OK” button.
How to fill empty cells manually
The easiest way to fill empty cells in a worksheet with values from the top cells is through the “Fill empty cells” function, which is located on the XLTools panel. Procedure:
- Press the button to activate the “Fill empty cells” function.
- A settings window should open. After that, it is necessary to mark the range of cells among which it is necessary to fill in the empty spaces.
- Decide on the filling method – from the available options you need to choose: left, right, up, down.
- Check the box next to “Unmerge Cells”.
It remains to press the “OK” button so that the empty cells are filled with the required information.
Important! One of the useful features of this function is the saving of set values. Thanks to this, it will be possible to repeat the action with the next range of cells without reconfiguring the function.
Available values for filling empty cells
There are several options for filling empty cells in an Excel worksheet:
- Fill to the left. After activating this function, empty cells will be filled with data from the cells on the right.
- Fill to the right. After clicking on this value, empty cells will be filled with information from the cells on the left.
- Fill up. The cells on top will be filled with data from the cells on the bottom.
- Filling down. The most popular option for filling empty cells. Information from the cells above is transferred to the cells of the table below.
The “Fill empty cells” function exactly copies those values (numerical, alphabetic) that are located in the filled cells. However, there are some features here:
- Even when hiding or blocking a filled cell, information from it will be transferred to a free cell after activating this function.
- Very often situations happen that the value for transfer is a function, a formula, a link to other cells in the worksheet. In this case, the empty cell will be filled with the selected value without changing it.
Important! Before activating the “Fill empty cells” function, you need to go to the worksheet settings, see if there is protection. If it is enabled, the information will not be transferred.
Filling empty cells with a formula
An easier and faster way to fill cells in a data table from neighboring cells is through the use of a special formula. Procedure:
- Mark all empty cells in the way described above.
- Select a line for entering LMB formulas or press the F button
- Enter the symbol “=”.
- After that, select the cell located above. The formula should indicate the cell from which the information will be copied to a free cell.
The last action is to press the key combination “CTRL + Enter” so that the formula works for all free cells.
Important! We must not forget that after applying this method, all previously free cells will be filled with formulas. In order to preserve the order in the table, it is recommended to replace them with numerical values.
Filling empty cells with a macro
In the event that you have to regularly fill in empty cells in worksheets, it is recommended to add a macro to the program, use it later to automate the process of selecting, filling in empty cells. Fill code for macro:
Sub Fill_Blanks()
For Each cell In Selection
If IsEmpty(cell) Then cell.Value = cell.Offset(-1, 0).Value
Next cell
End Sub
To add a macro, you need to perform several actions:
- Press the key combination ALT+F
- This will open the VBA editor. Paste the above code into a free window.
It remains to close the settings window, display the macro icon in the quick access panel.
Conclusion
Among the methods described above, you need to choose the one that suits best for a particular situation. The manual method of adding data to the free places of the worksheet is suitable for general familiarization, one-time use. In the future, it is recommended to master the formula or register a macro (if the same procedure is performed very often).