Here's how to create PivotTables with calculated fields based on other fields in Excel:

1. Prepare your data:

- Ensure your data is organized in a table format. This allows Excel to recognize relationships between columns.

2. Create a PivotTable:

- Select your data range.
- Go to the "Insert" tab and click "PivotTable."
- Choose where you want to place the PivotTable (new worksheet or existing one).

3. Add fields to the PivotTable:

- The "PivotTable Fields" pane will appear on the right side.
- Drag and drop the desired fields to the "Rows," "Columns," "Values," or "Filters" sections of the PivotTable layout.

4. Create a Calculated Field:

- Click on any cell within the PivotTable. This activates the PivotTable Analyze tab.
- In the "Calculations" group, click "Fields, Items, & Sets."
- Select "Calculated Field" from the dropdown menu.

5. Define the Calculation:

- A "Create Calculated Field" window will appear.
- Enter a clear and descriptive name for your calculated field in the "Name" box.
- In the "Formula" box, type your calculation using existing fields from the PivotTable. You can use basic arithmetic operators (+, -, *, /), comparison operators (> ,< , =), and logical operators (AND, OR).
- To reference existing fields, click on the field name in the "Fields" list or double-click it to insert it directly into the formula box.
- Example: If you have a "Sales" field and a "Quantity" field, you can create a "Total Revenue" calculated field with the formula: `[Sales] * [Quantity]`.

6. Click "Add" and then "OK."

Your newly created calculated field will now be available in the "Fields" list. You can drag and drop it to the desired section of your PivotTable layout (e.g., "Values") to display the calculated results alongside your existing data.

Tips:

- You can create multiple calculated fields based on different formulas and existing fields.
- Use clear and descriptive names for your calculated fields to improve readability.
- You can edit or delete existing calculated fields by right-clicking on them in the "Fields" list and selecting the appropriate option.

By following these steps, you can leverage the power of calculated fields to perform complex calculations and gain deeper insights from your data within PivotTables.