5.2 Blank Cells
Two different problems: blank cells that should repeat the value above (typical of exported reports), and completely blank rows.
Before
| City | Area | Orders |
|---|---|---|
| Pune | Kothrud | 412 |
| Baner | 356 | |
| Wakad | 298 | |
| Nagpur | Dharampeth | 275 |
| Sitabuldi | 241 |
After
| City | Area | Orders |
|---|---|---|
| Pune | Kothrud | 412 |
| Pune | Baner | 356 |
| Pune | Wakad | 298 |
| Nagpur | Dharampeth | 275 |
| Nagpur | Sitabuldi | 241 |
Steps in Excel – fill blanks with the value above
- Select A2:A7 (the City column).
- Home › Editing › Find & Select › Go To Special › Blanks › OK. Only the empty cells are selected.
- Type
=and press ↑ (the formula becomes=A2for the first blank) and press Ctrl + Enter – it fills every selected blank. - Select the column › Copy › Paste Special › Values so the formulas become fixed text.
Steps – delete fully blank rows
- Add a helper column:
=COUNTA(A2:C2); filter it for 0 › select the visible rows › Home › Cells › Delete › Delete Sheet Rows › remove the filter. - (Careful shortcut) Go To Special › Blanks › Delete › Entire row works only when a blank in one column means the whole row is empty.
For true missing values (e.g. a blank Delivery Mins), don't invent numbers. Mark them ("Not recorded"), count them with =COUNTBLANK(), and decide with your manager whether to exclude those rows.
Ravindra Bagale's Tip
Go To Special › Blanks nantar Delete › Entire row kelyavar khup students cha khara data pan jaato – karan ekhadya row madhe fakt ek cell rikami hota. Row delete karaychya aadhi poori row rikami aahe ka te COUNTA helper ne check kara. Ani Ctrl + Enter nantar Paste Values visru naka, nahitar sort kelyavar values gondhaltat.
Practice task
Take a city-wise report where City is written only once per group; fill the blanks with Go To Special + Ctrl + Enter, then remove two fully blank rows using a COUNTA helper.