Wrapping up my series on dashboards, I want to address an issue I see in just about every spreadsheet I’ve seen in my career as a marketer: using Excel’s (hideous) default formatting for visualizations.

You’ve seen them … the blue and red charts are particularly unforgivable. Worst of all, sticking with Excel’s default formatted table and pivot table styles can also be discordant, especially if you are a brand with your own panoply of branded colors.

So, let’s see if we can remedy that with this post and make the digital world more beautiful.

Customizing Chart Formats

If you haven’t run across any of my charting resources, check out this video tutorial I produced and this post I wrote on Search Engine Land about making your charts sexier.

I highly encourage you to use your company’s branded colors in your charts (always). If you don’t know them, you can use the Eye Dropper extension in Google Chrome, or use the Color Picker under the Miscellaneous tab of the Web Developer Toolbar.

Webmaster Developer Tools color picker

Once you create your branded charts, you should save them as templates. This will save the template in a folder on your hard drive. If you’re not in the practice of saving templates, this post/video I published on the Moz site will walk you through that process.

manage chart templates

Customizing Formatted Tables

I’m a huge proponent of using formatted tables as the first line of defense in corralling data. But Microsoft doesn’t provide us with too many formatting options. However, it’s not terribly difficult at all to format them.

The Microsoft site offers this tutorial for PC users. It was written for 2007, but the steps still apply to current versions of Excel. I couldn’t find any tutorials on the Microsoft site that demonstrated how to apply custom styles for the 2011 Mac version (surprise, surprise), but the same principles apply that you’ll see in the tutorial for PC users.

Excel formatted table

Customizing Pivot Tables

Microsoft offers a number of ways to customize pivot tables. The Microsoft site points to a very useful post published by Rider University. But, the way I customize mine is with the pivot table selected: go to PivotTable Tools > Design tab > New PivotTable Style (Mac: Data > PivotTable > PivotTable Styles > New PivotTable Style), which will bring up the New PivotTable Quick Style dialog.

pivot table styles

The options under Table Element are always the most intuitive, so what I do if something’s not clear is set the style to neon yellow fill and look to see what turned yellow. You just have to play around until you come up with a custom format you’re comfortable with.

Applying Custom Styles

One of the absolute dumbest limitations I’ve seen in Excel is the inability to save custom tables and pivot tables to use in other workbooks. I mean, you can with charts. It’s pretty insane. But there is a hack to get around this that I learned from the Contextures blog. And I mean hack.

What I do to apply all of my custom formats from one workbook to the next is save all of my custom formats in a single spreadsheet. So I just have a formatted table and pivot table with minimal data in them to keep the file size small. I also included a formatted text box since I frequently use these in client workbooks.

And then, I right-click on the tab of the worksheet I want to copy, choose Move or Copy, select the Create a copy option (very important), and choose the workbook I want to copy the worksheet into.

move worksheets in Excel

Once you pull it in to your workbook, you can delete the worksheet. Just bringing that formatted worksheet into a new workbook, even momentarily, is enough for Excel to access the styles, and they will be available to you when you go to format your table, pivot table, or text boxes.

Kind of ridiculous, but whatever works!

So, please stop using Excel’s nasty default formats, create your own, and present data that’s as pretty as it is actionable.

Opinions expressed in the article are those of the guest author and not necessarily Marketing Land.

Related Topics: Analytics | Analytics & Marketing Column | Channel: Analytics | How To Guides | How To Guides: Marketing Analytics

Sponsored


About The Author: is an SEO and analytics consultant. Her areas of expertise are analytics, technical SEO, and everything to do with data — collection, analysis, and beautification. She’s on a mission to rid the world of ugly data, one spreadsheet at a time. If you just can’t get enough data visualization tips, you can check out her blog, Annielytics.com.



Sign Up To Get This Newsletter Via Email:  


Share

Other ways to share:

Read before commenting! We welcome constructive comments and allow any that meet our common sense criteria. This means being respectful and polite to others. It means providing helpful information that contributes to a story or discussion. It means leaving links only that substantially add further to a discussion. Comments using foul language, being disrespectful to others or otherwise violating what we believe are common sense standards of discussion will be deleted. You can read more about our comments policy here.
  • ddalgleish

    I’m glad my hack helped, and it’s a pain to go through all that, just to copy our custom styles. Have you considered creating a workbook template with those styles, and using that instead of the default blank workbook?

  • netmeg

    I’ve been using that hack for a year and it ticks me off no end to have to do it. I love Excel and spend most of my day in it, but there are two things that grind me no end – not being able to easily save custom table styles to other spreadsheets, and the other is not being able to export your custom Quick Access toolbar so you can import it on other computers. Every time I get a new computer or lose a hard drive, I have to re-create it all over again.

    Great info as always, Annie!

 

Get Our News, Everywhere!

Daily Email:

Follow Marketing Land on Twitter @marketingland Like Marketing Land on Facebook Follow Marketing Land on Google+ Subscribe to Our Feed! Join our LinkedIn Group Check out our Tumblr! See us on Pinterest

 
 

Click to watch SMX conference video

Join us at one of our SMX or MarTech events:

United States

Europe

Australia & China

Learn more about: SMX | MarTech


Free Daily Marketing News!

Marketing Day is a once-per-day newsletter update - sign up below and get the news delivered to you!