Excel Tips & Tricks: Using CTRL + G to fill in PivotTable Export blanks

Our love affair with PivotTables began soon after entering the consulting ranks following college. For people who spent about 85% of their time (in this case “time” equaling about 60 hours per week) cleaning, manipulating, and analyzing data – we were spending more time with our PivotTables than our friends and family.

As people who needed to manipulate data outside of the PivotTable itself, a major problem early on in our relationship with Excel was the PivotTables’ insistence on exporting blank fields whenever repeated values occurred after we copied and pasted the data into a new worksheet.

To get around this issue we spent hours copying and pasting values to fill the empty cells – but surely there had to be a better way – and that was CTRL + G – the shortcut that allows you to populate blank cells in less than 30 seconds by following a few simple steps.

First, after copying and pasting your pivot data into a new worksheet, select the most upper-left empty cell and type a simple = formula to have the cell equal the cell directly above it. In this case in cell A17 we would type =A16. Then select the newly-populated A17 and hit Copy or CTRL + C. After that use your cursor to highlight all columns with blank cells you need to populate, which in this example is just two columns (Customer and Market). Then with the columns highlighted type CTL + G and the Go To box will appear.



Since we want only want to paste our formula that takes the above cell in cells that are blank, we now select Special, then Blanks, then hit OK. Once this is complete you will notice all blank cells will be shaded awaiting your command. Simply type CTRL + V to paste the formula that we already to copied and all previously-blank cells will now feature the name directly above them – solving our blank fields problem and allowing us to move forward playing with the data. Before moving forward one last step we recommend is to copy and paste special all names as values to prevent data from scattering as you sort the worksheet moving forward.

If you’re stuck with an Excel problem or have a tip you’d like to share feel free to contact us anytime.