Excel · Editor — cleanup
Last third of the Excel Editor group: delete blank rows/columns, batch-clean before share, apply house style, and strip chart chrome.
1. Map (this chapter)
| Control | Notes |
|---|---|
| Delete blanks | Blank/near-blank rows or columns |
| Batch clean | Thirteen ops — selection / sheet / workbook |
| Set Default | One-click house style on every worksheet in the workbook |
| Format charts | Plain look — no gridlines/ticks/fills |
Earlier Editor pages: formulas, text & fill.
2. Delete blanks
Remove blank or nearly-blank rows or columns in one batch.
| Scope | Selection / used range / entire sheet | | Criteria | Fully blank · key column/row blank · below non-empty threshold |
3. Batch clean
Thirteen operations for content, formatting, and metadata before share/archive. Scope: Selection (default), Current sheet, or Full workbook.
| Operation | Notes |
|---|---|
| Clear zero values | Blanks cells that are exactly 0 |
| Clear error values | Blanks #N/A, #VALUE!, #REF!, … |
| Remove hyperlinks | Keeps display text |
| Remove conditional formatting | Drops CF rules |
| Freeze conditional formatting to static colors | Paint current CF colours as fills, then drop rules |
| Remove data validation | |
| Remove comments / notes | |
| Remove pictures / embedded objects | |
| Remove external links | Always whole workbook; formulas → values |
| Remove broken defined names | Keeps Navifia link anchors |
| Remove cell styles | Always whole workbook |
| Remove hidden names | Off by default — also removes the hidden names Navifia uses for Excel↔PowerPoint links; turns off Push/Update for those shapes if used |
Remove CF and Freeze CF are mutually exclusive (checking one unchecks the other).
Do not enable Remove hidden names if you still need linked shapes to refresh.
4. Set Default
One-click house style on every worksheet in the workbook: font name and size (default Arial 10, configurable), autofit columns/rows, hide gridlines, thin A1 corner margin, optional theme colours from ContentLayouts.pptx (Templates folder — set from PowerPoint Getting started → Browse…). After you Apply once, new sheets you insert get the same treatment.
5. Format charts
Select a chart (or activate it), click Format charts — strips gridlines, tick marks, chart/plot fills and borders to a plain look. One chart at a time; no selection → clear message, not a silent full-sheet wipe.
6. Suggested workflow
- Delete blanks on the import sheet.
- Text / Date tools (text & fill) on messy columns.
- Batch clean (sheet or workbook) — leave Remove hidden names off if Sync links matter.
- Set Default if the sheet’s font/grid is a mess.
Troubleshooting
- Batch clean broke PowerPoint links — Remove hidden names was on; restore from backup and re-export/link shapes. Keep that option off for linked models.
- Format charts says nothing selected — click the chart once (ChartObject) or double-click into chart edit / open a chart sheet.