Join Dennis Taylor for an in-depth discussion in this video Design concepts for setting up large lists and restructuring downloaded data, part of Setting Up a Database in Excel 2013.
Microsoft Excel is not typically described as a database package, and yet many, many people do use Excel to manage large lists of data. Setting up those lists takes some effort, and also there are some unstated design techniques that are going to help, some principles that aren't really stated in Excel. The word database, by the way, doesn't occur anywhere in the menu system in Excel. Many, many times you will see a list like this and people will refer to it as their database. People who use database software like Access, for example, use terms like tables and queries and forums, and those together comprise what's called a database.
The term is used somewhat casually, and maybe incorrectly, some people would say, in Excel. We're going to talk about how you set up a list. This list here already set up does embody some of the basic concepts of how data should be laid out if you want to work with it efficiently in Excel. Within this list of names here we have no empty rows, nor do we have any empty columns. That's much less likely but nevertheless we don't have to have those two design features. No empty rows, no empty columns.
We might have empty cells, column I has empty cells here, nothing wrong with that. A less obvious criterion for good design is get your titles into a single row. If you have other information above this, for example a company name, an address, that sort of thing, maybe identifying information, that's fine. Keep it separate from this data here with at least one empty row. If we want to take advantage of the commands on the data tab in the ribbon, we're talking about sorting, filtering, the advanced filter, subtotal and perhaps a few more here, as well as the pivot table capability available on the insert tab, all of these features will work more smoothly if we make sure we have no empty rows, no empty columns and our titles are in a single row.
In the next worksheet over called interim totals, some totals have been inserted here at the end of each department. That's good useful information but if we want to sort this data or use other data management tools it's going to be awkward because these are in the way, we need to get rid of them. Then the next worksheet over called separated groups. We have empty rows at the end of each department. This is probably set up for readability, and it is easier to read this way, but that too is going to cause some problems if we leave our data in this state.
By the way we could easily select this data and sort it, and all those empty rows will end up at the bottom and therefore we can get rid of them pretty easily. In the next worksheet over called multi-row titles we see titles that take up two rows. Over the years some of the changes in Excel have allowed us to accomodate these a little more easily, but as a general approach to laying out your data it's best to redesign your titles to get them into a single row. You might be saying, "Yes, but I really want this to say "phone number, I want to see it spelled out and everything." Using this one as an example, eventually we do this with the other columns as well, I'm going to double click here in front of the N in number and simply type phone and press alt enter.
That puts the two entries together. Then when I press enter we see how that's looking. Eventually we do that with the other entries here as well. That's in a single row. That's going to make your work with Excel much, much more efficient. The next worksheet over called OS tax seems to have been designed either for a report or the printed page, maybe for display reasons. It certainly does have good information in it but the way it's laid out we really can't work with it very efficiently in terms of being able to sort the data, to create a pivot table from it, possibly insert some subtotals.
What we in effect would probably need to do in this worksheet here to make it more efficient in terms of usability and the ability to manipulate it, we probably need to somehow get rid of rows like this. Before doing that here's what we might need to do. Possibly put in a new column to the left of column B. Right click, insert. Take this text here, move it there, and copy it downward. Then we can get rid of these empty rows right here. Similarly we could do the same thing with this set of data and with this set of data.
Sometimes redesigning the data could be a chore, and it might well be in some cases, but if we can get our information into a row-by-row structure, row after row structure here, then it's going to be much more efficient to work with this data and be able to perform some real analysis on it. Redesigning data sometimes, although a chore, is really going to be the solution to working with this data efficiently. Going back to that first worksheet again, employees. Reminder, any data you want to work with efficiently, call it a database or not, we don't care, but make sure you get rid of the empty rows, the unusual rows, no empty columns, and redesign your titles to put them into a single row.
- Understanding database-design concerns like large lists
- Converting data into tables
- Working with formulas
- Using slicers to facilitate table filtering
- Using the Form tool to build and view data
- Using data validation to restrict data entries