Do you want to know how to reduce your Excel file size?
We all have that Excel file. You know, the one that takes minutes just to open or to save!
Would you like to stop waiting each time you work on it?
If so, this short post is for you!
Florence, Senior Consultant at UpSlide, will teach you how to reduce your Excel file size.
At the end of the article, you will know how to compress any Excel file size within a few seconds.
Ready to leave work earlier today?
First, audit your large Excel file
Before decreasing your Excel file’s size, we would like to share a tip with you to get started – you can skip it if you prefer to apply Florence’s tips right now.
The more you ad comments, formulas, conditional formatting, hidden sheets and so on, the heavier Excel files get. For instance finance professionals working in Transaction Services or Investment Banking build models with tons of formulas, tables and sheets.
Here’s a tip before you start: you can find out which sheets are the heaviest in your Excel file. To do so, make a copy of your Excel file and follow these steps:
- Change your Excel file copy’s extension to “.zip”
- Then open the zipped folder and go to xl > worksheets
- Sort by size
Spotting the heaviest sheets can help you choose which to start with. For instance, “sheet26.xml” accounts for almost one-third of the global volume. Usually, it corresponds to the n+1 sheet in your Excel file in the above example, we could start by trimming the twenty-seventh sheet.
Be careful! The n+1 rule takes into account hidden sheets. If you have a lot of hidden sheets, it can become tricky to find the right one. One way to know for certain you have the right sheet is to get the exact name. Here is the detailed guide for finding a sheet’s name.
Now, onto Florence’s tips!
Pick any large Excel file you want to lighten. As seen above, you can start by focusing on the heaviest worksheets.
Here are seven tips to reduce your Excel file size right now:
Tip #1: Remove conditional formatting
Conditional formatting comes in handy when you need to highlight specific data. But the truth is that it can quickly make your Excel file heavy. We suggest removing conditional formatting from your spreadsheet as soon as you are done with it. To do so:
- Select some formatted cells on your sheet
- Go to the Home tab > Conditional formatting > Clear Rules > Clear from Selected Cells
If you want to remove conditional formatting from all of your cells, pick “Clear Rules from Entire Sheet”.
Tip #2: Crop each worksheet to its used range
Did you know that Excel sheets can have up to one million rows? A good way to reduce your Excel file size is to get rid of the empty rows and columns. Here’s how you can do that:
- Select all unused row and columns (Shortcut = Ctrl+Shift+Right/Down Arrow)
- Right-click > Delete
Tip #3: Remove unnecessary formulas
We all use Excel formulas to manipulate and combine data dynamically! But we often forget to remove them when they’re no longer needed.
A good way to downsize your file and keep a record of your data is to replace your formulas with their values:
- Select the cells you are working with (you can use the shortcut Ctrl+A to select your entire sheet)
- Copy and Paste Special: Values
This will also remove hyperlinks, which make your file heavy.
Tip #4: Compress pictures in Excel
A golden rule is to avoid having pictures in Excel. If you have to use pictures anyway, a good practice is to always compress them:
- Select the picture you wish to lighten
- Go to the Picture Format tab
- Click on Compress
- We recommend to uncheck the first box (“Apply only on this picture”) to compress all pictures inside your Excel file
- Optional: you can choose the resolution you want (we recommend email).
Tip #5: Clear data formatting
Formatting enables the eye to naturally structure information. However, formatting your data will also weigh your sheets down. Trust me, we love formatting data too! If you’re running into difficulties loading a file though, we recommend removing your formatting:
- Select all formatted rows, columns and cells that do not contain data
- Home > Editing > Clear Format
Tip #6: Delete watches
People who work on large files with many sheets and formulas know what watches are (not the one on your wrist!). When numerous, they will weigh down your spreadsheets. Once you’re done with them, a good practice is to remove them from your files:
- Go to Formula tab > Watch Window
- Select all and click “Delete”
IMPORTANT: We recommend you apply this step last, as it can break formulas and cannot be reversed! Make a copy beforehand.
Let’s start with sheets! 😊
- Right-click on a sheet
- Select “Unhide”
- Repeat the previous steps for every sheet
Now let’s do the same with columns and rows:
- Select your entire sheet (Ctrl+A)
- Right-click on your rows/columns
- Click “Unhide“
Now that you can see your entire Excel file, you can delete all unnecessary sheets, columns and rows. Moreover, you can easily audit your file to spot heavy sheets with the method we used at the beginning of the article.
✨BONUS TIP: Reduce your Excel file size in one click