Lesson 10 – Excel Tables & Sub-Totals

In the last lesson we explored Excel’s fantastic charting capabilities, demonstrating how easy it is to create world-class charts in no time at all. As you learned, Charts can be one of the most time consuming elements of working with Excel, but they don’t need to be. In fact, a general misunderstanding of Excel’s charting prowess is generally what leads many people to either shy away from creating charts or just creating ugly charts quickly. And there is good reason for doing so, if a chart takes 10 minutes to create and you have to build many of them you’d probably try to get it done as quickly as possible, not bothering to go take those extra few steps needed to make them look nice. Fortunately, by setting up chart templates, you learned how to skip right past the hassle of recreating charts over and over again, instead relying on a few nice ones that you can depend upon.

Excel Tables are a similar tool in that they have been designed to help you work with data sets in a streamlined fashion. As you’ve seen in these lessons when you create a dataset you often want to take advantage of Excel’s amazing capabilities as an analysis tool, by adding totals, averages, etc. Normally this means creating individual formulas and copying them across the relevant ranges. When you do this it’s entirely up to you to tell Excel how big that range is and what you want to do with it. Tables put some of this burden on Excel’s plate, because a Table will recognize all of the contiguous data in a Table as related information. Tables allow you quickly apply any of the extensive Table formats from the Table Styles gallery. In addition, Tables add functionality called Structured References, which is completely alien to the Function and Formula concepts you’ve learned so far. Structured Reference methods do to tables what the AutoSum wizard can do to simple ranges, and we will devote a good portion of this lesson to understanding them. Before moving forward though, there is a distinction between an Excel Table, which is the tool we’ll be discussing in this lesson, and a table of data in Excel, which is the data we’ll be converting to an Excel Table. A table of data in Excel represents your raw data that is structured in a Row/Column format. Both are tangible entities, but where one is your physical data, the other is a tool for manipulating that data. Neither should be confused for an Excel Data Table, which is an analytical tool found on the Data menu in the What-If Analysis group.

Do tables do anything for you that you can’t do by yourself in Excel? Yes and no. Yes, because Tables incorporate a lot of features that you simply couldn’t add as fast by yourself. Granted you could probably write your own custom VBA code to replicate a table (and until now that’s what many people did to achieve the same thing), but why bother if it’s provided for you? On the other hand, you can technically do everything that a Table can do, but you have to do it manually. For instance you can apply the fancy table formatting that you can get from a table, but it’s going to take a lot more than 2 mouse clicks. Take the following example of unformatted data. It’s not very appealing, and is about as generic a display of data as you can possibly get. Finance and Accounting types love this kind of data display, but the rest of the working world probably feels a bit differently. After all if you have to look at the data, shouldn’t it at least be nice to look at it?

Sub-Totals - The primary thing that Tables can’t do for you is incorporate Sub-Totals, and it is a big thing, which is why it’s included in this lesson. Sub-Totals allow you to break your data down into chunks and summarize it along the way. Let’s say you have a table of data that’s broken down by Region, Product, Date, etc. Sub-Totals let you change it from just data to summarized data without you having to lift a finger toward the Formula menu. Sub-Totals also let you use several different functions for summarizing your data: Sum, Count, Average, Max, Min, Product, Count Numbers, and Deviations & Variances.

In the following example you’ll see some table data that has had a layer of sub-totals applied to both the Region as well as the Product type. This is the same data that we’ll be using in the Lesson 10 companion workbook.

Pivot Tables – Excel Tables are excellent candidates for creating Pivot Tables, which we’ll discuss in depth in the next lesson. Pivot Tables give you the ability to add the Sub-Totals that you can’t add in Tables.

 

 

Figure 399  

