EXCEL POWER USER QUICK GUIDES
by Szilvia Juhasz
"Laminate Worthy" Quick Guides for the Excel Power User. Includes:
- Shortcuts & Timesavers for the Power User
- Quick Reference Guide for Lookups and Conditional Calculations
I’m not always the biggest fan of info graphics. Many of the posters-sized info graphics released these days have issues. But lately I’ve also received several requests on how to do info graphics with Excel. Many people don’t know where to start.
Info graphics differ somewhat from your usual dashboard-style reporting. When we report with business tools, we use the data points–charts, tables, etc–to investigate a problem or monitor a system. That is, we use data to find results. Info graphics are used when we already know the results and we want to present it in an interesting, sometimes even artistic, way. Info graphics, then, are more about style and appearance–they wouldn’t necessarily find a good home on a dashboard. But they do work well in magazines, newspapers, and some student projects.
To help you follow along, go ahead and download the file that accompanies this post:
Many info graphics are made with graphic editing programs like Adobe Illustrator. As far as I know, these illustrations are static. So each change in the underlying data won’t be automatically updated in the graphic. You would just have to redraw the graphic. Excel provides a benefit here: if we use Excel’s charts to make our info graphics, we can update the underlying data and the result appears automatically.
Below, I’ve listed three different types of infographics: (1) a waffle chart; (2) a progress meter; and (3) a donut chart.
The waffle chart is simply a variation of the one created by Michael Alexander. You can go to his website to find out how they’re made.
Progress is a good thing, right? Below is a progress meter info graphic created with Excel. The chart is automatically linked to the cell F2 as seen in the picture, so changing that value will change the chart’s presentation automatically.
To create your own, you’ll start with a loveable bar chart. Below, I’ve linked the chart to our data cell, F2.
Now, you’ll have to make a few changes to get the chart ready.
If all went according to plan, you should a minimalist chart like the one shown below.
Now this next part is tricky. In the above picture, you can see plot area of the chart has been selected. That leaves a very large margin between the plot area and the chart area. For this next step, we want to make the plot area to be exactly the same size as the larger chart area. You can do this by dragging the anchors of the plot area beyond the contours of the chart area. You can see I’m doing this for the top anchor below. You’ll need to it for all four sides.
When complete, your chart should look something like this when selected.
In this next step, we’re going to anchor the entire chart to a cell. Specifically, in the image below, I am going to anchor the chart to cell B7.
To get the chart to fit to the cell exactly, we’re going to use the Snap-To-Grid feature. Once you’ve selected the chart, you can enable Snap-To-Grid from in Align dropdown from the on the Format context tab. Simply drag the anchors to fit in the cell as I do below.
Once snapped, notice you resize Row 7 and the chart height will size accordingly. Snapping the chart to a cell allows us to make quick size changes immediately without having to select the chart. It’s a huge timesaver, in my opinion.
As you can see from the chart below, I’ve decided to give the bar an electric green color. I’ve also right-clicked the bar chart and added data labels from on the popup menu.
You can select the data label to change it’s options and make it prettier. From the Format tab, you can change how the data label is presented. For starters, I’ve changed it’s shape to a rounded rectangle. The Change Shape dropdown is in the Insert Shapes group on the Format tab. To make things easy, I’ve simply selected one of the preloaded defaults (it’s rare I ever do that, but the colors fit so well) for its background and font color.
Now, with the data label selected, turn off Snap-To-Grid and then resize the label for effect. In addition, you can also select the plot area and give it a more stylistic background color as I’ve down below. Finally, you can add some color formatting in the cells that surround the chart.
Our final task will be to give the chart above that appearance being rounded. Select the chart above, and from on the Format context tab, ensure the shape outline has been set to none. Next, from on the Inset tab, insert a new rounded rectangle shape. The shape will require some formatting, so do this:
Once you’ve done these steps, flip Snap-To-Grid back on and size the shape to fill up the same cell area currently holding your bar chart. Your screen should look something like this:
Now, with the shape still selected, change its border width from on the Format tab. There’s no specific width you should set it to, but just remember you want it to be thick. A size too small will show the corners of the bar chart. Below, the width has been set to 4 1/2.
And that’s all it takes to make the progress meter! Remember, the bar chart is linked to the cell, so if you want to reuse the progress meter, simply change the value in the linked cell. Also, remember the chart and rounded rectangle have been anchored to a cell. So to easily make the chart larger or small, simply change the row and column size.
Don’t worry, the donut chart is easier to make than the progress chart. The basics of a donut chart are the current value (as a percentage) and its complement. The complement is simply the leftover when the value is taken away from 100%. The image below shows the value and complement. Cell D4 shows the formula used in cell D3.
To make your donut chart, select your value and complement and then insert a new donut chart from the Insert menu. See below.
As we’ve done previously, we’ll need to clean up a few things. Go ahead and delete the legend. You can also change the colors as I’ve done below. Personally, I like making the value a stronger color than the complement. This allows us to clearly see the signal of the chart. I’ve also moved the chart title to the center. More on that in a second.
Personally, I don’t really like how value (that green portion) starts at the top. So under the series options, I’ve set the Angle of the first slice to 90 degrees.
Now, we’re going to use the Chart Title to put something interesting inside the donut chart. Select that chart title, then up in the formula bar, link it to whatever cell holds what you want to appear in the center. You could go for the value as is popular. I’ve done this below.
But I’m partial to have an icon appear instead—another popular choice. I’ve used an icon from in the Webdings font. When you do that, you need to ensure you chart title is also set to use the Webdings font. Below, I’ve also created a new textbox and linked it to a cell holding the 35%. I also set the outlines of all the charts and textboxes to none. Finally, I set all items to have no fill and set the background color of the cells to be white. How does it look?
You maybe wondering why you’d even want to do an info graphic an Excel given all the other available illustration programs. Well, as we’ve covered, it’s easily reusable by changing a number in a cell. You don’t have to redraw it as you might in another program.
The second advantage comes in laying out the whole info graphic in Excel. If you have Excel 2013, you can select the cells bounding the charts and copy it it to the clipboard as you would anything else.
Then, on another sheet, you can paste the chart as a linked picture.
Now, you can treat a new worksheet tab as your canvas. The linked pictures move around the spreadsheet easily as any shape. This dynamic allows you to easily create your info graphic components on another tab. Then, on a final tab, you can lay everything out as desired by treating the charts as pictures.
If you don’t have Excel 2013, you can always use the Camera Tool instead. Unfortunately, the pictures are of lesser quality with the camera tool, but still look good.
If you missed downloading the file at the start of the this blog post, make sure to get it now.
Please log in again. The login page will open in a new window. After logging in you can close it and return to this page.
"Laminate Worthy" Quick Guides for the Excel Power User. Includes: