I came across this tip years ago and remember being amazed by it at the time (although now it's just part of my repertoire).
Often you come across data where there is just one entry in a column (e.g. for a date) and the empty cells which follow are assumed to have the same value as the last filled cell above. This may occur for several columns, as the attached file demonstrates, with a second column used for Department. This may look good on a printed report, but it's not very useful if you want to analyse the data using formulae like SUMIF or SUMPRODUCT, or if you want to set up a pivot table on the data. An easy way of filling those empty cells so that we have contiguous data is as follows:
starting with the cursor somewhere in the table of data,
press F5 (GoTo), then click Special, then click Current Region;
press F5 again, then click Special, then click Blanks;
begin to enter a formula by typing "=" (without the quotes)
click on the cell immediately above the activecell (coloured white in the highlighted block);
then hold down the CTRL key and press Enter.
This will have filled that formula into all the cells that were highlighted - amazing !!
Hope this helps.
Pete
EDIT: As Marvin has pointed out below, if you intend to do anything with the data that involves moving the rows around (like sorting), you should fix the values using [copy], [paste special | values | OK], then press the [Esc] key.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
