TABLE OF CONTENTS
4. Keep the source data outside of your workbook using PowerQuery
Microsoft Excel and PowerPoint have become irreplaceable work companions in most business environments.
Besides their impressive value, there are some painpoints that everybody meets one day or the other.
One of them is the excessive size of the files, causing multiple issues:
❌Slowing our work, negatively impacting our productivity
❌Long saving times, especially when the file is on a network and needs to be uploaded every time we hit "Save"
❌Unability to email files to colleagues or clients, because they exceed the limits of messaging applications.
To keep your productivity high and avoid these paintpoints, there are a few tricks you can leverage to reduce the size of your Excel workbooks and PowerPoint presentations.
How to reduce the size of Excel Files
Below are the techniques that will help you reduce the file size of your Excel workbooks. You may use all of them or pick the techniques that are best suited for your own project. Compare the results with the initial data and see the impact yourself.
1. Get rid of unnecessary or duplicate data
This is the easiest and one of the most effective ways to reduce your file size. If you have an Excel workbook and it has content that is not useful, then what’s the reason for keeping that in your file?
Get rid of those (or keep them in a separate file on your PC) and see the result. You will get a reduced file size that is easier to share with your clients and colleagues.
Typically, uncessary data includes:
Columns in a database that are not actually used in your spreadsheet, usually caused by over-extracting data from your business applications
Duplicate data, when you create multiple instances of the same data in your file. This is usually the sign of a bad structure. To optimize the size of your file, make sure to learn the key principles for properly structuring an Excel workbook.
2. Get rid of unnecessary formulas
Any given cell takes a significanly higher size if it contains formulas than if it contains a mere value. So your Excel file size will be bloated when there are too many formulas featured inside.
If your data will not change, and the formulas have become unnecessary, you can get a significant size reduction by simply converting formulas into values. You may also use PivotTables instead of formulas, since some tips below will help you keep the PivotTables size under strict contol.
Tip: always make sure you keep a version of your file with the formulas. You may need them again. Better be safe than sorry.
3. Duplicate PivotTables instead of re-creating them
When you create a PivotTable in Excel, an invisible cache of your data source is created. The cache can very significantly increase the size of your file. If you create multiple PivotTables from the same data source, the quantitiy of cached data will also increase without any actual need for it.
So instead of creating PivotTables from scratch, copy and paste a copy of your first PivotTable, then edit the fields for your new need. All the PivotTables will use the same cache and limit the increase of file size.
4. Keep the source data outside of your workbook using Power Query
Even if you have just 1 cache for your PivotTables, that's already duplicating the data in your workbook.
One solution to further reduce the file size is to keep the source data outside of the workbook. For this, you can load the data from another file (which contains all the source data) and choose to load it only in the PivotCache or in PivotCharts.
This way you can have 1 file for the source, and 1 file for the reports and charts, which will have a smaller size.
5. Optimize images
If your Excel / PowerPoint file has pictures in it, look no more: you probably have a big potential for file size optimization right there. Let's check 2 ways you can reduce the size of pictures.
The first and simplest solution is to use the built-in compression tool that already exists within PowerPoint, Excel and Word. For that, select a picture, go to the "Picture Tools" / "Format" tab, and click "Compress Pictures".
This has 3 advantages:
Quick and simple to use
Allows you to compress all pictures at once
Allows you to remove cropped areas of pictures (if you don't need them anymore)
However on the negative side:
Compression is not optimal
Compression results in visible loss of quality
So the second solution is to use a specialized online tool to compress your pictures. Some pictures can be compressed by a very significant factor without resulting in any visible loss of quality. One example is ImageOptim. Since it is longer to upload, compress and download each picture before inserting them back in your file, use this process only for high-quality / big size pictures.
6. ZIP your file
One more time-tested technique that you can use to compress an Excel file is by zipping it. This will immediately reduce the file size. You can then share the file via email and the recipient can access it while unzipping the file on their desktop.
7. Get rid of data formatting
Data formatting can impact the size of your file. A good practice would be to try not to over-format your spreadsheets. Formatting include changing the font size, background color, adding borders, etc.
To remove all formatting in your seletion, you can go to the "Home" tab, click the "Clear" menu (the rubber icon) and then "Clear Formats".
Conditional formatting can also add some size to the file, so avoid using conditional formatting on too large a scale.
8. Save your workbook as an .XLSB file
Saving your file as ".XLSB" instead of ".XLSX" will significantly reduce its size. But is also has other benefits, in particular allowing your files to open and close faster. The bigger your file is, the more visible the benefit will be.
However use this file format wisely, because there are some drawbacks to using XLSB:
Contrary to the XLSM format, you will not see if the file contains macros.
Most third-party applications are not able to read XLSB files.
Power Query doesn't work with XLSB.
To save your file as .XLSB, press F12 or go to "File" / "Save As". Then change the "Save as type" to XLSB, as shown below:
9. Remove PivotTables cache
As explained above, the cache of a PivotTable is an invisible copy of the data souce used for the PivotTable.
By default, the cache is also saved when you save your file. But Excel gives you the possibility to save the workbook without saving the cache, making for a potential big reduction in the file size.
To save your workbook without the PivotTables cache, follow these steps:
Right-click any PivotTable in your workbook,
Click "PivotTables Options",
Go in the "Data" tab,
Uncheck "Save source data with file",
Make sure the box "Refresh data when opening the file" is checked: that way the cache will be removed when the file is closed, and created again when the file is opened.
10. Check which parts of your spreadsheet are taking the more size
Little people know that there is a trick that allows you to see how much each sheet and element weights in your workbook. This is how it works:
Rename your file to change the extension ".xlsx" into ".zip".
Unzip the file. You now have multiple folders with .xml files inside.
Open the "worksheets" folder and compare the size of each worksheets.
With this method, you can find where you should focus your efforts to reduce the size of your file. When you are done, change the extension back.
By combining these various tricks, you should be able to lower the size of your Excel files and improve their performance.
How to reduce the size of PowerPoint files
Similar to working with Excel files, you may need to share you PowerPoint presentation with others. To reduce the PowerPoint file size, you may take steps similar to the ones described above.
1. Compress pictures
This is usually the first reason why PowerPoint files grow big very, very fast. Presentations often include many pictures, and if you add 10 high-quality images of 10 Mo each... well you can easily do the math.
Just like working with an Excel file, you can compress pictures directly in your PowerPoint presentation. The steps are similar:
Select a picture and go to "Picture Format" then click "Compress Pictures",
Select the desired picture quality.
Check "Delete cropped areas of pictures" unless you need to keep the cropped areas (very rarely needed).
Apply to one picture, or all of the at once.
But because neat pictures are essential for the visual impact of a presentation, it is important to make sure your pictures remain of impeccable visual quality. There is nothing more ugly than a pixellized picture.
So I recommend using online tools that will reduce the size of your pictures without changing the way it looks for the human eye.
2. Keep videos out of the presentation
If you have any video in your presentation, it will certainly have a huge impact on the size of your file. You need to know that you can compress videos in a similar way to pictures. For that, click "File" and "Compress media".
However videos tend to get so big in comparison to your slides, that it quickly becomes impossible to keep a video size under control. For this reason, you may want to consider keeping the videos outside of your presentation, hosting them on a video platform like Youtube or keeping them in a separate local file.
3 Clean the slide master
There is something terribly wrong with the slide master in PowerPoint. Every time you paste a slide coming from another presentation, the master of that presentation is imported in yours.
Because people naturally paste slides from here and there all the time, the slide master quickly gets out of hands, make your file size going higher and higher and higer...
You can often find presentations that have literraly THOUSANDS of slides in the master!
To quickly clean the master, you can use the Clean feature of our Power-user add-in to remove all master slides that are not being used on any "real" slide. Just like that, you can dramatically reduce the size of your file.
4. Remove hidden / unecessary slides
This is an obvious one, but if you have too many slides, sometimes even hidden ones, well... it can be the easiest way to start.
5. Zip the file or/end save it as a PDF
As described below for Excel, you can easily get a significant reduction in the size of your file by simply zipping it.
But for PowerPoint, you also have the very efficient option to save and send it as a PDF. Of course, this is only if the recipient is not meant to edit it.
6. Avoid embedding files
Some people like embedding files in their presentation, like an Excel spreadsheet or Word document that they want to open during their presentation. There is nothing wrong with that, but if it will increase the size of the presentation.
So if you are in need of some size optimization, consider keeping both files separate.
7. Avoid pasting charts from Excel
When you copy a chart from Excel, the entire workbook is pasted in your presentation. This can result in presentations becoming very heavy, very fast.
Some alternatives are to:
Create the charts directly in PowerPoint, so that it contains the data range for the chart but nothing more from the workbook
Paste charts as pictures, or as linked pictures
8. Check which parts of your presentation are taking the more size
Similarly to trick n°9 explained above for Excel, you can analyze your presentation to spot which of your slides and elements are taking the mroe size. This is very efficient to focus your optimization efforts on the biggest elements and have the greatest impact.
Final Words
By combining all these tips, you should be able to dramatically reduce the size of your Excel and PowerPoint files, making it easier to email them, and faster to open them. If you have some additional tricks that you find efficient for file size optimization, we'd love to hear them!