Add Calculated Subject to Pivot Desk: A Complete Information
Hey readers,
Are you wanting so as to add extra informative knowledge to your pivot tables? A calculated subject is a robust instrument that lets you create new knowledge fields based mostly on present ones. On this article, we’ll information you thru the method of including a calculated subject to your pivot desk in easy, easy-to-follow steps.
Understanding Calculated Fields
Calculated fields are expressions that you need to use to create new columns of information based mostly on present knowledge in your pivot desk. These fields can be utilized to carry out calculations, akin to including, subtracting, multiplying, or dividing values, or to create extra complicated calculations utilizing features.
Making a Calculated Subject
To create a calculated subject, observe these steps:
- Choose the Pivot Desk: Click on on the pivot desk the place you wish to add the calculated subject.
- Go to the "Analyze" Tab: Within the ribbon on the high of the Excel window, click on on the "Analyze" tab.
- Click on "Fields, Gadgets, & Units": Within the "Calculations" group, click on on "Fields, Gadgets, & Units".
- Choose "Calculated Subject": Within the "Calculated Subject" dialog field, enter a reputation to your new subject and a formulation for the calculation.
- Click on "OK": Click on "OK" so as to add the calculated subject to your pivot desk.
Utilizing Calculated Fields
As soon as you have created a calculated subject, you need to use it in your pivot desk similar to some other knowledge subject. Merely drag and drop the sector into the "Rows", "Columns", or "Values" space of the pivot desk.
Superior Calculated Fields
Along with easy calculations, you may also use extra complicated features to create calculated fields. Listed here are some examples:
- DATE: Returns the date part of a cell worth.
- SUMIFS: Provides values throughout a number of columns or rows based mostly on specified standards.
- CONCATENATE: Combines a number of textual content strings right into a single string.
Pivoting on Calculated Fields
You can too pivot on calculated fields to create new pivot tables that group and summarize knowledge based mostly on the calculated values. This may be helpful for creating extra detailed or custom-made experiences.
Desk Breakdown: Calculated Fields
| Function | Description |
|---|---|
| Identify | The title of the calculated subject. |
| Components | The formulation that defines the calculation. |
| Information Sort | The info kind of the calculated subject. |
| Utilization | How the calculated subject can be utilized within the pivot desk. |
Conclusion
Including calculated fields to pivot tables is a robust strategy to improve your knowledge evaluation and reporting. By following the steps outlined on this article, you may simply create custom-made fields that present invaluable insights into your knowledge.
For extra suggestions and tutorials on utilizing pivot tables and Excel, you’ll want to try our different articles.
FAQ about Including Calculated Fields to Pivot Tables
Q1: What’s a calculated subject?
A: A calculated subject is a brand new subject you create in a PivotTable that performs calculations on present knowledge.
Q2: Why would I would like so as to add a calculated subject to a PivotTable?
A: Calculated fields will let you add further insights and views to your knowledge evaluation, akin to calculating percentages, ratios, or operating totals.
Q3: How do I add a calculated subject to a PivotTable?
A: So as to add a calculated subject, go to the "Analyze" tab within the PivotTable Instruments group, then click on "Fields, Gadgets, & Units" > "Calculated Subject".
This autumn: What’s the syntax for making a calculated subject?
A: The syntax is:
=FieldName1 [Operator] FieldName2 [Mathematical Operation]
For instance: =SalesRevenue / TotalSales
Q5: Can I take advantage of features in calculated fields?
A: Sure, you need to use Excel features akin to SUM(), AVERAGE(), and MIN(). For instance: =SUM(SalesRevenue)
Q6: Can I add a number of calculated fields to a PivotTable?
A: Sure, you may add as many calculated fields as you want.
Q7: How do I format calculated fields?
A: You possibly can format calculated fields similar to some other subject in a PivotTable. Choose the sector, go to the "House" tab, and use the formatting choices.
Q8: Can I edit or delete calculated fields?
A: Sure, you may edit or delete calculated fields by right-clicking on them and deciding on the suitable possibility.
Q9: Why is my calculated subject displaying an error?
A: Errors can happen as a consequence of invalid syntax, round references, or incorrect knowledge varieties. Examine your syntax and knowledge fastidiously.
Q10: Can I share calculated fields with others?
A: Sure, while you share the PivotTable with others, the calculated fields can be included.
