In the following example, you will use the Clayton High School Track and Field club’s event record for the past six months. This rules out functions like COUNT, AVERAGE, IF, AND, NOT, and OR. Furthermore, you cannot use any functions that require cell references or defined names as an argument. This limits you from using a lot of functions. A calculated field is always performed against the SUM of the data. Limitation of the calculated fields in the pivot table when calculating a percentageĬalculated fields in pivot table have some limitations. In the Formula field, insert the formula =Profit/Sales by clicking on the Insert Field button from the Fields box.In the Insert Calculated Field dialog box,.Go to (Pivot Table Tools) Analyze > Fields, Items, & Sets > Calculated Field.For this example, we will use the sales and profit data for the eleven items during the 4 th quarter of the year.įrom this, we have the pivot table Sum of Sales and Profits for the Items. This would show the sales for each item as the percentage of total monthly sales.Ĭreate the calculated field in the pivot tableĪ calculated field is a column generated by the data in the pivot table. In the Value Field Settings window, on the Show Values As tab, choose % of Column Total.Click on pivot builder the entry Sum of Sales and select Value Field Settings.To calculate % of Sales for each month, you need to do the following: You will have the pivot table with the Sales for the Items for each Month. Scroll down and select the one that says Sum of Sales by Items and Month. Go to Insert > Recommended PivotTables.

To create the Pivot Table and apply conditional formatting, you need to perform the following steps: In this example, you have the beverage sales data of eleven items for the 3rd quarter of the year. Calculating percentage in the pivot table Though it has some limitations, calculated fields are a great way to find new insights, such as percentages, from pivot tables. You can create calculated fields in a pivot table that help expand your analysis with more data. You can create and modify pivot tables very quickly. They help you to aggregate, summarize, finding insights and presenting a large amount of data in just a few clicks, including calculating a percentage from given data. The Procedure for Calculating a Percentage in a Pivot TableĪre you struggling with calculating a percentage in a pivot table? Pivot tables in Excel are excellent tools for analyzing data.

Adding percentages calculator