Need Help With Excel? 10 Common Spreadsheet Problems and How to Fix Them
· admin
Excel is brilliant right up until it decides that a number is text, a formula is apparently decorative, or your tidy spreadsheet suddenly contains 4,000 duplicates.
If your spreadsheet is giving you grief, start here. These are ten common Excel problems, the first things worth checking, and the point at which fixing it yourself becomes more faff than it is worth.
1. Excel formulas aren’t calculating
If a formula is sitting there stubbornly displaying the wrong result, check whether calculation has been switched to manual. Also check that the formula begins with an equals sign and that the cells it refers to contain the type of data you expect.
Another common culprit is a number stored as text. It may look perfectly respectable on screen while Excel quietly refuses to treat it as a number.
2. Numbers are being stored as text
This often happens when data has been copied from another system, imported from a CSV file or extracted from a PDF.
You may see a warning indicator in the cell, totals may refuse to work properly, or sorting can produce bizarre results. Converting the affected cells back to numbers usually sorts the immediate problem, but with a large imported dataset it is worth checking the whole column rather than fixing cells one by one.
3. Your spreadsheet is full of duplicate rows
Duplicates are particularly common after combining exports, customer lists, transaction records or data from several files.
Excel has a built-in Remove Duplicates tool, but make sure you understand which columns define a genuine duplicate before deleting anything. We have a separate guide on how to remove duplicates in Excel without losing your data.
4. Dates have gone weird
Dates are one of Excel’s favourite opportunities for administrative chaos.
A column can contain genuine Excel dates, text that merely looks like a date, and dates imported in a different regional format. If sorting puts dates in a strange order or formulas involving dates fail, inconsistent underlying values are often the reason.
Standardise the column before relying on it for calculations, filtering or reporting.
5. Sorting has scrambled the data
If you sort one column without the rest of the table moving with it, records can become detached from each other. A customer name can suddenly acquire somebody else’s address, order or reference number.
Before sorting, make sure Excel recognises the entire dataset as one range or table. If something already looks wrong, do not keep editing blindly. Go back to a clean copy if possible and establish where the rows became misaligned.
6. CSV data opens in the wrong columns
A CSV file is simple in theory. In practice, delimiters, quotation marks, encoding and regional settings can turn it into alphabet soup.
If everything appears in one column or fields split in the wrong places, import the file using Excel’s data import tools rather than simply double-clicking it. That gives you control over delimiters and data types before the information lands in the worksheet.
7. Data copied from a PDF is a mess
PDFs are designed to preserve how information looks, not necessarily how the underlying data is structured. Tables can arrive in Excel with merged columns, repeated headings, missing rows and numbers stored as text.
For the straightforward route, see our guide on how to convert a PDF to Excel without making a complete mess of it.
8. You need to combine several Excel files
Copying and pasting from file after file works until it doesn’t. It is easy to miss rows, duplicate records, alter formats or paste data under the wrong headings.
If the files have a consistent structure, Excel can combine data far more reliably than manual copying. The larger the job, the more worthwhile an automated approach becomes.
9. The spreadsheet has become painfully slow
Huge datasets are not the only cause of a sluggish workbook. Excessive formulas, whole-column references, conditional formatting, external links and years of accumulated clutter can all contribute.
Before rebuilding everything, identify which sheets and formulas are doing the heavy lifting. Sometimes a surprisingly small clean-up makes the workbook usable again.
10. The spreadsheet technically works, but nobody understands it
This one is more common than people admit.
A workbook can be mathematically correct and still be horrible to use. Unclear headings, inconsistent formatting, unexplained abbreviations, hidden assumptions and twenty-seven tabs named things like Sheet4-final-FINAL2 are all warning signs.
A useful spreadsheet should make the job easier. If somebody needs an archaeological expedition to understand it six months later, it needs tidying.
Before you start fixing anything
Make a copy.
Seriously. Before removing duplicates, changing formulas, restructuring columns or experimenting with imports, save an untouched version of the original file. Excel has an Undo button, but it is not a substitute for having the original data safely parked somewhere else.
Can’t be faffed?
Sometimes the problem is a two-minute fix. Sometimes you open the workbook and realise somebody has apparently been feeding it after midnight.
If you have a broken formula, messy data, duplicate records, several files that need combining, a PDF that needs turning into usable Excel data, or a spreadsheet that simply needs sorting out, Faff It Off.
Send us what you’ve got, tell us what you need at the end, and we’ll quote for sorting the faff for you.