From the course: Excel: Managing and Analyzing Data

Structure data for optimum usage in Excel - Microsoft Excel Tutorial

From the course: Excel: Managing and Analyzing Data

Start my 1-month free trial Buy for my team

Structure data for optimum usage in Excel

“

- [Instructor] Let's talk about optimizin' your data so that it is useful, so that you can help Excel help you. Now, nothin' that I'm sayin' here is going to be sacrosanct, okay, because you have to understand your data and your purpose or your needs. So even if I say in column F, that Mayor column, it would be best to separate the mayor's first name from last name and have two separate columns, if you have data and you know you will never sort or filter by that mayor's name or run any XLOOKUPs against it, then fine, leave the name whole or even get rid of the column. It's always your context. But let's go on forward. We've got this county data, we've got Barco County we're lookin' at, we've got Gladd County, Raddlit, and Rampart County. Data from column B through H, okay. Oh, and in row three, we have a note sayin' that Barco City used to be named Long Log Township. Do we really need that? For one thing, that is not a good place for it, because if we do want to sort or filter, this is going to create a problem. We need to have contiguous datasets, consistent datasets. Let's look at the other data, let's look at Gladd County. And one thing is Mayors, plural, column is in column G, whereas over here, it's in column F. Okay, we don't have any notes or anything strange. But let's look at Raddlit County. Okay, now we have some math happenin' in rows seven, eight, and nine. Rampart County, completely different format. So now, this creates problems if we want to know what is the total population of all four counties, if we want to stack up the city hall addresses against the cities, if we want to list the mayors, or we want to check to see which information is missin'. We've got four different places. And if we find out that the mayor's name is wrong or is misspelled, we have four places to go fish it out. This is a better way. Look at this, all counties, everything is consistent. The mayor's names are split out, we've got the populations here, everything is all in one place. And I even have math happenin' over here, lettin' me know about rows that have missin' information. This yield sign is showin' that I'm missin' the number of terms over here. I'm missin' somethin' in row three, what's missin'? The mayor's name, first and last, and the number of terms for the mayor. Down here, we're missin' the founded date. And now, watch, I'm going to go straighten some of this out. Reeves, Gladys. Alright, hit Enter. Alright, and I'm still missin' data. Let me spell Gladys right. And then go over, and Gladys is in her third term, Enter. Look, the yield sign is gone. Now, what if I want to sort by population? Go into the column, I'm in the data tab already. Let's sort it descendin'. There we go. And we see here is one that's missin'. We still have our note about the former name of Barco City. Everything is right here. If we do want to look at just one county, put on the Filter buttons, go over here, and let's select Gladd County. Okay, now we're only lookin' at Gladd County. And then, because we're in a table, I can go over here and then put a Total Row, and get a Sum. Easy, because the data's all in one place. Let's get rid of this Total Row, and I'm going to clear the Filter. Go to data, clear the Filter. And now, we're back to havin' everything. I can create a Pivot Table. Go to Insert, Pivot Table. Put it on a New Worksheet. Let's look at the cities in rows, counties in columns, and then the population in values. Now, we can see two ways. Now we can see the population in Apple Springs, and we can see the total population for Barco City. Try doin' that on four different worksheets, and then you add a fifth, now you've got to go and change all of your formulas. No, keep your data in one place or as few places as possible, because that will make your analysis so, so, so much easier. Alright? Excel is a tool. Excel can be a friend, but you have to help Excel help you by formattin' your data properly.

Contents