Clean Inherited Excel Spreadsheets With 5 Built-In Tools

The sample workbook in the walkthrough will look familiar to anyone who has been sent a file as an email attachment. It has missing entries, repeated order IDs, inconsistent product names, some states spelled out and others abbreviated, contact details packed into single cells, and a Total formula that does too much at once. Microsoft's own documentation confirms how each tool behaves. It also adds a few limits that decide whether this cleanup is safe or destructive.

Go To Special finds the blanks before you change anything​

How-To Geek's first step is to understand the spreadsheet before editing it. Go To Special selects cells by what they contain, not by where they sit. Microsoft's documentation covers it for Excel for Microsoft 365, Excel 2024 and Excel 2021 on Windows. The steps are:

1. Select a single cell to search the whole worksheet, or select a specific range, rows or columns to limit the search.
2. On the Home tab, choose Find & Select > Go To, or press Ctrl+G.
3. Select Special.
4. Choose Blanks, then OK.

Excel then selects every empty cell in the area you chose. The walkthrough fills those cells with yellow, enters the missing information, and removes the highlight afterwards. Nothing is lost if you change your mind, because a fill color doesn't touch the cell contents.

The walkthrough also suggests typing a placeholder such as BLANK into the first selected cell and pressing Ctrl+Enter, which enters the same text into every selected cell at once. Unlike highlighting, this changes your data. Those cells now hold text, so they will no longer show up as blank, and any placeholder you forget to replace can end up in reports or calculations. Use it only when you really want a visible marker, and plan to clear it.

Blanks are only one of Go To Special's options. The same dialog can select constants, formulas (filtered by result type), cells whose formulas refer to the active cell, the last used cell on the sheet, only visible cells, and cells that already have conditional formatting or data validation. On an unfamiliar workbook, selecting Formulas shows which cells are calculated and which were typed in. That helps before you overwrite what looks like a static number.

Some blanks are there on purpose. An optional field left empty is not missing data. Go To Special shows you where the gaps are, and you still have to decide which ones need filling.

Conditional formatting turns duplicate order IDs into a review list​

Conditional formatting applies a format when a rule is met and leaves the underlying values alone. That makes it the right tool for the investigation stage. Microsoft documents the feature for Excel 2016 and every version since.

For repeated order IDs, the built-in rule is at Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. The same dialog can highlight Unique values instead. If you want more control, a formula-based rule can do the same job. Microsoft's example is =COUNTIF($A$2:$A$400,A2)>1, which flags any value that appears more than once in the range. Formula rules must return TRUE or FALSE.

In the walkthrough, the highlighted order IDs turned out to be complete duplicate rows, so the author deleted the extra records and removed the temporary rule. The row check is the step to copy. Depending on how the source system exports data, one order can legitimately appear on several lines, one per product. A repeated ID alone doesn't prove a record is duplicated. Compare every field across the matching rows before deleting anything, and keep an untouched copy of the original file.

Product names need a different approach. How-To Geek notes that the duplicate-value rules ignore case, so "Widget" and "WIDGET" count as the same value and a case difference never shows up. Its fix is a helper column that converts every product value to one case with UPPER, followed by the Unique rule on that column. Entries that appear only once are worth a look because they may be typos or odd spellings. As the author points out, a product that appears once may simply be a product that sold once.

For the State column, the walkthrough uses a rule based on LEN, which returns the number of characters in a text string. A rule along the lines of =LEN(F2)>2, applied to the state column, highlights anything longer than a two-letter code, such as "California" instead of "CA". Microsoft's documentation notes that spaces count as characters, so the same rule also catches "CA " with a stray trailing space. It cannot catch a two-letter value that isn't a real state code. Length is a quick filter, not a validation check.

TEXTSPLIT separates pipe-delimited contact details if your Excel version supports it​

The sample workbook's Contact Details column holds an email address, phone number and ZIP code in one cell, separated by the pipe character (|). How-To Geek uses TEXTSPLIT to spread each cell across three columns.

Microsoft describes TEXTSPLIT as the formula version of the Text to Columns wizard, and the inverse of TEXTJOIN. The full syntax is =TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with]). If the contact details are in A2, the formula is =TEXTSPLIT(A2,"|"), and the three pieces spill into the cell and the two cells to its right.

The optional arguments matter for messy data:

• The ignore_empty argument defaults to FALSE, so two pipes in a row create an empty cell. Set it to TRUE to skip empty pieces, but be aware this can shift values into the wrong column when a middle field is missing.
• The match_mode argument defaults to a case-sensitive delimiter match. That doesn't matter for a pipe, but it does if you split on letters.
• The pad_with argument sets what fills gaps in the result. The default is #N/A, and Microsoft suggests wrapping the formula in IFNA or supplying a pad_with value if you want something cleaner.
• More than one delimiter needs an array constant, such as {",","."}.

Version support is the real limit here. Microsoft lists TEXTSPLIT for Excel for Microsoft 365 and Excel 2024, on both Windows and Mac. Colleagues on Excel 2021 or earlier won't have the function. For them, the Text to Columns wizard does the same split as a one-time operation.

