Skip to main content
AmigoFish

Every Analyst Should Run This Check on Their Excel Report

From Leila Gharani · · 9 min

The player loads from YouTube only when you click. It uses YouTube's privacy-enhanced mode.

If the video does not play, the owner may have turned off embedding. Watch it on YouTube instead.

Watch on YouTube

Not rated yet

Description

How to find hard-coded numbers hidden inside Excel formulas, using REGEXTEST and conditional formatting. No VBA, no add-ins.

⬇️ Download the practice file: https://www.xelplus.com/find-hardcoded-values-excel/#download

📊 Learn to read financial reports and use Excel the way finance teams do: Fundamentals of Financial Analysis: https://www.xelplus.com/course/learn-financial-analysis/

Go To Special finds numbers typed into cells. But it can't see a number hidden inside a formula, like a +200 someone added and forgot. You'll see how to pull each formula out as text with FORMULATEXT, check it against a pattern with REGEXTEST, and highlight every match with conditional formatting. You'll also learn how to skip numbers you use on purpose (like dividing by 12) and how to switch between patterns with a dropdown. At the end, you'll see how Copilot handles the same check.

Note: REGEXTEST is available in Microsoft 365 and Excel for the web.

★ 500,000+ professionals learn Excel with our courses: https://link.xelplus.com/yt-d-all-courses

✉️ Get one practical Excel tip every week in our free newsletter, Between the Sheets: https://link.xelplus.com/yt-d-newsletter

00:00 The report that looks clean but isn't 00:18 Find hard-coded numbers typed into cells (Go To Special) 00:40 Why Go To Special misses numbers inside formulas 01:36 Not every number in a formula is a problem 02:32 The two regex patterns explained 03:30 FORMULATEXT: pull the formula out as text 03:58 REGEXTEST: check the formula against a pattern 04:38 Highlight them all with conditional formatting 05:55 Add exceptions for numbers you use on purpose 06:26 Switch patterns with a dropdown and XLOOKUP 07:49 What regex won't catch (and how AI can tune it) 08:16 Find hard-coded numbers with Copilot 09:02 What's the worst one you've found?

🎬 Introducing the Excel REGEX functions: https://youtu.be/YFnXV2be9eg 📄 Excel regex functions guide: https://www.xelplus.com/regex-in-excel/

🚩 Connect with us: Instagram: https://www.instagram.com/lgharani LinkedIn: https://www.linkedin.com/company/leilagharani

#excel #exceltips #regex

Back to Leila Gharani · Report a problem