Using Excel for Data Analysis: Methods

Name: Using Excel for Data Analysis: Methods
Author: Cliff Brozo

Learn the different methods of analyzing data Excel provides.

Preview the first lesson free — get full access to all 5 lessons.

Course: On-Demand

Welcome back to Knowledge City's course of using Excel for data analysis. I'm Cliff Brozo, I'm your instructor, and in today's lesson we're going to talk about ranges. When you discuss ranges, there are two different meanings and we need to distinguish between the two. There's an Excel range and there's a statistical range. An Excel range is, just a series of cells either in a row or a column or some sort of 3D combination. In statistics ranges the difference between the lowest and the highest values, and it measures variability and there's a formula for it that says the range is equal to the highest minus the lowest. The statistical range gives you ballpark numbers to work with when you have a list without any kind of extreme values and those are called outliers because one extreme value in the data set will give you a completely different range. The reason why we look at standard deviation is because it gives us an average of the variability in the list. Basically tells us how far is each value from the mean. If you have a high standard deviation the values are pretty far away from the average, and if you have a low standard deviation, they're pretty close. Standard deviation is a useful measure to figure out the spread for normal distributions. Let's take a look at Excel and see how this works. I've created a spreadsheet that has two different pieces to it. One is some sales information from a daily basis and the other is the ages of sales people. And let's take a look at the ages of sales people first. What I can do is I can declare this section right here to be a named range and I'll call it ages. I can also declare this section here and call it all ages. So I have a range called ages and a range called all ages which includes this outlier and I have salespeople who range in age from 17 to 32, but one person who is 66. What naming ranges does for me is allows me to use that named range in a formula, so I can say equal max of ages minus the min of ages will give me the range. And the range is 15, and what that's doing is it's telling me that the difference from 17 to 32 is 15. Now, if I include the outlier, do the same formula equal max of all ages, minus the men of all ages, all of a sudden because this person is much older than everyone else, my range is distorted, it's now 49. Here's when I can take a look at standard deviation and there's a formula that does that, equal STD and I'll say my range and my standard deviation is 4.6 and that means that it took the average and says that these are 4.6 years away. If I include the outlier equal standard deviation of all ages, my standard deviation now changes to 14.1 because they're further away. And again, that signifies an outlier. Let's see what else Excel can do when we use a range. I'll take this section here and I'll call it sales. Now, I can take this information, go into my data tab and my data analysis section and I would like Excel to create some descriptive statistics for me. I click on okay, it knows that I'm going from three to 10 my labels are in the first row. I'm gonna put this information in a new spreadsheet and I want summary statistics. All I need to do is click on okay and I get a brand new spreadsheet that has everything that I can possibly want. Let's take these numbers and make them all the same in terms of the number of decimal positions. And here is your statistical analysis of the sales data. My range is 4,321. That's the difference between the highest and the lowest. Here's my minimum value of 1211, my maximum of 5532 if I edit them all up. That's the amount of money that was taken in during the course of the week and there are seven days of the week. This is one way of using Excel's named ranges and combining it with the range of statistical analysis. Now you know how to create a named range. Now you know how to do the calculation for ranges. You can also figure out the standard deviation and you can let Excel create a statistical analysis of all of the mathematical statistics we like to look at when we're doing data analysis. I'll see you soon.

Intermediate Provider : Cliff Brozo 5 Lessons · 30m in Arabic, German, English, Spanish, French, Portuguese, Chinese

Course Description

In these lessons, you’ll explore the different methods of data analysis you can perform in Excel. You’ll also learn how formatting and summarizing your data can help you with the analysis process. We’ll go over the difference between Excel ranges and statistical ranges, and you’ll discover how tables can help you structure your data for easier sorting.

We’ll also cover how to format data on a conditional basis and add gradient data bars, top-10 values, and percentages. You’ll learn to group and subtotal data, turning mountains of information into understandable bite-sized pieces, as well as how to transform it from a vertical to horizontal layout.

What You'll Learn

Convert data into Excel ranges and tables to structure it for easier sorting
Apply conditional formatting, including gradient data bars, top-10 values, and percentages
Group and subtotal data to break large datasets into understandable bite-sized pieces
Distinguish between Excel ranges and statistical ranges
Transform data from a vertical to a horizontal layout to aid visualization

Key Takeaways

Excel supports different methods of data analysis, and formatting and summarizing data assists the analysis process.
Excel ranges and statistical ranges are not the same thing.
Tables help structure data for easier sorting.
Conditional formatting can be applied to data and includes gradient data bars, top-10 values, and percentages.
Grouping and subtotaling turns large amounts of information into understandable bite-sized pieces, and transforming data changes its layout to help with visualization.

Frequently Asked Questions

What does this course cover?

It covers the different methods of data analysis you can perform in Excel, including converting data into ranges and tables, using conditional formatting, grouping and subtotaling data, and transforming data from a vertical to a horizontal layout. It also explains how formatting and summarizing data helps the analysis process and the difference between Excel ranges and statistical ranges.

What lessons are included?

The course includes five lessons: Ranges, Tables, Conditional Formatting, Grouping and Subtotals, and Transform.

What skills will I gain?

You will build skills in Microsoft Excel, Analytic Applications, Analytical Techniques, Data Analysis, Quantitative Data Analysis, and Statistical Methods.

What types of conditional formatting will I learn to apply?

You will learn to format data on a conditional basis and add gradient data bars, top-10 values, and percentages.