Views > Excel charts > Tufte in excel - the bar chart

Inspired by the work of Lukasz Piwek—Tufte in R—I decided to start Tufte in Excel.

This is the first in a series of blog posts to recreate charts you find in the work of Edward Tufte.

I’ll start with the bar chart. The focus is on the white grids and the frame of the vertical axis as per the chart below. Here the vertical frame shows the high (127) and low (0) values. The chart below is based on the work of Edward Tufte in The Visual Display of Quantitative Information and Visual Explanations. How do we do that in Excel?

The data source for above chart is shown below. It stacks the daily death from cholera prior to, during and after the epidemic that broke out in the Broad Street area of central London beginning of September 1854.

1. Highlight the range D2:D45 and then click Insert > Insert Column or Bar Chart > Clustered Column. The chart should look like this:

2. Delete the Chart Title and the Vertical (Value) Axis Major Gridlines.
3. Turn off the border of the Chart Area by setting the Border to No line.
4. Change the chart font to Trebuchet MS, its size to 9 and color to black.
5. Change the Shape Fill color of the Deaths data series to light grey.
6. In the chart, right-click the Series “Deaths” and then, on the shortcut menu, click Format Data Series.
7. In the Format Data Series pane, with Series Options selected, set the Gap Width  to 50%. The chart should look like this:

TUFTE IN EXCEL - THE BAR CHART

8. Right-click the chart and then, on the shortcut menu, click Select Data.
9. In the Select Data Source dialog box, click the Edit button under Horizontal (Category) Axis Labels.
10. In the Axis Labels dialog box, edit the Axis label range to include cells C3:C45.
11. Press the Ok button twice in a row to close the Axis Labels and Select Data Source dialog boxes.
12. In the chart, select the Vertical (Value) Axis and press the Del key on the keyboard to turn it off. The chart should look like this:

13. Right-click the Horizontal (Category) Axis and then, on the shortcut menu, click Format Axis.
14. In the Format Axis pane, with Axis Options selected, set the Axis Position to On tick mark.
15. Next, under Tick Marks set Major type to Outside.
16. Then, under Labels, set the Interval between labels to Specify interval unit and set to 2.
17. Next, click on the Fill & Line icon and, under Line, set the Solid line Color to Black and its Width to 0.25pt. The chart should look like this:

18. Next, I need to create the horizontal gridlines. To do that I need to specify the major unit of the vertical axis. From that input I can calculate the required number of gridlines that covers the range of the data. To do this add the following formulas in the respective cells and the labels according the screen shot below:
  H3 --> type 20
  H4 --> = MAX(D3:D45)
  H5 --> = (H4 - MOD(H4,H3)) / H3 + 1
  H6 --> = COUNT(C3:C45) + 1

19. Now I’ll define the setup so that the chart can dynamically handle 20 gridlines. For that enter the numbers from 1 to 20 in the range E11:E30.
20. Next, add zeros in the range F11:F30.
21. Then type the following formula in cell G11:
    = IF(E11 <= $H$5,E11 * $H$3,NA())
22. Copy the formula in Cell G11 to the clipboard by pressing Ctrl + C. Next, select the range G11:G30 and paste the formula by pressing Ctrl + V. The data source should look like this:

23. Select the range G10:G30 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. The chart will look like this:

24. Right-click the chart and then, on the shortcut menu, click Change Chart Type.
25. In the Change Chart Type dialog box select Combo under the All Charts tab.
26. Next, under the Choose the chart type and axis for your data series set the Chart Type to Scatter for the y data series and turn off its Secondary Axis as per the below screenshot:

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

28. Right-click the chart and then, on the shortcut menu, click Select Data.
29. In the Select Data Source dialog box, select the y series and click on the Edit button under Legend Entries (Series).
30. In the Edit Series dialog box, refer to cell E9 in the Series name edit box. Next, edit the Series X values to include cells F11:F30 and the Series Y values to include cells G11:G30 as per the screen shot below:

31. Press the Ok button twice in a row to close the Edit Series and Select Data Source dialog boxes. The chart should look like this:

36. In the chart, right-click the Series “Grids” Data Labels and then, on the shortcut menu, click Format Data Labels.
37. In the Format Data Labels pane set the Label Position to Left.
38. Next, while the data labels are still selected, set their Alignment to Align Right on the Home tab.
39. 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 data markers. The chart should look like this:

40. Select the Grids series in the chart then 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:

41. Select the Series “Grids” Y Error Bars in the chart and then turn them off by pressing the Delete key on the keyboard.
42. Right-click the Series “Grids” X Error Bars in the chart and then, on the shortcut menu, click Format Error Bars.
43. In the Format Error Bars pane, with Error Bars Option icon selected, set the Direction to Plus and the End Style to No Cap.
44. Set Error Amount to Custom and click on the Specify Value button.
45. In the Custom Error Bars dialog box, edit the Positive Error Value to refer to cell H6. Press the Ok button to close the Custom Error Bars dialog box. The chart should look like this:

32. In the chart, right-click the Grids data series and then, on the shortcut menu, click Format Data Series.
33. In the Format Data Series pane click on the Fill & Line icon. Under Marker Options, select the large horizontal dash from the Type dropdown list and set its Size to 2.
34. Next, under Fill select Solid fill and set its Color to black. Then turn off its border by setting the Border to No line.
35. Then, In the chart, right-click the Grids data series and then, on the shortcut menu, click Add Data Labels. The chart should look like this:

46. In the chart, select the Series “Grids” X Error Bars and then set its Shape Outline color to white. The chart should look like this:

47. Now I’ll define the setup for the vertical axis frame. It will show the minimum and maximum values of the data. For that enter the numbers 0.1 in the range E35:E36.
48. Then type the following formula in cell F35:
      = MIN(D3:D45)
49. Next type the following formula in cell F36:
      = MAX(D3:D45)

The data source should look like this:

50. Right-click the chart and then, on the shortcut menu, click Select Data.
51. In the Select Data Source dialog box, add a new data series by clicking the Add button under Legend Entries (Series).
52. In the Edit Series dialog box, refer to cell E33 in the Series name edit box. Next, edit the Series X values to include cells E35:E36 and the Series Y values to include cells F35:F36. The chart should look like this:

53. Right-click the Series “Vertical axis” and then, on the shortcut menu, click Format Data Series.
54. In the Format Data Series pane click on the Fill & Line icon. Under Marker, set the Marker Options to None.
55. Under Line, set the Line to Solid, its Color to Black and its Width to 0.25pt. The chart should look like this:

56. Right-click the Horizontal (Category) Axis and then, on the shortcut menu, click Format Axis.
57. In the Format Axis pane, with Axis Options selected, set the Axis Position to On tick mark.
58. Then, under Labels, set the Interval between labels to Specify interval unit and set to 2.
59. Finally, add axis labels, data sources and commentary. The chart should look like this:

60. You’ll notice that the chart is dynamic. Try it. Change the Major unit of vertical axis (cell H3) to 25. The chart will look like this: