In this tutorial, you’ll learn how to create a Business Week Style Graph using Microsoft Excel. For this tutorial, I used Microsoft Excel 2007.

Original Graph

I think Business Week has some of the nicest, most consistent data visualizations.  Below is the graphic we will duplicate.

Let’s get started.

Step 1 – Create Spreadsheet

Create a spreadsheet with the following data.

To get the apostrophe’s to appear in column A, you need to enter a double apostrophe.

Step 2 – Create Chart

Select the range A1:C16 and Insert an Area Chart (choose Insert – Area – 2-D Area Chart from the menus)

You should end up with something like this:

Step 3 – Remove Legend

Remove the legend by single clicking on the legend to select it and then press the Delete key.

Step 4 – Format Cells

We are going to put the graph in cells H17:H20. Let’s prepare the size of these cells.

a. Make Column H 425 pixels (For directions, click here.)
b. Make Row 17 a height of 38 pixels.
b. Make Row 18 a height of 50 pixels.
c. Make Row 19 a height of 364 pixels.
d. Make Row 20 a height of 33 pixels.

Step 5 – Move Chart

Drag the area chart into cell H19. Keep the Alt key depressed while you move the chart. This will align the chart to cell boundaries. Once you have moved the chart, click the bottom right corner of the chart and reduce the size of the chart to the size of cell H19, keeping Alt key depressed while you scale the chart.

Step 6 – Format Y-Axis

Single click on the Y-axis to highlight it. Right click on the selection and choose Format Axis.

Under Axis Options, enter the following options:

Click on the Number tab:

Enter 0 in the Decimal Places field. I’m not aware of a way to remove the % sign from the Axis. I prefer it there. As an alternative, you could format the field as a Number and multiply it by 100.

While you still have the y-axis selected, choose a small font and make it bold.

Step 7 – Format X-Axis

Single click on the X-axis, right click, and choose Format Axis.

Choose the following options:

Your chart should now look like this:

Step 8 – Add Tick Marks

When you have negative numbers and you want to correctly place the zero-point at 0, you need a couple of extra steps to get your tick marks to appear. We will use a scatter plot to create the tick marks.

Add the following data to Column D:

Move your cursor to the graph. Right click somewhere between the horizontal grid lines in the plot area and choose Select Data. Choose Add Series button.

Enter the range D2:D16 in the Series values field.

Click OK and then OK again.

The graph will look like this:

Right click on the new area graph and choose Change Series Chart Type. Choose X Y (Scatter) – Scatter with only markers from the dialog box.

Choose Insert – Shapes – Line.

The cursor will change to a plus sign. Click anywhere on the spreadsheet and draw a vertical straight line (depress Shift key). When you release the mouse button, the Drawing Tools – Format menu will be open.

Make sure the Shape Outline is black and make the Width approximately .08″.

While the line is selected, right click on it and choose Copy.

Left click on any data point in the Scatter Plot to select them. It will look like this:

Press Ctrl-V to replace the current scatter plot marker with the line you just created.

Lastly, right click on the X-axis and re-select Position Axis – On Tick Marks

This should give you the following result:

Step 9 – Format Chart

These are some minor, but important, changes.

Place the mouse just slightly below the X-axis labels and right click. If you are in the right place, you should be able to choose Format Chart Area.

On the Fill tab, choose No Fill. On the Border Color tab, choose No line.

Place your mouse just under the grid line at 4% and right click. The Plot Area will be selected. You should see the scaling boxes.

Click on the middle bottom box and pull down (keeping the mouse button depressed) just below row 19 to extend the plot as far down as possible.

Click on the left middle box and pull to the left (keeping the mouse button depressed) into column G to extend the plot as far left as possible.

Click on the right middle box and pull to the right (keeping the mouse button depressed into column I to extend the plot as far right as possible.

Click on the top middle box and pull it down just a little to make room for the “Percent Change From A Year Ago” label.

The result will look like this.

Step 10 – Create Text Labels

We will be creating 3 Text Labels (Insert – Text Box from the menus and then click anywhere on the worksheet. The text box can be moved after it is created.)

Text Box 1: Choose a font size of 10 and make the font bold. Type: PERCENT CHANGE FROM A YEAR AGO. Position this label on top of the 4% grid line toward the left.

Text Box 2: Choose a font size of 10 and make the font bold. Type: UNIT LABOR COSTS. Position this label between the 4% and 3% grid lines.

Text Box3: Choose a font size of 9 and make the font bold. Type: EST. Position the label below the 2009 III quarter data point.

Draw a black horizontal line (Insert – Shape - Line) with a width of .2″ and place it above the EST. label.

Lastly, right click on the Y-axis, choose Format Axis, click on the Line Color tab, and choose No line. This will remove the Y-axis line.

We are almost done with the chart area. It should look something like this.

Step 11 – Change Plot Color

Right click any where in the colored area chart and choose Format Data Series. Click on the Fill tab. Choose Solid fill. I chose a color from the standard theme: Orange Accent 6, Darker 25% (right column, 2nd from the bottom). Choose a Transparency of 20%.

Click on any of the grid lines and right click. Choose Format Gridlines. Choose the Line Style tab and change the Width to 1.25 pt.

The graph will look like this:

Step 12 – Title and Data Source

In Cell H17, enter: AGGRESSIVE COST CONTROL

In Cell H18, enter: IS BUOYING PROFITS

Format the two cells with 20pt, Top Align, Wrap Text, and Bold. If you want, you can pick a font that matches more closely. I stayed with Calibri. In each cell, hit the increase indent button once to move the text to the right.

In Cell H20, enter: Data: Labor Dept., IHS Global Insight, BusinessWeek

Format the cell with 11pt, Top Align, and Bold. I hit the increase indent button once. I applied italics to BusinessWeek.

I then selected H17:H20, I clicked on the Borders button drop down and chose More Borders and applied the following settings.

Here is the final results.

Here is the original for comparison purposes. Pretty close, I think.

Hope you learned some things in the tutorial.

I’d love to hear your comments (especially, if you find an error or a better suggestion for the tutorial).

Comments