Excel Compatibility: Solve Version and File Format Problems

Your Excel file works perfectly on your computer. You send it to a colleague. It opens, but formulas show #NAME?, charts are missing, or the file is read-only. Compatibility problems.

This guide shows you how to identify and solve compatibility problems between Excel versions, operating systems and file formats. You will learn to check compatibility before sharing and to fix problems when they occur.

For general troubleshooting, see the Excel troubleshooting guide. For other problems, read common Excel problems.

Excel versions and functions

Version history

Version	Release year	Key features	Limitations
Excel 2003	2003	Basic functions	Max 65,536 rows, 256 columns, .xls format
Excel 2007	2007	Ribbon interface, .xlsx	Max 1,048,576 rows, 16,384 columns
Excel 2010	2010	Sparklines, slicers	—
Excel 2013	2013	Power Query, Power Pivot	—
Excel 2016	2016	Power BI integration	—
Excel 2019	2019	CONCAT, IFS	—
Excel 365	Ongoing	Dynamic arrays, XLOOKUP, FILTER	Functions are added continuously

Problem: Functions that do not exist in older versions

Excel 365-exclusive functions: – XLOOKUP – FILTER – UNIQUE – SORT – SEQUENCE – RANDARRAY

What happens: If you use =XLOOKUP(...) in Excel 365 and open the file in Excel 2016 → #NAME?

Solution: Use older equivalents

XLOOKUP → INDEX/MATCH

Excel 365:

=XLOOKUP(E2;A:A;B:B;"Ej funnen")

Compatible with all versions:

=OMFEL(INDEX(B:B;MATCH(E2;A:A;0));"Ej funnen")

FILTER → Advanced filter or pivot table

Excel 365:

=FILTER(A2:C100;B2:B100="Syd")

Compatible: Use Data → Advanced Filter (manually) or a pivot table.

Check compatibility before sharing

Step 1: Use the compatibility checker

File → Info
Check for Issues → Check Compatibility
Select the versions to check against
Excel lists all problems

Example results: – “Some formulas in this workbook use functions that are not available in earlier versions of Excel” – “This workbook contains data in cells outside the row and column limits of earlier versions” – “Some charts contain effects that are not supported”

Step 2: Fix the problems

For each problem: – Click “Find” to jump to the problem – Fix it manually or accept the limitation

Save in compatibility mode

File → Save As → Select “Excel 97-2003 (.xls)”

Advantages: – Opens in all Excel versions

Disadvantages: – Max 65,536 rows – Max 256 columns – Newer functions do not work – Larger file size

Recommendation: Use .xlsx and avoid new functions instead.

File formats: .xls vs .xlsx vs .xlsm vs .xlsb

Format overview

Format	Year	Description	Use
.xls	1997-2003	Binary format	Legacy compatibility
.xlsx	2007+	XML-based, compressed	Standard for modern files
.xlsm	2007+	.xlsx with macros	Files with VBA code
.xlsb	2007+	Binary, compressed	Large files, faster
.csv	—	Comma-separated values	Data exchange, no formatting

Problem: “Excel cannot open the file because the file extension is not valid”

Cause: The file extension does not match the actual content.

Example: The file is named “data.xlsx” but is actually in .xls format.

Solution: 1. Rename the file from .xlsx to .xls 2. Try to open it 3. If it works: Save as .xlsx again

Problem: Macros do not work

Cause: The file was saved as .xlsx (macros are not allowed).

Solution: Save as .xlsm instead: 1. File → Save As 2. Select “Excel Macro-Enabled Workbook (.xlsm)”

When to use which format

Use .xlsx if: – Standard file without macros – It will be shared with others – It will be stored long term

Use .xlsm if: – It contains VBA code/macros

Use .xlsb if: – The file is over 10 MB – You need fast opening/saving – It does not need to be shared outside the organisation

Use .xls if: – It must open in Excel 2003 or older – Otherwise: Avoid

Use .csv if: – Exporting to other systems – Database import – No formatting/formulas needed

Windows vs Mac

Excel on Mac differs from the Windows version.

Functional differences

Missing on Mac: – Some add-ins and ActiveX controls – The Visual Basic Editor has limitations – Power Query (limited in older Mac versions) – Some keyboard shortcuts differ

Keyboard shortcuts Mac vs Windows:

