Wilson Mar bio photo

Wilson Mar

Hello. Hire me!

Email me Calendar Skype call 310 320-7878

LinkedIn Twitter Gitter Google+ Youtube

Github Stackoverflow Pinterest

See your data


Overview

First, some definitions:

  • Histograms analyze numerical data

  • Frequency distributions analyze categorical (text) data

Create a Histogram in Microsoft Excel 2016

  • https://www.youtube.com/watch?v=53DOu_vstvI

  • https://www.youtube.com/watch?v=IVhQTAF1guc

Excel 2016 has a easier way to create histograms than Excel 2013.

  1. Select the category and numerical columns

  2. In the Insert pane, click the Insert Statistics Chart icon.

Category Frequency Distribution in Microsoft Excel 2013


This video is part of a series on statistics using Excel. Begin from 1:38

Get a unique list of values

In the Raw Data sheet:

  1. Press Ctrl+Home to get to the upper left of the Raw Data sheet.
  2. Click the heading to the column you want to analyze.
  3. Press Ctrl+Shift+down to highlight all the cells of the column.

  4. PROTIP: Specify a range name (such as “Priority”, etc.) so you can refer to the same range in several functions.

  5. Click Data ribbon, Advanced, Copy to another location, Unique records only, Copy to icon.
  6. Scroll right beyond the last column in the sheet and click a cell there.
  7. Click the Copy icon again, then OK.

    excel filtered unique 207x207

  8. Scroll back to the right where you specified.
  9. You may need to clean up values in entries.

    • A trailing space counts as a separate value.

    • Make sure there are no blanks in the data.

  10. Press Ctrl+H to do a Replace All on the errant values to fix them.
  11. Delete the generated cells and
  12. Repeat the above until there are no duplicates.

  13. Sort or manually rearrange the order of items (if you have categories that don’t sort, such as “Very High”, “High”, “Medium”, “Low”).

    Make the Frequency Distribution

  14. Create Frequency, and Interval columns to the right of the unique list created.

  15. In the first data cell under the Frequency heading, type a formula =COUNTIFS()
  16. Click the first data cell of the category data being analyzed.
  17. Press Ctrl+Shift+down to select all rows.
  18. Scroll back to the distribution being built.
  19. Press command to specify another parameter.
  20. Click on the first cell of the unique items (the Criteria).
  21. Press ) to close the formula.
  22. Press Ctrl+Enter to save the formula.
  23. Double-click on the lower-right corner to populate the rest

    Calculate total and percentages

  24. Add a total in a blank row under data rows in the Frequency column.
  25. Format the total cells with borders top and bottom.
  26. Click the cell holding the total and press Alt+=.

  27. PROTIP: Verify that the total matches the number of rows in the data being analyzed.

  28. Create a percentage formula. Put a $ in front of the row number.
  29. Double-click on the lower-right corner of the first formula to populate the rest of the rows.
  30. Highlight the Percentage cells and format it as Percents.

    Bar Chart

  31. Highlight the category data and percent, including the headings.
  32. Click Insert tab, Stacked bar.
  33. Right-click the categories to select Format Axis.
  34. Check Categories in reverse order and close the pane.
  35. Click on the title and change it to “Priority distribution” or whatever.

    Add totals

  36. A total at the bottom of counts makes it easier to verify whether you

    Another Category Distribution

  37. Repeat the above for another distribution, if desired.

Create a Histogram in Microsoft Excel 2013

  • https://www.youtube.com/watch?v=Giewd9yH4q0

  • https://www.youtube.com/watch?v=YfVu7xGHgnA

  • https://www.youtube.com/watch?v=SDUgEuFrJ3o

  1. Drag the lower-right corner to populate the other rows under Bin.

    • Leave the last row empty so Excel uses it for anything larger than the last number.
  2. Click in the first data cell under Frequency heading, and drag the lower-right corner to populate the other rows until the last Interval row.

  3. Press Ctrl+Shift+Enter to auto-populate (as an array).

  4. In the first data cell under the Frequency heading, type =frequency(.

  5. Click the first data cell under the value column (C) as the first parameter.

  6. Press comma.

  7. Click the first data cell under the Bin heading (K), then click on the last cell in the column.

    • To allow for easier…
  8. Press Ctrl+Shift+Enter.

    Adjust default gaps

  9. Double-click on one of the columns in the generated chart for the Series Options UI.

  10. Drag Gap Width to zero.