Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

Calculated Fields in Pivot Tables

| | Information Helpful? Why Not Donate.

Custom Pivot Table Calculations

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only ~~$145.00~~. $59.95 Instant Buy/Download, 30 Day Money Back Guarantee & Free Excel Help for LIFE!

PivotTables are used to display and extract a variety of information from a table of data that resides within either Microsoft Excel or another compatible database type. PivotTables are frequently used to extract statistical information from raw data. You can drag around the different fields within a PivotTable to view its data from different perspectives.

I thought we would look at a really nifty feature of Pivot Tables � the ability to insert a calculated field. For example, if you had a pivot table set up, with an amount displayed in the Data Items area, you may wish to display the GST (tax) component of the Amount Owing so you can see it at a glance.

First you need to create the pivot table. In our list, we have Names in column A and Amount Owing in Column B. We created a pivot table and moved the Names field to the Row area and the Amount Owing to the Data area.

Download Pre Example

With our PivotTable created, it would be nice if we could see at a glance the GST component of each of the amounts owing. We can do this by inserting what is known as a calculated field. To do this easily, select PivotTable on the PivotTable toolbar (if this toolbar is not displayed, go to View>Toolbars and select PivotTable, or right click in the toolbar area of your screen and select PivotTable) then select Formulas>Calculated Field. This will display the Insert Calculated Field dialog box. The first thing we need to do is give our new field a Name. We will call ours GST. Now move to the Formula area and type in the following formula =�Amount Owing�/10, select Add, then Close. This will insert a column to the right of the Sum of Amount Owing with the GST Amount displayed. You can of course use any formula you like in a calculated field, however one important thing to remember about using formulas in calculated fields is that you cannot use Excel formulas that REQUIRE a range reference.

Download Pre Example

Pivot Table Products

O2OLAP for Excel

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft