Tag Archives: xlsb

Why is my Excel file so LARGE? Learn how to reduce Excel file size!

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

In this post I will explain various ways to reduce Excel file size. Large Excel files slow down your system, increase the likelihood of the file crashing as well as obviously use more drive space.XLSB files save or open faster

Microsoft Excel was designed to be a tool for managing relatively small datasets for Finance & Accounting purposes, today we see that Excel is used often for large data analysis, prototyping of complex solutions (built often with VBA Marcos). You probably wondered here struggling with bloated Excel file, frustrated with having to wait for calculations to complete or constantly crashing Excel file.

Reduce Excel file size… or change the tool

Before we start working to reduce the Excel this is a good moment to ask yourself the question…

Is Excel the right tool for the job?

If you are convinced so proceed further, otherwise consider that Microsoft Excel has built several tools dedicated to managing with challenges that appear when working with larger data sets:

  • PowerQuery – this Microsoft AddIn for Excel was created specifically for pulling and running queries on large datasets in Excel. You can easily merge worksheets, csv files or run complex relationship queries across your worksheets
  • PowerBI – Microsoft PowerBI is a free application you can download from the Microsoft Store that allows you to create custom dashboards, run complex analytics… and easily publish your dashboards and reports online

Save file as Binary (XLSB)

xlsb vs xlsx
Saving a file in XLSB from my experience often resulted in reducing file size even by 70%.. If you don’t know the difference between a XLSX (OpenXML format) and XLSX (Binary Excel format) file format I encourage you read my post on XLSB.

Since Excel 2007 Microsoft was pressured to open up the Excel format so other applications could use it. This meant moving away from the binary file format which was storage friendly and would open efficiently, to the more heavy file format (XMLs files compressed to a ZIP file). In effect the XSLX file format will always be larger than the XLSB.

To save a file in binary format go to FILE click on Save As and select Excel Binary Workbook (*.xlsb) as shown below.
Save Excel file as XLSB

Remove Used Cell Range

A troublesome thing that can increase your Excel file size is Used Range. Imagine the worksheet below. Let us assume at some point I added some data into cell G16, and afterwards I deleted it. Although de facto I am only now using A1:C4, de jure Excel will keep in memory the entire range of A1:G12 expanding the file size.
Unempty Cells vs Actually Excel Used Range

How to check your Excel Used Range? Use the CTRL+END key combo. You will be moved to the last cell in your Worksheets Used Range

Removing Unused Ranged

So what to do to reduce your Used Range only to the actual range you are using? Follow the steps below:

Remove data and formatting from unused cells

Select all unneccesary cells that contain data or formatting and go to Home->Editing->Clear and select Clear All:
Excel Delete Unused Range

Reset Worksheet Used Range

Now we need to update the Used Range property in your Worksheet. Go to the Developer Tab and open the VBE. Next add the following VBA Macro to any VBA Module.

Next run the Macro (or use the F5 shortcut). The Used Range should be now updated.

Remove Hidden / Unused Worksheets

Another reason for large file size is having many unused or hidden worksheets in your Workbook. Each Excel Worksheet has it’s own share of metadata, more importantly, however, you may be keeping sheets with similar datasets, copies or unnecessary Pivot Tables that also take up a lot of space. Follow steps below to show and delete unnecessary hidden worksheets.

Unhide hidden worksheets

To unhide hidden worksheets right-click on a Worksheet and select Unhide:Excel Unhide Hidden Worksheets

Delete unused Worksheets

To delete an unused Worksheet right-click and select Delete:
Excel Delete Worksheet

Remove Formatting

Formatting adds additional kilobytes to your Excel file size. A good approach is to remove any formatting from cells that don’t need formatting.

To remove cell formatting you can read this MSDN article or follow below:

Select Cells and click Clear Formats

Select Cells for which you want to remove formats. Look for the Editing section in the Home ribbon and select Clear Formats:
Excel Clear Formatting

Compress Images

