Tony Rose of Decision Support Analytics tried the Export Chart add-in I posted in Enhanced Export Chart Procedure. Tony wondered about my statement that PNG images were so much superior to JPGs for Excel charts. So he did a quick and dirty experiment, and reported in Excel Add-in: Export Chart Feature that there was not much difference.
When I checked out Tony’s charts, I saw that they were nearly identical, and if I weren’t looking for differences I may not have noticed much. The major difference is that his JPG was substantially larger, 15476 bytes vs. 9549 bytes for the PNG.
I did think the text and the axis lines were less crisp in Tony’s JPG, though the PNG also was not as sharp as I like in my charts. I’ve felt that Excel 2007 has added this fuzziness to its visuals; I explicitly turn off things like ClearType, and I don’t care for the fact that Excel imposes this feature on me without my consent. I suspect but have yet to confirm that Tony’s using Excel 2007. Tony has informed me that he is in fact using Excel 2003.
Tony’s test chart was pretty simple. I decided to do my own test using a somewhat more involved chart. Below are PNG, JPG, and GIF files of my chart, showing one of my favorite topics, pageview stats for my blog.
Excel 2003 Exported Chart, PNG format, 7580 bytes
Excel 2003 Exported Chart, JPG format, 23350 bytes
Excel 2003 Exported Chart, GIF format, 9064 bytes
There seems to be no visible difference between PNG and GIF. Colors in the JPG are not rendered as faithfully to the Excel chart as in the PNG and GIF. The PNG and GIF show sharp color transitions from pixel to pixel, as expected in a non-JPG bitmap image. Text and axis lines are rendered fine in the JPG, but there is a little muddiness around the markers in the JPG, which is particularly pronounced around the “x” markers. The JPG file is three times the size of the PNG, while the GIF is only about 15% larger.
I decided to test the output of my add-in in Excel 2007. The add-in works properly in Excel 2007, with the Export Chart button finding a home on the Add-Ins tab. The dialog works without any issues, and the image files appear where expected.
Excel 2007 Exported Chart, PNG format, 28028 bytes
Excel 2007 Exported Chart, JPG format, 20425bytes
Excel 2007 Exported Chart, GIF format, 17724 bytes
All of the Excel 2007 images look fuzzier and more washed out than their Excel 2003 counterparts. The colors are slightly washed out. There is obviously some anti-aliasing in the 2007 images, which can be seen comparing sloped series lines. The 2007 JPG shows a bit more fuzziness than the PNG and GIF, but it actually looks better in 2007 than in 2003. The interesting observation is that the PNG is the largest of the three image files, and the GIF is only slightly smaller than the JPG. In fact, the 2007 JPG is smaller than its 2003 counterpart.
Bottom line: For Excel 2003 and earlier, PNG and GIF exported images are smaller and crisper than the JPG images. For Excel 2007 it’s a wash: The 2007 JPG looks better than its 2003 counterpart, while the 2007 PNGs and GIFs have become less clear and sharp. This is probably due to the anti-aliasing built into Office 2007 shapes; which not only changes the exported image file quality, it also has had a profound negative effect on how Excel charts behave after being copied, then pasted as a picture. But that’s a topic for another day (note to self…).
Please feel free to share your thoughts on the quality of these images.
Tony Rose says
If I had to choose the one I think renders best, I would go with the Excel 2007.png format. The gridlines and markers make it a lot easier to see the differences outside of the file size.
Thanks Jon!
derek says
I think what hammers the PNG file size in Excel 2007 is the default 24 bit color, with 16 million colors available. I took the PNG versions of the 2007 and 2003 graphs, and resaved them using Paint Shop Pro, with 256, 64, and 16 colors respectively (8, 6, and 4 bits). I selected Optimised Median Cut in all cases; that just means one of the ways to assign the best possible palette of colours.
2007, 256 colors (11.7 KB)
2007, 64 colors (9.93 KB)
2003, 256 colors (4.85 KB)
2003, 64 colors (4.85 KB)
2003, 16 colors (4.14 KB)
A mere 16 colors wasn’t enough to support the anti-aliasing of the XL2007 graph, while it seems 256 colors was more than the XL2003 graph needed, since 64 colors clearly yielded no loss of information, because there was no file shrinkage.
I freely admit that, beyond a reasonable size, excessive effort to squeeze the files down becomes just a hobby, that working people can’t afford to indulge in, but some choices can be surprisingly effortless, and eschewing the unneeded 16 million colors is easy if your software allows it.
Does the export facility of Excel, any version, allow the macro writer to choose the color depth? If not, then GIF may be preferable to PNG, as it uses 256 colors by default.
Jon Peltier says
Derek – Thanks for the analysis. The Chart.Export method allows for no adjustment of color depth. You get what you get, and you’d better like it.
I understood that a GIF allowed up to 256 colors in its palette. I’m not sure what your point is about using GIF.
And regarding the PNG files you posted, I could not see any difference among the three Excel 2003 charts. The 256 color Excel 2007 chart seemed to have more highly saturated colors and darker text than the 64 color Excel 2007 chart. This sounds like an ad for laundry detergent: My whites are whiter, my brights are brighter.
derek says
I noticed the slightly darker text of the 256 color PNG in the XL2007 image, and I’m guessing that, just as the ability to faithfully reproduce the anti-aliasing broke down completely with only 16 colors, so it was already starting to break with 64. Paint Shop Pro did the best it could, but was forced to re-use a dark gray for text, instead of assigning it its original proper black.
With the XL2003 images, you could not see any difference between 256, 64, and 16 colors, and yet the 16 color version was a smaller, less bloated file. How much more futile, then is 16 *million* colors? That’s my point about not using PNG.
I understood that a GIF allowed *up to* 256 colors in its palette.
You’re right, I should have put it a different way: PNG, as exported by Excel, assigns each pixel three whole bytes, to allow for up to 16 million colors, even if there are fewer than 256 colors in the image. GIF allots only one byte per pixel, immediately conferring a space saving.
Modern PNG is potentially more compact than old-fashioned GIF, because of its slightly more up-to-date algorithm, but that’s critically dependent on having a program that lets you specify as many bytes as you need for the colors, and no more. Without that control, PNG becomes the inferior of the two formats, because GIF gets you all the graphic depth you need in a 2003-style chart, in a smaller file.
derek says
Actually, I just noticed something. Compare your TestChart2007.png with my TestChart2007-8bit.png. Blow them up really big; focus on something like the number 1300. You have sharp rectangular pixels, I have fuzzy rectangular pixels. What the hell?
How is that even possible? I’m going to have to ask someone who knows about the graphics to explain that.
Jon Peltier says
Does PSP do any antialiasing? Maybe while you were changing the color depth, it was also resampling the image and antialiasing the text (which was untouched in the original image).
derek says
But that’s down to the pixel level! How can it be anti-aliasing between the pixels?
Jon Peltier says
Okay, this time I looked at them, using IrfanView. Both your 8-bit and my original at 400x have sharp square pixels in the text areas, black next to white. Even your 6-bit has sharp pixels too, but dark gray instead of black.
derek says
Yes, and I looked in Paint Shop Pro, and they’re sharp. It looks like something to do with my browser, Opera, and I’ve asked on one of their newsgroups. I’ve also tried it with a specially-saved 24-bit version, no other alterations. This is getting far away from Excel or infographics issues, but I’ll report back what they say for completeness’ sake.
One difference I did notice: I’ve been calling your Excel 2007 saved file 24-bit color. It’s not, it’s 32-bit color. I’m amazed at the concept of 4 billion possible colors.
john says
I own a small company that provides an Excel addin that produces thousands of charts that we paste special as metafiles. In 2007, we can only run about 800 of charts (stored as pics) before we get an error “Unable to Save” (we save after so many charts – user defined). In 2003 we can run 3200 without a so much as burp. I can’t even load an output file (from our addin) in 2007 (Not enough system resources) that 2003 displays just fine.
Has anyone seen this before? Personally, Excel 2007 seems like 10 steps backwards when it comes to industrial applications.
Jon Peltier says
John –
I haven’t used 2007 enough to have come across this problem, and I don’t know what is the largest number of metafiles I’ve worked with in a single Excel session (dozens, not thousands). I can’t say anything intelligent about your issue.
I have a different question. Do you notice a difference in the quality of your metafiles in Office 2007 compared to those in Office 2003?
John says
John P:
I haven’t noticed any difference, but I haven’t really looked. We only use metafiles because in the past, an Excel object took more memory than a picture. So we show the users pictures; we store alternative text, then use that to recreate various Excel objects using data we mined from other sources.
I spoke with my development team about this yesterday. I think MS has made it very difficult to store pics; a file in 2003 that used to have 1000 pics be 30 ro 40 megs; in 2007 that is 100 megs. We are going to cut out the pics and just use standard Excel graphs that we populate with spinner bars, etc.
I think MS grossly underestimated how many industrial users out there created daily reports using chart objects. Many of those macros were written by users who did not document the code and have since left. One of my customers has 35,000 spreadsheets they think are going to be impacted by 2007.
My question is…why change something that worked? What were you thinking Uncle Bill? Personally, I like the Microsoft monopoly – there are plenty of small companies like mine that only have to worry about one spreadsheet, but I seriously think 2007 puts the monopoly at risk.
Jon Peltier says
John –
I don’t think they put the monopoly in serious risk, but they sure caused a lot of problems. I understand wanting to update the shapes and charts, but all they effectively changed were formatting and ease of use. The default charts may look nicer, but I don’t like them any more than the old defaults, so I have to change as much about a 2007 chart as a 2003 chart, and the new UI makes it more work to change.
The new metafiles are in fact larger. To accommodate lines that may have a gradient, these lines are rendered in the new metafile by rectangles with gradient fill. So if you ungroup the metafile, the shapes you get are different than before, and to provide the antialiasing effect (which I WISH I could turn off), some lines may be a different color or width than the others. No more sharp looking graphics; everything’s blurry.
derek says
Report back on my fuzziness question: neither the Paint Shop Pro nor Opera newsgroups responded to my query. Reasonable in the former case, as it appears to be nothing to do with them. Experimentation showed the following:
PSP: both files sharp
IrfanView: both files sharp (source Jon)
Opera: Jon’s file sharp, mine fuzzy
Microsoft XP Home’s built-in Image and Fax Viewer: both files fuzzy
It seems to be a display eccentricity of Opera. I shan’t be worrying about it, as it only shows at extreme zoom.
Jon Peltier says
Derek – What if you use a browser other than Opera?
John says
Jon:
I don’t think the only change in Excel 2007 was formatting and ease of use; they changed the entire chart object model. Code that used to work doesn’t anymore. This is the first time in my institutional memory that a new release of Excel is not backwards compatible with older ones.
John
derek says
The only other browser I have is a version of MS Internet Explorer that can’t zoom, and I’m not dedicated enough to go installing more browsers just to check.
Jon Peltier says
John –
I was being flip. I know they completely rebuilt the charting facility from the ground up. I know it was a lot of work, yet they didn’t quite finish. I’m continually finding things that didn’t get implemented quite the same as before.
What I meant is that the only apparent changes were in the formatting options, and in the dialogs, which don’t seem as inclusive or effective as the old ones.
Eric says
Jon:
Thanks for the analysis.
At work I use Excel to generate very complex charts and then save as htm format. After the company upgrated to 2007, I notice immediately that charts inside Excel-based htm files looks very blurry as if it loses focus.
I wonder if you have come across this problem. If yes, could you repsond and share some of your thought or solutions?
Thanks,
Eric
Jon Peltier says
Hi Eric –
I’ve actually blogged about this issue in Why I don’t like Excel 2007 charts. Some people don’t agree, but apparently you and I think that this new look is fuzzy and out of focus.
Eric says
Hi Jon –
Thank you so much for such quick response.
At work, we extensively use Access/SQL to pull and manipuldate data, dump data into Excel templates and call Excel VBA to generate charts/reports, publish workbook to web servers or save Excel as htm on web servers so that reports and charts are refreshed and pubished automatically.
Regular line or bar charts look similar or are of very little visible difference between Excel 2007 and 2003. Some user like the slight blurrines created Excel 2007’s graphic engine.
But for charts that are complex or that have a lot of information (for example, in one chart there are dual axis, mutiple series of different chart types – lines, bars, areas, labels, text boxes…etc) the blurry effect can become a problem… :(
These complex charts saved as PNG file look as good as original Excel charts. But if I save these workbooks with charts as htm format and post it on a web server. Charts look blurry and out of focus when you open the file in Internet Explorer.
I also noticed that in Excel 2007, recording macro of certain activities, such as importing pictures, changing picutre object format settings cannot record any VBA codes…
I miss the Office 2000 to Office 2003 upgrade :)
Jon Peltier says
Eric – What format are the image files that Excel saves? Save As HTML produces HTML files with the text, and embedded in them are image files. Are these images PNG, GIF, or JPG? I can imagine the JPGs looking atrocious.
Eric says
Jon,
Say… I save an Excel workbook as Report1.htm
Inside the “Report1_files” folder generated by Excel, charts are saved in “PNG” format which are bigger in size than and also almost as clear as original Excel Charts.
I think the problem happens when Excel generates the html text. PNG pictures look fine but for some reason I do not know, pictures look blurry in IE
Jon Peltier says
I can think of two reasons for the blurriness.
1. The size that the HTML code uses as the dimensions of the images is not the same as the dimensions of the image files. The pictures are not shown at their true size, and some blurring occurs as they are mushed into the allotted size.
2. IE itself provides additional antialiasing or blurring.
Eric says
Jon,
I compared the difference of PNG/GIF files generated by Excel 2003 and 2007 when saving spreadsheet as htm formats.
PNG/GIF files in the files folder from both 2003 and 2007 look very clear.
PNG/GIF files from 2007 are larger in resolutions and the htm codes generated by Excel resize them to fit into the original dimensions as seen in Excel.
This is when the blurriness happens.
It almost feels like someone from MS can change the parameters fairly quick to fix this issue but who knows how long or if this problem will ever be resolved… :(
Thanks a lot for your explanations. And your blog is very informative and helpful!!!
Jon Peltier says
Good detective work. I guess the exports weren’t bad enough, so they messed with the image sizes.
Compare Files says
Nice Explnation on image file type Comparison With Exported Charts
Frank Hansen says
My biggest problem with the chart quality is not the bars etc, but the text rendering that is horrible.
And I disagree with you – I get better results with JPG than with GIF and PNG.
PowerPoint has the option of right-clicking charts and “Save as image”. GIFs from PowerPoint are very poor. PNGs are a little better but the text seems “diluted”, perhaps because of the automatic transparency. But JPGs from PowerPoint are far far better! I wish Excel could export with the same quality, as I don’t want to use Powerpoint for producing the images (used on a web site and/or incorporated in PDFs).
So what is the secret of PowerPoint?
Jon Peltier says
Frank –
I’m not sure you disagree with me.
In Excel 2003 and earlier, the exported GIF/PNG images were indistinguishable from each other, and were superior to the JGP images. In Excel 2007 and later, the images are closer in appearance to each other, and in my opinion at least, inferior to the best exported images from Excel 2003.
What may affect your text quality is whether your Windows settings have ClearType enabled. ClearType should improve the text rendering.
I don’t know if PowerPoint has any “secret”, other than the right-click ability to export objects as graphics.