Recently, I had a quite bizarre experience. In the space of an hour I was asked for help from two of my wonderful subscribers for help with the SAME PROBLEM… so that made me think that perhaps more of you would find this useful.
Essentially this is the problem. You get a download of data and let’s say it has got a load of dates but a lot of them are missing. However in order to complete a pivot table you need to have no blank spaces, so that means you have to manually (the horror!) copy and paste in the dates.
But do not fear! I discovered a trick to quickly enter this data. Now I have to be honest here and say thanks very much to Bill Jelen (www.mrexcel.com) who told me about this one.
Here is the link to the file: mind_the_gap
So here goes…here are the instructions.
- Highlight your data first (Ctrl, Shift and arrow down. It will stop at a gap but just keep Ctrl and Shift pressed down and press the arrow key again)
- Press F5. Choose Special
- Click on Blanks (this will automatically select the blank data)
- Type in =
- Press arrow up
- Now press Ctrl and Enter together.
- Data entered…amazeballs isn’t it?