Replace All Said 4,912 Changes and £70,653.00 of Margin Went Out on Last Year's Costs, Because Find and Replace Reads What a Cell Contains and Never What It Shows

The January job at Ashfield Interiors takes four minutes. Copy Stock and Margin 2025.xlsx, rename the copy to 2026, open it, press Ctrl+H, type 2025 in the top box and 2026 in the bottom one, set Within: Workbook, click Replace All. Excel puts up a small dialog that says how many replacements it made and offers you an OK.

On Monday 5 January 2026 it said 4,912.

Four thousand seven hundred and eighty of those were the point of the exercise: headings, tab labels written into cells, the year in a title, the year in a footnote, the year in ninety-odd column headers across fourteen sheets. Twenty-nine of them broke something loudly enough to be fixed before lunch — 23 cells showing #REF! and 6 showing #NAME?. The remaining hundred and three are what this article is about, and so are the four things Replace All never touched at all.

The Cost column on the Margin tab is not typed. It is filled by a Power Query connection called Standard costs, whose source is a file on the purchasing share: \\fs01\Purchasing\Cost extracts\2025\standard_costs.csv. A query is not a cell. Replace All has no reach into one, so the path still said 2025 on 6 January, and on 23 March, and every morning in between the query refreshed without a single error and returned last year's standard costs into a workbook where every visible thing said 2026.

Suppliers had gone up a weighted 6.8% on 1 January. Every quote the sales desk built between those dates was priced off a cost base eleven weeks out of date, showed a healthy margin on screen, and carried five and a half points less than it claimed.

What this covers. Find and Replace behaves the same in Excel 2016, 2019, 2021, 2024, Microsoft 365 and Excel for Mac (on Mac the shortcut is ⌘+Shift+H and the dialog is laid out differently, but every option below is there). Excel for the web has Find and Replace with a reduced option set — no Format, no workbook-wide scope — which is a real limitation rather than a simplification. SUBSTITUTE, REPLACE, FIND, SEARCH, TRIM, CLEAN, LEN, COUNTIF, COUNTIFS, SUMPRODUCT, ISNUMBER, ISTEXT, CELL, TEXT, CONCAT and IFERROR work everywhere; LET, FILTER and TEXTAFTER need 2021 or 365. A stock workbook is the example because the money is easy to count, but the same four minutes are a year-end roll, a rebrand, a supplier code change, a sort code change, a department rename, a currency switch — any job where somebody needs a string to become a different string in a lot of places at once.

1) Ten Cells From One Replace All, and the Four of Them It Could Not Reach

Read the third column against the fourth. The third is what a person looking at the screen would have said the cell was. The fourth is what Replace All did about it, and the two have almost nothing to do with each other.

Summary!B2 is the job working perfectly: the cell contains the text 2025 Budget — Ashfield Interiors, the cell shows that text, and the replacement changes both. Four thousand seven hundred and eighty cells behaved exactly like it.

Summary!D4 shows 2025 and was not found, because what it contains is the number 46023 with a yyyy number format on it. Nothing in that cell is the text 2025. It is a date wearing a year.

Stock!H4 contained =SUM(H6:H2025). Nothing in that formula is about the year at all — 2025 is a row number — and Replace All rewrote it to =SUM(H6:H2026), which is the line below the data, which is the carriage-in accrual, which is also added separately on the summary tab. The valuation came out £48,900.00 high for two months and nothing about the formula looked wrong, because nothing about it was wrong; it was simply a different range from the one anybody had written.

And the last two rows are the shape of the whole problem. A query and a chart title both showed the year and both were beyond reach, because neither of them is a cell.

🎯 Scenario: Open the workbook you rolled forward most recently, press Ctrl+F, type last year, set Within to Workbook, and click Find All rather than Find Next. If the list that comes back is empty, you did not roll it forward — you rewrote the labels on it. That is a different thing, and the list is where the difference lives.

2) The Replace Tab Has One Look In, and It Is Formulas

Press Ctrl+F, click Options >>, and you get a Look in box with three entries: Formulas, Values and Comments/Notes. Switch to the Replace tab and that box has one entry. Formulas.