• • As you can see, Sub-Totaling not only does a fantastic job of almost instantaneously adding Sub-Totals, it also groups each sub-totaled section, so you can expand or collapse the data as you see fit.
• • Before continuing perhaps we should lay out the Pros and Cons of Tables to let you decide for yourself it they’re a worthwhile tool. We’ll point this out because Tables introduce some foreign elements that can confound even the most seasoned Excel users.

 

• • By the looks of it, the Pros outweigh the Cons. If you haven’t used Tables in Excel before, then you have no reason not to do so. Frankly, the people who dislike tables the most are the ones who have been using Excel for years and are used to doing things by themselves. A lot of those people would switch if they knew how easy they are to use. Does this mean that Tables are the right choice for all data situations? Of course not, in fact you’ll find plenty of scenarios where they do nothing more than get in the way. But when they do come in useful, they can be worth their weight in gold, especially with regards to how much time they can save (and by now you should recognize that saving time is a fundamental tenet of this course)! If you recall the use that Data Filter can have when working with charts, then you can imagine how easy it might be for a user to work with a nicely formatted Excel Table while interacting with charts for different data perspectives.
• • What type of data is an ideal candidate for a Table? Any data that is cohesive and formatted following the Rows/Columns methodology you’ve learned so far will work for a table. Unlike charts Tables don’t require you to have numeric data, although they certainly can. Data that has poor or no structure won’t do you any good as a Table, but for that matter, it probably won’t be much use to you in Excel either. Ideally your data would be contiguous, but tables don’t require it. The Table’s Structured References will ignore the gaps in your data. This is nice in relation to functions/formulas that can potentially hiccup on gaps in data, especially if you think they’re including the external ranges, but they’re not. From a formula auditing perspective this can be very reassuring. However, you do need to know that if you try to use the Table Wizard to create your tables it will not include non-contiguous ranges. If you want them included you’ll need to select the entire range by hand.

Convert Data to a Table

To get started you need to convert your data to a Table, but it’s remarkably easy. In fact the most difficult part of creating a table is deciding on which format to choose! With the active cell somewhere in your table data range, simply goto Home, Format as Table, select the Table style that you want and you have a fully formatted table in two mouse clicks.

• • Table Formatting – This is probably the most used feature of Tables, so much so that many people will never step beyond this feature. And if this is all you need then there is no reason to go any farther. For many people it’s simply an added bonus that Excel Tables also include automatic Data Filtering.

 

Figure 400  

• • If this is the first time you’ve seen a table up close and personal, then the formatting should stick out like a sore thumb. As mentioned, this is the sole reason that many people will apply table formatting and never take a step beyond this point. To apply that formatting by hand would take the average user upwards of 5 minutes or more. That alone is enough of a reason to use them. But in case you’re curious about what else you can do with tables, we’ll continue exploring them next.

 

• • The most recognizable element of Excel Tables (next to the obvious formatting) is the Auto-Filter functionality. We’ve already discussed the power that Auto-Filter gives you, but we’ll review it briefly just to refresh your memory. One of the primary reasons to go over this is to show you how you can get data sub-totaled in your Tables, albeit for individual data sets, as opposed to Sub-Totaling an entire table of data. In this example our Table1 data has been filtered by the Central Region, Product ABC, for 2009 only. Note that there are totals at the bottom, which were applied from Table Tools, Table Style Options, Total Row, then selecting SUM for each of the three revenue columns (Revenue, COGS and Profit). Remember, you can quickly see which columns in a table have been Filtered by looking for the ActiveFilter symbol in the Filter drop-downs.

 

Figure 401  