Another reason for Excel to have an unreasonable file size is due to media, especially Images. Although you may think cropping and Image and resizing reduce its size, in fact Excel still keeps the entire image in memory. Hence the only way to free Image memory is to Compress Pictures.

Crop and Compress Images in Excel

To Compress your Images click on your Image(s) in Excel and go to Format->Adjust->Compress Pictures. Next select your preffered options. Select Delete cropped Areas of pictures to remove the unseen cropped areas of your images as well as the preffered resolution (the lower the more pixelated the image will seem):
Excel Crop Images and Reduce Resolution

Other options

Other honorable mentions to reduce your Excel file size may be:

  1. Remove unused Pivot Tables
  2. Remove Pivot Cache
  3. Replace Formulas with Data (Copy Paste as Data)

Feel free to comment below and add your ideas!

Optimize Excel formulas with the Excel Optimizer!

1 Star2 Stars3 Stars4 Stars5 Stars (6 votes, average: 4.33 out of 5)
Loading...

Ever been in a situation where your Excel workbook runs so slow you could make yourself a coffee before it finishes recalculating? Fighting to reduce the file size of your latest Excel report? Or just looking to optimize your Excel workbook a bit? Welcome to the AnalystCave Excel Optimizer the AddIn that will optimize Excel for you! Excel Optimizer
Stay tuned for updates on this post!
I must say this idea has been popping in and out of my head for some time now – although it turned out the first working version of the Optimizer took me only a couple of hours to build. Often coming along Excel files that literally called for wrath from the Gods, I yearned for a time that Excel would include a “Fix Me” button – doing away with all the wrongs of the Workbook I was working on. Unfortunately this is (still) not the case and building your Excel file many of us have to be constantly aware of the dangers that lie ahead. What is more, many Excel users waste a lot of time not appreciating simple tips and tricks that can seriously enhance their Excel experience (like simply saving files in XLSB file format).

Optimize Excel with the Excel Optimizer

The Excel Optimizer is a simple AddIn to Excel that runs certain rules across your Workbook and points out the main pain-points or suggests certain improvements – all with performance in mind. Some rules can be implemented (I am hoping making all as such) while others will simply point out what needs to get done.

This is still a beta-work-in-progress-prototype so keep in mind that there may be some error/bugs here and there. What is more the Excel Optimizer current will only be able to suggest certain fixes (such as replacing some Excel functions with others). Depending on how popular this AddIn becomes I am planning to extend it to be able to actually implement most of the recommended tweaks. Optimizing Excel for best performance has become much easier.

See this video for a quick showcase of how the Excel Optimizer works.

Documentation

The Excel Optimizer consists of 2 features:

  • Optimize Excel – the main feature. Opens a window (screen below) that will allow you to run a rule-analysis on your current Workbook. Each rule with validate against different performance standards, rules also have different scopes. There are rules validating the format of the Workbook, validating worksheets and then there are the low-level rules that validate each and every formula. Using the settings button you can turn rules on/off or configure them to enhance your experience
  • Timer Full Calculation Rebuild – a simple macro that calculates the time needed to do a Full Rebuild Calculation of your Excel workbook. This comes in handy when wanting to set a baseline for the improvements you might want to implement

Excel Optimizer: AddInRibbon buttons
Excel Optimizer: AddInRibbon buttons

Below a screenshot of how the main window looks like:
Excel Optimizer: Main window
Excel Optimizer: Main window

Rules

Rules are what drive the Excel Optimizer. The AddIn has been built in a way to make adding/removing/enabling/disabling rules as easy as possible. Currently the Excel Optimizer introduces the following types of rules – many of most still cannot be implemented automatically (expect this in the near future):

