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.
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
Under Analyze, click on Calculated Field under Fields, Items, &Sets:
In the Insert Calculated Field dialog box that will pop up, first write a Name for the new field (your choice) :
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:
Now enter the * character in the Formula ( multiply) , then double click on the Price in the Fields section:
This is our formula, click OK and you will see a new field added to our pivot table, calculating the Revenue:
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.
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:
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:
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:
To retrieve the month as the full month name we would do the following:
To retrieve the day of the week in the “ddd” format:
To retrieve the full day of the week name:
If we just want to turn the date into a text format:
We can turn the date to a text string and change how it is displayed: