Contains paid promotion

Advertisement

To solve the problem with a lot
to large Excel files

When you think 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.

Warum wird Excel Datei so groß

But still, there can be big eyes when you either glimpse 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 and solve the problem in Excel 2016 in our article.

Advertisement
SanDisk Ultra 64GB (SDSQUAR-064G-GN6MA) microSDXC Speicherkarte + Adapter bis zu 100 MB/Sek, Class 10, U1, A1,Grau, Rot
SanDisk Ultra 64GB (SDSQUAR-064G-GN6MA) microSDXC ...*
by SanDisk

Kapazität: 64 GB, Flash Card Typ: MicroSDXC, Flash-Memory-K...

List Price: € 30.99 You Save: € 11.99 (39%) Prime Price: € 19.00 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. August 2018 um 12:52 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

To solve the problem with a lot to large Excel files

When you think 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.

Warum wird Excel Datei so groß

But still, there can be big eyes when you either glimpse 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 and solve the problem in Excel 2016 in our article.

Advertisement
SanDisk Ultra 64GB (SDSQUAR-064G-GN6MA) microSDXC Speicherkarte + Adapter bis zu 100 MB/Sek, Class 10, U1, A1,Grau, Rot
SanDisk Ultra 64GB (SDSQUAR-064G-GN6MA) microSDXC ...*
by SanDisk

Kapazität: 64 GB, Flash Card Typ: MicroSDXC, Flash-Memory-K...

List Price: € 30.99 You Save: € 11.99 (39%) Prime Price: € 19.00 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. August 2018 um 12:52 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

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.

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.

2. Check hidden graphics in cell comments.

2. Check hidden graphics in cell comments.

Now there are not only obvious graphics and shapes 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 it should be graphics as a cell commentary (which of course looks good), then make sure that they are optimized in size.

See picture: (click to enlarge)

Edit cell comment in Excel
Format cell commentary in Excel
Graphic for cell commentary in Excel
Advertisement

Now there are not only obvious graphics and shapes 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 it should be graphics as a cell commentary (which of course looks good), then make sure that they are optimized in size.

See picture:

Edit cell comment in Excel
Format cell commentary in Excel
Graphic for cell commentary 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 the 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 the 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 absolutely nothing is seen of it.

This can happen if, for example, you have conditionally formatted cells and have selected an entire column or row instead of just the cells concerned.
However, we’ve already come across this problem, even though we did not do 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.

Advertisement
#1
SanDisk Ultra 64GB USB-Flash-Laufwerk USB 3.0 bis zu 100MB/Sek
SanDisk Ultra 64GB USB-Flash-Laufwerk USB 3.0 bis ...*
by SanDisk

USB 3.0 DRIVE, SANDISK,ULTRA CRUZER,64GB--- Memory Capacity ...

List Price: € 26.99 You Save: € 11.00 (41%) Prime Price: € 15.99 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. August 2018 um 12:52 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

If your cursor lands somewhere far off the actual content at 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 “Addins
  • In the “Manage” field, select “Com AddIns” and click on “Go to
  • In the Com Add-Ins dialog box, select the Inquire check box.

As of now you have the additional Inquire tab always available in Excel.

See picture: (click to enlarge)

Show Excel AddIns
Excel Com Add-Ins
Enable Excel Inquire
Advertisement

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 absolutely nothing is seen of it.

This can happen if, for example, you have conditionally formatted cells and have selected an entire column or row instead of just the cells concerned.
However, we’ve already come across this problem, even though we did not do 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.

Advertisement
#1
SanDisk Ultra 64GB USB-Flash-Laufwerk USB 3.0 bis zu 100MB/Sek
SanDisk Ultra 64GB USB-Flash-Laufwerk USB 3.0 bis ...*
by SanDisk

USB 3.0 DRIVE, SANDISK,ULTRA CRUZER,64GB--- Memory Capacity ...

List Price: € 26.99 You Save: € 11.00 (41%) Prime Price: € 15.99 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. August 2018 um 12:52 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

If your cursor lands somewhere far off the actual content at 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 “Addins
  • In the “Manage” field, select “Com AddIns” and click on “Go to
  • In the Com Add-Ins dialog box, select the Inquire check box.

As of now you have the additional Inquire tab always available in Excel.

See picture:

Show Excel AddIns
Excel Com Add-Ins
Enable Excel Inquire
Advertisement

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 free 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 this with “OK

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

See picture: (click to enlarge)

Execute Excel Inquire
Advertisement
Print Friendly, PDF & Email

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

To free 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 this with “OK

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

See picture:

Execute Excel Inquire
Advertisement
Print Friendly, PDF & Email
Become Guest Writer

We publish your guest contribution free of charge in German and English
incl. backlink to your website and identification of the author

Anzeige
Blogverzeichnis Bloggerei.de

About the author:

Michael Suhr
Michael SuhrWebdesigner / Economist
After 20 years in logistics management, I have been working as a freelance web designer and office trainer since the beginning of 2015. Incidentally, I give tips and tricks for more digital skills in my blog as time permits.

Search by category:

Advertisement
#1
APEMAN 4K Action Cam 20MP WIFI Wasserdichte Unterwasser Action Kamera 40M Digitale Videokamera mit 170 ° Weitwinkelobjektiv Eis Sony Sensor, Zwei verbesserten Batterien, Transportskoffer und 24 Multiples
APEMAN 4K Action Cam 20MP WIFI Wasserdichte Unterw...*
by Apeman

Das ist unser Leben,verpasse nie wieder einen wunderbaren Mo...

List Price: € 109.99 You Save: € 20.00 (18%) Prime Price: € 89.99 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. August 2018 um 15:32 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

About the author:

Michael Suhr
Michael SuhrWebdesigner / Economist
After 20 years in logistics management, I have been working as a freelance web designer and office trainer since the beginning of 2015. Incidentally, I give tips and tricks for more digital skills in my blog as time permits.

Search by category:

Advertisement
#1
APEMAN 4K Action Cam 20MP WIFI Wasserdichte Unterwasser Action Kamera 40M Digitale Videokamera mit 170 ° Weitwinkelobjektiv Eis Sony Sensor, Zwei verbesserten Batterien, Transportskoffer und 24 Multiples
APEMAN 4K Action Cam 20MP WIFI Wasserdichte Unterw...*
by Apeman

Das ist unser Leben,verpasse nie wieder einen wunderbaren Mo...

List Price: € 109.99 You Save: € 20.00 (18%) Prime Price: € 89.99 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. August 2018 um 15:32 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.
2018-08-09T11:50:31+00:00By |Categories: Excel 2016, Office 2016|
In order to optimize our website for you and to be able to continuously improve it, we use cookies. By continuing to use the website, you agree to the use of cookies. Ok