Mynda's Excel Dashboard Webinar

The Excel Addict - Help with Excel 2013, 2010, 2007, 2003
May 12, 2016
 

Greetings from The Excel Addict
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'.

Data With Repeating Values Blank in Microsoft Excel 2007 2010 2013 2016 365

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)
Email:  fhayes[AT]TheExcelAddict.com


Francis Hayes (TheExcelAddict.com)


If you missed my last newsletter, you can click here to view it online.
 
TheExcelAddict.com Quote of the Day

"Fall seven times, stand up eight."
-- Japanese proverb --

If you have a favourite quote, send it to me and I may post it in my newsletter.


Today's Microsoft Excel Tip

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.

Data With Repeating Values Blank in Microsoft Excel 2007 2010 2013 2016 365
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…

FOLLOW ALONG USING THIS PRACTICE FILE

1) Select all of the data, both blank and non-blank cells, in a single range (e.g. B4:E24);

Select Range To Fill With Repeating Values in Microsoft Excel 2007 2010 2013 2016 365
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;

F5 Key Goto Special Blank Cells in Microsoft Excel 2007 2010 2013 2016 365

3) Now, only the blank cells are selected;

Only Blank Cells Selected in Microsoft Excel 2007 2010 2013 2016 365

4) Press the equals sign (=) key, then press the 'up arrow' key once to reference the cell above the active cell (e.g. =B4);

Press Equals And Up Keys in Microsoft Excel 2007 2010 2013 2016 365

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;

Formula To Fill Blank Cells With Values From Cells Above in Microsoft Excel 2007 2010 2013 2016 365
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.

Right Drag Click Convert Formulas To Values in Microsoft Excel 2007 2010 2013 2016 365

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...
DOWNLOAD PRACTICE FILE HERE

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.

Sub FillBlankCells()
' Fill Blank Cells with the Data From Above
'
' Keyboard Shortcut: Ctrl+m
'
    On Error GoTo finish
    With Selection
        .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
        .Copy
        .PasteSpecial Paste:=xlPasteValues
    End With
    Application.CutCopyMode = False
finish:
End Sub




Thanks for supporting this newsletter and website

Mynda's Excel Dashboard Webinar

Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 10 years as someone who provides exceptional value to my readers. So I'm not willing to risk that. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are EVER not completely satisfied with anything I recommend, please let me know and you will get your money...GUARANTEED. You can't lose.
"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