Anzeige
Advertisement

Solve the problem with a lot to big Excel files

If you’re thinking of Excel files, you probably do not ponder whether you still have enough space on your hard drive for the file. And certainly, hardly anyone will stuff their hard drive so much that there are not a few gigabytes of space left.

Why is excel file so big

Topic Overview

Anzeige

But it can still be very eye-catching if you either take a quick look at the file size of your Excel spreadsheet, or you may wonder why it takes until the file is loaded.

Now the troubleshooting starts where all the megabytes come from, although the table is actually quite manageable.

Find out how to solve the problem in Excel 2016 and find out more in our article.

Solve the problem with a lot to big Excel files

If you’re thinking of Excel files, you probably do not ponder whether you still have enough space on your hard drive for the file. And certainly, hardly anyone will stuff their hard drive so much that there are not a few gigabytes of space left.

Why is excel file so big

Topic Overview

Anzeige

But it can still be very eye-catching if you either take a quick look at the file size of your Excel spreadsheet, or you may wonder why it takes until the file is loaded.

Now the troubleshooting starts where all the megabytes come from, although the table is actually quite manageable.

Find out how to solve the problem in Excel 2016 and find out more in our article.

1. Check obvious pictures and shapes

1. Check obvious pictures and shapes

The more data you put into your Excel spreadsheet, the bigger and slower the file will be over time.

Make sure that you can not slightly reduce the size of the inserted images by the number of pixels.
This is very simple under Windows with the on-board tool Microsoft Paint.

In addition, you can reduce graphics that you have inserted as a png or jpeg almost lossless with the online tool Tiny PNG significantly.

Advertisement

The more data you put into your Excel spreadsheet, the bigger and slower the file will be over time.

Make sure that you can not slightly reduce the size of the inserted images by the number of pixels.
This is very simple under Windows with the on-board tool Microsoft Paint.

In addition, you can reduce graphics that you have inserted as a png or jpeg almost lossless with the online tool Tiny PNG significantly.

Advertisement

2. Check hidden graphics in cell comments

2. Check hidden graphics in cell comments

Now there are not only obvious graphics and shapes that you can see immediately.

These can also hide in cell comments that only become visible when you move the mouse directly over the cell.

  • Replace the graph of the cell comment as follows:
  • Right-click on the relevant cell and there “Edit comment”
  • Right-click again on the frame of the cell comment and “Format Comment”
  • In the next window on “Colors and lines”
  • Then the item “Color” on “Fill Effects”
  • In the next window, click on “Graphics” and “Select graphic”

If you want graphics to be a cell comment (which of course looks fancy), make sure they are optimized in size.

See picture: (click to enlarge)

Zellkommentar in Excel bearbeiten
Zellkommentar in Excel formatieren
Grafik als Zellkommentar in Excel
Advertisement

Now there are not only obvious graphics and shapes that you can see immediately.

These can also hide in cell comments that only become visible when you move the mouse directly over the cell.

  • Replace the graph of the cell comment as follows:
  • Right-click on the relevant cell and there “Edit comment”
  • Right-click again on the frame of the cell comment and “Format Comment”
  • In the next window on “Colors and lines”
  • Then the item “Color” on “Fill Effects”
  • In the next window, click on “Graphics” and “Select graphic”

If you want graphics to be a cell comment (which of course looks fancy), make sure they are optimized in size.

See picture: (click to enlarge)

Zellkommentar in Excel bearbeiten
Zellkommentar in Excel formatieren
Grafik als Zellkommentar in Excel
Advertisement

3. Check diagrams

3. Check diagrams

Also should be mentioned that such a small bar or pie chart does not inflate the file immediately infinitely.

But also here one must always be aware that the more graphics, and also graphical effects, as well as queries (possibly over several spreadsheets) are included, this of course also on the file size, and thus the loading times of a table can have a negative effect ,

It may well be worthwhile to take a critical look at included graphics and diagrams.

Advertisement

Also should be mentioned that such a small bar or pie chart does not inflate the file immediately infinitely.

