Excel Oddity: Camera Objects bloat File Size

Camera objects (aka linked pictures to cell ranges) can considerably increase the size of an Excel workbook. Why?

Vintage Camera - Photographer: Peter Miller (flickr.com)Camera Objects (aka linked pictures) are an easy-to-use, powerful and flexible technique in Excel, especially for making dynamic and interactive charts and dashboards.

I learned this technique years ago from Charley Kyd’s fantastic e-book Dashboard Reporting With Excel.

My good friend and Excel MVP Chandoo also wrote several articles about the Camera Object (all articles with ‘camera tool’ tag on chandoo.org).

The feature may still not be as known among Excel users as it should be, but there are so many very good tutorials available that I do not need to write another one.

As great as they are, Camera Objects always had a few issues (printing, merged cells, distorting captured charts, etc.) as Charley points out here: A New Excel 2010 Camera Tool Bug, and a Workaround.

Today’s article will describe not a real bug, more an Excel oddity or annoyance regarding Camera Objects: the extensive use of Camera Objects can let the file size of your workbook explode, especially if they are linked to cells with inserted images sitting on top. I took a closer look into this and today’s post describes what I found out.

The Issue

During the development of the EU unemployment dashboard for the previous post (Highlighting on Excel Dashboards) I thought about spicing up the display with flag buttons of the EU member states close to the axis labels of the bar chart and the row headers of the data table.

Since it is an interactive dashboard, I needed camera objects based on named formulas to display the correct flags depending on the order of the bar chart and the vertical position of the data table. After I inserted the flag pictures and created the camera objects, the size of the Excel workbook exploded from 700 KB to almost 8 MB. No surprise that I decided to waive the idea. Flags would have been a nice little eye catcher, but they definitely do not justify this file size increase.

However, this made me curious. Why is this happening? The total size of the 28 inserted pictures was 1.2 MB, so I expected the workbook size to increase by this, maybe a bit more. But it didn’t. It increased by more than 7 MB.

I decided to take a closer look.

The Investigation

In order to watch the increase of the size, I started with an empty workbook and made one step at a time:

Step 1: Create a new Excel workbook and save it.

File Size: 8.7 KB

A little overhead of information is stored even in an empty file. No surprise.

Step 2: Insert the data (no formulas, just the data):

Camera Objects bloat File Size - Step 2

File Size: 10.2 KB

I would have expected the file size to increase even more.

Step 3: Insert the 28 PNG pictures of flag buttons of the EU member states. The original PNG files have between 34 and 75 KB, 1.2 MB in total:

Camera Objects bloat File Size - Step 2

File Size: 1.22 MB

No surprise again. Storing 1.2 MB pictures in a workbook increases the file size by a little bit more than 1.2 MB.

Step 4: Decrease the size of the pictures in Excel. To be crystal clear: resizing, not compressing:

Camera Objects bloat File Size - Step 4

This time, I was first surprised by the result:

File Size: 158 KB

Only resizing the pictures dramatically reduced the file size. On second thought this makes sense. By reducing the size of the picture, Excel reduces the resolution from e.g. 256×256 pixels to 40×40 pixels. Less pixels, smaller file size. Saved as a picture again, the smaller icons had between 2.6 and 3.7 KB each and 88.6 KB in total.

Step 5: Reposition the pictures and include only one Camera Object to one flag button:

Camera Objects bloat File Size - Step 5

The file size increases already considerably:

File Size: 187 KB

Not yet a problem, but an indication. Inserting only one camera object linked to a cell range where an image is sitting on top of the cell, increases the file size by 29 KB (18%) although the image itself has only 2.7 KB.

Step 6: Create the remaining 27 camera objects for the other member states:

Camera Objects bloat File Size - Step 6

The issue scales:

File Size: 893 KB

28 camera objects bloat the workbook to more than 5 times the size.

When I conducted the same steps as described above, but skipped step 4, i.e. left the pictures at their original size, inserting the camera objects increased the file size from 1.22 MB to 7.92 MB, i.e. almost 6.5 times the size.

Why is that?

There is evidence that a Camera Object is not only storing the link to the range, but rather the picture captured there. For instance, if you move the target cell range to another sheet or delete the range, the picture is still shown in the camera object, although the link is broken. If you select the Camera Object, you see #REF in the formula bar but the picture is still being displayed:

Camera Objects bloat File Size - #REF

But still, even if Excel saves the images again in the Camera Objects, I would have expected the file size to double after inserting the linked pictures. How come the file explodes to more than 5 times the size? What is going on there?

A closer look inside

Let’s look inside the Excel workbooks to find out.

Excel workbooks since version 2007 are actually zipped folders containing XML and other files. If you rename the extension of the Excel file from .xlsx to .zip, you can open the workbook as a zipped folder with Windows Explorer and you see something like this:

Camera Objects bloat File Size - Inside Excel 1Inside the folder “xl” is another folder called “media”. This is where Excel stores the pictures. Let’s first look at the workbook created in Step 4 (see above), i.e. the file with the pictures, but without the camera objects:

Camera Objects bloat File Size - Inside Excel 2The folder contains 28 PNG (Portable Network Graphics) files: the inserted flag buttons.

After I created one Camera Object linked to the cell where the first picture is located (step 5), the folder looks like this:

Camera Objects bloat File Size - Inside Excel 3The 28 PNG files again, but additionally 2 EMF (Windows Enhanced Metafiles). The EMF files are larger than the PNGs. Excel compresses them by ~50% but they are still almost three times the size of the PNG.

Let’s look into the final version of the workbook with the 28 camera objects (from step 6 above):

Camera Objects bloat File Size - Inside Excel 4

Same story. Excel keeps all 28 PNG files and inserts 2 additional EMF files for each camera object.

This is the root cause for the bloating of the file size:

  • Excel takes a snapshot of the cell range in an EMF file, which is ~3 times larger than the PNG file captured
  • Excel inserts not only one, but two EMF files for each Camera Object, so additional files of ~6 times the size of the original PNG are added to the workbook.

This explains the overall workbook increase described above.

Why is that?

Truth be told, I have no idea why Excel is doing this.

I assume that a snapshot of a cell range in a Camera Object is always stored in EMF file format by definition. Furthermore I could imagine that storing the latest snapshot within the workbook (instead of storing only the reference to the range) might have advantages regarding the performance: Excel doesn’t have to update the Camera Objects every time the workbook is opened.

Storing them twice, however, stays a mystery to me. I copied 2 corresponding EMF files from the folder and compared them using the Windows comp command. They are identical. Why Excel is inserting the same EMF file twice is beyond my comprehension.

Recommendation

The file size issue may become a problem under certain circumstances, but Camera Objects are still a very flexible and powerful Excel technique. You should keep on using them (or start using them, if you never did before), but be careful, especially if you are linking to cell ranges with an image sitting on top. A few Camera Objects usually do no harm, but mind the file size if you are using them extensively and/or linked to cell ranges with inserted pictures on top.

Stay tuned.

Add a Comment

Your email address will not be published. Required fields are marked *