Structure data for optimum usage in Excel - Microsoft Excel Tutorial

- [Narrator] Let's look at some ways that you can format your data so that you can help Excel help you get what you need from your data. And I want to point out one thing, none of what I'm going to show you is sacrosanct. For example, in this mayor's column, I'm going to show you that it's better to keep the first name and last name separated in two separate columns, which you cannot right now as this data sits. But you are rarely ever going to sort this data by the mayor's last name. Then don't worry about it, leave it like this. But you have to be clear about what you need and what you might have as foreseeable needs. So if your whole purpose is to look at the years the cities were founded and the city hall address, and the mayor's details is nice to have. Okay, but let's move forward and look at some general things. First of all, we've got three counties data, Barco County, Gladd County, and Raddlit County. The first problem is these data sets are on three separate worksheets, let's look. Barco and then Raddlit. Let's go back to Barco. Somebody has inserted a note saying that Barco City used to be named Long Log Township, okay? If this is important, this needs a better place than breaking up the data set because we can't sort the data with that note sitting there. It's hard to write formulas if we wanted to write formulas against the population data or the number of terms. So that is not good, having that note there, okay? We looked at Gladd County, and here is Raddlit County. One issue here is notice, column F, column H. So what's going on? Highlight both of those columns, right click, unhide. Here is data about the number of colleges and universities in each of these cities. Again, if this is important, then it needs a better place to be than right here in the middle of the data and in a hidden column. And none of the other counties have this data. Look at something else. We've got county, city, founded city hall, mayor, colleges and universities, number of terms for each mayor, and then the population. Okay, for Gladd County, the mayors and the number of terms are in H and I. So now the columns are even out of order. Also with these being in three sheets, if we have updates, we've got three places to look. So if a mayor's name was misspelled or is wrong it needs to be updated. You've got three different places to look. If you do want to add a column for universities and colleges, you got to do this in three different places. So let's look at a better way. Here we have the data in a table. And in this course I am going to talk about tables. And you can tell it's in a table because the cursor is inside the data set. And up top, we see a tab for table design. If we click outside of the table, that tab goes away. So let's click back in it. Everything is in one place. And yes, it is harder to read. But one thing we can do is we can sort. I'm going to go over here and then go to data and then sort ascending, okay? So now all of the Barco County data is all together. All of the Raddlit County data is all in one place. When we get new information, we have one place to go. If we want to write formulas or create a pivot table, it's all right here. So if you did want to look at the data by population, go over here and then sort it descending. Boom, now we know Raddlit County has three of the biggest cities in the whole data set. And then Barco County has the fourth largest. We can't see that so easily with the data in three different places. So those are a few of the things to look at when you're working with data. And throughout this course, I am going to talk about ways to sweep data into one place when it comes to you because certainly you wouldn't build an octopus spreadsheet with everything everywhere, no. But if some other person, some other client sends you data. And if they sent you an octopus spreadsheet, I'm going to give you some tools for sweeping it all together, all right? So always be cognizant of how you format your data so that you can best help Excel help you.