Noel Lynne Figart

Tables in Excel and Why They Rock Quick, when someone mentions the word “table” concerning Excel, what do you think of? Probably an array of some sort organizing rows and columns of data. While that’s not incorrect, necessarily, tables are objects in Excel that can do so much more. What are Tables? Tables are specific objects. When you format a range of cells as a table, you’re telling Excel that you’re going to treat this range as a unit to display and manipulate your data. So, let’s say you take a range with some data in it that looks like this: We’re listing students, their Hogwarts houses, their favorite candies, and how Ordinary Wizarding Levels they achieved. We’re going to be doing market research on a new candy flavor, and we will be taking this sample data to try to decide how to target different demographics based on the information we have. Why Use Tables? We can manipulate the data in this step by step if we want to – apply color, borders, fill, filters, and so on. But there is an easier way. Layout A nice-looking array with banded rows and different formatting for the header isn’t just about the “pretty.” Differences in format, color, and shade make the table easier and faster to read. That’s why your supervisor gets excited at well-formatted tables. The data is easier to analyze. Let’s make this array a table. 1. Select the range of cells you want to format as a table. In this case, it will be cells A1:E6 2. On the Home tab in the Styles group, click on the Format as Table button. 3. The table style gallery will appear. Choose a layout you like. 4. A popup will appear asking if your table has headers and to verify the range of cells you want to make a table. Most of the time, it will have headers, so make sure you have checked it. Tables in Excel and Why They Rock Figart Consulting 1 5. Click OK. You now have your table. Isn’t it pretty? The pretty might be enough for you. After all, this is a decent layout, quick to do, and easy to add to. Any time you add another record to this table, the range of the table automatically expands to include it, and you’ll notice the banded rows continue. It looks good automatically. However, while this is nicely laid-out and easy to read, there’s more to tables than just looking good easily. Some other functions can also be important. Structured Referencing For our example, we’re trying to analyze how important someone’s opinion might be based on two things – their Hogwarts House, and how many OWLs they’ve achieved.1 With tables, we can easily create this equation, then apply it to a large table. 1 For the non-Harry Potter fan, an OWL is a test passed in a particular subject. The more OWLs you’ve achieved, the better student you probably are and more likely to have an influential job. Tables in Excel and Why They Rock Figart Consulting 2 For this example, we’re going to be listing the houses with Slytherin being the most influential with a score of 4, then Gryffindor with a score of 3, Ravenclaw 2, and Hufflepuff 1. We’ll add column to our table called Influence. Then we write this equation in the first cell of the Influence column. =IF([House]="Slytherin", 4, IF([House]="Gryffindor", 3, IF([House]="Ravenclaw", 2, IF([House]="Hufflepuff", 1, 0)))) Looks rather like a monster, doesn’t it? What this is doing is a series of evaluations that will assign an influence integer based on the House name. Instead of referring to a specific cell in Column C (i.e., C3), it instead goes by the Structure of the table and refers to the House field. Structured referencing means that we can perform calculations easily based on structure. Now that nested IF equation is a bit of a pain in the butt to write, and I only want to do it once. Because I write the equation in the first cell of the Influence column in the table, it will automatically copy that equation down to each of the cells in the Influence column. Now we are going to create our Influence Score based on the number of OWLs achieved times the Influence a particular entry has. We will again create another column calling it InfluenceScore. We write our equation as =[OWLS]*[Influence], not as =SUM(E2*F2). Now you can see how structured referencing works when calculating values. We’re doing calculations based on the field names, not the specific cells. You will also note that the field names are not case sensitive. When we press Enter, the equation will copy down the entire column. For eight records, this is not a particularly big deal. Autofill might serve as well. But if you have thousands of records, you might find that this comes in handy. (I sure do!) Once we have our InfluenceScore, we can then click on one of the AutoFilter buttons on the right of InfluenceScore header to sort the table from the highest score to the lowest. Tables in Excel and Why They Rock Figart Consulting 3 From there, we can see that Albus Dumbledore is probably the most influential person to approach about candy marketing in the Wizarding World. Conclusion As you can see, formatting a range as a table can make sorting, filtering, and evaluating data much easier. While this example only has a few records, if you need computer enhancement, you’re probably dealing with hundreds or thousands of data points. When you’re doing so, formatting data in tables for evaluation can make your job much, much easier! Tables in Excel and Why They Rock Figart Consulting 4