Saturday, 5 May 2012

Filling up blank cells in Excel

Compare the two screenshots below:

Sales data ( as shown in the picture at the Left) is entered in an Excel sheet.  The problem with this type of data entry is that Excel functions like SUMIF or COUNTIF will not work correctly. SUMIF, SUMIFs, etc are useful for finding the sales of a particular Salesman, say Govind, from the entire list of data.  For that we need data like the one at the right. So how do we quickly/automatically fill up the blanks?

Fill up a sheet with the incomplete data as shown above.
( "+" in the steps below indicates "and")
1. Click any cell with data in it. Example, B5 with "Govind"
2. Press Ctrl + * keys to select the block of data. (If you are pressing the * (asterisk) key in the QWERTY part of the keyboard, then press Ctrl + Shift + *), This will select the entire block of data.
3. Press Ctrl + g (g can be capital or small).  Go To box will appear.
4. Click on the "Special " button
5. Next, click on radio (option) button: "Blanks"
6. Click on Ok.
7. Press the = key
8. Press the ↑ (up arrow) key
9. Press the Ctrl + Enter keys.
The blank cells will be filled with the value in the cell above.
10. Select the entire block again.(See step 2 above.)
11. Press Ctrl + C to copy
12. Click on Paste Values (in the Home Tab-->Clipboard Group) - see graphic below

(Note: "Paste Values" is done because the blank cells are filled up using formulas and unless the formulas are replaced with values they can mess things up when the data is sorted, etc.).

If the dates in Column A are not displayed correctly and are displayed as numbers: Click cell A2 which has the correctly formatted date. Next click on the Format Painter  (in the Home Tab-->Clipboard Group)(see graphic below). A paint brush symbol will be attached to the mouse.
Drag the mouse over the cells with dates in Column A to apply the correct format.

1 comment: