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
After Go To Special › Blanks, when they do Delete › Entire row, many students lose real data too – because a row had only one empty cell. Before deleting a row, check with a COUNTA helper whether the whole row is empty. And after Ctrl + Enter, don't forget Paste Values, otherwise the values get mixed up when you sort.
Ravindra Bagale's Tip – मराठी
Go To Special › Blanks नंतर Delete › Entire row केल्यावर बऱ्याच students चा खरा data पण जातो – कारण एखाद्या row मध्ये फक्त एकच cell रिकामी होती. Row delete करण्याआधी पूर्ण row रिकामी आहे का ते COUNTA helper ने check करा. आणि Ctrl + Enter नंतर Paste Values विसरू नका, नाहीतर sort केल्यावर values गोंधळतात.
Ravindra Bagale's Tip – हिंदी
Go To Special › Blanks के बाद Delete › Entire row करने पर बहुत से students का असली data भी चला जाता है – क्योंकि किसी row में सिर्फ़ एक ही cell खाली थी. Row delete करने से पहले COUNTA helper से check करो कि पूरी row खाली है या नहीं. और Ctrl + Enter के बाद Paste Values मत भूलना, वरना sort करने पर values गड़बड़ हो जाती हैं.
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.