As you know, for full-fledged work with data (filtering, sorting, summing up, etc.) you need a continuous list, i.e. table without breaks (blank lines and cells – if possible). In practice, we often have just tables with missing empty cells – for example, after copying the results of pivot tables or exports to Excel from external programs. Thus, it becomes necessary to fill the empty cells of the table with values from the upper cells, that is …
| of |  | do |  |
In general, it may be necessary to do such filling not only down, but also up, to the right, etc. Let’s look at a few ways to implement this.
Method 1. Without macros
Select the range of cells in the first column to be filled in (in our example, this is A1:A12).
Press the key F5 and then press Highlight (Special) and in the window that appears, select Highlight empty cells(Blanks):
Without removing the selection, enter the equal sign in the first cell and click on the previous cell or press the up arrow (i.e. create a link to the previous cell, in other words):
And finally, to enter this formula at once into all selected (empty) cells, press Ctrl + Enter instead of the usual Enter. And that’s all! Simple and beautiful.
As a final stroke, I would advise you to replace all the created formulas with values, because when sorting or adding / deleting rows, the correctness of the formulas may be violated. Select all cells in the first column, copy and then paste back with special insert (Paste Special) in the context menu by selecting the option The values (Values). So it will be very good.
Method 2. Filling empty cells with a macro
If you have to do such an operation often, then it makes sense to make a separate macro for it, so as not to repeat the entire chain of actions listed above manually. For this we press Alt+F11 or button Visual Basic tab developer (Developer)to open the VBA editor, then insert a new empty module there via the menu Insert – Module and copy or enter the following short code there:
Sub Fill_Blanks() For Each cell In Selection If IsEmpty(cell) Then cell.Value = cell.Offset(-1, 0).Value Next cell End Sub
As you can easily imagine, this macro loops through all the selected cells and, if they are not empty, fills them with the values from the previous cell.
For convenience, you can assign a keyboard shortcut to this macro or even place it in the Personal Macro Workbook so that this macro is available when working in any of your Excel files.
Method 3. Power Query
Power Query is a very powerful free Excel add-in from Microsoft that can do almost anything with data, including our task of filling empty cells in a table. This method has two main advantages:
- If there is a lot of data, then the manual method with formulas or macros can noticeably slow down. Power Query will make everything much faster.
- If the source data changes, it will be enough just to update the Power Query query. In the case of using the first two methods, do everything again.
To load our range with data in Power Query, it needs to either be given a name (via the Formulas – Name Manager), or turn it into a “smart” table with the command Home – Format as a table (Home — Format as Table) or keyboard shortcut Ctrl+T:
After that, on the tab Data (Date) press the button From table/range (From Table/Range). If you have Excel 2010-2013 and Power Query installed as a separate add-in, then the tab will be named accordingly, Power Query.
In the query editor that opens, select a column (or several columns by holding Ctrl) and on the tab Transformation choose a team Fill – Fill Down (Transform — Fill — Fill Down):
That’s all 🙂 It remains to upload the finished table back to the Excel sheet with the command Home — Close and Load — Close and Load in… (Home — Close&Load — Close&Load to…)
In the future, when changing the source table, you can simply update the query with the right mouse button or on the tab Data – Refresh All (Data — Refresh All).
- Combining two columns with data
- Quickly select all cells with formulas or constants
- Quickly fill empty cells with a macro from the PLEX add-on