This is not a quirk of the user interface, it is the definition of the feature. Replace changes what a cell holds. It cannot change what a cell shows, because what a cell shows is the joint product of a value, a number format, a column width, a conditional format and whatever the formula happened to return this morning — none of which Replace has any business rewriting.

The consequence is an inversion people get wrong in both directions at once:

Stare at the last two rows for a second, because they are the same cell to a reader and different cells to Excel. One rounds on display and one does not, so an identical-looking column of money is half replaced and half not, and the half that changed did so by a pound.

The rule in one line: Replace reaches everything a person cannot see, and nothing a person can.

🎯 Scenario: Before any Replace All on a number-bearing sheet, select the column and press Ctrl+Shift+~ to force General format. What appears is what Replace is working on. Press Ctrl+Z when you have looked.

3) Partial, and Case-Insensitive, Until You Say Otherwise

Two boxes in the Options >> panel decide how big your replacement is, and both start in the permissive position.

Match entire cell contents is unticked. A search for 2025 therefore matches every cell that contains those four characters anywhere: 42025, FA2025-118, Phone ext 2025, =SUM(H6:H2025) and Contract 2025/26, which becomes Contract 2026/26 and is now a thing nobody has ever heard of.

Match case is unticked. A search for north replaces North, NORTH and Northampton, and writes your replacement's capitalisation into all three.

Both of them are worth a worked example, because the cost of the default is easiest to see in text:

That last row is the one that quietly changes data types. Replace All writes the result back into the cell and Excel re-parses it: 1050 with its zeros replaced is 1O5O, which is text, which no longer sums, and a column that looked numeric this morning now has a SUM at the bottom reading 0 with no error anywhere.

The third box, Format, is the one that ruins the next search rather than this one. If anybody ever clicked Format and picked, say, bold, that filter stays set — and a later perfectly ordinary search comes back with Microsoft Excel cannot find any data matching, on a sheet where you can see the thing you are looking for. The fix is Format ▸ Clear Find Format, and the habit worth having is clicking it before every search whether or not you think you need to.

🎯 Scenario: Tick Match entire cell contents by default and untick it deliberately when you need a substring. It is the difference between replacing a value and replacing a piece of text that happens to look like one, and it is one click.

4) Inside a Formula, Four Digits Are Not a Year

A formula is what a cell contains, so a formula is squarely inside Replace's territory, and a four-digit number inside a formula can be any of six things. Only one of them is a year.

=SUM(H6:H2025)          2025 is a ROW NUMBER      → =SUM(H6:H2026), a different range
='2025 Actuals'!G88     2025 is a SHEET NAME      → a sheet that does not exist
=Cost_2025*D7           2025 is part of a NAME    → a name that was never defined
=VLOOKUP(A2,Rates2025,3,0)   part of a table name → #NAME?
=IF(B2>2025,1,0)        2025 is a THRESHOLD       → a different rule, silently
=DATE(2025,4,1)         2025 IS a year            → the one case you meant

Five of those six are damage, and they land in three different tiers of visibility:

• #NAME? and #REF! are the good outcome. They shout. Ashfield's 29 of them were found and fixed by 11am on the fifth, which is precisely why the morning ended with everybody believing the roll-forward had gone fine.
• A moved range is the bad outcome. =SUM(H6:H2026) is a valid formula that returns a plausible number. There is nothing to see.
• A moved threshold is the worst. =IF(B2>2025,1,0) becoming =IF(B2>2026,1,0) changes a rule that is applied thousands of times and returns nothing but ones and zeros either way.

There is one more, particular to roll-forwards. If the find text is a valid cell reference on its own — replacing B2 with B3, or Q1 with Q2 in a workbook that uses quarter labels — then every formula referring to that cell is rewritten too, so =SUM(Q1:Q100) becomes =SUM(Q2:Q100) and the first row of a hundred drops out of every total on the sheet.

🎯 Scenario: Before a Replace All that could land in formulas, press **Ctrl+** (the grave accent, left of 1) to switch the sheet into formula view, and look at what you are about to search *through*. Ctrl+ again puts it back. It is the cheapest ten seconds in this article.

5) It Rewrites the Reference and Never Renames the Tab

