Using Excel for Data Analysis: Essential

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

Learn to use Excel’s built-in functions to produce faster, more efficient results.

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

Course: On-Demand

Welcome back to KnowledgeCity's course on using Excel for data analysis. I'm Cliff Brozo, I'm your professor. And in today's lesson, we're going to take a look at some basic Excel functions, and then we'll make them not so basic. The easy Excel functions, the ones that everybody is pretty much familiar with, are MIN, MAX, COUNT, AVERAGE, and SUM. And those will get us the smallest, largest, count the number of values we have, return an average, and total up all the values in a range. We can add some complexity by adding an IF statement to each one of these basic functions. And what the IF statement will do is allow us to put in a condition that will return the smallest, largest, count how many, find the average, or return a sum based on a condition. And finally, we'll figure out how to match data using the INDEX and MATCH commands. INDEX will give us a row and column coordinate, MATCH will search for a specific item in a range of cells, and perhaps the most important one, XLOOKUP, which will allow us to look in one column for an item, and then return a result from the same row in another column. Let's take a look at some data, dealing with how we live our lives and how much it costs us based on where we live. Once again, going to kaggle.com, I picked a database that deals with today's cost of living. This is relatively current data. As you can see, there are a bunch of cities, and let me make this a little bit larger, along with where those cities are located. There's a cost of living index, a rent index. In fact, let's expand these columns and wrap the text so I can see them a little bit better. Cost of Living, Rent, the Cost of Living Plus Rent. How much does it cost for groceries? How much does it cost to go to a restaurant? And what is your local purchasing power? We have 578 rows that deal with different cities and the averages for each. Right now, they're sorted by the overall Cost of Living Index, and you can see that the city of Hamilton in Bermuda is the most expensive place to live. And we can go all the way down to the bottom and find that Peshawar, Pakistan, is the least expensive place to live. The United States poses a problem. Highest on the list here is Honolulu, Hawaii, in the United States. I have three values, one, two, three, where in other areas, I have two values. What I need to do is make this all consistent. So I'm gonna start out by separating these, using my Text to Columns function that will put these items in three different cells. First, I'll grab all of my data and move it to the right so that I have room to put the cities and countries. I'll move it out there for now. Now that I've made some room, I can separate the cities from their countries as well as the cities from their states from their countries. Here's how it's going to work. In my Data tab, I have a Text to Column button, and I'm gonna highlight all of the cells in column B. Click on Text to Column. I'll say that this is a delimited list, meaning that there are commas that separate everything. I'll click Next, tell it to pay attention to the commas. And as you can see, as I scroll through the list here, when I get to a city in the United States, I will get three columns. Click Next, agree that this is general formatting, and I'll click Finish. And now I have a separation. I am not done. And the reason I'm not done is because now the United States has a state while other cities have a country, and they should not have a state. I don't wanna do this individually, so I'm going to use a formula. I'll come into cell E. The formula that I'm going to do is an IF statement that checks to see if this column, column D, contains the United States. If it does, I'll move the word United States into column E; otherwise, I'll move the country into column E. What it's going to lead me with is everything separate. So I'll start with my formula, an equal sign and IF statement that says, IF this cell, and I know it's empty right now, is equal to United States, put that in quotes, then I wanna return this same cell; otherwise, I want to return the country outside the United States. And it gives me Bermuda. That's exactly what I want it to have happen because cell D2 is not equal to the United States. I'm going to drag down because I've made an error that I want you to see. I drag down, and everything's fine until I get to the United States, and it returns Hawaii. So it's not recognizing United States. So I look closely and my spelling is correct, and I look here and spelling is correct. And here's one of those difficult errors to spot. The problem is right before the word "United States." There is a space. You can see right before the "U" in "United States," there is a space. Whoever typed this data set in, put a space before the word "United." So what I need to do is I need to copy their mistake to fix it and add in a space before United States. That doesn't change countries outside the United States, but when I drag down, it brings in the United States. I come down further, drag it all the way down to the bottom, and I now have every country set up in a specific column. I still have an issue on how to get Hawaii and New York and California by themselves. I don't wanna do these individually. What I do want is to put in a formula. So I'm going to do the same thing only in reverse. Before I can do anything, however, I need to take the formulas that I just copied and change them into values 'cause I don't wanna lose what I have done. So I'm gonna highlight this whole row all the way down to bottom, Ctrl+C to copy, right click, and paste as values in the same spot. All I've done is I've gotten rid of the formula and replaced it by a value. I wanna create a formula that's going to bring the state of the United States into this column. So I'll start with an equal sign and IF statement again. This time I'm testing to see if this cell is equal to United States, but it's space United States. Because that's what was in the data set originally. If it is, then I would like to return the value that is in column C. Otherwise, I'd like to return nothing, close my parentheses, hit Enter. And it appears nothing happened because Bermuda is not in the United States. As I drag down, I get values on those states. So I can grab go all the way down to the bottom, and everywhere we have the United States I have filled in the proper value. Now what I can do is the same thing that I did before, going to grab this and change it into values. Because I'm gonna eventually delete these other columns. I'm going to get rid of this. Because I no longer need it. I'm also gonna get rid of E, F, G, and H. I don't need those. And I'll give myself a little space for the country name. That was cleaning the data. Yes, it takes some time, but now I'm sure that what I have is valid. I might wanna put in state and country headings so I know exactly what I have. City, state, and country. I'll see you soon.

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

Course Description

In these lessons you’ll be introduced to the SUM, MIN, and MAX functions, and we’ll demonstrate how built-in Excel functions can save you hours of time when producing analytical results. You’ll also learn how to validate user input data before it enters the system, as well as how to use lookup tables.

We’ll show you how to apply conditions to Excel functions to better control how and when they execute. You’ll also learn how to look up data in a table and return one or more results, and how to concatenate cells, determine the length of strings, and convert text into numbers.

What You'll Learn

Use the SUM, MIN, MAX, COUNT, and AVERAGE functions to produce analytical results
Apply conditions to Excel functions using IF, SUMIF, COUNTIF, and nested formulas
Validate user input data before it enters the system
Look up data in a table using vertical and horizontal lookups and return one or more results
Concatenate cells, determine the length of strings, and convert text into numbers

Key Takeaways

Built-in Excel functions can save hours of time when producing analytical results.
Applying conditions to Excel functions gives you better control over how and when they execute.
Data validation lets you check user input before it enters the system.
Lookup tables allow you to find data in a table and return one or more results.
Text-handling functions can concatenate cells, measure string length, and convert text into numbers.

Frequently Asked Questions

What Excel functions does this course cover?

It introduces the SUM, MIN, MAX, COUNT, and AVERAGE functions, covers conditional functions such as IF, SUMIF, COUNTIF, and nested formulas, and includes data validation, concatenation and length, and vertical and horizontal lookup values.

What will I learn to do with data validation and lookups?

You will learn how to validate user input data before it enters the system, how to use lookup tables, and how to look up data in a table and return one or more results.

What skills does this course help build?

It builds analytical skills, Microsoft Excel and spreadsheet skills, analytic applications, data analysis, and Data Analysis Expressions (DAX).

What are the learning objectives of this course?

The objectives are to describe basic Excel functions and how they work, explain how to apply conditions to Excel functions, and understand how to validate data.