Repeat fill cells

Previous to excel 2010(ie 2007 & before), there is no option of repeating labels for pivot tables. This delivers an output as given below:

But this is grossly inadequate if you want to use this pivot output for further analysis (like vlookup on this to check the forecast of Namibia on product Motor – E3). What we want is to repeat the labels for all cells for which it is applicable.

If you want to get the output in a basic flat table (with all cells having an appropriate entry), follow the following simple steps:

1.       Copy the pivot table in a different sheet(only values)

2.       Select the area which contains blanks, and you want the labels above to be repeated

3.       Go to F5->Special-> Select Blanks, and then click OK

4.       Now type =, then press upper arrow key to refer to cell above. Now press Ctrl + Enter (press Enter while keeping Ctrl key pressed). The operation (ie copying above cell) will be repeated in all the selected cells and we achieve the desired result.

Precaution: Please note that all the copied cells still carry the formula (referring to a cell above them). It is a good idea now to copy all the cells in these columns and paste as values (so that if we sort them, the formulas don’t spoil your table)