The steps to add calculated fields in Excel pivot table are as follows: 1. Find the "Fields, Projects, and Sets" functional area, click "Analysis" or "Pivot Table Analysis" in the top menu bar with the Pivot Table selected, and then find and click the "Fields, Projects, and Sets" button; 2. Select "Calculate Fields", enter a name (such as "Profit Profit") and formula (such as ='Profit'/'Sales'), note that the field name must be enclosed in English single quotes; 3. Click "Add" to confirm, after completion, drag the new field to the "Value" area, and right-click to adjust the value format to ensure the display is correct. If the result is abnormal, check whether the formula logic or field name is accurate.

Adding calculated fields in Excel Pivot Table allows you to customize calculations based on existing fields. For example, you can use it to calculate profit margins, unit prices, or do other operations that conform to business logic. The operation is not complicated, but you need to pay attention to formula writing and data accuracy.

Find the Fields, Projects, and Sets ribbon

To add a calculated field, first find the correct entry. With the Pivot Table selected, click Excel for Windows or Pivot Table Analysis (Mac) in the top menu bar and find the Fields, Projects, and Sets button.

After clicking, a window will pop up with options for "calculate fields" and "calculate items". What we want is "calculate fields".

Tip: If you don’t see this button, confirm whether you have actually selected the Pivot Table. Sometimes you can’t find the wrong area.

Set your calculated fields

After clicking "Calculate Field", the settings window will appear. Here are three key inputs:

• Name : Give this new field a meaningful name, such as "profit rate" or "unit price".
• Formula : Here you need to use the field name to perform calculations. For example, if you want to calculate the profit margin, the formula may be =利潤/銷售額. Note that the field names must be in existing fields and must be enclosed in English brackets, like this: ='銷售額'-'成本'
• Add & OK : After setting, click "Add" first, then click "OK".

Notice:

• Do not directly refer to cell addresses, such as A1 or B2, as this is invalid in the pivot table.
• It is best to wrap the field names in the formula in single quotation marks, especially when there are spaces in the field names.

Check the results and adjust the format

After the addition is completed, the new field will appear in the "Field List". Drag it to the "Value" area to see the calculation results.

However, the values ??may not be very intuitive at this time, such as the profit margin is displayed as a decimal rather than a percentage, or the appropriate number of digits is not retained. you can:

• Right-click the value in the pivot table → select "Value Field Settings" → Set the number format
• Or right-click the field directly and adjust it in "number format"

If you find that the value is wrong, it is likely that the formula has written the field name incorrectly, or there is a logic problem. For example, using the wrong divisor, missing quotes, etc.

Basically these steps. The whole process is not difficult, but pay attention to the correctness of the field name and the formula. As long as the formula is correct, the data will naturally be accurate.

The above is the detailed content of how to add a calculated field in an excel pivot table. For more information, please follow other related articles on the PHP Chinese website!