When you are setting up your source data for an Excel pivot table, there are a few data layout guidelines that will help you create the best possible pivot table. Sometimes you get data that isn’t well suited for creating a pivot table, like the example shown below. It has a column for each month, [...]

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 [...]

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 [...]

In Excel 2007, when you create a second pivot table from the same source data, you don’t get an option to base the new pivot table on an existing pivot table, the way you can in Excel 2003. In Step 1 of the Excel 2003 Wizard, you can select the first option, to create an [...]

When you add a numeric data field to the Values area in a pivot table, the summary function defaults to either Sum or Count. You can change that field to use one of the other summary functions, such as Average. The Average Function The Average function in a pivot table works like the AVERAGE function [...]

An Excel Table is a new feature in Excel 2007, similar to the List feature in Excel 2003. An Excel Table makes it easy for you to sort, filter, and review your data, whether it’s a few rows and columns, or thousands. You can also use an Excel Table as the source for a Pivot [...]

When you add a field to the pivot table Data area, it automatically gets a custom name, such as Sum of Units or Count of Units. Instead of “Sum of Units”, you might want the name to show as “Units”, so it’s easier to read and the column is narrower. Unfortunately, if you select the [...]

While working in the pivot table source data, you might add new items. For example, if you start selling a new product, that product will appear in the list of orders. If your pivot table is based on that list of orders, the new product will also appear in the pivot table, when you refresh [...]

If your pivot table is based on an Excel list, and you probably add records to the existing source data table. You’d like the source data range to automatically expand to include any new rows and columns. You don’t want to manually change the pivot table range every time you add new data. Create a [...]

You might have to make changes to a workbook that contains a pivot table. If someone else created that workbook, you may not be sure where to find the Excel table that was used as the pivot table’s source data. In some workbooks there can be several Excel tables, and it’s not immediately obvious which [...]