This is the specific mechanism behind Ashfield's 23 #REF! cells, and it is worth its own section because the correct way to do the job is the exact opposite of the way people reach for.

A sheet tab is not a cell. Find and Replace searches cells. Therefore:

• ='2025 Actuals'!G88 is searched, because it is the content of a cell, and Replace All rewrote it to ='2026 Actuals'!G88.
• The tab called 2025 Actuals was not renamed, because it is not a cell, and no amount of Replace All will ever rename it.
• So 23 formulas now point at a sheet that does not exist, and every one of them returns #REF!.

Now the opposite, which is the fix: right-click the tab, choose Rename, and type the new name. Excel updates every formula in the workbook that refers to that sheet — on every other tab, including the ones you have forgotten about — automatically and correctly, in one action, with no dialog reporting a count because there is nothing to report. That is a structural update. Replace All is a text edit that happens to be applied to formulas.

The same asymmetry runs through the whole feature:

Every entry in the right-hand column updates the things that depend on it. Every use of Replace All in the left-hand column produces a file where the references and the objects they name have quietly stopped agreeing.

🎯 Scenario: If a roll-forward means renaming tabs, do the renames first, one at a time, by right-clicking. Then run any Replace All you still need afterwards — and it will be a much smaller one, because the references have already looked after themselves.

6) The Nine Places Replace All Cannot Reach

Everything in this list showed the year on screen at Ashfield. None of it changed. The reason is always the same sentence — it is not a cell — and the list is worth keeping somewhere, because it is the checklist for every rename anybody ever does.

1. Power Query source paths and steps. The M code behind a connection is not in a cell. This is the one that cost £70,653.00. Data ▸ Queries & Connections, right-click, Edit, look at the Source step.
2. Data validation rules typed into the dialog. A dropdown whose list is 2023,2024,2025 typed straight into Data ▸ Data Validation ▸ Source is not a cell. A list pointing at a range is cells, and is replaced normally — so two dropdowns that look identical behave differently. Worse: Replace All writes into validated cells without validating, because validation fires on entry and a replacement is not an entry. Ashfield's Summary!C1 ended up holding 2026, a value its own dropdown does not offer.
3. Conditional formatting formulas. A rule of =$A2="2025" lives in the rule, not the sheet. After the replace, the rule stops matching anything and the colour just stops appearing, which looks like nothing at all.
4. Chart titles, axis titles, data labels and legend text typed as text. Chart text linked to a cell updates because the cell updated; chart text typed into the chart does not, ever.
5. Shapes, text boxes, SmartArt and WordArt. Find will not even find them, let alone replace them.
6. Headers and footers. Page Layout ▸ Print Titles ▸ Header/Footer. The year in the footer of every printed page is not in a cell, so every page printed in those eleven weeks carried last year's footer.
7. The target of an inserted hyperlink. The display text is in the cell and is replaced; the address behind it is a separate property and is not. So the link now reads 2026 cost extract and opens the 2025 folder. The exception proves the rule: a link built with =HYPERLINK("...\2025\...","2025 cost extract") is a formula, so both halves are replaced.
8. Defined names, table names, and the sheet tabs themselves. Section 5.
9. PivotTable field names, calculated fields and the cache, and VBA code in modules. A pivot rebuilt from a replaced source updates on refresh; a calculated field's own formula does not.

There is a tenth that belongs here for a different reason: cell notes and comments. The Find tab can search them, via Look in ▸ Comments/Notes. The Replace tab cannot, because its Look in is fixed to Formulas. So a search that finds 4,912 hits and a Replace All that makes 4,912 replacements may genuinely be searching different populations, and the count matching is a coincidence you should not read anything into.

🎯 Scenario: Write the nine down as a roll-forward checklist and keep it in the workbook, on the tab nobody prints. Next January the four minutes become six, and the six are the whole job rather than the visible quarter of it.

7) Scope: Sheet, Workbook, Selection — and Grouped Tabs

Within has two settings and a hidden third.

