There is no defined rule to capture data in Excel worksheet; however, it’s always advisable to record details in tabular form and don’t leave blank cells in between. Some users don’t want to repeat the values as shown here in the zone column below:
In such cases we face problem if we want to apply filter or like to create a pivot table with data. To get desired result, first of all we have to fill the blank cells with required value. In the above picture we have to update blank cells of “Zone” column with “North” or “South”. If data is small we can easily do it using “Fill Down”, but it will be really tiring if we have to fill thousands of blank cells. In such cases, instead of using “Fill Down” repeatedly, we can use a trick to fill all blank cells in one go.
First of all select all blank cells. For this, select the data range and press “F5” a pop-up window will appear, click “Special…”, a new window will appear, select the option “Blanks” and press “OK”. Excel will select all blank cells in one go for you.
Now, type “=” and press ↑ (up arrow) symbol, press “Ctrl” first then press “Enter” and Excel will update all blank cells with required values.
At this stage updated blank cell contain the formula. Do remember to convert the same as values.
In case you have any query regarding above, you may send the same as comment to this blog.
I’ve learned that people will forget what you said, people will forget what you did, but people will never forget how you made them feel.
By Rajesh K Saraogi
Rajesh is a hardcore finance person with a penchant for latest technology. His passions is to improve the productivity with simple, yet powerful software – mainly on Microsoft Excel platform. He is a qualified MCA. Whenever he gets time he loves to blog, to share the knowledge, which is the only thing which increases after sharing!