A calculated filed exists in the memory of Microsoft Excel 2013. You can use it to gain insights into how your business is performing. The field allows you to make independent calculations based on your original data. A calculated field is convenient, especially when you cannot alter the original data source file. In this online video, you'll learn all about creating a calculated field and adding it to your PivotTable.
PivotTables let you analyze the data in your original data source. They also let you add calculated fields that can create calculations based on that data. In this movie, I will show you how to create a calculated field using the Calculated sample file, which you can find in the Chapter02 folder of your Exercise Files archive. This PivotTable has four fields. They are Department, Quarter, Revenue, and Sales, and currently Department, Quarter, and Revenue are displayed within the body of the PivotTable. In this case, the PivotTable is based on data in the same workbook, but for the moment, let's assume that I don't have access to the original data; say it's stored in an external source, or it's a file that I'm not allowed to change for some reason.
If I want to perform a separate calculation on the data, such as by combining values into fields produce another value, then I can do so by adding a calculated field. To do that, I go up to the Analyze contextual tab, and then in the Calculations group, click the Fields, Items, & Sets button, and from list that appears click Calculated Field. Doing so displays the Insert Calculated Field dialog box. Now I can type a name for the field. I'll call it average sale, and then I can click in the Formula box, and Backspace over the 0 to start creating my formula.
In this case, I want to divide my total revenue by the number of sales. To add those fields to my formula, I click the first field, in this case, Revenue and then click the Insert Field button. Then I'll type a forward slash for divide, and then I want to divide by Sales. Click Insert Field again. So, my formula will divide my total Revenue by the number of Sales. I'll click Add to add the calculated field, and you can see that average sale now appears in the field list, and I'll click OK.
When you click OK and create your calculated field, Excel automatically adds it to the body of the PivotTable. So, let me drag the Sum of Revenue field out of the Values area, so we can focus on just the calculated field that I just created. When I do, you can see that the data summary shows the average sales for each department in each of the four quarters that are covered. Most of the data is in the $30-$50 range, but you'll notice that there are some large values. For example, in the Play and Sport departments in the first quarter, the average sale is quite low, whereas for Work, the average sale is quite high.
It's that type of insight that you can get from creating calculated fields. One thing I should point out is that the calculated field that I just created only exists in Excel's memory. It doesn't exist in the original data source. If I click Sheet1, which contains the original data source, you can see that there is no average sale column. Go back to Sheet2. However, Excel does store the new field that I just created inside of the pivot cache, which is the internal representation of the PivotTable.
The ability to create calculated fields means that you can perform all sorts of terrific calculations, and gain insight into your business data.
Author
Released
4/11/2013- Creating a PivotTable
- Summarizing multiple data fields
- Managing subtotals and grand totals
- Grouping PivotTable fields
- Filtering with selections, rules, slicers, and search filters
- Applying PivotTable styles
- Formatting cells
- Creating PivotCharts
- Enabling PowerPivot
- Using DAX operators
- Visualizing data with matrices, cards, and tiles
- Building charts and maps
Skill Level Intermediate
Duration
Views
Related Courses
-
Office 2013 New Features
with David Rivers2h 31m Intermediate -
Managing and Analyzing Data in Excel 2010
with Dennis Taylor1h 32m Intermediate -
Excel 2013: Shortcuts
with Curt Frye3h 16m Intermediate
-
Introduction
-
Welcome58s
-
-
1. Creating and Pivoting PivotTables
-
Introducing PivotTables3m 14s
-
Creating a PivotTable2m 37s
-
Pivoting a PivotTable3m 13s
-
Configuring a PivotTable3m 17s
-
Managing PivotTables3m 48s
-
-
2. Summarizing PivotTable Data
-
Grouping PivotTable fields2m 40s
-
3. Sorting and Filtering PivotTable Data
-
Sorting PivotTable data3m 41s
-
Creating a custom sort order3m 52s
-
Formatting slicers3m 7s
-
-
4. Formatting PivotTables
-
Applying a PivotTable style2m 36s
-
Creating a PivotTable style4m 27s
-
-
5. Applying Conditional Formats to PivotTables
-
6. Creating and Manipulating PivotCharts
-
Creating a PivotChart2m 34s
-
Pivoting a PivotChart2m 24s
-
Filtering a PivotChart2m 48s
-
Formatting a PivotChart3m 38s
-
-
7. Printing PivotTables
-
Printing a PivotTable2m 53s
-
Printing a PivotChart1m 14s
-
-
8. Manipulating PivotTables Using Macros
-
Running an Excel macro4m 4s
-
9. Starting with PowerPivot and the Data Model
-
Introducing PowerPivot4m 32s
-
Importing PowerPivot data3m 24s
-
Managing table columns3m 21s
-
10. Introducing Data Analysis Expressions (DAX)
-
Introducing the DAX language2m 29s
-
Using DAX operators4m 35s
-
Surveying DAX functions2m 35s
-
Adding calculated columns2m 11s
-
Adding calculated fields3m 7s
-
-
11. Visualizing Power View Data Using Matrices, Cards, and Tiles
-
Starting out with Power View2m 16s
-
Creating a card3m 30s
-
Creating a tile3m 18s
-
Filtering Power View objects3m 31s
-
-
12. Visualizing Power View Data Using Charts and Maps
-
Creating a pie chart2m 7s
-
Creating a line chart2m 40s
-
Creating a map2m 31s
-
Creating chart multiples2m 38s
-
Conclusion
-
Next steps54s
-
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.
CancelTake notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: Creating a calculated field