TUFTE IN EXCEL - THE DOT-DASH-PLOT

xxxxxxxx

7. Lets do some cleaning. Delete the Chart Title, the Vertical (Value) Axis, the Vertical (Value) Axis Major Gridlines, the Horizontal (Value) Axis, and the Horizontal (Value) Axis Major Gridlines.
8. Turn off the border of the Chart Area by setting the Border to No line.
9. Change the chart font to Trebuchet MS, its size to 9 and color to black. The chart should look like this:

10. Right-click the Series “y” and then, on the shortcut menu, click Format Data Series.
11. In the Format Data Series pane, with Series Options selected, click on the Fill & Line icon. Under Marker, set Marker Options to Built-in and its Size to 6.
12. Next, set Fill to Solid fill and change its Color to Green its Transparency to 75%. Next, set its Border to Solid line and its Color to Green. The chart should look like this:

13. Here I’ll introduce in the range E4:E203 an input that can take the value of either “Yes” or “No” to define whether the label (i.e. country name) will be displayed or not on the chart.
14. Next, I’ll provide the y coordinate for each data label—Lets say its 0.25. Hence, introduce 0.25 in all the cells in the range F4:F203.
15. In order to show only those selected countries, I need to write 2 formulas, one that picks the country name and another the value for those countries that have “Yes” and evaluates to #N/A otherwise. Hence, introduce the following 2 formulas in the respective cells:
G4 --> = IF(E4 = "Yes",B4,NA())
H4 --> = IF(E4 = "Yes",C4,NA())
16. Select the range G4:H4 and press Ctrl + C to copy the formulas to the clipboard. Then select the range G5:H203 and press Ctrl + C to paste the formulas as per the screen shot below:

17. Right-click the chart and then, on the shortcut menu, click Select Data.
18. In the Select Data Source dialog box, click the Add button under Legend Entries (Series).
19. In the Edit Series dialog box, refer to cell G3 in the Series name edit box. Next, edit the Series X values to include cells H4:H203 and the Series Y values to include cells F4:F203 as per the screen shot below:

20. 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:

21. Right-click the Series “Label” in the chart and then, on the shortcut menu, click Add Data Labels.
22. Next, right-click the Series “Label” Data Labels and then, on the shortcut menu, click Format Data Labels.
23. In the Format Data Labels pane, with Label Options selected, set the Label Position to Above.
24. Under Label Contains, click on Value Form Cells. In the Data Label Range dialog box set the Select Data Label Range to refer to G4:G203. Click the Ok button to close the Data Label Range dialog box.
25. Under Label Contains, uncheck Y Value and Show Leader Lines.
26. Next, under Text Options, click the Textbox icon and set the Text direction to Rotate all text 270°.
27. Set all Margins to 0 and uncheck the Wrap text in shape option. The chart should look like this:

28. Next I’m going to use Error Bars to show label leader lines. For that, select the 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:

29. Delete the Series “Label” X Error Bar. Next, right click the Series “Label” and then, on the shortcut menu, click Format Data Series.
30. In the Format Data Series pane, with Series Options selected, click on the Fill & Line icon. Under Marker, set Marker Options to None.
31. Then, in the chart select the Series “Label” Y Error Bars and and then, in the Format Error Bars pane, set its Direction to Minus, its End Style to No Cap and its Error Amount to Fixed Value of 0.1. The chart should look like this:

35. 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. Right-click the Series “Highlighted” and then, on the shortcut menu, click Format Data Series.
37. In the Format Data Series pane, with Series Options selected, click on the Fill & Line icon. Under Marker, set Marker Options to Built-in and its Size to 6.
38. Next, set Fill to Solid fill and change its Color to light orange. Next, set its Border to Solid line and its Color to orange. The chart should look like this:

39. Next, I need to create the horizontal axis, its labels and tick marks. To do that I need to specify the major unit of the horizontal axis. From that input I can calculate the required number of tick marks 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:
L3 --> type 3%
L4 --> = MIN(C4:C203)
L5 --> = MAX(C4:C203)
L6 --> = L5 - L4
L7 --> = (L6 - MOD(L6,L3)) / L3 + 2

