Fill Gaps Based on Cells Above
What will you do, if you have an output from a Pivot table and you need to use it as a source for another pivot table? Or you simply have gaps in the table that you need to fill with information above (or some other side). Not knowing the right approach might leave you the only option to change the cells manually – which would be a terrible waste of time for larger tables.
As an example – the left table below is not suitable to use in data analysis – you can’t even filter rows by ‘Category’ if you would like to:
 |
Convert to >> |  |
Fortunatelly, there is an elegant time-saving solution to do this automatically. The logic behind the solution is that you can use the Excel command to select only empty cells and then change them all at once.
The steps are the following:
- Choose the area (column) you need to fill – the first column in our example
- Press Ctrl+G or choose from the menu Edit > Go To…
- Select ‘Blanks’ and click OK. The empty cells of the first column should now be selected.
- Write formula – type equation sign (=) and press UP arrow. By doing this, every selected cell gets the value directly above.
- Press Ctrl+Enter. Holding Ctrl tells Excel to change all the active cells at once.
- One more thing – you’ll have to convert the formulas to static values to quarantee that the values will stay the same even after sorting and other transformations. Use ‘Copy’ and Paste Special -> Values. Now you’re done.
Thanks for sharing this. How about if the data on category is A and all the rows are filled out but on subcategory only A1 is known and A2-A5 are blanks.. Do you have any suggestion on how these blanks can be automatically filled out with a formula or VB code? will appreciate any help. thanks
p.s i forgot to mention that as with the above example the values in subcategory should increment by 1 for every next blank cell until the next set of data (B1)…
Sorry, for a late reply, I was away for a while.
One solution is to create a new column where you have numbers which increment by one with each new row.
Then you can enter a formula: =[reference to the main category cell on the left] & [incremented value]
This will always create a subcategory by adding the index value number to the main category identifier.
AMAZING FIND … saves me from the headache of copy pasting individual data … thanks a lot …
@Abel totally agree! Thanks for the great post! This is a huge help!