How to add a calculated field to an Excel Pivot Table

2019 3 Tip Friday Top Of Post Images.png

Welcome to Excel 3 tip Friday, your weekly dose of Excel tips and tricks. This week we cover how to add a calculated field to an Excel Pivot Table, shortcut to create a new sheet in Excel workbook and converting a date to text.

Adding a calculated field in a Pivot Table

We can add a field in a Pivot table as a calculation of other fields available in this pivot table.

Let’s consider the following data and Pivot table. We want to see the total revenue per product.

Adding a calculated field in a Pivot Table:

We have the Price and the Quantity sold.

To get the revenue we will multiply the Price by the Quantity sold.

We can, of course, do this in the raw data and then pivot, but this will just be longer and add unnecessary calculations to our sheet.

The pivot table can do this for us very easily:

  • Click on your Pivot table

  • This will add a section PivotTable Tools in your ribbon

image3.jpgAdding a calculated field in a Pivot Table:
  • Under Analyze, click on Calculated Field under Fields, Items, &Sets:

Adding a calculated field in a Pivot Table:
  • In the Insert Calculated Field dialog box that will pop up, first write a Name for the new field (your choice) :

Adding a calculated field in a Pivot Table:
  • In the Formula field, we will add our calculation which is =Quantity sold*Price.

Click on the Formula field, remove the 0 that is already there and double click on the Quantity sold in the Fields section below. You will see that the field will be added to the formula:

Adding a calculated field in a Pivot Table:
  • Now enter the * character in the Formula ( multiply) , then double click on the Price in the Fields section:

Adding a calculated field in a Pivot Table:

This is our formula, click OK and you will see a new field added to our pivot table, calculating the Revenue:

Adding a calculated field in a Pivot Table:

To find out more about this functionality, please follow this link.

Shortcut to create a new sheet in Excel

There is a quick and easy shortcut in Excel to add a new sheet to a workbook.

Press Shift+F11 and a sheet will be added in front of the active sheet in your workbook.

To get a full list of Excel shortcuts, check out the Microsoft help article

Convert a date to text in Excel

The Text function in Excel can be very useful when working with dates.

The function takes two arguments:

Convert a date to text in Excel

value- this is the value or cell that you want to turn to a text value

format_text-this is the desired format that you want the text to have

You might need to use this function to just turn a number to text format, this will give you the text value of that number:

image8.png

We also use this function when we need to display the month of a date as a text for example.

If we want to retrieve the month of date 12/09/2018 in the “mmm” format, we would do the following:

Convert a date to text in Excel

To retrieve the month as the full month name we would do the following:

image10.png

To retrieve the day of the week in the “ddd” format:

Convert a date to text in Excel

To retrieve the full day of the week name:

Convert a date to text in Excel

If we just want to turn the date into a text format:

Convert a date to text in Excel

We can turn the date to a text string and change how it is displayed:

Convert a date to text in Excel

Get More Exclusive Content

Sent Directly to your inbox with Excel 3 Tip Friday. A weekly dose of Functions shortcuts and tips for getting more out of Excel.

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit