Microsoft Office Tutorials and References
In Depth Information
Working with Excel Tables
Working with Excel Tables
One of the challenges you can encounter when building a data model is a data table that expands
over time. That is to say, as you add new data, the number of records increases. Take a look at Figure
11-18. In this figure, you see a simple table that serves as the source for the bar chart. Notice that the
table lists data for January through June.
Imagine that next month, this table expands to include July data. You’ll have to manually update
your chart to include July data. Now imagine that you have this same issue across your data model,
with multiple data tables that link to multiple staging tables and dashboard components. You can
see that keeping up with changes each month would be an extremely painful task.
Figure 11-18: This table has the potential to grow every month.
To solve this issue, you can use Excel’s table feature (you can tell they spent all night coming up with that
name). The table feature allows you to convert a range of data into a defined table that’s treated
independently of other rows and columns on the worksheet. After a range is converted to a table, Excel views the
individual cells in the table as a single object that has the functionality a normal data range doesn’t have.
For instance, Excel tables offer the following features:
➤ Drop-down lists in the Header row that allow you to filter and sort data in each column easily
➤ A Total row feature with various aggregate functions
➤ Ability to apply distinct formatting to the table independent of the rest of the worksheet
➤ Ability to automatically expand in dimensions to accommodate new data (key for data
modeling purposes)
The table feature did exist in Excel 2003 under a different name. In Excel 2003, this
feature was the List feature (found in Excel’s Data menu). The benefit of this fact is that
Excel tables are fully compatible with Excel 2003!
Tip
Search JabSto ::




Custom Search