Excel File Formats: XLSX, XLS, XLSB & CSV Explained
The file came back from the client with a note: "The macros aren't working." I'd sent an XLSX. The macros were gone, silently stripped the moment I'd saved in the wrong format, and I had no idea until it was too late. That's the thing about Excel file formats: the wrong choice doesn't always announce itself. Sometimes it just quietly removes everything you built. This guide gives you a decision framework (not a glossary) so you know exactly what to save before you hit that button.
If you're newer to managing workbooks, the Excel for Beginners starter guide is worth a read first. But if you've got the basics down and just need to know which format to pick, you're in the right place.
The Four Formats That Actually Matter: XLSX, XLS, XLSB, and CSV
There are technically 24 file formats Excel supports. You need to care about four of them.
XLSX is the default since Excel 2007: an open XML format, widely compatible, no macro support. XLS is the older binary format from pre-2007 Office. It works, but it comes with real baggage (more on that below). XLSB is Excel's binary workbook format, offering the same features as XLSX but significantly faster and smaller on large files. CSV strips everything down to raw data: no formatting, no formulas, no multiple sheets. Just text, commas, and a surprising number of ways things can go wrong.
If your workflow touches Google Sheets, Power BI, Python's pandas library, or any API, your format choice isn't just about Excel anymore. Those tools have opinions about what they'll accept.
|
| The Save As dialog is where most format mistakes happen. The dropdown sits just below the file name field. |
Step 1: Run This Decision Check Before You Hit Save
Now that you know what each format is, the only question that matters is: which one fits your actual situation? Here's how to think through it.
Start here: does your workbook need to talk to something outside Excel? If you're exporting data to Python pandas, feeding a Power BI dashboard, or handing off to a team running Google Sheets, CSV is almost always what the other tool expects. It's not glamorous, but it's universally readable. The tradeoff is real (you lose formulas, formatting, and every sheet except the active one) but for data pipelines, that's usually acceptable.
If you're staying inside the Microsoft Office ecosystem and just need a reliable, shareable workbook with no macros, XLSX is the right call. It's compatible with Excel 2007 and later, opens in Excel Online, and most modern tools handle it without complaint. Worth noting: Excel Online's behavior with XLSX isn't always identical to the desktop version. I've run into date formatting inconsistencies across Windows 11, MacBook Air M1, and Excel Online that I still can't fully explain after multiple tests.
If You Need Macros: XLSM Is the Only Safe Answer
This is the mistake I made with that client file. If your workbook contains VBA macros, you must save as XLSM (macro-enabled workbook). Saving as XLSX silently deletes all your VBA. Excel will warn you, but the warning is easy to dismiss when you're moving fast. There's no undo that brings those macros back after you've closed the file.
If you're sharing an XLSM with someone on Excel 2016 or 2019, test it on their environment first. Certain macro features don't behave identically across older versions.
If File Size or Speed Is the Problem: XLSB Over XLSX Every Time
XLSX files are zipped XML packages. Every time Excel opens or saves one, it's parsing XML, and on large workbooks that overhead adds up. XLSB skips the XML translation entirely, which is why it can shrink file sizes by 10–50% and open noticeably faster. It's the format for the heavy reporting files where workbooks hit tens of thousands of rows of cleaned export data.
The catch: XLSB is a binary format. Older Power BI connectors and Python pandas don't always read it cleanly. It's not the format for sharing outside Excel. It's the format for keeping things internal when XLSX is slowing you down.
As for XLS: use it only if someone sends you a file in that format and you need to return it unchanged. It's a legacy format with a hard row limit of 65,536 (versus XLSX's 1,048,576) and it's the format most associated with macro-based malware delivery. Organizations with stricter IT policies will block XLS attachments entirely. There's rarely a reason to create a new XLS file.
Step 2: Save in the Right Format and Confirm It Actually Worked
Once you've made your format decision, saving is the easy part. The confirmation step is where most people check out too early.
- Go to File → Save As (or press F12 on Windows).
- In the Save As dialog, click the format dropdown below the file name.
- Select your target format: XLSX, XLSM, XLSB, or CSV.
- If Excel shows a warning dialog, read it before clicking. "This format does not support workbooks with multiple sheets" means you're saving CSV and will lose all but the active sheet. "VBA project will be lost" means you're about to delete your macros.
- After saving, check the file in your folder or the title bar and confirm the extension matches what you chose.
That last step sounds obvious. I still do it every time, because a misread warning dialog once cost me twenty minutes of rebuilt formulas in front of eight people waiting on a report.
For more on the broader workflow around saving, the guide on saving Excel files properly covers version history, AutoSave behavior, and a few things the standard Save As flow won't tell you. And if you're thinking about how this fits into your file organization system, Excel file management and sharing is worth reading alongside this one.
Common Mistakes When Choosing an Excel File Format (Including the One That Quietly Corrupts Your Data)
Four mistakes show up constantly, and three of them are silent. You won't know they happened until someone else opens the file.
Saving a macro workbook as XLSX. Already covered above, but it bears repeating: Excel warns you, the warning is easy to click past, and your macros are gone. The fix is to make XLSM your default save format the moment any VBA goes into a workbook.
Defaulting to XLS out of habit. Some users inherited XLS files years ago and kept saving in that format. Beyond the macro-malware association, XLS has hard row limits that will eventually bite a growing dataset.
Sending XLSB to tools that can't read it. If a colleague opens your XLSB in Google Sheets, they'll likely hit an error. Python pandas requires a specific engine to handle XLSB, and not every environment has it configured. Use XLSB internally; convert to XLSX before sharing outside your team.
Losing data precision in CSV. CSV looks safe because it's just text. It's not. Leading zeros vanish: ZIP codes, employee IDs, product codes. Dates reformat unpredictably depending on whether the file is opened on Mac or Windows. Encoding issues between UTF-8 and ANSI can corrupt characters that aren't standard ASCII. If your CSV contains dates or IDs with leading zeros, verify the output before sending it anywhere.
CSV date behavior is the most inconsistently handled thing in Excel's entire feature set, based on testing across platforms. A rogue space character once took forty-five minutes to find in a 3,000-row export. It was in row two.
Frequently Asked Questions
What is the difference between XLS and XLSX?
XLS is the legacy binary format used before Excel 2007, with a row limit of 65,536 and a higher security risk due to its association with macro-based malware. XLSX is the modern open XML format with a row limit of over one million, broader compatibility, and no macro support, which is also why it's safer to receive from unknown senders.
What Excel format supports macros?
XLSM is the only standard Excel format that preserves VBA macros. Saving a macro-enabled workbook as XLSX will silently strip all VBA code. Excel warns you, but it's easy to click past the warning by mistake.
Why is XLSB faster than XLSX for large files?
XLSX stores data as zipped XML, which Excel has to parse every time you open or save. XLSB stores data in a binary format that skips that translation step entirely, which is why it opens faster and can reduce file size by 10–50% on large workbooks. The tradeoff is that fewer external tools can read it reliably.
What Excel file format works best with Python pandas?
CSV is the safest choice for pandas: it reads cleanly with no special dependencies. XLSX also works well using the openpyxl engine. XLSB requires the pyxlsb engine, which isn't always available in every environment, so avoid it for data pipelines unless you control the infrastructure.
Join the conversation