• Sheet is the default, and means the sheet in front of you. Run a roll-forward with it and thirteen of your fourteen tabs are untouched, which is a problem that announces itself slowly, one tab at a time, over the following month.
• Workbook means every sheet, including the ones you have never opened and the ones somebody hid in 2019. This is what Ashfield used, and it is usually right — but it is also why a replacement intended for one tab lands in a lookup table on another.
• A selection of two or more cells silently becomes the scope. If more than one cell is selected when you press Ctrl+H, Excel searches only inside the selection. If exactly one cell is selected, it searches the whole sheet. That difference — one cell versus two — is the single most useful safety feature in the dialog and the one nobody is told about.

The hidden third is grouped sheets. If several tabs are selected together (the title bar says Group), a Sheet-scoped Replace All applies to all of them at once. People group sheets to format them and then forget to ungroup, so a search that reports 'Sheet' in the dialog can still hit six tabs.

Search: By Rows / By Columns changes only the order Find Next walks the cells, and has no effect whatsoever on Replace All. It is worth knowing so you do not go looking for meaning in it.

And the boxes remember. Match case, Match entire cell contents, Within, Look in and Format carry over from the last search — into the next workbook, across the session, and usually across a restart of Excel. The Replace All that surprises you is very often the one running with somebody else's settings from yesterday.

🎯 Scenario: Make selecting the range part of the action, not an afterthought. Select the column, then Ctrl+H. A Replace All confined to one column cannot reach a formula on another tab, and you can see its whole blast radius while you decide.

8) Wildcards, and the Tilde That Turns Them Off

* and ? are wildcards in the Find box whether you want them to be or not. * is any run of characters including none; ? is exactly one.

Find: 2025*          every cell starting with 2025
Find: ????-2025      a four-character code, a hyphen, then 2025
Find: *2025*         with Match entire cell contents ticked: cells containing 2025 anywhere
Find: ~*             a literal asterisk
Find: ~?             a literal question mark
Find: ~~             a literal tilde

The tilde is the escape, and it is the answer to the most common wildcard accident in Excel: a search for * with an empty Replace box, intended to clear the asterisks a colleague used as footnote markers, which instead matches every cell in the range and empties it. Replace All will happily report that it made several thousand replacements, because it did.

Wildcards apply to Find, not to Replace. There is no way to say keep the bit the * matched; the replacement text is literal. If you need to keep part of what you found, you need formulas, which is section 11.

🎯 Scenario: If the thing you are searching for contains *, ? or ~, type the tilde first and test with Find All before you go anywhere near Replace. The one-keystroke difference between * and ~* is the difference between deleting three asterisks and deleting a sheet.

9) The Characters You Cannot Type Into the Box

Three of them account for most of the searches that come back with Excel cannot find any data matching on data you are looking straight at.

Line breaks. A cell where somebody pressed Alt+Enter contains CHAR(10). Press Ctrl+J in the Find box to enter one — the box will look empty, because a line feed has no width, which is its own small trap when you forget it is in there. Replacing Ctrl+J with a space is the fastest way to flatten an address column.

Non-breaking spaces. Text pasted from a web page or an SAP export is full of CHAR(160), which looks exactly like a space and is not one. Ordinary space in the Find box does not match it, TRIM does not remove it, and a lookup against it fails forever. You can type it with Alt+0160 on the numeric keypad, or do it in a formula, which is more reliable: =TRIM(SUBSTITUTE(A2,CHAR(160)," ")).

Trailing spaces and invisible control characters. =LEN(A2) against the length you expected tells you there is something there; =CODE(RIGHT(A2,1)) tells you what it is — 32 for a space, 160 for the non-breaking kind, 10 or 13 for line endings, 9 for a tab. CLEAN strips the low control characters, TRIM strips ordinary leading, trailing and doubled spaces, and neither touches 160.

🎯 Scenario: When a Find comes back empty on a value you can see, stop searching and measure instead. =LEN(A2) and =CODE(RIGHT(A2,1)) in two spare cells will tell you in five seconds what is in there, which is something no amount of retyping into the Find box will.

10) It Will Tell You It Changed 4,912 Things and Never Which

This is the feature's real flaw, and everything in the rest of this article is a workaround for it.

Replace All reports a count. It does not report a location, a sheet, a list, a log, or a before-and-after. There is no way, after clicking OK, to ask Excel what it just did. The number 4,912 is true, unhelpful, and the last you will ever hear of it.

Your options at that moment are two:

