Pivot Tables

Hello Dj,

Hope you enjoyed your Microsoft Excel courses with Best STL.

Thank you for your question regarding adding columns to a pivot table or creating a pivot table from two or more worksheets.

Let's begin with adding columns. You cannot add a column or row to a pivot table in the conventional way. If you add new rows of data to your existing data sheet which contain new entries not on the pivot table, then when you refresh the pivot table these newly added items will appear as new columns. An example would be a Region field containing 5 regions. You have added 3 new regions to the data rows and you are using this field in the column labels area then you will now see 8 columns and not 5.

Another way to add a column to the pivot table is by creating a calculated item.

Next, we'll take a look at how to add data from multiple sheets. This feature was removed from 2007 but the 'old' 2003 PivotTable wizard can still be accessed by using the shortcut ALT+D followed by P. The steps are as follows:

1) Press Alt+D followed by P to open the PivotTable Wizard.
2) In step 1 of the wizard, choose Multiple Consolidation Ranges. Click Next.
3) In step 2a, choose 'I Will Create the Page Fields. (You don't actually have to create page fields, you just don't want Excel to create page fields.)
4) Click Next.
5) In step 2b, choose the range on the first sheet. Click Add.
6) Repeat 5 above for each additional worksheet.
7) Click Finish.

Excel will create a pivot table that summarises all the worksheets. The fields have been given names Row, Column and Value.

Here's a great trick! If you double-click the Grand Total cell in the pivot table, Excel will produce a new worksheet with all your data in detail format. All you have to do is rename the headings correctly.

Give this a try!


I hope this resolves your question. If it has, please mark this question as resolved.

If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?

Have a great day.
Regards,

Rodney
Microsoft Office Specialist Trainer