• • Total Row – The example shows a Total Row, although it is technically a Sub-Totaled row. A side-effect (feature?) of Auto-Filter is that it will only apply functions to visible rows, so the rows that have been hidden are excluded from our current totals. If you were to change the filtered criteria, the totals would only apply to the new view. If you look closely at the example you’ll see that there is a Sub-Total function instead of a regular SUM function: =SUBTOTAL(109,[Revenue]). If this table had not had filters applied* then the function would look like this: =SUM([Revenue]). We’ll get more into the details behind Sub-Total and Structured References a bit later, but what you see here is a mix of both (note the named reference to [Revenue] as opposed to a range reference like =SUM(A1:A10) – when you see that you know you’re dealing with Structured References). Remember that in order for filtered data to perform the way that you expect, your data needs to be sorted first, otherwise filtering can exclude some results. Fortunately, Auto-Filter contains Sorting functionality, so you don’t need to leave the comforts of the Auto-Filter dialog to apply different sort criteria. You can apply multiple sort criteria by selecting the columns and sorting them in the order that you choose. You can also invoke the Sort Wizard if you want to add all of your criteria at once. However, in both cases the Sort-by-Color options will not work in Excel Tables, as the formatted table row color won’t be recognized.

 

Figure 402  

 

• • Table Tools – Once you’ve created your table Excel exposes a new Ribbon group for you called “Table Tools”. It’s like any of the other additional Ribbon elements you’ve seen so far and it deals strictly with Table elements.
• • Properties – This is a very straightforward group in that you only have 2 options: Table Name and Resize Table.
• • Table Name – By default each new Table that you create will be named sequentially with a non-descript “Tablexx” name. If you want something more descriptive please feel free to change it here. Note that the naming conventions for Tables are the same as with Named Ranges, so “Expense Tracking” wouldn’t work, but “Expense_Tracking” (note the underscore) would, and if you enter an invalid name Excel will be sure to tell you. In fact, Table names get stored in the Named Range Manager, so you can’t have duplicate names. The Table 1 example in the companion workbook has been named “Table_1_Example”. Naming tables can come in very handy if you find yourself creating a lot of them, because it’s a lot easier to remember what each one does if it has a descriptive name. Although we won’t cover it in this course, naming your tables also helps if you decide to interact with them in VBA at some point.

 

 

Figure 403  

• • Resize Table – This is a feature that will probably see very little use, as Excel Tables will automatically expand the Table range if you add data in any contiguous Row or Column. You will use it however, if you decide to add data that is not contiguous. The following examples will show what happens if you add contiguous data vs. non-contiguous data.

 

 

Figure 404  

 

Figure 405  

• • As you can see in the non-contiguous data example, Excel added the new column, but it was also forced to add a column in between to bridge the gap between the existing table and the new data. Adding a non-contiguous row would be the same, except if you already have a Total row in place, in which case you have one of two options:

1. Remove the Total row by unchecking it from Table Tools, Table Style Options, Total Row

2. Select the Total row and insert a new row(s) with ALT+I+R (or any other long way to do it)

• • Sticking Point – If you have totals in the rightmost column of the table the table will automatically expand if you enter new data in the next column, but it won’t move the totals that you already have in place. In this case you’re better off inserting a new column immediately before the totals with ALT+I+C. Perhaps in the future Microsoft will change this and have that new column automatically inserted inside of the existing totals, but for now you’re well off knowing how this particular aspect of Tables behaves. As with anything else in Excel you’ll get the hang of it with some experimentation.

 

Figure 406  

• • Undoing Added Data – If you accidentally add either an extra row or column to a Table, you might notice that Excel presents you with a Smart Tag near the insertion point. This gives you the ability to undo the addition (you could also use CTRL+Z), and it gives you the option to turn of the Expanding Table behavior if you find it irritating.

 

Figure 407  

• • Manually Resizing Tables – If you noticed the bottom of the Table you’ve probably seen the resizing handle in the lower right-hand corner. With it you can expand a table to the side or down, but not both at the same time.

 

Figure 408  

