Set up large lists and restructure downloaded data

- [Instructor] Anytime you're working with a collection of data in Excel. It's going to be better to organize it as a list. If you frequently work with Excel and Microsoft Access a database package it's certainly going to be advantageous as it is with other kinds of database software as well too. In a list, make sure that every row, row after row has data in it. Now, in some cases, based on the nature of the data like column J here, we've got some empty cells, totally appropriate. Some of these people don't have benefits in this organization. Get rid of empty rows, get rid of empty columns and it's best to have your titles in a single row on this very first worksheet called employees. Everything seems to be an order here we're set up for efficient use. And that means that when we go to the data tab when we do sorting and filtering and possibly insert subtotals, that's certainly going to be of value. Later in this course you'll learn about converting data to a table that has some particular advantages that you want to consider as well. Now, the next worksheet over violates one of the rules that I mentioned empty rows, now not completely empty rows but we see row seven has totals in it . Now there is a feature that I alluded to earlier called subtotal found on the data tab, and that's certainly valuable. But if you use that think of it as being used occasionally, temporarily. And when you use it, you print it perhaps, but eventually what do you want to do? Get rid of those rows. Now with that feature, by going back to subtotal you can easily get rid of these rows. Next worksheet, over separated groups same idea here, but nothing in those rows. Now that might be great for printing purposes but if we've got lots of breaks here it's going to take some time to do. Another approach it would take some time too is to get rid of an empty row and then just make sure that the last row in your entry for example, row six, here, I'm going to make this taller dragging that boundary down to there and then clicking here and on the home tab, I'll put that in the top part of the cell with this button right here. And then we can get rid of that empty row getting rid of row seven there, just to give you some idea of how you could work around that idea but it is best to get rid of those empty rows in the list. And anytime you start to use commands on the data tab, nearly always, if you have empty rows you're going to encounter problems with sorting and filtering and subtotal. And some of the other tools that we see here on the data tab in the ribbon. Next worksheet over, multi-role titles. Again, looks good maybe for your purposes but it just poses unnecessary problems as you work with Excel. A little tip here by the way, now I'll just use it on one of the columns. If I wanted to put an anniversary month together here and I don't want to terribly wide I don't want the word month behind it. I'm going to double click on cell H1 on from the right side Double Click, just behind the Y and I'll press alt + enter and then I'll type month and enter and make column H a bit wider. There we go. And eventually I'd make row one, not as tall by double clicking the boundary between one and two. And I clean up the other entries here too. And then ultimately get rid of row two. It's always best to have your titles in a single row. You just avoid all kinds of little problems in Excel. Again, a little bit tedious to do that in all of these, but as you quickly get used to the idea of pressing alt + enter when you type, whenever you are putting in new titles that makes your data appropriate from the beginning. Next worksheet over, in its current state what we're seeing over here in columns A through Q certainly valuable for display purposes but that's not easy to work with in Excel. Anytime you want to do any kind of calculation. And it may take awhile, but if you were to restructure this and ultimately give many of these cells their own row now that sounds like a lot of work, but there are some techniques that it gets you there relatively quickly. If we were to restructure all that data and put it over here, and by the way I've highlighted cells that have formulas they're in yellow here, but this is the restructuring of the data we see over in columns, A through Q and it takes up a lot of space this way. But once we have done that and from now on, if we work with our data that way then we can use, for example, a pivot table to come up with summaries in a much more flexible way like this. This is based on that data and it's easy to manipulate. I, for example, could take months and put it down here and switch this. I could pivot it quickly and easily that may or may not be a better layout. We've got other fields here that we can manipulate quickly and easily by way of a pivot table. Now that's quite a leap and as well beyond the scope of this course, but once again, data like this where we've got detailed data here and all these then suddenly we have a total here down here that's for every other row. And then the one below it, every other row this is just not conducive to working with the data in a very efficient way. And in the next movie, we'll be talking about some poor design issues and how to correct them. So all in all, when you work with data, stick to that idea of when you have lists of information, make it be row after row without empty rows, no empty columns in list. That's much less likely and keep your titles in a single row.