Join Dennis Taylor for an in-depth discussion in this video Structure data for optimum usage in Excel, part of Managing and Analyzing Data in Excel (Office 365/Excel 2019).
- To optimize your use of the various data management tools on Excel, you need to start with data that adheres to some basic standards. Now, none of these standards are requirements within Excel. A list like we're seeing on this worksheet right now called, Poor Design, We might have some hidden data here But, as lists get larger, there are certain aspects of the layout that we see here that really should be changed. Titles work best when they are in a single row and again, nothing truly wrong with what we're seeing here but if you sort the data, there will be times when Excel mistakenly takes row two and treats it like the other cells. At different times, you'll see how Excel works differently in working with the data that way. That always will better if your titles are on a single row. And by the way, if you are making some adjustments here, here's one that could help, imagine that I want the word Date to appear with Origination but I want to write under the word Origination. I'm going to be editing cell G1, I'll double click just behind the n, and then press Alt + Enter and then I'll type the word Date, and then enter. So those two words are in the same cell. And I'll clean up some of the others eventually And let's look at some other changes too. Empty rows, there's one in row 13, let's get rid of it. I'll simply right-click the row number and delete it. We might have an empty column. Are all columns visible? There's nothing wrong with hiding a column but what's in column E here? Let's drag across, right-click, Unhide. Again, now maybe you put in the empty row cause you were thinking of adding daily but you didn't get around to it. You hide the column, let's get rid of it. For efficient use of this list, we don't want empty columns or empty rows within the list. Right-click. In this case, delete. Columns A, B, and C all reflect a certain kind of layout that can be problematic. If this list gets larger and larger, can we sort our list by last name which you might want to do then. No, we can't, there are techniques for reversing those Either split the data into separate columns, a first name column and a second name column or reverse the order. That first entry in cell A3 could be and ideally would be and do that for the others as well too. And there are techniques such as Flash Fill that we can clean up the data more efficiently with. That's covered by the way, in one of our other movies called Cleaning Up Your Data in Excel. In column B, as the list gets larger and larger, We can't do that here. We can't sort it by ZIP code either. Ideally, 3 separate columns, City, State , Zip. There are two for cleaning up that data. and needed to notify the people on Pine Lane, So in certain situations, the data in column C should be in three separate columns, like Courtland, boulevard, and etctera. Nothing truly wrong with column D or E either but these are actually dashes here. If you type these without dashes, you can have a format that puts in the dashes for you. Remember, most people don't type dollar signs and yet they often see them, they applied a format that uses dollar signs. Same thing here and with phone numbers. Why type 14 characters here, when you can only type the numbers. So these can be cleaned up quickly and easily. Eventually, you can jump right into a feature called Format Cells, Special formats for number and Social Security Number, So, after getting rid of the other symbols, we take your left The date entries in column F are nonstandard with certainty you want to clean those up. Make them consistent. And don't put two different dates in the same cell based on the way we're seeing these, we can't use them in a computational way. Here are two. None of these triggers any kinds of actions that is apart of Excel to say there's something incorrectly. But, if you stick to some of the standards that we mention, some of the tips we mentioned in this movie, working with lists of data will be a lot more efficient.
- Prepping data for analysis
- Multiple-key sorting
- Sorting based on custom lists
- Creating single- and multi-level subtotals
- Using text, numeric, and date filters
- Filtering tables using slicers
- Using the Advanced Filter
- Eliminating duplicate data
- Using SUMIF and related functions for quick data analysis