Data Visualization and Graphs:

From Excel Defaults to Excellence

MDE Assessment Conference, August 4, 2016

Kirsten Rewey, Quantitative Analyst

Bar or Column Chart Default-to-Best Practices Conversion

  1. Remove border around graph
  2. Right click on graph; select “Format Chart Area” (last option on the pop-up list)
  3. Menu on the left – select “Border Color”
  4. Select “No line”
  5. Click “Close” button at the bottom of the pop-up
  6. Remove Legend from right side of the graph
  7. At the top of the screen, select “Chart Tools: Layout” ribbon
  8. Click “Legend” and select “None Turn off legend”
  9. Remove tick marks from horizontal and vertical axes
  10. Horizontal axis
  11. Select (click on) the horizontal axis
  12. Right click and select “Format Axis” from the pop-up (last option on the pop-up list)
  13. For “Major tick mark type” select “None” using the drop-down menu
  14. Click “Close” button at the bottom of the pop-up
  15. Vertical axis
  16. Select (click on) the vertical axis
  17. Right click and select “Format Axis” from the pop-up (last option on the list)
  18. For “Major tick mark type” select “None” using the drop-down menu
  19. Click “Close” button at the bottom of the pop-up
  1. Remove horizontal marker lines
  2. Click on one of the lines
  3. Press “Delete” or “Backspace”
  4. Edit bar width
  5. Select/click on a bar
  6. Right click and select “Format Data Series” (last option on the list)
  7. In “Gap Width” move the selector to 35% or type “35” in the field below the slider
  8. Click the “Close” button at the bottom of the pop-up
  9. Add values/data labels to the bars
  10. At the top of the screen select the “Chart Tools – Layout” ribbon
  11. Select “Inside End”
  12. Select a value
  13. Select the “Home” ribbon
  14. Change type color
  15. Note – this would be a good time to also change the font, style, and size!
  16. Change the order of the bars
  17. Highlight all of the cells of the data
  18. In the “Home” ribbon, select “Sort & Filter” and then “Custom Sort”
  19. In the pop-up:
  20. “Sort by” is Column B
  21. “Order” is ‘Largest to Smallest”
  22. Click the “OK” button
  23. Note: If you want to change the color of the bar to something that is aligned with the school’s/district’s colors or brand, now is a good time!
  1. Add title and caption
  2. Click the mouse in the white space above the bars (you want to select the space that contains the bars of the graph)
  3. Click+Drag the white box/white square at the top/middle of the container and drag down (we’re making some space to work in and will adjust this container to a better size later)
  4. At the top of the screen select the “Chart Tools – Layout” ribbon
  5. Select “Text Box”; note that the cursor will change from to an upside down ‘t’
  6. Click-Drag a text box from the upper-left corner to the upper-right corner and then down (don’t worry about it being ‘too big’!)
  7. Type in the following 2 lines of text:

2015 Minnesota 4-Year Graduation Rates

White students graduate at a higher rate than do Students of Color

  1. Edit the title and caption
  2. Select the first line of text
  3. Select the “Home” ribbon
  4. Change the size to 20 pt Bold
  5. Select the second line of text
  6. Change the size to 14 pt.
  7. Adjust the size of the text box (if needed)
  8. Adjust the size of the graph container with the lines
  9. Click the white space around/near the lines
  10. Click+Drag the white box/white square at the top-middle of the container and drag up
  1. Done!

Line Graph Default-to-Best Practices Conversion

  1. Remove border from the outside of the graph
  2. Right click on graph; select “Format Chart Area” (last option on the pop-up list)
  3. Menu on the left – select “Border Color”
  4. Select “No line”
  5. Click “Close” button at the bottom of the pop-up
  6. Remove the legend
  7. At the top of the screen, select “Chart Tools: Layout” ribbon
  8. Click “Legend” and select “None Turn off legend”
  9. Remove tick marks from horizontal and vertical axes
  10. Horizontal axis
  11. Select (click on) the horizontal axis
  12. Right click and select “Format Axis” from the pop-up (last option on the pop-up list)
  13. For “Major tick mark type” select “None” using the drop-down menu
  14. Click “Close” button at the bottom of the pop-up
  15. Vertical axis
  16. Select (click on) the vertical axis
  17. Right click and select “Format Axis” from the pop-up (last option on the list)
  18. For “Major tick mark type” select “None” using the drop-down menu
  19. Click “Close” button at the bottom of the pop-up
  20. Remove horizontal marker lines
  21. Click on one of the lines
  22. Press “Delete” or “Backspace”
  1. Mark or Identify the lines in the graph
  2. Select the top line
  3. Select the right-most spot/data point on the line
  4. Select the “Chart Tools – Layout” Ribbon
  5. Select “Data Labels” and “More Data Label Options”
  6. Select “Series Name” and deselect “Value”
  7. Click “Close” button at the bottom of the pop-up
  8. Repeat steps 5a through 5c for the remaining two lines
  9. Note – If you want to change type size, do it here so you won’t have to repeat step 5f below!
  10. Select one of the labels
  11. Select the “Home” ribbon
  12. Select 12 pt. font
  13. Repeat 5e for the other two data labels
  14. Adjust the line container so that there is little/no overlap between the lines and the labels
  15. Click in the white space of the graph
  16. Click+Drag the white box/square in the middle-right until the graph lines and their respective labels no longer overlap
  17. Change the lines
  18. Select a line
  19. Right click and select “Format Data Series” (last option on the pop-up)
  20. On the menu on the left, select “Line Color”
  21. Select “Solid Line”
  22. Select black
  23. On the menu on the left, select “Line Style”
  24. For “Dash Type” select something other than a solid line
  25. Click “Close” button at the bottom of the pop-up
  26. Repeat steps 6a and 6b for a second line
  27. Repeat step 6a (only) for the last line
  28. Add title and caption
  29. Click the mouse in the white space around/near the lines (you want to select the space that contains the lines of the graph)
  30. Click+Drag the white box/white square at the top/middle of the container and drag down (we’re making some space to work in and will adjust this container to a better size later)
  31. At the top of the screen select the “Chart Tools – Layout” ribbon
  32. Select “Text Box”; note that the cursor will change from to an upside down ‘t’
  33. Click-Drag a text box from the upper-left corner to the upper-right corner and then down (keep the text box within the boundaries of the graph, but don’t worry about it being ‘too big’!)
  34. Type in the following 2 lines of text:

