The Value Field Settings dialog box is displayed.. Let's take a look at how to include the Grand Total as a dynamic text feature in a pivot chart, as seen above. The first thing we want to do is make sure that the Grand Totals option and the Get Pivot Data option are both turned on for our pivot table. =IF(Units>100,Total*4%,0) Click Modify to save the calculated field changes, then click Close. Also change the Custom Name into Percent of Grand Total to make it more presentable. In this technique, you'll create another copy of the pivot table, then hide columns in both pivot tables, so simulate grand totals at the left of the data. In Excel 2007 or 2010, click the "Change Source Data" button on the Pivot Tools Options tab. STEP 1: Insert a Pivot able by clicking on your data and going to Insert > Pivot Table > New Worksheet or Existing Worksheet STEP 2: In the ROWS you have to put the Months field, in the COLUMNS the Years field and in the VALUES area the Sales field twice, I explain why below: STEP 3: Now click on the second Sales field’s (Sum of SALES2) drop down and choose Value Field Settings For the first part: Go to the Field List of your Pivot Table; Under Values Click the drop-down you have there and Click Value Field Settings; Once here, select Show Values As then select % of Grand Total; This will show how much each person spends on each task. However, depending on your needs, you may want to turn these on or off. Those instructions are for Excel 2003 - if it's Excel 2007 then the menus may be a little different, just try searching for pivot calculated field and remove grand totals. Alter the source data for your pivot table. When you create a new pivot table, you’ll see grand totals displayed below the table and to the right of the table. The Source Name is the name of the field in the data source.. This Year and Last Year) side by side within the pivot table. Right click the Total label cell, and choose Field Settings from the context menu, see screenshot:. There are two ways to manage grand totals. Select any cell in the pivot table. The Custom Name displays the current name in the PivotTable report, or the source name if there is no custom name. STEP 5: Notice that the Percent of Grand Total data is in a decimal format that is hard to read: To format the Percent of Grand Total column, click the second Sales field’s (Percent of Grand Total) drop down and choose Value Field Settings. If I us the show value as option, I lose the data in the columns and I would like to keep that. And if you use pivot tables, get my free pivot table add-in too! 6.After showing the subtotals at bottom in the pivot table, you can select multiple function for your subtotals. But the existing Pivot Table is not effective in calculating the variance between the 2 periods. You can place and compare the numbers by categories between 2 periods (e.g. use Slicers to filter the pivot table ; do not need to change the pivot table layout after setting it up. To change the Custom Name, click the text in the box and edit the name. Click OK. Go to my Contextures website, to get the sample file for this example, and more information on pivot table calculated fields. On the Analyze tab, in the Active Field group, click Active Field, and then click Field Settings.. Pivot Table is a great tool to group data into major categories for reporting. To set up the grand totals at the left: Is it possible to change the grand total in a pivot table to be a difference of the columns vs a total? More on Calculated Fields. Grand Totals Feature. In Excel 2003, relaunch the pivot table wizard utility by clicking inside the pivot table and choosing "Wizard" from the pop-up menu. Column grand totals appear in the last row of the table, and row grand totals appear in the last column of the table. Set Up the Pivot Table . Also, to get rid of the hokey Grand Total, right-click the pivot table and under Pivot Table options you can remove grand totals for rows or for columns or both.