Excel File Checks: 5 Things to Verify First
Before you trust any spreadsheet someone else built, you need to check five specific things: the formulas are actually calculating what they claim, the data types are consistent, there are no hidden rows or columns, the file isn’t corrupt, and the numbers match the source. These checks take about five minutes and can save you from making decisions based on faulty data. Here’s exactly what to look for and how to do it.
Why Spreadsheets Fail Silently
Spreadsheets are the most widely used data tools in business, yet studies consistently show that a large percentage of them contain errors. The problem is that errors are often invisible — a formula can look correct, produce a number, and still be wrong. This happens because spreadsheets are built by humans, and humans make mistakes: a wrong cell reference, a typo in a formula, a pasted value that overwrote a calculation, or a filter that hides half the data. When you receive a file from a colleague, client, or vendor, you inherit all of those potential mistakes. The five checks below are designed to catch the most common and most damaging issues before you rely on the data.
Check 1: Formula Integrity
The first thing to do is see what’s actually in the cells. Click on any cell that contains a number you care about, and look at the formula bar. If you see a hardcoded number (like 1500) instead of a formula (like =SUM(B2:B10)), that’s a red flag — someone may have pasted values over formulas, breaking the live calculation. To systematically check, press Ctrl + ~ (or Cmd + ~ on Mac) to toggle formula view. This shows every formula in the sheet. Look for:
- Formulas that reference the wrong ranges (e.g., summing B2:B10 when the data is in B2:B20)
- Formulas that point to other sheets or workbooks — these can break if the source file moves
- Inconsistent formulas in a column (e.g., some cells use SUM, others use AVERAGE)
- Circular references, which Excel usually warns about but can sometimes sneak through
If a formula references another workbook, the value may be stale or completely missing. Check the links by going to Data → Edit Links and see if the source is available.
Check 2: Data Types and Consistency
Numbers stored as text are a classic spreadsheet killer. A cell that looks like a number but is actually text will not sum correctly, and sorting will put it in the wrong place. To spot this, select a column and look at the status bar — if it shows “Count” instead of “Sum” when you select multiple numeric cells, some of them are text. You can also use the ISNUMBER() function in a helper column to test each cell. Another common issue is inconsistent units: one column might have values in thousands, another in millions, and a third in raw numbers. Look at the source data or ask the sender to confirm units. Also check for dates — Excel stores dates as serial numbers, so a date that displays as “01/02/2023” might actually be January 2 or February 1 depending on your regional settings. If the file was created in another country, the date interpretation may be different.
Check 3: Hidden Rows, Columns, and Sheets
Hidden data is the easiest way to mislead someone. A row or column can be hidden accidentally, or deliberately, to make a total look different. To reveal everything, select all cells by clicking the triangle at the top-left corner of the sheet, then right-click any row number and choose Unhide, and do the same for columns. Also check for hidden sheets — right-click any sheet tab and select Unhide to see if there are any. Pay special attention to rows or columns that are hidden at the very end of a range — for example, a SUM formula that covers A1:A100 but row 100 is hidden and contains a manual override. Another trick is to look for rows that are filtered out. If a filter is active (the row numbers appear blue), the data is still there but not visible. Clear the filter by going to Data → Clear to see the full dataset.
Check 4: File Integrity and Version
Before you even open the file, check the file size and extension. A file that is unexpectedly small may have lost data. A file with a .xls extension (old format) may not support newer features, and a .csv file has no formulas at all — only values. If the file is corrupt, Excel will often warn you when opening. If you get a repair prompt, be cautious: the repair may drop some content. To check for corruption without opening, you can try opening the file in a text editor (if it’s a .xlsx, it’s a zip file — you can rename to .zip and inspect the contents, but that’s advanced). A simpler check is to open the file in Excel and look for any cells with #REF!, #VALUE!, or #DIV/0! errors. These indicate broken formulas. Also check the file’s properties (right-click → Properties) to see when it was last modified and by whom — if it’s months old, the data may be outdated.
Check 5: Source Data and Totals
Finally, verify that the numbers actually match reality. If the spreadsheet claims to show sales for the last quarter, spot-check a few figures against your own records or the original source system. If you don’t have access to the source, ask the sender for a sample of the raw data. Also check the totals: add up a column manually (or with a calculator) and compare to the sum in the spreadsheet. If they differ, there’s a problem. Another technique is to look for duplicated rows — use Data → Remove Duplicates (but make a copy first) or use a pivot table to count unique values. Duplicate entries can inflate totals. Finally, check the date range — many spreadsheets are accidentally missing the most recent month because the data wasn’t refreshed.
Common Mistakes and How to Avoid Them
When you perform these checks, be careful not to make things worse. Don’t edit the original file — always make a copy and work on that. If you find an error, don’t assume it’s the only one; errors often come in clusters. Also, don’t rely solely on Excel’s built-in error checking (the green triangle indicator) — it only catches a few types of issues. A common misconception is that a file that opens without warnings is safe. That’s not true — most errors are silent. Another mistake is to trust a spreadsheet because it came from a reputable source. Even professional firms make spreadsheet errors. The best practice is to have a second person review the file, or to build a small test case with known numbers to verify the formulas produce the expected result.
Frequently Asked Questions
How do I quickly see all formulas in a sheet?
Press Ctrl + ~ (or Cmd + ~ on Mac) to toggle between showing values and showing formulas. This is the fastest way to scan for hardcoded numbers or inconsistent formulas.
What if the file has a password to open?
If you don’t have the password, you can’t open the file. Ask the sender for it. If the file is password-protected for editing, you can still open it and view the data, but you may not be able to change anything. Never try to bypass passwords — it’s not legal or safe.
Can I trust a .csv file?
A .csv file contains only raw values — no formulas, no formatting, no multiple sheets. It’s safe in the sense that there are no hidden formulas, but it’s also easy to misread the data type (everything is text until Excel interprets it). Always check that dates and numbers are interpreted correctly when opening a .csv.
What does #REF! mean?
#REF! means the formula references a cell that no longer exists — usually because a row or column was deleted. This is a critical error that must be fixed before trusting the spreadsheet.
How do I check for hidden rows in a large sheet?
Select all cells, then go to Home → Format → Hide & Unhide → Unhide Rows (and Unhide Columns). Alternatively, press Ctrl + A twice to select all, then right-click a row number and choose Unhide.
Is it safe to use a spreadsheet from a client?
It can be, but you should always run these five checks first. Client spreadsheets often contain macros or external links that could be broken or even malicious. If the file has macros, be extra cautious — only enable them if you trust the source completely.
Related
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.