• Ctrl+Z, which reverses the whole Replace All in one step, correctly, across every sheet it touched. Use it the instant anything looks wrong. It is gone the moment the file is closed, and on a big workbook it is worth pressing before you press anything else, because a few more edits and the Replace All is buried in the undo stack behind them.
• Live with it, which is what happens by default, because the dialog is reassuring, the count is large, and nothing on screen is red.

The third option is to have made it unnecessary, which means never running the blind version in the first place.

🎯 Scenario: Save immediately before a Replace All, not after. Then the recovery from a bad one is closing without saving, which is a decision you can make calmly ten minutes later, rather than an undo you have to get right in the next four seconds.

11) Find All First, and Sort the List

Ctrl+F, type the text, Options >>, set the scope, and click Find All rather than Find Next. The dialog grows a list of every match: Workbook, Sheet, Name, Cell, Value, Formula — one row per hit, with a count at the bottom.

That list is the report Replace All refuses to give you, and it costs one extra click:

• Click the Value column header to sort it. Text and numbers separate. In a search for a year, the numbers are the accidents — stock codes, amounts, extensions, the rows your replacement was never meant to touch.
• Click the Formula column header to sort by that, and every hit that lives inside a formula groups together. That is where #REF! and moved ranges come from.
• Click any row and Excel selects that cell so you can look at it in context.
• Click one row and press Ctrl+A and every found cell is selected at once — which is how you apply a colour, a format, or a look before you decide anything.

Then do the replacement, in tranches, with the scope narrowed to what the list showed you. Three Replace Alls that you understand beat one that reports 4,912.

Two counts worth putting in spare cells before you start, because they tell you the size of the job and the size of the accident:

=COUNTIF(A:Z,"*2025*")                          every cell containing the text 2025
=SUMPRODUCT(--(ISNUMBER(A2:Z9000)),--(ISNUMBER(SEARCH("2025",A2:Z9000&""))))
                                                how many of those are numbers, not text

The second number is the one to be afraid of. At Ashfield it was 61.

🎯 Scenario: Make Find All the default motion and Replace All the second step. It is the same two keystrokes in a different order, and it turns an irreversible action taken blind into an irreversible action taken having read the list.

12) When You Need Case, or the Third One Only: SUBSTITUTE and REPLACE

Find and Replace has no case sensitivity in its replacement, no way to change only the second occurrence, and no way to keep part of what it matched. All three live in formulas, in a helper column, where you can see the result before you commit it.

=SUBSTITUTE(A2,"2025","2026")        every occurrence — and case-SENSITIVE, unlike Ctrl+H
=SUBSTITUTE(A2,"2025","2026",2)      the SECOND occurrence only
=SUBSTITUTE(A2,CHAR(160)," ")        the non-breaking space that Ctrl+H cannot type
=REPLACE(A2,1,4,"2026")              four characters from position 1, whatever they were
=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))     the three-in-one import clean-up
=IF(ISNUMBER(A2),A2,SUBSTITUTE(A2,"2025","2026"))   leave the numbers alone, on purpose

The differences that matter:

The workflow is: build the helper column, look at it next to the original, then copy it and Paste Special ▸ Values over the source when it is right. That last paste is the only irreversible step, and by then you have read every row of the answer.

🎯 Scenario: For anything you would describe as 'a careful replacement', use a helper column. For anything you would describe as 'obviously fine', use Find All first. The category that goes straight to Replace All should be empty.

13) Make Next January Not a Replace At All

The structural fix for a roll-forward is that there is nothing to roll. Put the year in one cell and never type it anywhere else.

Config!B1   2026                                 the only place the year is written
Summary!B2  =Config!$B$1&" Budget — Ashfield Interiors"
Stock!A1    ="Stock and margin, year to "&TEXT(DATE(Config!$B$1,12,31),"d mmmm yyyy")
Margin!C7   =INDEX(CostTable[Cost],MATCH([@Code],CostTable[Code],0))
Stock!H4    =SUM(Stock[Value])                    a Table, so the range grows with the data

Four properties fall out of that, and each of them removes one section of this article:

