Views > Excel charts > Tufte in excel - multifunctioning graphical elements

In chapter 7—Multifunctioning Graphical Elements—of The Visual Display of Quantitative Information, Edward Tufte proposes the following principle:

"Mobilize every graphical element, perhaps several times over, to show the data."

Consider, for example, the multifunctioning gridlines and labels in the graph below. By tying the grid to the bars the gridlines along with the labels positioned at the exact values double-function as the marginal distribution.

Mobilizing graphical elements to perform several functions—show several different pieces of data—can effectively display complex, multivariate data.

The data source for the above chart is shown below. It shows the monthly sales for 33 months.

Lets build it in Excel.

1. Highlight the range C2:C35 and then, on the Insert tab, in the Charts group, click Insert Column or Bar Chart > Clustered Column. The chart should look like this:

TUFTE IN EXCEL - MULTIFUNCTIONING GRAPHICAL ELEMENTS

2. Next, select the range C3:C35, press Ctrl + C to copy the cells into the clipboard, then select the chart and press Ctrl + V to paste the data into the chart. The chart should look like this:

3. Right-click the chart and then, on the shortcut menu, click Change Chart Type.
4. In the Change Chart Type dialog box select Combo under the All Charts tab.
5. Next, under the Choose the chart type and axis for your data series set the Chart Type to Scatter for the Series2 data series as per the below screenshot:

6. Press the Ok button to close the Change Chart Type dialog box. The chart should look like this:

7. In the chart select the Series2 data series.
9. Next, click the Charts Elements button located on the upper-right corner of the chart. Next, in the expanded menu, check the Error Bars option, as per the screenshot below:

10. Lets do some cleaning. Delete the Vertical (Value) Axis Major Gridlines.
11. Turn off the border of the Chart Area by setting the Border to No line.
12. Change the chart font to Trebuchet MS, its size to 7 and color to white.
13. Set the Shape Fill color of the Sales data series to No Fill.
14. Set the Marker Options for the Series2 data series to None.
15. Set the Axis position of the Horizontal (Category) Axis to On tick marks. Delete the Horizontal (Category) Axis.
16. Set the color of the Series 2 X Error Bars and the Series 2 Y Error Bars to white.
17. Set the Shape Fill color of the Chart Area to black and the chart should look like this:

19. Right-click the Series2 Y Error Bars in the chart and then, on the shortcut menu, click Format Error Bars.
20. In the Format Error Bars pane, with Error Bars Option icon selected, set the Direction to Minus and the End Style to No Cap.
21. Set Error Amount to Custom and click on the Specify Value button.
22. In the Custom Error Bars dialog box, edit the Negative Error Value to include cells C3:C35. Press the Ok button to close the Custom Error Bars dialog box.
23. Repeat steps 19-22 for the Series2 X Error Bars while selecting the range D3:D35 for the Negative Error Value and the chart should look like this:

24. Next, change the Line Width of the Series2 Y Error Bars to 1.75 pt.
25. Change the Line Width of the Series2 X Error Bars to 0.25 pt and its Dash type to Round Dot.
26. Adjust the Maximum Bound of the Vertical (Value) Axis to 9.6E6 and its Unit Major to 200,000.
27. Delete the Vertical (Value) Axis, adjust the size of the chart and the chart will look like this:

18. Next I’m going to resize the Error Bars to display the vertical bars and the horizontal grids. To do that introduce the following formulas in the respective cells:
D1 --> = COUNT(C3:C35)
D3 --> = SEQUENCE(D1,1,1,1)

The data source should look like this:

28. Next, I need to create a series to show the labels along a vertical axis at x = 0. To do this, add the following formula in the respective cell:
E3 --> = SEQUENCE(D1,1,0,0)

The data source should look like this:

29. Select the range C3:C35 and press Ctrl + C to copy the data to the clipboard. Next, select the chart and press Ctrl + V to paste the data into the chart.
30. Right-click the chart and then, on the shortcut menu, click Select Data.
31. In the Select Data Source dialog box, select the Series 3 series and click on the Edit button under Legend Entries (Series).
32. In the Edit Series dialog box, refer to cell E2 in the Series name edit box. Next, edit the Series X values to include cells E3:E35 and the Series Y values to include cells C3:C35 as per the screen shot below:

33. Press the Ok button twice in a row to close the Edit Series and Select Data Source dialog boxes.
34. In the chart select the Series "x-Labels" data series. Next, click the Charts Elements button located on the upper-right corner of the chart. Then, in the expanded menu, check the Data Labels option.
35. In the chart, right-click the Series "x-Labels" Data Labels and then, on the shortcut menu, click Format Data Labels.
36. In the Format Data Labels pane set the Label Position to Left.
37. Adjust the size of the Plot Area by dragging its left edge a bit to the right to avoid the data labels overlapping with the horizontal gridlines. The chart should look like this: