Many times in a list, especially so when it is imported from a database, cells down below that have same headings or subheadings are left blank for the sake of easier readability. For eg. See the screenshot below
However this creates a difficulty when such lists are required to be sorted and/or subtotaled. To fill the cells down below with the subheadings of the cells above them, do the following:
-
Select the list
-
Click CTRL + G.
-
In the [Go To] dialogue box that appears, click the ‘Special’ button
-
In the [Special] dialogue box, select ‘blanks’. This will highlight only the blank cells in the list, with focus on the 1st blank cell. See figure
-
In the 1st blank cell, i.e. the cell with the focus, enter the formula =A2 (see figure) and press CTRL+ENTER.
-
As a result, the blank cells down below get filled up with the subheadings immediately above them. See figure
Marvelous. Very useful command to save a huge time wastage.
thanks, Very useful earlier it takes much time for this.
good, that is so usefull
Very usefull. A day before I spent a full day for this working. Today I am able to do it within 5 mints.
thanks, very useful information , this will help in day to day working