Most Excel users never think carefully about how they save and share files — until something goes wrong. This lesson teaches you the right file format for every situation, how AutoSave and version history actually work, and how to build a file organization system that protects your work.
You've spent three hours building a beautiful sales analysis workbook. Formulas are humming, the layout is clean, and your manager is waiting for the file. Then your laptop freezes. Or a colleague accidentally overwrites your pivot table. Or the client opens your .xlsx file and suddenly all your macros are gone. Any of these scenarios will ruin your afternoon — and they're all entirely preventable once you understand how Excel actually handles saving, file formats, and version history.
This isn't a topic most tutorials cover seriously, and that's a shame. How you save and share a workbook is as consequential as the data inside it. The wrong file format strips out your work. The wrong sharing method creates five conflicting copies. The absence of a version history strategy means one bad save is permanent. For data professionals, these aren't minor housekeeping concerns — they're fundamental workflow competencies.
By the end of this lesson, you'll know exactly which file format to use in every situation, how to configure AutoSave so it protects rather than frustrates you, how to navigate Excel's version history to rescue earlier states of a file, and how to share workbooks in a way that keeps everyone working from the same source of truth.
What you'll learn:
This lesson assumes you're comfortable opening, navigating, and editing Excel workbooks. You should understand what worksheets, cells, and formulas are. If you want to brush up on how workbook structure works before continuing, the lesson on Understanding Excel Workbook Structure: Worksheets, Cells, Rows, and Columns for Data Professionals covers those fundamentals thoroughly.
Before we talk about how to save, we need to talk about what you're saving. Excel doesn't have one file format — it has several, and they're not interchangeable. Choosing the wrong one is one of the most common causes of lost work and broken files in professional settings.
The .xlsx format is the standard Excel workbook format introduced with Excel 2007. It stores everything you'd normally build: data, formulas, formatting, charts, tables, and pivot tables. When in doubt, this is the format you should use.
Under the hood, .xlsx is actually a compressed ZIP archive containing a collection of XML files. That's why file corruption is rarer than it used to be — if one component breaks, the others may still be recoverable. It's also why .xlsx files are relatively compact.
What .xlsx does NOT support: macros (VBA code). If you open a macro-enabled workbook and save it as .xlsx, Excel will strip out every macro without ceremony. This is a silent data loss — Excel will warn you, but only if you're paying attention.
The .xlsm format is identical to .xlsx with one addition: it supports VBA macros. If your workbook contains any recorded macros or custom VBA code, you must save it as .xlsm or that code is gone the moment you save.
Warning
When you save a macro-enabled file for the first time, Excel will show a dialog asking if you want to "Keep Using This Format" (which strips macros) or "Save as Macro-Enabled Workbook." Always choose the macro-enabled option when your workbook contains VBA. It's easy to click past this dialog too quickly.
The .xlsb format is a binary workbook format. It supports everything .xlsm supports, including macros, but stores data in binary rather than XML. The practical result: file sizes can be 50–75% smaller, and open/save times on very large files (say, 100,000+ rows with complex formulas) can be dramatically faster.
The tradeoff is compatibility. Some third-party tools, scripts, and older systems have trouble reading .xlsb files. Use it when you're working with large internal files that don't need to be opened by external systems.
A .csv (Comma-Separated Values) file is not really an "Excel format" at all — it's a plain text format that Excel can open and save. Every row in your spreadsheet becomes a line of text, and every cell value is separated by a comma (or sometimes a semicolon or tab, depending on regional settings).
CSV is the lingua franca of data work. Databases export it. APIs deliver it. Python and R read it natively. When you need to hand data to any system that isn't Excel, CSV is almost always the right answer.
Warning
CSV files cannot store formatting, formulas, multiple sheets, or any Excel features whatsoever. When you save an Excel workbook as CSV, you save only the values on the currently active sheet, as plain text. Everything else is permanently discarded in that file (your original .xlsx is unaffected as long as you use "Save As" rather than "Save").
The .xls format is the old Excel 97–2003 format. It has a 65,536 row limit, doesn't support newer Excel features like dynamic array functions, and produces larger files than .xlsx. The only reason to use it is if you absolutely must share a file with someone using an ancient version of Excel. In practice, this is rare enough in 2024 that you can almost always use .xlsx instead.
AutoSave is one of Excel's most misunderstood features. Many people assume it works like the autorecover function that saves your work every 10 minutes in case of a crash. It doesn't. AutoSave is fundamentally different — and more powerful, with some important caveats.
AutoRecover is the traditional crash protection. It periodically saves a temporary recovery file in the background. If Excel crashes, the next time you open it, Excel offers to restore the unsaved work. You can configure the AutoRecover interval by going to File → Options → Save, and changing the "Save AutoRecover information every X minutes" setting. 10 minutes is the default; for critical work, dropping this to 2–5 minutes is worth considering.
AutoSave (available in Microsoft 365) is a real-time cloud sync feature. When a file is stored on OneDrive or SharePoint, AutoSave continuously syncs every change you make — there's no interval. The file is updated almost instantly after each edit. The toggle for AutoSave appears in the top-left corner of the Excel window, just above the ribbon.
Key insight
AutoSave only works when your file is saved to OneDrive or SharePoint. If your file lives on your local hard drive (C:\Users...), AutoSave is grayed out. This is a frequent source of confusion — people assume AutoSave is on when it isn't, then lose work after a crash.
Here's the counterintuitive part: AutoSave can actually cause problems if you're not careful. Because changes are saved instantly and continuously, there's no "undo" in the traditional sense once you close the file. If you accidentally delete a column, close the workbook, and reopen it, that column is gone from the saved version.
This is exactly why version history matters — and why understanding it is a non-negotiable skill for anyone using AutoSave regularly.
If you're about to make significant experimental changes to a file and don't want them auto-saved, you have two practical options:
Tip
Treat the AutoSave toggle like a "do not disturb" sign. Turn it off when you're doing exploratory work — restructuring data, testing formulas, overhauling a layout — and turn it back on when you've landed somewhere you're confident about.
Version history is one of the most underused features in Excel's cloud-connected workflow. When a file is stored on OneDrive or SharePoint with AutoSave enabled, Excel keeps a running history of every saved state of the workbook. You can browse this history, preview old versions, and restore any previous version at any time.
To open the version history panel, go to File → Info → Version History. Alternatively, in Microsoft 365, you can click the file name in the title bar at the top of the screen — a dropdown appears that includes a "Version History" option.
The panel that opens on the right side shows a chronological list of saved versions, each stamped with a date, time, and (if the file is on SharePoint) the name of the person who made changes. Clicking any version opens a read-only preview of the workbook at that moment in time.
When you're previewing an older version, a yellow notification bar appears at the top of the workbook. From there, you can click "Restore" to revert the main file to that state. Excel automatically saves the current version before restoring, so you won't lose the most recent state — it just moves into the history list.
Tip
Before restoring a version, consider using "Save a Copy" on the old version first. This gives you both the restored state and a preserved copy of what existed just before the restore. This two-step approach has saved more than a few data professionals from a secondary panic.
If your file isn't on OneDrive or SharePoint, you don't have automatic version history through Excel. Your options are:
This is a strong practical argument for keeping working files on OneDrive or SharePoint when possible, even if your final deliverables are stored elsewhere.
Sending someone an Excel file sounds simple. In practice, there are three distinct sharing models, and picking the wrong one creates duplicate copies, version conflicts, and the inevitable "which version is the real one?" conversation.
The classic approach: File → Share → Email → Send as Attachment. The recipient gets a copy of the file. Changes they make don't affect your version; changes you make don't affect theirs. You now have two diverging copies.
This is the right method when:
It's the wrong method when you expect ongoing collaboration, because you'll soon be juggling sales_analysis_v2_FINAL_johns_edits_v3_FINAL_FINAL.xlsx.
When a file is on OneDrive or SharePoint, you can share a link rather than the file itself. Go to File → Share → Share with People (or the Share button in the top-right corner), type the recipient's email, choose their permission level (can edit vs. can view), and send.
Everyone with the link works on the same file. Changes are visible in real time. The version history captures who changed what. This is the correct method for active collaboration.
Note
Be deliberate about permission levels. "Can edit" is appropriate for teammates who need to contribute data or update formulas. "Can view" is better for stakeholders who should be reading reports, not changing them. If you're sharing a workbook that contains protected ranges or hidden formulas, review the lesson on Protecting Excel Workbooks and Worksheets: Lock Cells, Hide Formulas, and Set Permissions for Shared Files before distributing.
When multiple people have a cloud-shared file open simultaneously, Excel shows colored cursors and labels indicating where each person is working. This is co-authoring, and it works surprisingly well for data entry and formula work. You can see colleagues' changes appear in real time.
Co-authoring has one practical limitation worth knowing: if two people edit the same cell simultaneously, Excel resolves the conflict automatically, usually by keeping the most recent change. For highly structured workbooks where cell-level conflicts could cause data integrity problems, designate editing responsibilities by sheet or section and communicate that to your collaborators.
The best version control system in the world doesn't help if you can't find the file, or if you have three files named "report.xlsx" in different folders. A consistent naming and organization system is infrastructure for your work.
A filename should answer three questions without opening the file: what is it, when was it created or last updated, and who owns it (if relevant). A structure that works well in practice:
[Project]_[Description]_[YYYY-MM-DD]_[Initials].xlsx
For example:
Q3Sales_RevenueAnalysis_2024-09-15_KM.xlsx
CustomerData_CleanedImport_2024-10-02_KM.xlsm
MarketingBudget_FinalDeliverable_2024-10-30.xlsx
Using ISO date format (YYYY-MM-DD) means files sort chronologically by default in any file explorer. Avoid using "FINAL" in filenames — there's always another final version. Date stamps are more honest.
A project-based folder structure works better than a type-based one (don't create a folder called "Excel Files"). Something like:
/Projects
/Q3-Sales-Analysis
/raw_data
/working_files
/deliverables
/archive
The raw_data folder holds source files that should never be modified. The working_files folder is where your active workbooks live. The deliverables folder holds final outputs you've shared externally. The archive folder holds old versions you're keeping for reference but no longer actively using.
Key insight
The discipline of keeping raw source data in a separate, never-modified folder is one of the highest-ROI habits in data work. If you corrupt a working file, you can always rebuild from raw data. If you corrupt the raw data and have no original, you have nothing. This concept comes up repeatedly when you start importing and cleaning external data in Excel.
Work through this exercise to cement the concepts from this lesson. You'll need a Microsoft 365 account with OneDrive access.
Setup: Open a blank Excel workbook and add the following data starting in cell A1:
Month Revenue Region
January 45200 North
February 38900 North
March 52100 South
April 47800 East
May 61200 West
Step 1 — Save with the correct format
Press Ctrl+Shift+S (or File → Save As) and save this file to your OneDrive folder. Name it SalesExercise_[Today's date]_[Your initials].xlsx. Confirm the file format is .xlsx.
Step 2 — Verify AutoSave is active Check the top-left corner of Excel. The AutoSave toggle should now show as "On" since the file is on OneDrive. If it's off, toggle it on.
Step 3 — Make a change and check version history Change the January revenue from 45200 to 99999. Wait 30–60 seconds for the auto-save to register. Then change it back to 45200. Wait another 30 seconds.
Step 4 — Open Version History Go to File → Info → Version History (or click the filename in the title bar). You should see at least two or three versions listed. Click on the version from before your first change. Verify that January shows 45200 in that snapshot.
Step 5 — Save a CSV copy Go to File → Save a Copy. Change the file format dropdown to "CSV (Comma delimited)" and save to your Desktop. Open the CSV in Notepad (right-click → Open with → Notepad) to see the raw text structure. Notice there's only the data — no formatting, no formulas.
Step 6 — Share the cloud file Back in your .xlsx file on OneDrive, click the Share button in the top-right corner. Enter your own email address with "Can edit" permission and send it. Check your email — you should receive a link. Open it to confirm it shows the same workbook.
"AutoSave is grayed out and I can't turn it on." This happens when the file is saved locally rather than to OneDrive or SharePoint. The fix: File → Save a Copy → choose your OneDrive location → save. Close the local copy and work from the OneDrive copy. AutoSave will now be available.
"I saved my macro-enabled workbook and now all the macros are gone."
You saved it as .xlsx instead of .xlsm. If you caught this immediately, check the AutoRecover folder (File → Info → Manage Workbook → Recover Unsaved Workbooks) or look in your version history if the file was on OneDrive. Going forward, always verify the file format in the title bar — .xlsm should appear after the filename.
"My colleague can't open the file I sent." If they're using an older version of Excel or a different spreadsheet application (like Google Sheets or LibreOffice), newer features may not render correctly. Features like dynamic array functions that spill results across multiple cells aren't supported in Excel 2016 and earlier. Test compatibility by checking File → Info → Check for Issues → Check Compatibility before distributing to unknown recipients.
"My file on SharePoint shows changes I didn't make." Someone else with edit access has modified the file. Check the version history — each version shows who made changes. If unauthorized edits are happening, review sharing permissions (File → Share → Manage Access) and consider restricting some collaborators to view-only.
"The version history panel is empty even though AutoSave is on." Version history can take a few minutes to populate after the first save. If you just moved a file to OneDrive and enabled AutoSave, give it a few edits and a few minutes. If the history remains empty after active use, the file may be in a OneDrive folder that isn't syncing properly — check the OneDrive sync status icon in your system tray.
Tip
One of the most useful keyboard shortcuts for saving is Ctrl+S (save in place) versus F12 (Save As dialog, which lets you change name, location, or format). Making F12 a reflexive habit for "new version" moments is a small change that prevents a lot of overwriting accidents. While you're building good keyboard habits, the lesson on Excel Interface Mastery: Advanced Ribbon, Quick Access Toolbar, and Keyboard Shortcuts for Data Professionals is well worth your time.
Let's consolidate what you've built in this lesson.
File formats: Use .xlsx for standard workbooks, .xlsm when you have macros, .xlsb for large files where performance matters, and .csv when handing data to external systems. Avoid .xls unless forced into it by legacy constraints.
AutoSave: Understand that it only works with cloud-stored files and that it saves continuously rather than on an interval. Use the toggle deliberately — turn it off during exploratory changes, and always pair it with an understanding of version history.
Version history: Store working files on OneDrive or SharePoint to get automatic version history. Know how to access it via File → Info → Version History, and use "Save a Copy" before restoring to preserve optionality.
Sharing: Send attachments for final deliverables; share links for collaborative work. Set permissions deliberately and use co-authoring for real-time teamwork.
Organization: Name files with project, description, and date in ISO format. Keep raw data untouched in its own folder. Archive old versions rather than deleting them.
As your workbooks grow in complexity — housing data validations, structured tables, and data entry best practices — solid file management becomes the quiet foundation everything else rests on. The professionals who never lose work, never send the wrong version, and never break a shared file aren't just lucky. They've built these habits deliberately.
From here, a natural next step is understanding how to protect the contents of the workbooks you're now saving and sharing so confidently — which means locking cells, hiding formulas, and setting permissions so collaborators can only touch what they're meant to touch.