One of the most useful new tools in Excel 2010 is the Excel Slicer. Add a Slicer to the workbook, and you can filter multiple pivot tables with a single click. In the screen shot below, there are Slicers for the Severity and Priority fields, and they are filtering both pivot tables. Drill to Detail [...]

It’s interesting to see what people are saying about Excel in Twitter, and I post a few favourite Excel tweets, from my daily reading. Of course, many of those tweets are about pivot tables, so here, for your Friday entertainment, are some highlights from the past. Do any of these pivot table tweets sound like [...]

In a pivot table, you can apply a manual filter to a pivot field, by using the check boxes in the field’s drop down list. In this example, there is a date field in the Row Labels area and a few dates have been selected in the manual filter. Updating the Pivot Table Data If [...]

When you have a long list of fields in an Excel 2003 pivot table, it can take a long time to drag them into the pivot table layout, using the PivotTable Wizard. For a quicker way to add the fields, don’t go into the Layout screen in Step 3 — just click the Finish button. [...]

If you need to sum specific items in a pivot table field, you can create a calculated item. For example, if your pivot table contains an Order Status field, with four types of status: Shipped Pending Backorder Canceled You could create a calculated item, named Sold, that sums the units sold, for orders with a [...]

One of the benefits of using an Excel pivot table to analyze your data, is that you can use filters to focus on a specific part of the summarized results. Hiding some of the data with filters can be misleading though, if you can’t tell that filters have been applied. If you apply a filter [...]

In some pivot tables, you might want to add comments to a few cells, to help the users understand what the results mean, or to explain the different pivot fields. However, if you add comments to a pivot table, you’ll run into a couple of problems. Add a Comment to the Pivot Table The Insert [...]

For a pivot table that’s based on a Microsoft Access query, you might need to find out which database and query were used as the source data. In Excel, if you click the Change Data Source command, on the Ribbon’s Options tab, you can see the connection name in the Change PivotTable Data Source dialog [...]

A powerful feature of pivot tables is that you can filter a pivot field, to see specific results. However, if you apply a different filter to that pivot field, the first filter is removed. For example, if you filter a Row field for region names starting with "East", and then add a Top 10 filter, [...]

In Excel 2010, you can use the free PowerPivot add-in to create a report from multiple Excel workbooks or worksheets. With PowerPivot, it’s easy to connect the tables using the Primary and the Foreign key, such as ‘ProductID’ in a Sales table and a Pricing table. In some cases though, you might want to combine [...]