• The year appears once. Changing it is typing in one cell, not 4,912 replacements, and there is nothing to get partially right.
• The sum is a Table reference. =SUM(Stock[Value]) cannot be moved by a text replacement, cannot drift onto an accrual row, and grows when the data does.
• The cost lookup is by code, against a Table, so the name of the year has nothing to do with it.
• The query's source is parameterised. In Power Query, Home ▸ Manage Parameters, make a Year parameter, and build the path from it. Then the source path changes when the parameter changes, in the one place a person would think to look.

The counter-argument is always that the workbook already exists and rewiring it is a day's work. Ashfield's day's work would have been repaid many times over by 23 March, and that is only counting the margin.

🎯 Scenario: You do not have to rewire everything to get most of this. Put the year in one cell today and point the titles at it. That alone takes the noisiest 4,780 of the 4,912 replacements off the table, and what is left is small enough to read.

14) Five One-Cell Checks

1. How many cells contain the text at all? =COUNTIF(A:Z,"*2025*"). Run it on every sheet before you replace anything; run it again after, and the answer should be 0 if you meant it to be.
2. How many of them are numbers? =SUMPRODUCT(--ISNUMBER(A2:Z9000),--ISNUMBER(SEARCH("2025",A2:Z9000&""))). These are the stock codes and the amounts. This is the accident, counted in advance.
3. Did a column stop being numeric? =COUNT(H:H) against =COUNTA(H:H). If they disagree after a replace, something in that column is now text, and SUM is quietly ignoring it.
4. Is there something invisible in this cell? =LEN(A2) and =CODE(RIGHT(A2,1)). 160 is the non-breaking space, 10 and 13 are line endings, 9 is a tab, 32 is an ordinary space you can see if you look.
5. Does the total still tie? =SUM(H6:H2025)-SUM(Stock[Value]) — the old hard range against the Table reference. If a replacement moved the range, this is not zero, and it is the only thing in the workbook that will say so.

15) Twelve Traps

1. Match entire cell contents is unticked by default, so every replacement is a substring replacement until you say otherwise.
2. Match case is unticked by default, so north replaces NORTH and writes your capitalisation over theirs.
3. The Replace tab can only look in Formulas. What a cell shows — through a number format, a date format, or a formula's result — cannot be found and cannot be replaced.
4. A four-digit number in a formula is usually a row number, not a year, and a replaced row number is a valid formula returning a plausible wrong answer.
5. Replace All never renames a sheet, a defined name or a table. It rewrites the references and leaves the object, which is exactly backwards from what you want.
6. A query, a validation list, a conditional format, a chart title, a text box, a header and a hyperlink's target are not cells, and are all invisible to the feature.
7. Replacing into a validated cell does not validate. Validation fires on entry; a replacement is not an entry.
8. * and ? are wildcards in the Find box. A Replace All of * with nothing empties every cell in scope and reports a large number of successful replacements.
9. A selection of two or more cells becomes the search scope; one cell does not. It is the best safety feature in the dialog and the least advertised.
10. Grouped sheets turn a Sheet-scoped replace into a multi-sheet one, and the dialog still says Sheet.
11. The options are sticky — case, entire-cell, scope and especially Format — across workbooks and across restarts. A search that finds nothing obvious is usually a leftover Find Format; clear it.
12. The count is the only feedback you will ever get. There is no log, no list and no diff. One Ctrl+Z reverses everything; closing the file ends that.

Nobody at Ashfield was careless. The roll-forward was documented, it had been done the same way for six years, the person doing it followed the note exactly, and when 29 cells broke they were found and fixed within three hours — which, reasonably enough, was taken as evidence that the exercise had been checked.

What went wrong is that Find and Replace is a text editor that has been let loose inside a spreadsheet. It does not know what a year is, or a price, or a row number, or a sheet. It knows what four characters are, and it changes them wherever it can reach — which is everywhere you cannot see, and nowhere you can.

So the discipline is three habits, and none of them is about being careful. Find All before you Replace All, and sort the list by Value, because the report exists and you simply have to ask for it. Rename things with the tool that renames them — the tab, the Name Manager, the query's Source step — and leave Replace All for text that is only ever text. And put the year, the code, the name, the thing that changes, in one cell, so that the January after next is one keystroke and not four thousand nine hundred and twelve.