But also here one must always be aware that the more graphics, and also graphical effects, as well as queries (possibly over several spreadsheets) are included, this of course also on the file size, and thus the loading times of a table can have a negative effect ,

It may well be worthwhile to take a critical look at included graphics and diagrams.

Advertisement

4. Excessive cell formatting in Excel

4. Excessive cell formatting in Excel

Finally, we come to a point where you can actually despair, because this is really only to be found with detective work.

It is the so-called “excessive cell formatting” that makes an Excel file incredibly slow, and also takes its toll in size, although it shows absolutely nothing.

This can happen, for example, if you have conditionally formatted cells and have selected an entire column or row instead of just the cells in question.
However, we have already come across this problem, although we have not done anything like that.

You can easily test it by simply selecting the top-most cell (on Windows) in your worksheet and then pressing CTRL + End.

visa_300x250
Anzeige

If your cursor lands far away from the actual content somewhere along a line number, there is a suspicion that Excel considers the cells to be filled cells and takes them into account each time they start up, and actively loads them.

Note:
In Excel 2016, the maximum number of lines is 1,048,576 and the maximum number of columns is 16,384.

But fortunately, Microsoft has taken on this problem and offers an add-in for it. Unfortunately, this is not enabled by default and must first be done manually to work with it.

To do this, proceed as follows:

  • Go to the tab: “File” on “Options” and “Add-ins”
  • In the Manage field, select Com Add-Ins and click Go To
  • In the Com Add-Ins dialog box, select the Inquire check box.

From now on you have the additional Inquire tab always available in Excel.

See picture: (click to enlarge)

Excel Add-Ins anzeigen
Excel Com Add-Ins
Excel Inquire aktivieren

Finally, we come to a point where you can actually despair, because this is really only to be found with detective work.

It is the so-called “excessive cell formatting” that makes an Excel file incredibly slow, and also takes its toll in size, although it shows absolutely nothing.

visa_300x250
Anzeige

This can happen, for example, if you have conditionally formatted cells and have selected an entire column or row instead of just the cells in question.
However, we have already come across this problem, although we have not done anything like that.

You can easily test it by simply selecting the top-most cell (on Windows) in your worksheet and then pressing CTRL + End.

If your cursor lands far away from the actual content somewhere along a line number, there is a suspicion that Excel considers the cells to be filled cells and takes them into account each time they start up, and actively loads them.

Note:
In Excel 2016, the maximum number of lines is 1,048,576 and the maximum number of columns is 16,384.

But fortunately, Microsoft has taken on this problem and offers an add-in for it. Unfortunately, this is not enabled by default and must first be done manually to work with it.

To do this, proceed as follows:

  • Go to the tab: “File” on “Options” and “Add-ins”
  • In the Manage field, select Com Add-Ins and click Go To
  • In the Com Add-Ins dialog box, select the Inquire check box.

From now on you have the additional Inquire tab always available in Excel.

See picture:

Excel Add-Ins anzeigen
Excel Com Add-Ins
Excel Inquire aktivieren
Anzeige

5. Clean up excessive cell formatting with Inquire

5. Clean up excessive cell formatting with Inquire

That was actually the more complicated part of the whole action.

To clean up your spreadsheet of unnecessary cell formatting, under your new “Inquire” tab, click on “Remove excessive cell formatting” and in the next dialog box just select whether you want to perform this action on the current or all worksheets contained in the folder and confirm with “OK”

After saving your file again, you will notice that the file size has shrunk drastically, and your spreadsheet will load as usual in the future.

See picture: (click to enlarge)

Excel Inquire ausführen
Kauf auch du bei der Nummer 1!
Anzeige
Blogverzeichnis Bloggerei.de

That was actually the more complicated part of the whole action.

To clean up your spreadsheet of unnecessary cell formatting, under your new “Inquire” tab, click on “Remove excessive cell formatting” and in the next dialog box just select whether you want to perform this action on the current or all worksheets contained in the folder and confirm with “OK”

