Microsoft Office Tutorials and References
In Depth Information
You can link both slicers (Category and Color) or make a selection of which slicer to link to the
PivotTable and which ones not to use. If you link both slicers to both PivotTables, both tables
are updated to reflect the filters when you make a selection in the slicers. This simple
characteristic of slicers makes them a great option when you need to create interactive reports.
Caution Whenever you place more than one PivotTable on the same worksheet, you need to
guard against overlapping them. PivotTables, by nature, change their size depending on the
selections you make. (As you change the number of categories, the PivotTable containing the categories
increases its height to accommodate the new categories selected.) If you want to place PivotTables
side by side, for example, you need to make sure that they cannot grow to the point of overlapping.
If this happens, Excel raises an error.
In this chapter, you learned some of the most useful features of PowerPivot:
■ Format strings should be set in the PivotTable field settings so that resizing of columns
is carried on by the PivotTable itself.
■ Useless columns should be deleted from the PowerPivot data model, and technical ones
should be hidden so that you have fewer columns to search to produce the report.
■ You can add simple calculated columns or use more complex functions, such as RELATED,
to enrich a table with information computed on related tables. RELATED is a very useful
function because it allows us to reduce the number of tables and move columns to
locations the user expects them.
■ You created your first linked table, which enriches the original data model with other
information directly stored inside the Excel workbook.
■ You learned the basic difference between calculated columns and measures, something
we discuss in greater detail in the next chapter.
■ Whenever the data inside the data model is too detailed, you should create aggregating
columns to avoid slicing too many details. You saw a demonstration of this technique on
date columns, but it can be easily extended to other types.
■ Data that needs to be refreshed needs to be reloaded from the database because
PowerPivot tables are a copy of the original data, not a link to them. This might be
an issue with very large databases.
■ Slicers are graphical tools to make filters for one or more PivotTables. They are useful
and good looking, and they allow you to build interactive reports by combining more
than one PivotTable in the same Excel workbook.