How to Handle Large Excel Spreadsheets? | Excel Hotshot

If you are working with large volume of data, you must bother about the size of the file and processing speed of the spreadsheet irrespective of whether you are using MS Excel, Libre Calc or any other Spreadsheet Software.

Size is very important when you need to mail the file and processing speed is also be considered while handling large volume of data.

Let’s understand how to deal with both the problems:

 

How to reduce file size?

The most common file type we use while working in MS Excel or Libre Calc is “Excel 97-2003 Workbook (*.xls)”, but we have many other file types to store Excel Spreadsheet.

Format	Extension	Description	Row Limit
Excel 97- Excel 2003 Workbook	.xls	The Excel 97 - Excel 2003 Binary file format (BIFF8).	65,536
Excel Workbook	.xlsx	The default XML-based file format for Excel 2007 and later versions. Cannot store VBA and Macros	10,48,576
Excel Macro-Enabled Workbook (code)	.xlsm	The XML-based and macro-enabled file format for Excel 2016, Excel 2013, Excel 2010, and Excel 2007. Stores VBA and Macro code	10,48,576
Excel Binary Workbook	.xlsb	The binary file format (BIFF12) for Excel 2007 and later versions.	10,48,576

From the above table, we come to know that the File format we were using contains lesser rows than the latest version contains and you will be surprised to know that the newer version consumes less memory to store the same data stored in .xls version.

1.      Use Excel Workbook (.xlsx)  

This advanced version of the excel file format introduced in MS Office 2007 with the power to store large with rows capacity 16 times more data than earlier versions.

 

2.      Use Macro-Enabled Workbook (.xlsm)

If you are working with macros and VBA you are advised to use .xlsm instead of .xls as it can handle more data than the earlier version and also file size will be much lessor than xls and avoid xlsx file format as this format not support the VBA and Macros.

3.      Use Excel Binary Workbook (.xlsb)

If you are using xlsx file format but still the file size is very large than expected, prefer Excel Binary Workbook (.xlsb) as it converts the data into binary and capable of handling large file with heavy formulas and formatting.

4.      Compress pictures in Excel

We generally don’t use pictures in excel but, if you did it is advised to compress the images. Just select the image PICTURE TOOLS appears on the top toolbar, at the very left group of icon click on the Compress Pictures.

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, it is advised to remove your formatting:

 

Select all formatted rows, columns and cells that do not contain data

Home -> Editing -> Clear -> Clear Format

 

 

6.      Eliminate hidden data (sheets, rows and columns)

IMPORTANT: It is recommend to apply this step last, as it can break formulas and cannot be reversed! Make a copy for safety.

 

Start with sheets!

Right-click on a sheet -> Select “Unhide”

Repeat the previous steps for every sheet

Do the same with columns and rows.

 

  

How to Handle Slow Excel Spreadsheets?

1.      Use Excel Binary Workbook (.xlsb)

As already explained that the binary file is much faster in processing due to its features to convert the file into binary.

2.      Use Conditional Formatting with Caution

We absolutely love conditional formatting. But, if you are dealing with a large data file use of Conditional Formatting is not preferable as it will slow down the processing speed of the file.

 

3.      Avoid Array Formulas

If you are working on a large data file you might have observed that it takes relatively more time to calculate the result of the formula. Yes formulas are also fast and slow in nature. Using array formulas like Vlookup and Offset is not advisable. Instead use combination of Index and Match.

 

4.      Use Excel Tables and Named Ranges

Excel Table and Named Ranges are two amazing features that hold your data and makes referencing super easy. It may take a while to get used to it, but when you start using it, life becomes easy and fast.

 

5.      Convert Unused Formulas to Values

Too many formulas will result in a slow Excel workbook. If you are having formulas that are not even being used, if you don’t need formulas, it’s better to convert them into a value (Use Paste Special – Paste as Values).

 

6.      Use Manual Calculation Mode

If your file contains lots of data and formulas, the excel keep on recalculating with any change in the excel file which leads to slow down your working. Better switch to the Manual Calculation Mode.

·         To switch to manual mode, go to Formula Tab –> Calculation Options –> Manual (press F9 key to recalculate and Shift+F9 to recalculate within sheet only)

7.      Increase Priority of Excel in Task Manager

Still you are facing slow processing in your Excel File? The most useful trick to handle heavy data is here. We all used Task Manager quite frequently while playing full screen computer games, right!

·         Open Task Manager by Right Clicking on the Taskbar -> Start Task Manager or just Press Ctrl+Alt+Del -> Start Task Manager 

·         Goto Process Tab

·         Find EXCEL.EXE

·         Right Click and Change the Priority to Realtime or High

Note: Realtime may work based on your operating system.

This trick also work for other applications.

 

I hope, the above mentioned tricks will be useful for your day to day activities and optimize your work.