After saving your file again, you will notice that the file size has shrunk drastically, and your spreadsheet will load as usual in the future.

See picture: (click to enlarge)

Excel Inquire ausführen
Kauf auch du bei der Nummer 1!
Anzeige
Blogverzeichnis Bloggerei.de

Search for other topics:

About the Author:

Michael W. Suhr
Michael W. SuhrDipl. Betriebswirt | Webdesign- und Beratung | Office Training
After 20 years in logistics, I turned my hobby, which has accompanied me since the mid-1980s, into a profession, and have been working as a freelancer in web design, web consulting and Microsoft Office since the beginning of 2015. On the side, I write articles for more digital competence in my blog as far as time allows.

Search by category:

You might also be interested in:

Anzeige

Search for other topics:

About the Author:

Michael W. Suhr
Michael W. SuhrDipl. Betriebswirt | Webdesign- und Beratung | Office Training
After 20 years in logistics, I turned my hobby, which has accompanied me since the mid-1980s, into a profession, and have been working as a freelancer in web design, web consulting and Microsoft Office since the beginning of 2015. On the side, I write articles for more digital competence in my blog as far as time allows.

Search by category:

You might also be interested in:

Anzeige

Popular Posts:

1907, 2022

Encrypt emails in Outlook – How it’s done

July 19th, 2022|Categories: Data Protection, Homeoffice, Microsoft Outlook, Office 365, Uncategorized|Tags: , , , , , , |

Standard e-mails are like postcards that everyone can theoretically read. But there are solutions for more security.

1707, 2022

Environmentally friendly printing for more climate protection

July 17th, 2022|Categories: Homeoffice, Microsoft Windows, Uncategorized|Tags: , |

Climate protection starts small. And so not only can costs be saved through environmentally friendly and conscious printing in the office, but the climate can also be protected.

Special Offers: Word & Excel Templates

More

Special Offers: Design CV Templates

See more

Monatliche Technik Bestseller:

Bestseller 2022-2023 WLAN-Heizkoerperthermostate

Kategorie: SmartHome | Energie & Sicherheit

SmartHome | Energie & Sicherheit

Bestseller 2022-2023 WLAN-Heizkoerperthermostate
Produkte anzeigen
Bestseller 2022-2023 Notebooks

Kategorie: Homeoffice

Homeoffice

Bestseller 2022-2023 Notebooks
Produkte anzeigen
Bestseller 2022-2023 Smartphones

Kategorie: Smartphone & Zubehör

Smartphone & Zubehör

Bestseller 2022-2023 Smartphones
Produkte anzeigen

Popular Posts:

1907, 2022

Encrypt emails in Outlook – How it’s done

July 19th, 2022|Categories: Data Protection, Homeoffice, Microsoft Outlook, Office 365, Uncategorized|Tags: , , , , , , |

Standard e-mails are like postcards that everyone can theoretically read. But there are solutions for more security.

1707, 2022

Environmentally friendly printing for more climate protection

July 17th, 2022|Categories: Homeoffice, Microsoft Windows, Uncategorized|Tags: , |

Climate protection starts small. And so not only can costs be saved through environmentally friendly and conscious printing in the office, but the climate can also be protected.

Special Offers: Word & Excel Templates

More

Special Offers: Design CV Templates

See more

Monatliche Technik Bestseller:

Bestseller 2022-2023 WLAN-Heizkoerperthermostate

Kategorie: SmartHome | Energie & Sicherheit

SmartHome | Energie & Sicherheit

Bestseller 2022-2023 WLAN-Heizkoerperthermostate
Produkte anzeigen
Bestseller 2022-2023 Notebooks

Kategorie: Homeoffice

Homeoffice

Bestseller 2022-2023 Notebooks
Produkte anzeigen
Bestseller 2022-2023 Smartphones

Kategorie: Smartphone & Zubehör

Smartphone & Zubehör

Bestseller 2022-2023 Smartphones
Produkte anzeigen

Title

Anzeige

Most read:

Search by category:

Alle Vorlagen ansehen

Auch interessant:

Go to Top