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 |
About this data: Teaching sample for a fictional shop “Skyline Clinics”. Figures and names (e.g. Priya Deshmukh) are made up for this lesson on blank cells.
After
| City | Area | Orders |
|---|---|---|
| Pune | Kothrud | 412 |
| Pune | Baner | 356 |
| Pune | Wakad | 298 |
| Nagpur | Dharampeth | 275 |
| Nagpur | Sitabuldi | 241 |
About this data: Demo table only — not real customer or company data. Built to practise blank cells.
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.
Loading practice tasks...
- Filled phone numbers (Easy): How many phones are filled in C2:C9?
- Missing phone numbers (Easy): How many phone cells are empty in C2:C9?
- Missing emails (Easy): How many emails are empty in D2:D9?
- Forms with a premium (Easy): How many premiums are filled in F2:F9?
- Filled boxes in one form (Medium): How many boxes are filled for F-04 (B5:F5)?
- All empty boxes (Medium): How many cells are empty in B2:F9?
- Is F-05 complete? (Medium): Is the form in B6:F6 complete? (TRUE or FALSE)
- Share of forms with a premium (Medium): What part of forms have a premium? (filled premiums ÷ all forms)
- Status message for F-03 (Hard): For row 4 (B4:F4) show "Complete" if nothing is empty, else "Missing " and the number.
- Missing contact details (Hard): How many cells are empty in the phone and email columns together (C2:D9)?
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