• • Tab your way to a New Row – If you’re in the last row and last column of a Table, you can also create a new row simply by hitting the Tab key. This will also work if you already have a Totals row, which is a bonus.
• • Tools – There are three options in the Tools menu.
• • Summarize with PivotTable – This is one of the most powerful side-features of Tables, in fact the entire next lesson is devoted to PivotTables, so we’re not going to discuss it here. Suffice it to say, when Excel creates a Table it also takes internal stock of your data and its structure and sets it up for seamless transition as a PivotTable. Earlier in the lesson we discussed how Excel Tables don’t support Sub-Totals. PivotTables are the solution to that issue and they are as powerful an analysis tool as you can possibly imagine. PivotTables make doing in Excel what could only be done with database functionality previously. If you analyze a great deal of data, the Excel Tables are a great start, and a fantastic lead into PivotTables.
• • Remove Duplicates – This is another fantastic tool. In the past if you had duplicate data in your Excel table, you had to go through some fairly laborious steps to identify and/or remove it, many of which involved VBA code (some of it graciously provided by Microsoft because they knew this was one of Excel’s weaknesses). You could also use Conditional Formatting to identify the duplicates (and you still can) or fairly complex formulas, but that also meant finding a mechanism to delete the duplicates once you identified them. More often than not it was faster to take that data, put it into a database to cleanse the duplicates and then pull the re-mediated data back into Excel. You can imagine how long that took, but the alternative in many cases was to manually identify duplicates and remove them by hand (talk about a gigantic time vacuum!) The Remove Duplicates tool is the same as found on the Data Ribbon, it’s just included as a native Table option to save you the time of switching to another Ribbon group while you’re working in the Table environment.

 

Figure 409  

• • Unfortunately, the table data we’re using for examples isn’t a good candidate for removing duplicates because it’s transactional data and each row is a unique record, so we’re going to use a very simple dataset instead.

 

Figure 410  

• • In the example given, if you were to proceed with both Data points selected for elimination you’ll get a message that Excel can’t find any duplicates. Even though you can obviously see duplicates in column A, when you compare them in conjunction with column B’s values you end up with unique data. So we’re going to go back and only select the Data 1 criteria and see what happens. As you can see, Excel got rid of the Column A duplicates. An added bonus is that the list wasn’t sorted when we started!

 

Figure 411  

• • Convert to Range – If you love Table formatting, but not the added functionality that they bring you then you’ll spend a lot of time here. This simply lets you remove the Table elements from your data, but retain the Table formatting. The giveaway for determining data that is an Excel Table vs. data that is formatted as a table is the lack of the Auto-Filter drop-downs.
• • If you happened to have totals in your duplicate data set, then you’ll see the function be converted from a Structured Reference back to a normal Excel function, although it does retain the Sub-Total functionality.

=SUBTOTAL(109,[Data 2]) vs. =SUBTOTAL(109,Duplicates!$B$2:$B$8)

• • External Table Data – This allows you to export your Table data to outside applications, most notably SharePoint and Visio.
• • SharePoint - If you’re not in a corporate setting it’s unlikely that you’ll deal with SharePoint, because as we’ve already discussed it is prohibitively expensive for small businesses. If you do deal with SharePoint then you’ll need to know the server URL for where to publish the table, as well as having the proper network access to be able to do so. If you have that information then the Wizard can walk you through it with relative ease. If you don’t then you’ll probably want someone from your SharePoint services/IT department to do it for you.
• • Visio – This is another application that very few users outside of a corporate setting will likely have installed, but if you do bravo! It’s an excellent application. Visio is a very complex application, but it is also remarkably easy to use without having to delve into its complexities. Simply put Visio gives you the ability to draw all kinds of diagrams, like flow & process charts, organizational charts, floor plans, network maps, database designs, etc. If you can think of data that you have that could benefit from a diagram standpoint, then you’d do well to explore Visio. When you combine Visio with Excel Tables you have a very powerful descriptive diagramming tool. Here’s an example of our Excel table data output to Visio in the form of an org chart with Region and Product selected as the primary data points.

 

Figure 412  

 

Figure 413  

 

