The 10 Most Common Excel Problems and How to Solve Them

The 10 most common Excel problems and how to solve them

Your Excel sheet crashed. Or your formula shows #REF!. Or the file is just painfully slow. You are not alone – these are the most common Excel problems we see every day. In this guide you get concrete solutions that work, both quick fixes you can do yourself and guidance for when you need professional help.

We have helped hundreds of companies through Excel Department, and the pattern is clear: 70 percent of all Excel problems are caused by two simple mistakes. You can solve the rest with the right tools and a bit of method. Here are the answers.

Quick guide and problem overview

Which problem do you have? Jump straight to the right section:

Excel won’t open or crashes → Problem 1
Formulas show errors or don’t calculate → Problems 2 and 6
Data was sorted incorrectly → Problem 3
Pivot table doesn’t update → Problem 4
Excel is slow → Problem 5
Error messages (#REF!, #N/A!) → Problem 6
Can’t paste → Problem 7
Numbers are stored as text → Problem 8
Printout looks strange → Problem 9
Formulas are displayed as text → Problem 10

Two mistakes that cause 70% of all problems

1. The cell format is wrong

If Excel treats numbers as text, or formulas are displayed instead of results, it is usually down to the cell format. Select the cells, right-click and choose ”Format Cells”. Change from ”Text” to ”General” or the correct number format.

2. Automatic calculation is turned off

If formulas don’t update: click Formulas → Calculation Options → Automatic. Problem solved.

Basic checklist before troubleshooting

Before you start digging deeper:

Save the file and close it. Open it again. Many problems disappear.
Press Ctrl+Z if something just went wrong.
Check that you have selected the right range before you sort or format.
Make sure the worksheet isn’t protected. Column headers greyed out? Then the sheet is locked.
Restart Excel. Sometimes the program gets stuck in a bad state.

Now on to the problems and the solutions.

Problem 1: Excel crashed and won’t open

Symptoms:

The file won’t open at all, Excel freezes right at startup, or you get the error message ”Excel has stopped working”.

Why it happens:

Excel can crash because of corrupt add-ins, files that are too large, missing fonts, or because the file was created in a different version of Excel. Sometimes the file is simply damaged.

Step-by-step solution

Step 1: Open Excel in safe mode

Press Windows + R, type excel /safe and press Enter. This starts Excel without add-ins. Does it work? Then the problem is an add-in.

Go to File → Options → Add-ins. At the bottom, select ”COM Add-ins” and click ”Go”. Untick all add-ins and try again.

Step 2: Repair the file

Open Excel (blank). Go to File → Open. Browse to the file, but don’t click ”Open”. Click the small arrow next to the ”Open” button and choose Open and Repair.

Excel tries to fix the file automatically. If that doesn’t work, choose ”Extract Data” instead – you lose the formulas but keep the values.

Step 3: Check AutoSave and recovery

Go to File → Info → Manage Workbook → Recover Unsaved Workbooks. Excel automatically saves versions every 10 minutes (if AutoSave is on). You might find a newer version there.

Step 4: Open in Google Sheets or LibreOffice

As a last resort: upload the file to Google Sheets. It can sometimes open damaged files that Excel can’t handle. Then download it as Excel again.

Prevention tips

Save often with Ctrl+S. AutoSave is good but not perfect.
Use .xlsx instead of .xls. Newer formats are more stable.
Avoid extremely large files (over 50 MB). Split them into smaller files or use Power Query.
Update Excel regularly. Microsoft fixes bugs in its updates.

If the file still won’t open and contains critical data, our Excel help can rescue most of it. We have tools for advanced data recovery.

Problem 2: Formula doesn’t work or shows errors

Symptoms:

You type a formula but the result is wrong, the cell shows the formula instead of the value, or nothing happens at all.

Why it happens:

The most common causes are the wrong cell format, syntax errors in the formula, circular references, or the cell being formatted as text.

Step-by-step solution

Step 1: Check the cell format

Select the cell. Right-click → Format Cells. If it says ”Text” under ”Category”, change it to ”General”. Press Enter in the cell again to force Excel to recalculate.

Step 2: Check the syntax

Excel with Swedish regional settings uses a semicolon (;) instead of a comma (,) as the separator. Example:

=SUMMA(A1;A10)    ← Rätt
=SUM(A1,A10)      ← Fel (om du har svensk Excel)

Also check that parentheses are closed correctly. Excel highlights matching parentheses when you click in the formula.

Step 3: Use formula auditing

Go to the Formulas tab → Trace Precedents and Trace Dependents. Excel draws arrows showing which cells the formula uses. Does an arrow point to the wrong cell? Then you have found the error.

Step 4: Check the cell references

If you copied a formula and the result looks odd, the references may have shifted. Use $ for absolute references:

=$A$1    ← Låser både kolumn och rad
=$A1     ← Låser kolumn, rad kan ändras
=A$1     ← Låser rad, kolumn kan ändras

Step 5: Turn on automatic calculation

Go to Formulas → Calculation Options → Automatic. If it says ”Manual”, formulas only update when you press F9.

Common formulas that cause trouble

VLOOKUP: Don’t forget that the last argument (TRUE/FALSE) determines whether the lookup should be exact
IF: Check that you have as many ”if” as ”then” and ”else”
INDIRECT: Doesn’t work if you refer to closed files
DATE: Swedish Excel wants YYYY-MM-DD, or use DATE(year;month;day)

If the formula still doesn’t work, the problem may be more complex. Our Excel consultant service helps with advanced formulas and error analysis. Want to learn to build robust formulas from scratch? Check out our guide to Excel courses.

Problem 3: Data was sorted randomly

Symptoms:

You sorted column A, but the rest of the rows didn’t follow. Now the data is a jumble and no longer matches up.

Why it happens:

You selected only one column instead of the whole dataset. Excel sorted that column on its own and left the rest.

Step-by-step solution

Emergency fix: Undo immediately

Press Ctrl+Z right away. If you have already done other things since then, you can click the small arrow next to the undo button (top left) and undo several steps back.

If it’s too late for Ctrl+Z:

There is no easy solution unless you have a backup. You can try:

Go to File → Info → Versions and see if there is an auto-saved version from before the sort
If the file is on SharePoint/OneDrive: check the version history online
Check the recycle bin (Windows) or Time Machine (Mac) for older versions

How to sort correctly:

Method 1: Select the whole table

Click in a cell in your data
Press Ctrl+A (selects the whole table if it is contiguous)
Go to Data → Sort
Choose column and order
Click OK

Method 2: Use AutoFilter

Click in your data
Press Ctrl+Shift+L (turns on filters)
Click the small arrow in the column header
Choose the sort order

Method 3: Turn the data into a table

Select the data
Press Ctrl+T (create table)
Now you can never accidentally sort just one column – Excel always keeps the rows together

Prevention tips

Use tables (Ctrl+T) instead of ordinary ranges. They protect against this problem.
Be careful with your selection before you sort. Double-check that everything that belongs together is selected.
Save before major changes. Press Ctrl+S before you sort or delete data.

Problem 4: Pivot table doesn’t update

Symptoms:

You have changed the source data but the pivot table still shows old values. New rows don’t appear.

Why it happens:

Pivot tables don’t update automatically in Excel. You have to refresh them manually, or the pivot table is pointing to the wrong data source.

Step-by-step solution

Step 1: Refresh manually

Right-click anywhere in the pivot table → Refresh. Or press Alt+F5.

Want to refresh all pivot tables in the workbook at once? Go to Data → Refresh All or press Ctrl+Alt+F5.

Step 2: Check the data source

Right-click in the pivot table → PivotTable Options → the Data tab. Look at ”Change Data Source”.

If it says Blad1!$A$1:$D$100 but you have added rows up to row 150, the pivot table is too small. Click Change Data Source and select the whole range, or even better:

Step 3: Use a table as the source

Turn your raw data into a table (Ctrl+T). When you create the pivot table, point to the table name instead of a cell range. Now the pivot table grows automatically when you add rows to the table.

Step 4: Turn on auto-refresh on opening

Right-click in the pivot table → PivotTable Options → the Data tab. Tick ”Refresh data when opening the file”. Now the pivot table updates every time you open the file.

Step 5: Structured references (advanced)

If your data source is a table, you can use structured references:

=TabellNamn[Kolumn]

These update automatically as the table grows.

Power Pivot problems

If you use Power Pivot, the problem may be that the data model isn’t updating:

Go to Data → Queries & Connections
Right-click your query → Refresh
To automate: File → Options → Data → tick ”Refresh data when opening the file”

Prevention tips

Use tables as the data source. Always.
Document where the data comes from. Add a comment saying which range or table the pivot table uses.
Refresh before a presentation. Press Ctrl+Alt+F5 before you share the file.

Need to build advanced reports with automatic updates? Our Excel help can set up Power Query solutions that update with one click. If you handle larger datasets, you may also want to read about Power BI vs Excel to see which tool suits you best.

Problem 5: Excel is slow or shows ”Calculating…”

Symptoms:

Excel hangs, you see ”Calculating… (4 processors)” at the bottom, or it takes several seconds to type in a cell.

Why it happens:

Too many formulas, volatile functions (INDIRECT, RAND, NOW), large datasets, or many conditional formatting rules.

Step-by-step solution

Step 1: Temporarily switch to manual calculation

Go to Formulas → Calculation Options → Manual. Now Excel only calculates when you press F9. It isn’t a permanent solution but gives you time to work.

Step 2: Find and remove unused formulas

Press Ctrl+End. Does the cursor jump far away from your actual data? Then Excel thinks the file is larger than it is.

Delete empty rows and columns at the bottom/to the right. Select entire rows/columns (Shift+Space or Ctrl+Space) and press Ctrl+- to delete them.

Step 3: Replace volatile functions

These functions are recalculated on every change in the workbook:

INDIRECT → Use INDEX/MATCH instead
RAND/RANDBETWEEN → Calculate once, copy values
NOW/TODAY → Use only where needed, not in large ranges
OFFSET → Use structured table references instead

Step 4: Simplify formulas with helper columns

Instead of one mega-formula that does everything:

=OM(OCH(LETARAD(A1;Tabell;2;0)>1000;MÅNAD(B1)=1);"Ja";"Nej")

Split it into helper columns:

Kolumn C: =LETARAD(A1;Tabell;2;0)
Kolumn D: =MÅNAD(B1)
Kolumn E: =OM(OCH(C1>1000;D1=1);"Ja";"Nej")

Excel calculates faster with many simple formulas than with a few complex ones.

Step 5: Remove unnecessary charts and objects

Charts are recalculated every time data changes. If you have 20 charts pointing at the same data, it can make Excel slow.

Press Ctrl+G → Special → Objects → OK. This selects all objects. Check whether there are hundreds – then you can delete the ones you don’t need.

Step 6: Clean up conditional formatting

Go to Home → Conditional Formatting → Manage Rules. Check whether there are hundreds of rules. Delete duplicate or unnecessary ones.

Step 7: Save as a binary format (.xlsb)

Go to File → Save As → choose ”Excel Binary Workbook (.xlsb)”. This can reduce the file size by 50% and speed up calculations.

Prevention tips

Avoid INDIRECT and OFFSET if possible
Use tables and structured references instead of entire columns (A:A)
Turn off automatic calculation temporarily when building complex models
Split large files into smaller parts or use Power Query for data import

If the file is still slow, it may be time for professional optimisation. We can analyse bottlenecks and rebuild the solution. For recurring workflows you can also consider automating Excel with VBA – it can save 20+ hours per month.

Problem 6: #REF!, #N/A!, #DIV/0! error messages

Symptoms:

Instead of results you see error codes in the cells.

Why it happens:

Different errors mean different things. Here is the guide.

Error messages and solutions

#REF! – The reference doesn’t exist

Cause: You have deleted a row, column or sheet that the formula pointed to.

Solution:

Press Ctrl+Z if you just deleted something
Click in the cell and look at the formula. Do you see #REF! where a cell reference should be? Change it to the correct cell
If you have many: use Find and Replace (Ctrl+H) to fix them all at once

Prevent it: Use structured table references or named ranges instead of hard-coded references.

#N/A – Not found

Cause: VLOOKUP, HLOOKUP or INDEX/MATCH didn’t find the value.

Solution:

Check that the lookup value actually exists in the table
Check that there are no extra spaces. Use TRIM() to clean them up
Make sure the data type matches (text vs. number)
If it is OK for the value not to exist: use IFERROR(VLOOKUP(...); "Not found")

#DIV/0! – Division by zero

Cause: You are dividing a number by 0 or an empty cell.

Solution:

=OM(B1=0; 0; A1/B1)    ← Returnerar 0 om nämnaren är noll
=OMFEL(A1/B1; 0)        ← Enklare variant

#VALUE! – Wrong value type

Cause: You are trying to calculate with text, or the formula receives the wrong type of argument.

Solution:

Check that all cells contain the right data type
If numbers are stored as text: see Problem 8 below
Use VALUE() to convert text to numbers

#NAME? – Unknown name

Cause: Excel doesn’t recognise the function name or the range.

Solution:

Swedish Excel: use SUMMA instead of SUM, OM instead of IF, etc.
Check that you have spelled the range name correctly
If you copied from English Excel: find and replace function names

#NUM! – Invalid number

Cause: The number is too large, too small, or the formula gives an invalid result (e.g. the square root of a negative number).

Solution:

Check the input values
Use IF() to catch invalid values before the calculation

#NULL! – Ranges don’t intersect

Cause: You are using a space incorrectly in a formula (a space means ”intersection” in Excel).

Solution:

=SUMMA(A1 A10)    ← Fel (mellanslag betyder snitt)
=SUMMA(A1:A10)    ← Rätt (kolon betyder intervall)

Find all errors quickly

Press Ctrl+G → Special → Formulas → tick only ”Errors”. Now all error cells are selected. You can then delete or fix them all at once.

Problem 7: Can’t paste from another program

Symptoms:

You copy from Word, a browser or a PDF but Excel formats it wrongly, puts everything in one cell, or pastes strange characters.

Why it happens:

Excel tries to preserve the formatting from the source, but that doesn’t always work.

Step-by-step solution

Step 1: Use Paste Special

After you have pasted (or instead of an ordinary paste):

Right-click → Paste Special
Choose ”Text” or ”Unicode Text”
Click OK

Now you get just the text without formatting.

Keyboard shortcut: Ctrl+Alt+V → T → Enter.

Step 2: Paste into Notepad first

Paste into Notepad (Windows) or TextEdit (Mac) first. That removes all formatting. Then copy from Notepad to Excel.

Step 3: Clear formatting after pasting

Select the cells → go to Home → Clear (the small eraser icon) → Clear Formats.

Step 4: Text to Columns

If all the data ended up in one column although you want it split up:

Select the column
Go to Data → Text to Columns
Choose ”Delimited” and click Next
Choose the delimiter (tab, comma, semicolon, space)
Click Finish

Step 5: Find and replace strange characters

If you see strange symbols:

Press Ctrl+H (Find and Replace)
In ”Find what”: paste in the strange character
In ”Replace with”: leave empty or type what it should be
Click ”Replace All”

Prevention tips

Always use Paste Special when you paste from external sources
Copy values instead of cells if you only need the content
Import data with Power Query if it is recurring work – then you can save the transformation steps

Problem 8: Numbers are stored as text

Symptoms:

Numbers are left-aligned instead of right-aligned. Formulas don’t work on them. A small green triangle in the top-left corner.

Why it happens:

Often when you import from CSV, copy from the web, or if the cell was formatted as text before you typed the number.

Step-by-step solution

Method 1: The VALUE() function

Use =VALUE(A1) in a helper column. It converts text to numbers. Then copy the result and paste it as values over the original.

Method 2: The Find and Replace trick (fastest)

Select the column
Press Ctrl+H
Find what: (leave empty)
Replace with: (leave empty)
Click ”Replace All”

Sounds odd but it works – Excel is forced to re-evaluate the cells.

Alternative variant:

Type 1 in an empty cell
Copy it (Ctrl+C)
Select the text-numbers
Right-click → Paste Special → Multiply
Click OK

Now the text has been converted to numbers.

Method 3: Format Cells

Select the cells
Right-click → Format Cells
Choose ”Number” or ”General”
Click OK
Important: Press F2 in each cell and then Enter to force Excel to convert

Method 4: Text to Columns (for many cells)

Select the column
Go to Data → Text to Columns
Click ”Next” twice
In the last step: choose ”General” under column data format
Click ”Finish”

Prevention tips

Format columns as numbers before you paste
Use Power Query for import – there you can specify the data type before the data is loaded
Be careful with CSV files – open them via Data → From Text/CSV instead of double-clicking

Problem 9: The printout looks completely wrong

Symptoms:

Data prints on 10 pages although everything should fit on one, columns are cut off, or the printout is tiny.

Why it happens:

Excel guesses how you want to print, and often guesses wrong.

Step-by-step solution

Step 1: Use Print Preview

Go to File → Print (or press Ctrl+P). You see exactly how it will look. Adjust before you print.

Step 2: Set the print area

Select exactly what you want to print. Go to Page Layout → Print Area → Set Print Area.

Now only the selection is printed.

Step 3: Adjust the page layout

In the print preview, click Page Setup at the bottom. Here you can set:

Orientation: Portrait or landscape
Scaling: ”Fit Sheet on One Page” (forces everything onto one page)
Margins: Make them smaller if the content almost fits
Centre: Centre horizontally and vertically for a neater printout

Step 4: Page breaks

Go to View → Page Break Preview. Now you see blue lines where Excel will break pages. Drag the lines to adjust where the breaks should be.

Step 5: Print titles on every page

If you have a long table:

Go to Page Layout → Print Titles
Click in ”Rows to repeat at top”
Select the header row (e.g. $1:$1)

Now the headers are printed on every page.

Step 6: Export as PDF instead

Go to File → Save As → choose ”PDF”. You get full control and the recipient sees exactly what you see, regardless of their Excel version.

Prevention tips

Check the printout before meetings – always
Use PDF for sharing – avoids all printing problems
Save print settings – once you have it right, save the file so the settings are kept

Problem 10: Formulas turn into plain text

Symptoms:

You type =SUM(A1:A10) but the cell shows just that – the formula text, not the result.

Why it happens:

The cell is formatted as text, or you have turned on the ”Show Formulas” mode.

Step-by-step solution

Step 1: Check the cell format

Select the cell → right-click → Format Cells. If it says ”Text”, change it to ”General”. Then press F2 (edit the cell) and Enter. Now the formula is calculated.

Step 2: Check whether ”Show Formulas” is on

Go to Formulas → Show Formulas (or press Ctrl+ö in Swedish Excel / Ctrl+` in English). If the button is pressed in, click it again to turn it off.

Step 3: Remove spaces before the equals sign

If you accidentally typed a space before =, Excel interprets it as text:

 =SUMMA(A1:A10)    ← Text (mellanslag före =)
=SUMMA(A1:A10)     ← Formel

Remove the space.

Step 4: Turn on automatic calculation

Formulas → Calculation Options → Automatic. Then press F9 to force a recalculation.

Step 5: Bulk fix for many cells

If you have many cells:

Select them all
Ctrl+H (Find and Replace)
Find what: = (an equals sign)
Replace with: = (the same equals sign)
Click ”Replace All”

This forces Excel to re-evaluate the cells.

Prevention tips

Check the cell format before you write formulas
Don’t use spaces before formulas
If you want to show a formula as text, type an apostrophe first: '=SUM(A1:A10)

Bonus: The quick fixes – Tips that solve 90% of problems

Here are seven magic fixes that solve most Excel problems:

1. Save, close, reopen

Press Ctrl+S, close the file, open it again. Excel ”resets” many internal states.

2. Undo with Ctrl+Z

Did something go wrong? Press Ctrl+Z right away. Don’t forget that you can undo several steps – click the small arrow next to the undo button.

3. Restart Excel completely

Not just the file – close the whole program and start again. Clear the memory.

4. Clear all formatting

Select the cells → Home → Clear → Clear Formats. Sometimes it’s the formatting that causes trouble.

5. Check automatic calculation

Formulas → Calculation Options → Automatic. This problem shows up more often than you think.

6. Press F9 to force recalculation

If a formula doesn’t update, press F9. It recalculates all formulas in the workbook.

7. Right-click and repair (files)

For files that won’t open: File → Open → select file → click the arrow on the ”Open” button → Open and Repair.

When nothing works

If you have tried everything above and the problem remains:

Create a new file and copy the data there (not the whole sheet – copy the cells)
Check whether the problem exists in other files – then it may be an Excel setting or a corrupt installation
Update Excel via Windows Update or Microsoft Office updates
Repair the Office installation via Control Panel → Programs → Microsoft Office → Change → Repair

Still having problems? Here is where you can get help

You are not alone. These problems are frustrating, and sometimes the solution isn’t obvious even to experienced users.

When you should seek professional help

Contact Excel Department if:

The file contains critical data and you don’t dare experiment
The problem keeps coming back even though you have fixed it
You need to automate the solution so the problem doesn’t occur again
Formulas or macros have broken and you don’t know why
Excel crashes consistently during specific actions

What we can help with

At Excel Department we solve:

Data recovery from damaged files
Formula optimisation for slow files
Automation with VBA/Power Query to avoid manual errors
Custom solutions for recurring problems
Training so the problem doesn’t happen again

First step: Quick diagnosis (15 min)

Call 010-264 20 20 or email info@exceldepartment.se for a quick assessment. We can often point you in the right direction straight away – sometimes the solution is simpler than you think.

If the problem is complex, we give you a time estimate and cost before we start. No surprises.

Resources for further learning

Excel help – Urgent support and problem solving
Excel course – Learn to avoid the problems from the start
Excel consultant – For complex solutions and automations

You can handle most things yourself with the right tools. But when time is critical or data is too important to risk – we are here.

Excel Department

Slottsbacken 8, Stockholm

Phone: +46-10-264-20-20 / 010-264 20 20

Email: info@exceldepartment.se

Need help with this? Our Excel consultants will help you – the first consultation is free of charge.

Book a free call or call 010-264 20 20.

The 10 Most Common Excel Problems and How to Solve Them