Graduation Rates of Students with IEPs in Minnesota

4-, 5-, and 6-Year graduation rates have increased since 2003

  1. Edit the title and caption
  2. Select the first line of text
  3. Select the “Home” ribbon
  4. Change the size to 20 pt Bold
  5. Select the second line of text
  6. Change the size to 14 pt.
  7. Adjust the size of the text box (if needed)
  8. Adjust the size of the graph container with the lines
  9. Click the white space around/near the lines
  10. Click+Drag the white box/white square at the top-middle of the container and drag up
  1. Done!

Using Best Practices Graphs as Templates

  1. Select the graph
  2. Copy
  3. Keyboard: CTRL+C
  4. Mouse: Right click; select “Copy”
  5. Menu: “Home” ribbon; select “Copy” icon
  6. Navigate to the file you want to put the graph
  7. Paste
  8. Keyboard: CTRL+V
  9. Mouse: Right click; select “Paste”
  10. Menu: “Home” ribbon; select the “Paste” icon
  11. Select the graph
  12. Select the new data
  13. Select the “Chart Tools – Design” ribbon
  14. Select “Select Data”

Note: Excel is going to go back to the original worksheet for data. Make sure you’re at the correctworksheet to select the new data!

  1. Navigate back to the new data file
  2. In the pop-up, click on the data selection icon
  3. Select the cells with the data
  4. Click on the data selection icon again
  5. Click “OK” button at the bottom of the pop-up
  6. Your new data should be in the graph now!
  1. For bar or column graphs: Sort the data as you did earlier
  2. Highlight all of the cells of the data
  3. In the “Home” ribbon, select “Sort & Filter” and then “Custom Sort”
  4. In the pop-up:
  5. “Sort by” is Column B
  6. “Order” is ‘Largest to Smallest’
  7. Click the “OK” button
  8. For the line graphs: Label the lines as you did earlier
  9. Select the top line
  10. Select the right-most spot/data point on the line
  11. Select the “Chart Tools – Layout” Ribbon
  12. Select “Data Labels” and “More Data Label Options”
  13. Select “Series Name” and deselect “Value”
  14. Click “Close” button at the bottom of the pop-up
  15. Repeat steps 5a through 5c for the remaining two lines
  16. Note – If you want to change type size, do it here so you won’t have to repeat step 5f below!
  17. Select one of the labels
  18. Select the “Home” ribbon
  19. Select 12 pt. font
  20. Repeat 5e for the other two data labels
  21. Adjust the line container so that there is little/no overlap between the lines and the labels
  22. Click in the white space of the graph
  23. Click+Drag the white box/square in the middle-right until the graph lines and their respective labels no longer overlap
  24. Adjust the title and caption as needed
  25. Done!

Resources

Color or Palette Selection – Use these resources to match graph contents with your brand!

Adobe Color CC

Color Palette FX

Colors Pallete Generator

TinEye Labs

Color Brewer 2 This site can help you identify colors that are appropriate for those who are color blind and/or photocopy-safe colors for different types of data (diverging vs. sequential).

Cleveland and McGill

Cleveland, W. S., & McGill, R. (1984). Graphical perception: Theory, experimentation, and application to the development of graphical methods. Journal of the American statistical Association, 79(387), 531-554.

Cleveland, W. S., & McGill, R. (1985). Graphical perception and graphical methods for analyzing scientific data. Science, 229(4716), 828-833. Retrieved from

Edward Tufte

Topic Forum: (Currently no option to subscribe to this blog.)

Stephanie Evergreen

Evergreen, S. D. H. (2014). Presenting data effectively: Communicating your findings for maximum impact. Thousand Oaks, CA: Sage.

Evergreen, S. D. H. (2016). Effective data visualization: The right chart for the right data. Thousand Oaks, CA: Sage.

Blog: (To sign up for Stephanie’s blog, use the “Sign up for my newsletter!” box on the right side of the screen.)

Stephanie Evergreen and Ann Emery co-authored a blog post and a data visualization checklist (Cheryl Videen shared the checklist at last year’s Assessment Conference). You can read the blog and download the list here:

Ann Emery

Blog: To subscribe to Ann’s blog, use this link () and select “Blog” for the subscription.

Stephen Few

Few, S. (2009). Now you see it: Simple visualization techniques for quantitative analysis. Burlingame, CA: Analytics Press.

Few, S. (2013). Information dashboard design: Displaying data for at-a-glance monitoring (2nd ed.). Burlingame, CA: Analytics Press.

Blog: (Currently there is no option to subscribe to Stephen’s blog.)

Chris Lysy – website

Blog: (To subscribe to Chris’s blog, click “Follow” in the upper right corner.)

2016 Minnesota Department of Education Assessment ConferencePage 1