32. Right-click the chart and then, on the shortcut menu, click Select Data.
33. In the Select Data Source dialog box, click the Add button under Legend Entries (Series).
34. In the Edit Series dialog box, refer to cell H3 in the Series name edit box. Next, edit the Series X values to include cells H4:H203 and the Series Y values to include cells D4:D203 as per the screen shot below:

40. Now I’ll define the setup so that the chart can dynamically handle 20 tick marks. For that enter the numbers from 1 to 20 in the range I11:I30.
41. Next, add zeros in the range K11:K30.
42. Then type the following formula in cell J11:
= L4 - MOD(L4,L3)
43. Then type the following formula in cell J11:
= IF(I12 <= $L$7,J11 + $L$3,NA())
44. Copy the formula in Cell J11 to the clipboard by pressing Ctrl + C. Next, select the range J13:J30 and paste the formula by pressing Ctrl + V. The data source should look like this:

45. Right-click the chart and then, on the shortcut menu, click Select Data.
46. In the Select Data Source dialog box, click the Add button under Legend Entries (Series).
47. In the Edit Series dialog box, refer to cell I9 in the Series name edit box. Next, edit the Series X values to include cells J11:J30 and the Series Y values to include cells K11:K30 as per the screen shot below:

48. 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:

49. Draw a short vertical line—say 0.1 in height—anywhere on the worksheet using the Shapes in the Illustration Group under the Insert Tab.
50. Copy the newly created shape to the clipboard by pressing Ctrl + C.
51. In the chart area, select the markers of the Tick marks data series and paste the shape by pressing Ctrl + V.
52. Next, in the chart, right-click the Tick marks data series and then, on the shortcut menu, click Format Data Series.
53. In the Format Data Series pane click on the Fill & Line icon. Under Marker Options, set Border to Solid line, its Color to black and its Width to 0.25 pt.
54. Then, In the chart, right-click the Tick marks data series and then, on the shortcut menu, click Add Data Labels. The chart should look like this:

55. In the chart, right-click the Series “Tick marks” Data Labels and then, on the shortcut menu, click Format Data Labels.
56. In the Format Data Labels pane set the Label Position to Below.
57. Under Label Contains, uncheck Y Value and Show Leader Lines and check X Value.
58. Next, under Text Options, click the Textbox icon and set all Margins to 0 and uncheck the Wrap text in shape option.
59. Adjust the size of the Plot Area by dragging its bottom edge a bit to the top to avoid the data labels overlapping with the data markers. The chart should look like this:

60. Now I’ll define the setup for the horizontal axis frame. It will show the minimum and maximum values of the data. For that enter the numbers 0.02 in the range J34:J35.
61. Then type the following formula in cell I34:
= MIN(C4:C203)
62. Next type the following formula in cell I35:
= MAX(C4:C203)
The data source should look like this:

63. Right-click the chart and then, on the shortcut menu, click Select Data.
64. In the Select Data Source dialog box, add a new data series by clicking the Add button under Legend Entries (Series).
65. In the Edit Series dialog box, refer to cell I32 in the Series name edit box. Next, edit the Series X values to include cells I34:I35 and the Series Y values to include cells J34:J35 as per the screenshot below:

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

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

70. Finally, add title, notes and data sources. Resize the chart so it looks like this:

The chart can be easily transformed into a Barcode Plot as follows:
1. Draw a short vertical line—say 0.3 in height—anywhere on the worksheet using the Shapes in the Illustration Group under the Insert Tab.
2. Copy the newly created shape to the clipboard by pressing Ctrl + C.
3. In the chart area, select the Series “y” data series and paste the shape by pressing Ctrl + V.
4. Next, In the chart area, select the Series “Highlighted” data series and paste the shape by pressing Ctrl + V.
5. Then, right-click the Series “Highlighted” data series and then, on the shortcut menu, click Format Data Series.
6. In the Format Data Series pane click on the Fill & Line icon. Under Marker Options, set Border to Solid line, its Color to orange and its Width to 0.75 pt.
7. Next, change the y coordinate of the bars to 0.09 (range D4:D203) to align the bars with the horizontal axis.
8. Then, in the chart right-click the Series “Label” Y Error Bars and then, on the shortcut menu, click Format Error Bars. Next, in the Format Error Bars pane, under Error Bar Options, set the Error Amount to Fixed Value of 0.07. The chart should look like this:

Download Excel model