Rule Scope Description
XLSB Workbook Checks if workbooks is in XLSB format if not suggests so
UsedRange Worksheet Validates the Used Range of each Worksheet to see if the Used Range is excessive and can be reduced for better performance and optimum file size
VLOOKUP Range Looks for exact (FALSE) VLOOKUPs. If found suggest replacing them with double approximate (TRUE) VLOOKUPs for best performance
Repeating formula Range Looks for exact repetitions of certain functions and suggest replacing them with a certain cell to minimize the need to recalculate the same function multiple times
Volatile functions Range Looks for volatile native Excel functions and suggests removing or replacing them
External links Range Looks for any external links used within cells in the entire Workbook and suggests removing them or reducing to a bare minimum
Array Formula Range Looks for Array Formulas within cells in the entire Workbook and suggests replacing them with regular Excel functions if possible
Error Range Looks errors within cells in the entire Workbook and suggests correcting them

Download

You can download the latest version here:

Do you have ideas for new rules? Any suggestions / wishes for additional features? Put your comment below or on my Facebook / Twitter page.

Excel XLSB vs XLSX file format. The Pros and Cons of XLSB Files

1 Star2 Stars3 Stars4 Stars5 Stars (8 votes, average: 4.63 out of 5)
Loading...

An XLSB file is a Microsoft Office Excel Binary file. Information inside XLSB file is stored as binary data compared, while in usual XSLX files data is stored as XML files which are then compressed.

Working with large Excel files is often a drag. They open slower, they take an eternity to save and they often need to be uploaded to the Cloud to be shared with your coworkers or family. Why not explore the benefits of the XSLB file format then (sometimes mixed up with XLSB)?

What is an XLSB file format?

This is what is inside an XLSX (or XLSM) file (image on the right). What do you mean inside? – you ask.

Inside an XLSX file
Inside an XLSX file

XLSX and XLSM files are in fact compressed archives with XML files inside. That is because Microsoft has opened the Excel file format and decided to break the insides into XML files. When an XLSX or XLSM file is saved Excel needs to break it down into separate XML files, compress it and finally save it as XLSX or XLSM.

The XLSB file format on the other hand is a binary Excel file. It resembles the old XLS file format which was also a binary file.
What happens when Excel wants to save an XSLB file? Not much really. It mostly dumps the binary data into a binary file and saves it as XLSB.

XLSB advantages (XLSB vs. XLSX)

  • XLSB files are smallerSmaller file size – the Excel binary file uses noticeably less space. This is more noticeable especially when working with large Excel files. In some cases I heard of there being XLSB files that required 10% of the original file size – this is more visible with VERY large files
  • XLSB files save or open fasterOpens/saves more quickly – loading binary data is faster than parsing text (XML) files – similarly as you would compare opening a book in Spanish and having to translate every sentence to English as opposed to picking up a ready translated copy. Similarly, when saving the file – the binary format is more efficient than dumping the data back into the XML and then compressing it. From my experience XLSB files open and get saved 2x faster
  • XLSB files support longer formulasSupports formulas above the 8192 character limit. In other file formats they don’t save properly

Yahoo! Well it would seem there is nothing less obvious to do then to start working only on the XLSB file format. However, it’s not a straightforward decision as there are some minor setbacks. Here are some that come to mind…

XLSB disadvantages (XLSB vs. XLSX)

  • Compatibility – the XLSB Excel format is not supported by Excel 2003 and earlier versions, which frankly is less of a problem nowadays
  • Security (VBA) – with the distinction between the XLSM and XLSX format you know which files may or not contain VBA macros. With XLSB you won’t know for sure. So beware when opening XLSB files from unknown sources or from people/websites you don’t trust
  • You can’t make changes to the Excel Ribbon when working on an XLSB. You must temporarily save your file as XLSX or XLSM, makes changes and save back as XLSB.
  • Lack of interoperability with third-party tools. XLSB is a binary file format unlike the open XML XLSX and XLSM files. Hence you often won’t see your XLSB files working everywhere – like in OpenOffice

Other suggestions when working with large Excel files or datasets

I usually start with the quick wins suggestions and leave the more complex for desert. The XLSB file format is a good start and often won’t require that you meddle with the data/formatting/content of your workbook, rightfully as you shouldn’t have too. However, from time to time there will be those moments when that won’t be enough and you just won’t be able to work any longer with a slow and large Excel file. Here are some useful tips:

  • Reduce the file size by deleting unused cells
  • Save data files without formatting – formatting may account for a lot of storage space and if you are simply working with a dataset and don’t need formatting save your file in .xml format
  • Save data files without formatting – formatting may account for a lot of storage space and if you are simply working with a dataset and don’t need formatting save your file in .xml format or as a .csv
  • Turn automatic calculations off – often even not so large Excel files cause Excel to freeze or crash. That may be because of an abundance of Excel formulas having to recalculate each time a change is made etc. Try to turn Calculation Options to Manual to get rid of this problem
  • PowerQuery Add-In – Excel has its limitations when working with large datasets. The Excel PowerQuery Add-In by Microsoft has been designed to handle Big Data and complex data queries to external databases or datasources. PowerQuery will allow you to work more efficiently with these data sets and will not limit the amount of records you can handle within a single Excel Worksheet

Common myths about XLSB

Do XLSB runs formulas faster than XLSX?

  • Not true

XLSB file are only loaded and unloaded faster (saved and closed) than XLSX files. Afterwards both formats run in RAM memory with similar performance on the same Excel engine. Hence, you won’t see your Excel formula’s running significantly faster. However, if you do save your file frequently you will definitely notice that the XLSB file format saves faster.

Do XLSB files crash more often?

  • Not true

The XLSB file format does not in any way increase the probability of crashing. However, if the file does crash it may be harder to recover. XLSB are binary files, where XLSX and XLSM files are in fact compressed XML files – text files in XML format. Therefore, in a critical situation you have definitely a better chance of reading a text file than a binary file. Then again I wouldn’t worry about this too much.

Can XLSB files be opened only via Excel?

  • True

Binary Excel files are (usually) not supported by other Office Suites (such as Open Office). This is because by introducing the *X formats (docx, xlsx etc.) Microsoft introduced an open file format that allowed these other programs to open as well as edit these files more easily than before. For this reason be aware when working with the binary Excel format when working with collegues who might want to use alternative programs to collaborate with you.

Conclusions

On a daily basis I would recommend sticking to XLSX and XSLM as standard Excel file formats. It is worth reaching out to the XLSB file format whenever you file starts running slow or uses an enormous amount of space.

XLSB will actually not benefit small Excel files and you might even see small XLSB files taking more space than small XLSX/XSLM files. Your clients / coworkers may also have doubts when opening XLSB files as Excel treats these file formats with an extra dose of caution.

Use the XLSB file format MAINLY with very LARGE Excel files.

Guide to Improving VBA Performance. Faster Excel VBA

1 Star2 Stars3 Stars4 Stars5 Stars (12 votes, average: 4.58 out of 5)
Loading...

There are many different articles and post on improving Excel performance. What I personally was missing was a simple, comprehensive, short overview of how the performance of VBA macros can be improved without needing to read through long articles on how the Excel compiler works.

That is why I gathered all VBA performance guidelines into this single post which contains all the known ways of Improving VBA Performance, organized by impact on VBA Performance.

Turn Off Automatic Calculation

vba performanceTo increase VBA speed of execution be sure to set the Calculation mode to xlCalculationManual so that no Excel formula calculations are carried out within the Excel Workbook until the Calculation mode is changed back to xlCalculationAutomatic or by running Application.Calculate:

Many case you will see a boost in VBA speed.

Turn Off Screen Updating

vba performancePrevent Excel from updating the Excel screen until this option is changed to True:

Avoid Range Copy/Paste Functions

vba performanceThe Excel Range.Copy procedure is a memory hog. Unfortunately in VBA we have limited control over the Garbage Collector – the mechanizm by which Excel will free up internal memory (declared unused variables, tables, objects etc.). The Excel Garbage Collecter unfortunately runs seemingly at specific intervals which means that if you create a lot of objects in a short amount of time you may run out of memory resulting in slowing down of Excel or simply crashing the application altogether

Use For Each on Collections

vba performanceThe For and For Each Loops are not equivalent and should be used in different situations. ALWAYS use the For Each Loop when looping through Collections, Ranges etc.

This is because looping through Objects of a Collection is slower.

Use Option Explicit

vba performanceIt is not so much the presence of this macro that benefits your performance but the impact of the behavior it requires from you. Option Explicit requires that you declare ALL VARIABLES. Add this option at the very beginning of your VBA project module or class. This will force you to explicitly declare the types of your variables. VBA is a dynamic typed language therefore you do not normally have to declare the type of your variable or declare variables in that matter. This causes some additional overhead when your VBA code is executed especially if this variable is used often in your code:

The XLSB format vs. XLSM

vba performanceA known way to improve Excel VBA speed and efficiency, especially fore large Excel files, is to save your Workbooks in binary XLSB format. To read more on the benefits read my post on the XLSB format.

Use Early binding

vba performanceWhen working with OLE objects remember that it is more efficient to declare the OLE object type explicitly. Use:

Use vbNullString vs “”

vba performanceInstead of comparing an empty string use thge vbNullString constant:

Disable VBA Events

vba performancePrevent Excel from firing Excel Events while processing your macro:

Use With…End Clause

vba performanceUse the With…End Clause when working with Object such as Ranges, Worksheets, Workbooks, Collections etc. It is not only more efficient but also much easier to read, and simplifies your code.

With…End Clause on this MSDN Article here

Disable Excel PageBreaks

vba performanceDisabling PageBreaks in Excel can increase Excel and VBA Performance. Read here why this helps your VBA performance: MSDN PageBreaks and Performance. You can disable PageBreaks like this:

Use VBA Constants

vba performanceConstants are precompiled variables that can’t be modified during VBA untime. Because of this they are more efficient than regular variables defined with the Dim statement.

Increasing VBA performance

It is good to handle turning off some of the optimizations mentioned above. I usually use a procedure I defined which I named “OptimizeVBA”. I turn optimizations before running my macro and then turn them off after completion.

Calculations and Screen updating

Turning ScreenUpdating and Calculations off although improves VBA performance can sometimes causes some issues.

Manual recalculation

In case calculations are turned off do not expect that Excel with recalculate its formulas based on any changes made to your Worksheets. In case you want to control these recalculations use (in this order):

  • ActiveSheet.Calculate – this will recalculate only the “dirty” cells within your ActiveSheet
  • ActiveWorkbook.Calculate – this will recalculate all the “dirty” cells within your ActiveWorkbook
  • Application.CalculateFull – this will recalculate all open Excel Workbooks
  • Application.CalculateFullRebuild – this will recalculate all open Excel Workbooks and rebuild all dependencies

Screen Updating

When turning ScreenUpdating off you will not be able to access or see what is happening on the Excel Worksheet. In some rare cases Excel may crash assuming that the Application is not responding. So how to respond to this? How to control when the Screen is Updated and when it is not? Use the DoEvents procedure. See the example below

Still slow?

If you still aren’t satisfied with your VBA performance try Compiling your VBA to a .NET COM DLL or multithreading VBA. VBA code can be mulithreaded although this makes sense for macros running more than approx. 10 seconds (as there is an overhead).

Measuring VBA execution time

VBA Time Saver KitWhen working on improving VBA performance you will certainly want to verify how some optimization will reduce your total execution time. There are several known methods for measuring VBA code execution time. The Performance Module of the VBA Time Saver contains at least 2 methods for measuring execution time as well as other way for Improving VBA Performance. There is also a hack to Create an Additional VBA Thread.

The Performance Module contains:

  • 2 Methods for Calculating VBA Execution Time (up to 1/1’000’000 of a Second)
  • Turn Optimizations On/Off
  • Create Additional VBA Thread (unstable on some environments)

Before you start optimizing your VBA macro…

Are you sure you need VBA (Visual Basic for Applications) to solve your problem? Really? Check out my Do you really need Visual Basic for Applications macro for that? article to make sure you aren’t wasting your time writing VBA macros for something available as a feature in Excel.