Awesome Excel Chart Quick Tips for Web Display

Last week, my colleagues and I created 3 Excel spreadsheets in an effort to highlight some of VCU participants' responses to the EDUCAUSE ECAR Undergraduate Student and Faculty Technology Research Studies Surveys. This division of labor resulted in an unfortunate consequence. Different machines, different data, and different perspectives resulted in different data presentation styles. To standardize the look of the charts, I offered to be the general editor. One set of eyes working on one machine is likely the best way to make the look and language of the charts consistent. This is our first pass at presenting the data. We had wanted to present the data using a more interactive format. For now, we've created a blogpost for each chart and used WordPress's Facet plugin to facilitate a search-mix-and-connect possibility. At this point, our thinking is that, perhaps, once we see what the overall data findings show in this round, we could work on improving the data presentation style.

At first, we were limited by Microsoft's color palette of Chart Styles in Excel. All three of us had agreed on a blue-red-green style because the Student Survey I was working on had 3 columns of data to compare (VCU 2013, VCU 2014 and National Average 2014). Once we uploaded all the images from the 2 surveys, the colors looked pretty dismal on the web. Tom Woodward, my supervisor, suggested we google for some "awesome color palettes for websites" to replace the hideous-looking charts.

A google search led us to the Betterment site with a collection of web color palettes. The palette for us had to allow for high contrast and yet be aesthetically pleasing. #42 - Adam Hartwig's palette it is. Thank you, Mr. Hartwig! [Note: He has a pretty awesome website himself, check it out.]


Creating a Palette in Excel (Mac computer)

I had no idea how to insert Hartwig's palette into Excel's color charts. As far as I could recall, Excel had no option for a user to enter hexadecimal web color codes. Once again, Tom came to the rescue.

On an iMac, double-clicking on the chart area opens up a Format Plot Area window. From there, select Fill > click on the drop-down menu besides Automatic > select More Colors.

Format Chart Area Window

Make sure you are then on the Color wheel tab (see image below). See the Magnifier icon beside the white color box? Click on it and it turns into a color dropper.

Selecting the Correct Color

I had Hartwig's color palette open just beside the color wheel window. I clicked on all 5 colors and they showed up right away on the white little swatch boxes below the color wheel.

Hartwig's Color Palette Shows on My Color Swatch boxes!

Back to my Excel charts with this color palette. At first, I thought this arrangement looked okay, but after applying it to the existing charts, it began to look a bit too bubble-gummy and jaundiced-looking. Especially if I just had 2 columns of data - VCU 2014 and National Average 2014 to compare (yellow and green; yellow and green, ... yuk!). I couldn't tolerate it too long and didn't know how anyone else could.

Pink-Yellow-Green Color Selection

So after I had reformatted all the charts to the pink-yellow-green palette, I decided to switch it to another color arrangement - green, turquoise, black. It seemed to look more professional.

Green-Turquoise-Black Color Selection

Save Your Preferred Chart Style as a Template

How could I change all 70-something charts to the same color palette efficiently? The solution is to save my preferred Chart Style as a template. This option is available under the Chart > Other (Chart Type) menu. Click on the dropdown menu > Scroll to the bottom. See the Save as Template phrase?

Creating a Chart Template in Excel

Once your preferred template(s) is saved, you can click on your existing chart > click on Other > Select your XYZ template and apply it to the hideous chart, and voila! All the settings you had saved in your template apply to your existing chart -- including chart title, text, horizontal and vertical axes and colors. How amazing is this?! 

Even though I was a research assistant in grad school and processed much survey data, I had not once thought of how the chart colors might look set against an entire website. This perspective is new to me. I'm happy to learn time-saving ways to speed up the work process. Thanks Tom and Adam Hartwig!