The walkthrough checks the spilled results first, then pastes them as values and deletes the original Contact Details column. The order matters. A TEXTSPLIT result stays tied to its source cell, so deleting Contact Details while the formulas are still live breaks them. Paste as values first, then delete. Leave empty columns to the right before you split, too: a spill formula won't overwrite data that's in the way, and Microsoft warns that Text to Columns can overwrite neighboring cells.

Excel tables come last, and structured references won't write themselves​

Once the range is clean, How-To Geek converts it to an Excel table. Tables add built-in filters, grow automatically when rows are added, and support calculated columns. The author waits until the end on purpose, to avoid adding table structure while still deleting duplicates and fixing inconsistencies. This is a workflow choice, not something Excel requires. Tables work fine on messy data. It is simply easier to reshape a plain range before the table structure is in place.

The key detail, confirmed by Microsoft, is that when you convert a range to a table, Excel doesn't automatically change any cell references of this range to their equivalent structured references. Structured references replace cell references, such as C2:C7, with predefined names for the items in a table. An inherited Total formula written as =D2*E2 will still say =D2*E2 after conversion.

The walkthrough rewrites the formula in the first data row by hand so that it uses column names, and presses Enter. Excel then fills the formula down the column. How-To Geek's account matches independent guidance from Ablebits: to perform the same calculation in each table row, it is enough to enter a formula in just one cell. All other cells in that column are filled automatically. The result reads as a statement of business logic, such as quantity times price, instead of a set of worksheet coordinates.

Excel can help with the rewrite. By default, when you create a formula, selecting a cell range within a table semi-selects the cells and automatically enters a structured reference instead of the cell range. If that isn't happening, check the Use table names in formulas check box in the File > Options > Formulas > Working with formulas dialog. Also note that this is a one-way conversion. Coursera's guide warns that if you convert a table to a range, your structured reference formulas will automatically become cell references, and converting back to a table does not restore them.

Tables also get default names such as Table1. To rename a table, select any cell within it and use the Table Name box in the Table Design tab. A meaningful name makes the structured references readable in formulas elsewhere in the workbook as well.

Named ranges and a discount helper column pull constants out of the Total formula​

The last problem in the sample workbook is a Total formula with the sales tax rate, two discount thresholds and two discount rates all typed directly into it. When the tax rate changes, someone has to find the right number in a long nested formula and hope they changed the only copy.

How-To Geek fixes this in two steps. First, it lists the constants in cells with labels next to them and names them using Create from Selection (Formulas > Create from Selection, or Ctrl+Shift+F3). Excel reads the labels and creates matching names, and names created this way apply to the whole workbook. Microsoft's naming rules still apply: names can't contain spaces, and Excel ignores capitalization, so TaxRate and taxrate are the same name. The Name Manager shows every name in the workbook along with what it points to. When you open someone else's file, check it for leftover or broken names.

Second, the walkthrough adds a Discount helper column to the table, so the discount for each row is calculated and visible on its own. The Total formula then uses that column together with the named tax rate. Anyone auditing the sheet can see exactly which discount each order got, and a change to a threshold is a single edit to one labeled cell.

This makes the calculation easier to maintain. It doesn't make it correct. If the original formula applied the wrong discount tier, the tidied version will apply the same wrong tier more clearly. Check the rebuilt Discount and Total values against the business rules they are supposed to follow, and compare a sample of rows against the original workbook before you switch over.

What this means for you​

If you regularly receive other people's workbooks, adopt the order along with the tools. Look first, then flag, then change, and add structure last. Keep an untouched copy of the original file before you start, because two of the five steps (deleting duplicate rows and replacing Contact Details) permanently remove data. If your organization runs a mix of Excel versions, find out who is on Excel 2021 or earlier before you share a workbook that still contains live TEXTSPLIT formulas.

The walkthrough ends with the habits that prevent the mess in the first place. Put fields in columns and records in rows. Keep one data point per cell. Avoid blank rows and columns, and blank cells where possible. Use a single header row. A workbook built this way needs none of the repairs above.

• Run Go To Special > Blanks (Ctrl+G > Special) before editing, and use a fill color to mark gaps. Typing placeholder text with Ctrl+Enter permanently changes the cells.
• Treat the Duplicate Values and Unique rules as a list of things to review, and compare whole rows before deleting any record that shares an order ID.
• Normalize text with UPPER in a helper column before looking for inconsistent names, because the duplicate rules ignore case.
• Use =TEXTSPLIT(A2,"|") only on Excel for Microsoft 365 or Excel 2024, leave empty columns for the spill, and paste as values before deleting the source column.
• After converting to a table, rewrite the old cell-reference formulas by hand in the first row, because Excel won't convert them for you.
• Move constants into labeled cells, name them with Create from Selection (Ctrl+Shift+F3), and check the rebuilt totals against the original before relying on them.

None of these five features is new. What the How-To Geek walkthrough gets right is the order: a sequence where every destructive step comes after a step that only inspects. For anyone maintaining a workbook they didn't build, the best result is a file the next person never has to clean up, with one fact per cell, visible helper columns, named constants and table formulas that read like the business rules behind them.