Function	Windows	Mac
Copy	Ctrl+C	Cmd+C
Paste	Ctrl+V	Cmd+V
Save	Ctrl+S	Cmd+S
Find	Ctrl+F	Cmd+F
Open VBA	Alt+F11	Option+F11 (limited)

Problem: VBA does not work on Mac

Why: Some Windows API calls do not work on Mac.

Example:

Declare Function GetUserName Lib "advapi32.dll" ...

This only works on Windows.

Solution: Use conditional compilation:

#If Mac Then
    ' Mac-version av kod
#Else
    ' Windows-version av kod
#End If

Problem: File paths

Windows:

filväg = "C:\Users\Användare\Dokument\fil.xlsx"

Mac:

filväg = "/Users/Användare/Documents/fil.xlsx"

Solution: Use relative paths or detect the OS:

#If Mac Then
    filväg = "/Users/" & Environ("USER") & "/Documents/fil.xlsx"
#Else
    filväg = "C:\Users\" & Environ("USERNAME") & "\Documents\fil.xlsx"
#End If

Problem: The file opens as read-only

Cause 1: Open on another computer

The file is open by someone else (on a network drive or SharePoint).

Solution: – Wait until the other user closes it – Or open it read-only and save a copy

Cause 2: File marked as read-only

Windows: 1. Right-click the file → Properties 2. Clear “Read-only” 3. OK

Mac: 1. Right-click the file → Get Info 2. Under “Sharing & Permissions” → Unlock 3. Change the permission to “Read & Write”

Cause 3: OneDrive/SharePoint sync error

Solution: 1. Open the file directly in a web browser (Office Online) 2. Or: Right-click the OneDrive icon → Settings → Unlink and link again

Cause 4: Network permissions

Solution: Contact the IT department to have the permissions changed.

Problem: Formatting looks different

Cause: Missing fonts

If the file uses fonts that are not installed on the computer, they are replaced.

Solution: – Use standard fonts (Calibri, Arial, Times New Roman) – Or: Embed fonts (File → Options → Save → “Embed fonts in the file”)

Warning: Embedding fonts increases the file size and may breach licence terms.

Cause: Different screen resolution

The file was created on a 4K screen and opened on a laptop with a lower resolution.

Solution: – Use percentage scaling (Page Layout → Scale) – Avoid pixel-based positions

Cause: Different Excel versions render differently

Solution: – Export as PDF for an exact appearance – Or: Accept small differences

Best practices for compatibility

1. Use standard functions

Avoid Excel 365-exclusive functions if the file is shared with older versions.

Check function compatibility: – Microsoft’s documentation lists which version each function requires

2. Test on the target platform

Before an important presentation or sharing: – Open the file on the same type of computer/Excel version as the recipient has – Or: Ask the recipient to test and give feedback

3. Always save as .xlsx

.xls is obsolete. If the recipient has Excel 2007 or later (most do), use .xlsx.

4. Document the Excel version

Add a comment in the workbook: “Requires Excel 2016 or later” or “Compatible with all versions”

5. Consider PDF for final documents

If the recipient only needs to read (not edit): – File → Save As → PDF – Guarantees the same appearance everywhere

6. Use cloud-based solutions

OneDrive/SharePoint + Excel Online: – Same version for everyone – Collaborate in real time – No compatibility problems

When you need professional help

Contact Excel Department if:

Large-scale workbook compatibility (100+ files)
VBA code that must work on both Windows and Mac
Migration from old Excel versions to Excel 365
Company-wide compatibility standards
Integration between Excel and other systems (different versions)

We offer: – Compatibility analysis of workbooks – VBA rewrites for platform-independent code – Migration strategy for organisations – Training in version differences

Call 010-264 20 20 or email info@exceldepartment.se.

Summary: Your compatibility checklist

Before sharing a file: 1. [ ] Check compatibility (File → Info → Check Compatibility) 2. [ ] Use standard functions (avoid XLOOKUP, FILTER if the recipient has older Excel) 3. [ ] Save as .xlsx (or .xlsm if macros) 4. [ ] Use standard fonts (Calibri, Arial) 5. [ ] Test on the target platform if possible

If the recipient has problems: 6. [ ] Ask them to check their Excel version (File → Account) 7. [ ] Send as PDF if read-only is enough 8. [ ] Consider Excel Online (web version) for guaranteed compatibility

For VBA: 9. [ ] Use conditional compilation (#If Mac Then) if needed 10. [ ] Avoid Windows API calls if the file is used on Mac 11. [ ] Test on both Windows and Mac