• • You can add any of your Table categories (Columns), as well as any Totals that you might want. One interesting feature about the totals is that they are added to the diagram in the order that they’re selected, not the order of appearance in the list (on the lower-left side of the Visio pane). Another nice feature is that once the data is exported to Visio it remains linked to your Excel Table data, so you can make changes there and have it refresh in your Visio diagram.
  
   
• • Table Style Options – These options let you refine the Table Style you selected when you first converted your table data to an Excel Table, and with the exception of the Total Row, the rest of the options are purely personal preference. There is no performance loss/gain by choosing one option over another. In most cases you will at the very least have a Header Row. Here’s an example with Rows unbanded and all the Column options selected so you have a better idea.

 

Figure 414  

• • But you can overdo it, so you’ll generally only want to choose Rows or Columns, but not both.

 

Figure 415  

• • Table Styles – This allows you to apply the color theme of your choice, whether you choose to format the table when you convert your data, or if you’d rather insert an unformatted table and format it later. You can choose from Light, Medium or Dark, and all of your style colors are dependent on your Default Theme (Page Layout, Themes). The default is the Office Theme, but you can definitely go with some unusual colors if you’ve changed Themes, or created your own (although the ubiquitous high-school classroom vomit green might be a choice to reconsider).

 

Figure 416  

• • Conditional Formatting – Tables work very nicely with Conditional Formatting and its new Data Bars, Color Scales and Icon Sets. Microsoft added something to Tables that’s incredibly useful called “Persistent Formatting”, which means that if you apply formatting to an Excel Table and then add another row, Excel will automatically extend the formatting (both conditional formatting and the table formatting) to the new row. When you start working with Excel you’ll see how much this simplifies things the first time you add a row to some non-table formatted data and have to apply the formatting to the new row by hand. Is that feature going to make you substantially more productive in Excel? No, but it will certainly have incremental benefits.

 

Figure 417  

• • Structured References – When Microsoft created the new Table format they also created a new function language that’s similar to the function syntax you’ve seen so far, but it is less like native functions that use range references (=SUM(A1:A10)), as opposed to using Named Ranges in formulas. Let’s say you create a data range like the one you see in the next example, then name that range “MyNewDataRange”, and try to sum that new range. The result is what you would expect.

 

• • Calculated Columns - Structured References in Excel Tables are remarkably similar to using Named Ranges. As you can see in the following examples we’ve added Calculated Columns for Price, Price/Piece & Cost/Piece formulas, as well as totals at the bottom.

 

Figure 418  

• • The formulas are as follows:
• • Price – =[@Revenue]-[@COGS]
• • Price/Piece – =[@Revenue]/[@Quantity]
• • Cost/Piece - =[@COGS]/[@Quantity]
• • As you can see, the Structured References use the Table Headers as the formula arguments as opposed to cell references. Had this example not been formatted as a table, the Price formula would have been =G2-H2. The interesting thing about the Structured References is that Excel knows where each formula is, so that it automatically updates the cell references internally without needing to display physical changes with then, like =G3-H3, =G4-H4, and so on. These are examples of Unqualified References, since Excel knows that you’re referring to the current table.
• • When you enter a Structured Reference in a column, Excel will automatically fill the formula in the entire contiguous range of table data, so you don’t need to copy/paste like you would need to with a standard formula.

 

Figure 419  

