Did you know a staggering 30% of finance professionals spend 4 hours in Excel every day?
Even small delays and inefficiencies will be slowing you down, like working with an Excel file that’s too large to open or save.
Florence, UpSlide’s Senior Consultant and Excel expert, has a few easy hacks up her sleeve to help reduce your Excel file within seconds. Whether you follow the steps in chronological order or pick one to solve your issues, by the end of this guide, you’ll certainly have a lighter Excel file size!
Why is my Excel file so large?
For many finance professionals working in investment banking or advisory, Excel serves as the cornerstone for their financial modeling and analysis. Their data-filled workbooks are packed with sheets, formulas, and tables, which will undoubtedly be making the file too heavy. Before we show you how to reduce your Excel file size, it’s key to understand why the file is so large in the first place and which sheets you should focus your attention on.
Follow these steps to audit your workbook:
- Open your Excel workbook.
- Right-click on each sheet and select ‘Move or Copy’
- Select ‘New book’ from the drop-down and then tick the box which says ‘Create a copy’
- Save the individual file to see how heavy it is in File Explorer
- Do this action for other sheets within your workbook, and start by optimizing the heaviest first
By analyzing your Excel workbook first, you’ll gain valuable insights into where you should focus your efforts to shrink the file.
How to reduce Excel file size
Remove conditional formatting
Conditional formatting comes in handy when you need to highlight specific data, but it can quickly make your Excel file too heavy. We recommend removing conditional formatting from your spreadsheet when it’s no longer necessary. To do so, you must:
- Select the formatted cells on your sheet
- Go to the Home tab and click ‘Conditional formatting’
- Select ‘Clear Rules’, followed by ‘Clear from Selected Cells’.
Top tip:
If you want to remove conditional formatting from all of your cells, pick ‘Clear Rules from Entire Sheet’ instead.
Crop each worksheet to its used range
An Excel sheet can hold up to one million rows, so removing any you’re not using is an easy way to compress your Excel file size. Here are some easy steps to achieve that:
- Select all unused rows and columns. An easy shortcut to use is Ctrl+Shift+Right or Down Arrow.
- Right-click anywhere in your spreadsheet, and then click ‘Delete’.
Read more about using shortcuts to work faster in Microsoft
Remove unnecessary formulas
Using Excel formulas is a great way to manipulate and combine data dynamically. But it’s easy to forget to remove them when those formulas are no longer needed.
Be sure to replace your formulas with their values to reduce your file size and keep a record of your data. Here’s a quick way to do it:
- Select the cells you are working with. If you prefer working with Excel shortcuts, press Ctrl+A to select your entire sheet.
- On the Home tab, click ‘Copy’
- On the Home tab, click the arrow below ‘Paste’, and then click ‘Paste Values’.
This step will also remove any hyperlinks in your Excel file, which will help shrink the file size.
Compress pictures in Excel
Generally, it’s best practice to avoid including any images in your Excel workbook, as this will increase the file size significantly. If you must use pictures to aid your data commentary, we recommend always compressing them. Here’s how:
- Select the picture you’d like to compress in Excel
- Go to the Picture Format tab
- Click on ‘Compress’
- Uncheck the first box ‘Apply only on this picture’ to compress all pictures inside your Excel file
Recommendation:
When choosing the resolution for your image, we always suggest choosing email as it helps reduce the file size even more.
Clear data formatting
Most Excel users will leverage formatting to structure information, making your workbook easier to navigate and comprehend. However, formatting data also weighs your file size down. If you’re running into difficulties loading a file, we recommend removing formatting, particularly from rows that do not contain data. Follow these quick steps:
- Select all formatted rows, columns and cells that contain zero data.
- Navigate to Home, then Editing, and then choose ‘Clear Format’
Clearing unnecessary formatting from your workbook should help shrink the file size and make it easier to open or save.
Did you know some reporting automation software will finalize and optimize your workbook in seconds? Discover more
Delete watches
Using the watch function in Excel is useful for inspecting and auditing formula calculations and results in large worksheets. However, including numerous watches will make your Excel file size heavy. Once you have finished with a particular watch, it’s good practice to remove them from your files. Here’s how:
- Go to the Formula tab and then click ‘Watch Window’
- Select all watches and click ‘Delete’
This easy hack will help you reduce your Excel file size in no time.
IMPORTANT: We recommend you apply this step last, as it can break formulas and cannot be reversed! Be sure to make a copy beforehand.
When you’re working on an Excel file with hundreds of sheets, it’s easy to forget about hidden data. Follow these easy steps to unhide any sheets, columns, or rows so that you can then delete any that are no longer necessary.
To unhide Excel sheets:
- Right-click on a sheet
- Select ‘Unhide’
- Choose whether the sheet is still relevant; if not, delete it
- Repeat the previous steps for every sheet.
To unhide columns and rows:
- Select your entire sheet (Ctrl+A)
- Right-click on your rows/columns
- Click ‘Unhide’
- Choose to keep or delete the rows.
While these steps may seem simple, removing this unnecessary data will help you compress your Excel file size and help you work faster.
Reduce your Excel file size in one click with UpSlide
At UpSlide, we’ve built reporting automation features to help you build reports and navigate even the most complex financial models in half the time.
Our Smart Clean feature enables you to crop workbooks quickly, reset zoom levels and replace formulas to perfect your workbook before sending. With one click in the UpSlide ribbon, you could reduce your Excel file size up to 99% of its initial weight!
Reach out to a member of our team to find out more.
Key takeaways
Most heavy Excel users are regularly faced with file sizes that are too large, making it difficult to open or save and ultimately slowing them down.
However, there are many easy tips and tricks to help you reduce the file size quickly and maintain the quality of the data. From removing conditional formatting, compressing image sizes, to doing it in one click with automation tools, there is a solution out there for everyone.
Discover more of our Microsoft efficiency tips here.
TL;DR
You can easily reduce your Excel file size, by either removing conditional formatting and unnecessary formulas, cropping workbooks to the used range, compressing pictures in Excel, and more!
One of the most effective ways to reduce your Excel file size and boost efficiency is using reporting automation. It enables you to eliminate manual tasks and perfect your workbook in seconds.
