Excel Statistical Analysis 05: Frequency Distributions & Visualizations for Categorical Data

Learn about creating Frequency Distributions & Column and Bar Chart Visualizations for Categorical Data. See an Epic Summary of EVERYTHING you need to know about Column and Bar Charts.
Topics:
1. (00:00)
2. (00:22) Download Excel
3. (00:39) Descriptive Statistics: Tabular (Reports) and Graphical (Visualizations)
4. (01:43) What type of tabular and graphical summaries based on one variable or two variable
5. (03:00) Cheat Sheet with Terms for chapter
6. (03:16) Epic Summary for when and how to use Column and Bar Charts
7. (07:32) PivotTable to create Frequency Distribution for Categorical Variable “Payment Method”. See three calculations: Frequency, % Frequency, Relative Frequency
8. (10:20) Number Formatting for the PivotTable Values Area.
9. (12:47) Four examples of Column and Bar charts for Categorical Data from a Frequency Distribution PivotTable
10. (13:35) Change Default Settings in a PivotTable: PivotTable Options dialog box and Excel Options Default PivotTable Settings dialog box
11. (14:14) Why Tabular Layout is helpful
12. (16:40) #1 Rule for Visualizing Data with Charts: “No Chart Junk”.
13. (18:17) Linking Chart labels to cells with formula
14. (20:23) Copy Chart and change elements to create a slightly different visualization with a different emphasis: Add Data Labels and remove gridlines
15. (21:26)Copy Chart and create Bar chart that emphasis differences more forcefully
16. (22:17) Chart Format Task Pane and Keyboard: Ctrl + 1
17. (25:08) Type a new Chart Title
18. (26:43) Order of category labels
19. (26:55) Pareto Chart: Categorical Data Frequency Numbers Sorted in chart and add a % Cumulative Line
20. (28:58) Combo Chart: Column and Line
21. (29:54) Custom Number Formatting to show noting – this is done to hide axis numbers
22. (32:50) Video Summary
23. (33:25) Closing and Video links

[excelisfun]

02:52 Video Topic for chapter 2 list is not correct. The video shows 5 to 9 videos, in this chapter 2 there are only 5 - 8 videos. Video 9 is in chapter 3.

[harunrhimu]

I searched for multiply days for some advanced tutorials but at last, found some videos in my desired way (statistics and excel both). Thanks, I will recommend your channel to others.

[wayneedmondson]

Hi Mike. Another super awesome Excel Stats video!! Thanks for the great review and many reminders of best practices. One tip I can add.. in the Pareto chart, you can also remove the display of each axis by first selecting the axis and then CTRL+1 for Format Axis >> Labels >> Label Position >> None. The axis disappears from view.. it becomes a thin vertical line object on the side of the chart. It is the same effect as formatting with ;;;. Just another way to get there. As always, thanks for the great lesson, tips, tricks and an all around fun time with Excel Pivot Tables and Charts :)) Thumbs up!!

[nadermounir]

Thank you Mike for this amazing video 📹. Excelisfun is indeed my favorite channel on YouTube!

[johnborg]

Thanks Mike. rather late to watch this one, but I'm still here following all. That was very helpful and interesting. :) :)

[bipower]

Love watching Mike's videos. There is always something new to learn

[davespace]

The Pareto chart was new to me and will be a great tool to add to the work I do. Love continuing to learn with your videos and looking forward to the rest of the series.

[sedarathnadcd]

Thank you Mike, It is a important piece of information. Acually Love to learn with your videos

[shubhampawar]

You always makes complex things simple and interesting ❤️🤗

[garethwoodall]

Mike, just wanted you to know both me and Sioux are enjoying the content! G

[GeertDelmulle]

That sort by functionality in a pivot table is very handy! I didn’t know/remember it was there.
Manual sorting can be fun for categorical frequency distributions: you can also do that by drag and drop in the pivot table.
Great video and lesson, as always! :-)

[darrylmorgan]

Boom!Excellent Class With Some Great Tricks...Thank You Mike :)

[nsanch]

Thank you Mike for the great stats and visualization video. So when I see catagorical data I should be targeting the Frequency, % Frequency, Relative Frequency with some visualization.
I also enjoy when you get into the options dialog box, so many options in there, and it helps that you show that.

[barttitulaerexcelbart]

Very well explained. Looking forward to next lesson. Nice tip to deselect "update column with" in the default settings as As for sorting: I always select the cell I want to sort and press a->z or z->a. I think that works always. I also encounter the problem that after a right click I can't remove the field buttons. Any idea why that sometimes is not posible. I am glad you mentioned this issue..so this happens also to MVP's...😉

[RogerStocker]

Greate! I still can learn a lot from the Excel Entertrainer. 🎉👍

[darylseaton]

Hi Mike! Boom!!! Another great video. I have a question, when you distribute a survey with yes/no answers, how would you visualize that? It is quantitative, there are only two answers, so would you create a column chart with no spaces between the columns? Or, should you separate the columns? Or, do you stack the columns? Many thanks for your help and your service. You are doing great work with your teaching videos. My daughter and I enjoy watching them together...

[spilledgraphics]

Like n°48, comment n°4.... visualizations ???? 😵🤯😵 buckling up Captain ! 🚀🚀

[thomaskuriakose]

Hi Mike, this is now the most awesome Excel statistical analysis videos, The BDPs are really mind-blowing. I have a question on grouping negative an positive data together - the data reads -10, -8, -7, -3, 0, 3, 6, 8, 9, 10, 12, is it possible to group this category into - 0-3 months left, 3-6 months left and so on if negative and 0-3 months due, 3-6 months due and so on if positive. If yes, how can this be done. Thank you so much for your guidance.

[marshmallow]

I'm taking business analytics now and I have comparable data 3 columns. I used a bar chart but was marked down because my instructor wanted me to use a histogram or box/whisker, which I have no clue how to use and compare the data that way.

[adsgame]

Hey Mike, I have a simple question, I would personally know what a cumulative frequency % normally tell in data analysis. Like if this data or the graph was supposed to show any trends of a certain frequency?