Hi fellow Excel Addict,
In today's 'Excel in Minutes' tutorial, I'm repeating a tip I shared in this newsletter a couple of years ago. It's something that I get asked about quite often...'How to Fill Blank Cells With The Values From Above'.
If you missed my 'Excel in Seconds' newsletter on Tuesday, I showed you how to 'Show Individual Cell Values for a Range in a Formula'. You can read all about that here.
Wishing you another great day of Excelling,
Francis Hayes (The Excel Addict)
If you missed my last newsletter, you can click here to view it online.
If you have a favourite quote, send it to me and I may post it in my newsletter.
How to Fill Blank Cells With The Values From Above
When you bring data into Excel from another program (or copy data from a Pivot Table), often some of the cells are left blank, indicating a repeating value from above. This may make the information easier to read but it also means that you cannot use many of Excel's best features, such as filtering, subtotaling and Pivot Tables, to manipulate the data.
If you have a small amount of data, you can simply fill down (CTRL+D) or Copy and Paste the values for each blank section. However, when you are dealing with hundreds or even thousands of records, those methods would not be practical (unless you have nothing better to do and you really enjoy doing boring, monotonous work)
I'd like to show you a 'fairly simple' process to help you complete this same task in less than a minute, regardless of how many records you are dealing with.
In this example we are going to fill in all the blank cells for columns B to E…
1) Select all of the data, both blank and non-blank cells, in a single range (e.g. B4:E24);
2) Press the F5 key on your keyboard and click the Special... button in the Goto dialog. Then select the Blanks option and click OK;
3) Now, only the blank cells are selected;
4) Press the equals sign (=) key, then press the 'up arrow' key once to reference the cell above the active cell (e.g. =B4);
5) Hold down the CTRL key and press Enter. This fills each of the blank cells with a formula that references the cell above itself;
6) Next, re-select the entire data range again and move your mouse to the edge of the selected range until the mouse pointer changes to a 4-headed arrow (see image below);
7) Pressing the right mouse button, drag the cells a little to the right, BUT DON'T RELEASE THE MOUSE BUTTON. Still holding down the mouse button, drag back to the original location and release the button (see image below).
8) Click the 'Copy Here as Values Only' option from the menu that pops up to replace the formulas with fixed values.
Now all of the previously empty cells are filled with the values from above and your data is ready for sorting, filtering, subtotaling, etc...;
Open this practice file and try it for yourself now...
VBA (Macro) Option
If you want a VBA (macro) option for that will allow you to "Fill Blank Cells With The Values From Above" in 2 seconds, copy and paste this macro to your Personal Macro Workbook and you can do the whole shebang in 2 to 3 seconds.
' Fill Blank Cells with the Data From Above
' Keyboard Shortcut: Ctrl+m
On Error GoTo finish
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
Application.CutCopyMode = False
|"Spreadsheets Tips From An
Excel Addict" is a weekly publication of TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630