Join Curt Frye for an in-depth discussion in this video Creating an Excel table, part of Excel 2007: Creating Charts with Dynamic Data.
- View Offline
One very useful new feature in Excel 2007 is the Excel table. In Excel 2003, you had lists, which provided some of the same functionality as Excel tables, but Excel tables take it and run. You can create an Excel table by setting up your data as a list. In this case, we have data from A1 all the way down to be D25. There are no breaks. In other words there are no empty rows, so Excel knows its all part of the same data range. And also there is no extraneous data to the right or below the table.
That's important, because if you created a table and there was data to the side or data below it, then Excel will try to include that data in the table even though you wouldn't want it there. To create the table, on the Home tab, go to the Styles group, click Format as a Table and click the table format you want. Verify that Excel identified the table data, A1 through D25, that your table has headers, it does, those are here in A1 through D1, and click OK. And there is your Excel table.
The first thing you'll notice is that you have filter arrows, which allow you to limit the data that appears in the table. So let's say for example that I only wanted to see data from the month of January. I can click the Month columns filter arrow, clear Select All, click January, click OK and Excel limits my data. In the context of dynamic charts, Excel tables are very useful because they allow you to create expandable ranges. In other words, an Excel table is a collection of dynamic named ranges.
So for example, if I wanted to refer to this Excel table in a formula, I would type =sum and then the name of the table. When Excel creates a table, it uses names such as Table1, Table2. This one will be named Table1 because it's the first one that has been created. I'll enter that and I want to find the sum of the Sales values. So I will type a left square bracket and then type S for Sales. Press Tab to accept it from the Formula AutoComplete List, right square bracket, right parenthesis closed, hit Return and there is my value, which I'll format to make it easier to read.
If I add data to an Excel table, that formula will update. So for example, let's say that in 2011 first-quarter month of January, we had Sales of $500,000. As soon as I hit Enter to include the data in the table, this formula updates. Referring to Excel tables in your formulas make your formulas easier for you and your colleagues to understand. But remember that Excel tables can only be used in workbooks created in Excel 2007 and later.
That said, if you work with colleagues who run Excel 2003, they will see any Excel tables as lists, which were the predecessor to Excel tables included in 2003. If you collaborate with users who still have Excel 2002 or earlier however, you should use dynamic named ranges to refer to data columns that you could expand or contract.
- Creating an expanding chart using formulas Specifying the first point and the number of points for a series Plotting points at regular intervals in a series Filtering a PivotChart report to find the right information