• • Table Total Row – We’ve already discussed this briefly, but Tables also give you the ability to add a Totals row with multiple Functions (the same as with Sub-Totals that were mentioned earlier). In the Structured References example we’ve added a Total row with Averages for the Quantity, Price/Piece and Cost/Piece columns, and Sums for Revenue, COGS & Profit.
• • Here are the formulas for each column. Note that they all take the Sub-Total function arguments, which we’ll discuss in more detail when we review Sub-Totals (101 – Average/109 – Sum).
• • Quantity - =SUBTOTAL(101,[Quantity])
• • Revenue - =SUBTOTAL(109,[Revenue])
• • COGS - =SUBTOTAL(109,[COGS])
• • Profit - =SUBTOTAL(109,[Profit])
• • Price/Piece - =SUBTOTAL(101,[Price/Piece])
• • Cost/Piece - =SUBTOTAL(101,[Cost/Piece])
• • External References to Table ranges – You use formulas to refer to table ranges, although the result is somewhat broader then the internal table calculations, as Excel needs to identify the table by name. In this case we created a formula for Revenue - COGS = Profit. The result is the same as the $57,904 Profit figure that was calculated in the Totals row in the above example, but the syntax has been expanded.
• • Revenue – COGS , =Table_StructuredRefs[[#Totals],[Revenue]]-Table_StructuredRefs[[#Totals],[COGS]]
• • Where Table_StructuredRefs is the table name, [#Totals] is called a “Special Item Specifier, and refers to the Totals Row, [Revenue] & [COGS] are called “Column Specifiers” and refer to the specific table columns as identified by the column headers. This is an example of a Fully Qualified Reference, which is necessary to let Excel know explicitly which table you’re referring to.
• • If the COGS was a negative number you could use this formula instead:
• • SUM(Revenue:COGS) , =SUM(Table_StructuredRefs[[#Totals],[Revenue]:[COGS]])
• • In this case the syntax is somewhat shorter, because Excel knows that the arguments within the Sum function refer to contiguous columns in the table vs. the two distinct arguments in the previous formula. Again, you have a Table name, Special Item Specifier and Column Specifiers.
• • Scrolling without Freezing Panes! When you scroll through a large data table in Excel you need to use View, Windows, Freeze Panes otherwise your header row will get lost and you might not know which column is which. With Tables, Microsoft took care of this in a way that you couldn’t do programmatically even if you wanted to: they replace the worksheet headers (the A, B, and C) with your Table’s column headers! This incredibly useful if you have multiple tables on a sheet, because it means that you won’t lose track of whichever table you’re in at the moment. You simply couldn’t recreate that behavior in Excel because you can only Freeze Panes once. So if you had another non-Table data series underneath a first, then your second series would inherit the frozen headers from the first data set. For sake of argument, consider the following example.

 

Figure 420  

• • If both of those data sets had been converted to Excel Tables you wouldn’t have that problem.

 

Figure 421  

• • If you only had a single table, then using Freeze Panes will override the column header replacement functionality.

 

• • Excel Table Errata
• • ALT+E+D+R will delete a table row, but ALT+E+D+C won’t, unless you’re in the Table’s Header Row. You can right-click anywhere in a Table and select the Delete Table Rows/Columns option.
• • You can also use the Insert/Delete functions from the Home tab.
• • Referring to Tables in External Workbooks – Unfortunately, if you want to refer to a table in another workbook, Excel requires you to have that workbook open, otherwise you will get a #REF! error.

 

Figure 422  

• • Sub-Totals

Sub-Totals are a fantastic tool for summarizing your data without having to resort to lengthy formulas. Microsoft built in a very robust series of Sub-Total functionality to do it for you. In addition you can use what’s called nested Sub-Totals in which you can sub-total one series of data and then add additional layers. We’ll go back to the companion workbook and use the example that you saw in the beginning of the lesson to detail Sub-Totals and how to use them.

• • The first rule of thumb with Sub-Totals is that your data needs to be sorted in the order that you want to Sub-Total. If your data isn’t ordered correctly, you will experience some unexpected results. This is generally easy to fix with smaller data sets, but if you have a large data set, you might not realize that the Sub-Totals are incorrect, or at the very least you’ll have a hard time spotting the anomalies that were created.
• • The good news is that applying Sub-Totals is incredibly easy. Once you’ve sorted your data goto Data, Subtotals and you’ll see the following dialog.

 

Figure 423  

• • Starting from the top down, select the Sub-Total criteria, in this case is by Region, which means that we want Excel to treat each Region differently. Next is the function to use, which will be SUM (your options are the same as with the Excel Table Total Rows), then check the columns you want to include in your Sub-Total. As soon as you apply the Sub-Totals you’ll see that Excel has grouped your data into three distinct views.

 

Figure 424  

• • The first group is the Grand Total, the second is by the Change In criteria (Region in this case), and third is a completely expanded view. The third tier view is the default when you first apply Sub-Totals.

 

 

Figure 425  

• • Each of the Plus/Minus signs beneath the grouping options will allow you to expand or collapse each view independently of the others.
• • Adding Layers of Sub-Totals – Very few people know that you can have more than one level of Sub-Totals, when in fact you can have about as many as you want, although you will probably never need more than just a few. There is a specific procedure to follow when doing this though. First is to apply the primary Sub-Total, and then apply the sub groups in descending order of importance, so if you wanted to Sub-Total by Region, Product and Month you need to apply the Sub-Totals in that order. Otherwise you won’t end up with the results that you expected. The key to applying secondary Sub-Totals is the “Replace current subtotals” check box in the Sub-Total dialog. In this example we’ll apply another layer of Sub-Totals on the Product category.

 

Figure 426  

• • As soon as your secondary Sub-Totals have been applied you will see another item added to the Sub-Totals grouping in the upper left-hand corner.

 

Figure 427  

• • If you look at each formula the Sub-Total has applied you’ll see a similar syntax to the Sub-Totals that were applied in the Excel Table’s Total Row.

 

• • Mixing Functions in Sub-Totals – Unfortunately, when you apply Sub-Totals you only have the option of selecting one function at a time, so you can’t ask Excel to apply and Average and a Sum for the same criteria. But if you have the Sub-Total functions at the right, you can manually swap them out. In this case we probably don’t want a Sum of Quantity, but an Average, so you could use Find & Replace (Ctrl+H) to change the function.
• • If you have data that’s been converted to an Excel table you’ll first need to convert it back to a range before you can apply Sub-Totals. However, Sub-Totaling adds rows, so if you had banded rows in your Table format the banding will not stay consistent. If you want to maintain the table format and also have Sub-Total functionality you would need to use a Pivot Table instead, which we’ll discuss in the next lesson.

 

Figure 428  

 

Unit Summary: Lesson 10 – Excel Tables & Sub-Totals

• • In this lesson you learned about Excel Tables and how they can help you if you just want to quickly format your data, or go farther and take advantage of the additional tools that they offer:
• • AutoFilter
• • Integrated Sorting (including Sort by Color & Custom List)
• • Structured References
• • Total Row
• • Automatically Add Rows & Columns to a data set
• • Remove Duplicates
• • We discussed Sub-Totals and how they give you the ability to quickly summarize multiple data points with different functions (Sum, Average, Count, etc.)

Review Questions – Lesson

1. Name two reasons for using Excel tables.

a. __________________________________________________

b. __________________________________________________

2. If you want to Sub-Total an Excel table what tool would you use (hint – Lesson 11).

a. __________________________________________________

3. If you create an Excel Table, but only want the Table Formatting what do you do?

a. __________________________________________________

4. How can you resize an Excel Table?

a. __________________________________________________

b. __________________________________________________

5. What’s the difference between 109 and 9 in a Sub-Total function?

a. __________________________________________________

6. What are Zebra Stripes?

a. __________________________________________________

7. What’s Structured Referencing?

a. __________________________________________________

Lesson Assignment – Lesson 10 – Excel Tables & Sub-Totals

• • Your assignment is to explore the Excel Tables and Sub-Totals in the Lesson 10 companion workbook.
• • Take some of your own data and apply different Table styles to it.
• • Experiment with Structured References
• • Apply Totals to a Table structure
• • Apply Sub-Totals to the same data and take note of which method would be beneficial in different situations.