Which Excel Format to Save In: Practical Guide
Learn which excel format to save in for sharing and archiving. Compare .xlsx, .xlsb, .xls, and .csv, with tips to preserve formulas and data integrity.
By the end of this guide, you'll know which excel format to save in for different needs, from collaboration to long-term archiving. You'll learn when to save as .xlsx, .xlsb, .csv, or .xls, and how features like macros or data validation affect compatibility. You'll also see practical checks to avoid data loss.
Why the right format matters in Excel
Choosing the correct format affects how your data is preserved, shared, and reused. The question which excel format to save in matters beyond just file extensions—it determines whether formulas survive edits, whether macros run, and how portable your workbook is across platforms. According to XLS Library, picking the right format also helps protect sensitive data by avoiding unnecessary exposure when exporting to plain text or CSV. In practice, this choice influences collaboration speed, audit trails, and long-term accessibility. By choosing thoughtfully, you reduce rewrite time and ensure teams see the same numbers, formulas, and structure every time the file is opened.
The most common formats and their tradeoffs
Excel workbooks can be saved in several formats, each with pros and cons. The default .xlsx preserves formulas, formatting, charts, and connections while keeping files reasonably small and widely compatible. .xlsb is a binary format that can dramatically reduce file size and open times for large datasets, but may sacrifice some interoperability. CSV leaves behind all formatting and multiple worksheets, making it ideal for data exchange but unsuitable for formulas or macros. .xls is legacy-compatible but limited in features compared to modern formats. Understanding these tradeoffs helps you pick the right option for a given task.
Save as .xlsx: the default and reasons
The .xlsx format is the standard for modern Excel use. It supports all built-in features like tables, data validation, conditional formatting, and rich chart types. It also facilitates collaboration through real-time editing in supported environments and generally offers better compatibility across platforms, including Excel Online. When your audience uses mixed devices or needs future-proofing, .xlsx is usually the safest starting point. If you routinely share files with users who rely on older Excel versions, you may still want to confirm compatibility before sending.
Macro considerations: .xlsm vs .xlsx
If your workbook contains VBA macros, saving as .xlsx will strip those macros and could break automation. The .xlsm format preserves macros while still offering most of the modern features of .xlsx. Use .xlsm whenever your data processing relies on scripts, automated tasks, or user-defined functions. Be mindful of macro security settings in recipients’ environments, as macro-enabled files can trigger security warnings or be blocked by default in some organizations.
When to save as .xlsb
The .xlsb binary format can drastically reduce file size and improve performance for very large workbooks or data models. It is especially helpful when a workbook contains many complex formulas, slicers, or heavy data connections. However, .xlsb has limited compatibility with some non-Microsoft tools and may not always preserve features like certain add-ins or external data connections in all environments. If performance is critical and you control the environment, .xlsb is worth considering—but verify that colleagues can open and work with the file.
Interchange formats: .csv and .txt
CSV and TXT formats are plain-text exports that do not support multiple worksheets, cell formatting, or formulas. They are excellent for data exchange between systems that don’t understand Excel’s native formats. If you export to CSV, expect loss of formulas, formatting, and named ranges. For sections of data that need to be re-imported cleanly, CSV is ideal, but you’ll need to reapply formulas and formatting on import.
Older compatibility: .xls 97-2003
The .xls format is legacy-compatible with very old Excel versions. It has strict limits (65,536 rows, 256 columns) and supports fewer features. If you must share with users on legacy software, saving as .xls is sometimes unavoidable. Be aware, though, that newer features like modern charts, Power Query, or dynamic arrays won’t be available in this format. When possible, provide a modern version as well for advanced users.
Checking compatibility before sharing
Before sending a file, run Excel’s Compatibility Checker to spot potential issues when opened on older versions. Go to File > Info > Check for Issues > Check Compatibility. The checker highlights functions, features, and formatting that may not translate well. This proactive step helps you avoid surprises and keeps your data intact across environments.
Practical scenarios and examples
Consider a budget workbook with multiple sheets, macros for monthly refresh, and rich charts. If colleagues use Excel 2016 or newer and you don’t need cross-platform macros, save as .xlsm to preserve automation. If your team simply consumes the data in dashboards, .xlsx is usually sufficient. For an exported data dump that will be loaded into another system, export as .csv. For very large datasets, .xlsb can speed up file operations while keeping macros intact.
How to convert safely and preserve data
Always start from a copy of the original workbook. Use Save As to choose the target format, then thoroughly test critical features: formulas, charts, conditional formatting, macros, and external connections. Open the new file on a different machine or Excel version if possible to verify behavior. Keep a changelog describing why and when you saved in a particular format to aid future maintenance.
Best practices for teams and governance
Establish a team-wide policy for which formats to use in common workflows (shared workbooks, data exports, and automation). Document when to prefer .xlsx for daily work, when to switch to .xlsm for macros, and how to handle data interchange via CSV. Regularly review compatibility outcomes using the built-in checker and maintain version-controlled backups of original workbooks.
Authority sources and further reading
For deeper guidance on Excel formats and compatibility, consult official documentation and reputable education resources. Microsoft’s support and docs pages cover format nuances, limitations, and best practices, while broad Excel tutorials can help you apply these concepts in real scenarios. See the sources listed below for more detail.
Tools & Materials
- Computer with Excel installed(Office 365 or Office 2021+ recommended)
- Original workbook file(Source file to save from)
- Reliable backup storage(USB drive or cloud backup)
- Recipient environment notes(List of Excel versions used by teammates)
- Macros awareness(If workbook contains VBA, plan for .xlsm)
Steps
Estimated time: 15-25 minutes
- 1
Assess the workbook’s purpose
Identify whether you need formulas, macros, charts, or data connections. This determines which format will best preserve functionality and accessibility for your audience.
Tip: Document the primary use-case before choosing a format. - 2
Inventory workbook features
Note macros, data connections, external links, and any features that may not survive a format change.
Tip: If macros exist, expect .xlsx to strip them; plan for .xlsm if required. - 3
Consider recipient environments
Ask about Excel versions and platforms in use. If recipients are on older software, compatibility becomes a priority.
Tip: When in doubt, check with stakeholders first. - 4
Choose a primary format
For most users, start with .xlsx. If macros exist, switch to .xlsm. For legacy recipients, consider .xls or provide an alternate delivery.
Tip: Avoid switching formats mid-project unless necessary. - 5
Save a test copy and verify features
Save a copy in the chosen format and reopen it to test formulas, charts, and any automation.
Tip: Use Save As to prevent overwriting originals. - 6
Run a compatibility check
Use Excel’s Check Compatibility to flag issues that may affect other versions.
Tip: Address flagged items before sharing. - 7
Document and share the decision
Record the chosen format and rationale, then share guidelines with your team.
Tip: Maintain a quick-reference policy for future work.
People Also Ask
What format should I save in for sharing with colleagues who use older versions of Excel?
If your colleagues are on older Excel versions, consider saving a COPY in .xls for compatibility, or provide a separate export in CSV for data without features. Always verify with a compatibility check.
For older versions, save as .xls or provide a data-only CSV export after confirming the recipients' capabilities.
Will saving as CSV preserve formulas?
CSV files store plain data only. Formulas are not preserved; only their resulting values appear as text in the cells when imported.
No. CSV removes formulas; it only keeps the resulting values.
Can I save a workbook with macros as CSV or XLSX?
Macros cannot be saved in CSV or standard XLSX. Save as .xlsm to preserve macros, or extract macro functionality into a separate automation file.
Macros don't survive CSV or plain XLSX; use XLSM if you need automation.
What is the main difference between .xlsx and .xlsb?
xlsx is the standard open XML format with broad compatibility. xlsb is a binary format that saves space and speeds up loading for large files, but may have stricter compatibility in some environments.
xlsx is the default; xlsb saves space and speeds things up but isn’t always as widely compatible.
How can I ensure a new format won’t break formulas or data connections?
Test critical sheets by saving a copy in the target format and rechecking formulas, named ranges, and external connections. Use the Compatibility Checker to spot issues.
Test the saved file by reopening and verifying formulas and connections; use the built-in compatibility checker.
Is there a scenario where I should always use CSV?
CSV is ideal for data exchange between systems that don’t understand Excel features. Do not use it when you need formulas, charts, or multiple worksheets.
Use CSV for data export only when you don’t need formulas or formatting.
Should I keep multiple versions of the same workbook in different formats?
If collaboration requires, maintain a primary version in .xlsx and create format-specific copies as needed for compatibility or automation.
Yes—keep one primary format and add copies for specific needs to avoid confusion.
Watch Video
The Essentials
- Choose format based on need (formulas, macros, or data interchange).
- Preserve functionality with .xlsx or .xlsm when appropriate.
- Test after saving and verify compatibility with recipients.
- Use compatibility checks and document decisions for teams.
