Pivot tables are highly effective instruments in Microsoft Excel that let you summarize and analyze massive datasets. One of the vital helpful options of pivot tables is the flexibility so as to add calculated fields. Calculated fields are new fields that you would be able to create based mostly on present fields within the pivot desk. This lets you carry out calculations in your information with out having to create a brand new components in every cell.
Calculated fields can be utilized for quite a lot of functions, equivalent to:
- Including totals or subtotals
- Calculating percentages or ratios
- Combining information from completely different fields
- Creating new fields based mostly on present fields
On this article, we are going to present you add a calculated subject to a pivot desk. We may also present some examples of how calculated fields can be utilized to enhance your information evaluation.
Including Calculated Discipline in Pivot Desk
Calculated fields improve information evaluation and supply invaluable insights.
- Create new fields
- Carry out calculations
- Add totals/subtotals
- Calculate percentages
- Mix information fields
- Enhance information evaluation
- Simplify advanced formulation
- Improve pivot desk experiences
Unlock the facility of calculated fields to remodel your information evaluation.
Create new fields
One of the vital helpful options of calculated fields is the flexibility to create new fields that don’t exist within the unique information supply. This may be achieved by combining information from a number of fields, performing calculations, or extracting particular elements of a subject.
For instance, as an example you might have a pivot desk that reveals gross sales information for various merchandise and areas. You possibly can create a brand new subject that reveals the whole gross sales for every product class through the use of the next components:
=SUM(Gross sales)
You possibly can additionally create a brand new subject that reveals the typical gross sales worth for every product through the use of the next components:
=AVERAGE(Gross sales) / COUNTROWS(Product)
Or, you can create a brand new subject that reveals the share of gross sales for every product class through the use of the next components:
=SUM(Gross sales) / SUM(Complete Gross sales)
These are only a few examples of how you need to use calculated fields to create new fields that present invaluable insights into your information.
With calculated fields, you aren’t restricted to the info that’s already obtainable in your information supply. You possibly can create new fields which are tailor-made to your particular wants and necessities.
Carry out calculations
Calculated fields can be used to carry out calculations in your information. This may be helpful for duties equivalent to:
-
Calculating totals and subtotals
You should use calculated fields so as to add totals and subtotals to your pivot desk. This will help you to shortly and simply see the general efficiency of what you are promoting or group.
-
Calculating percentages and ratios
You should use calculated fields to calculate percentages and ratios. This may be helpful for evaluating completely different information units or for figuring out developments.
-
Calculating operating totals
You should use calculated fields to calculate operating totals. This may be helpful for monitoring the progress of a challenge or marketing campaign.
-
Calculating variations between values
You should use calculated fields to calculate the distinction between two values. This may be helpful for figuring out outliers or for monitoring modifications over time.
These are only a few examples of the various calculations that you would be able to carry out utilizing calculated fields. With calculated fields, you might be solely restricted by your creativeness.
Add totals/subtotals
One of the vital frequent makes use of of calculated fields is so as to add totals and subtotals to a pivot desk. This will help you to shortly and simply see the general efficiency of what you are promoting or group.
-
Add a grand complete
So as to add a grand complete to your pivot desk, merely drag the sector that you just wish to summarize to the Values space. The grand complete might be displayed on the backside of the pivot desk.
-
Add subtotals
So as to add subtotals to your pivot desk, drag the sector that you just wish to group by to the Rows space or the Columns space. The subtotals might be displayed for every group.
-
Add a number of ranges of subtotals
You possibly can add a number of ranges of subtotals to your pivot desk by dragging a number of fields to the Rows space or the Columns space.
-
Customise the best way that totals and subtotals are calculated
You possibly can customise the best way that totals and subtotals are calculated through the use of the Summarize Values By dialog field. To open the Summarize Values By dialog field, right-click on the sector that you just wish to summarize and choose Summarize Values By.
Including totals and subtotals to a pivot desk is a good way to shortly and simply summarize your information. With calculated fields, you’ll be able to add totals and subtotals to any subject in your pivot desk, no matter whether or not or not it’s already displayed within the pivot desk.
Calculate percentages
Calculated fields can be used to calculate percentages. This may be helpful for evaluating completely different information units or for figuring out developments. For instance, you can create a calculated subject that reveals the share of gross sales for every product class through the use of the next components:
=SUM(Gross sales) / SUM(Complete Gross sales)
You possibly can additionally create a calculated subject that reveals the share of shoppers who’ve made a purchase order within the final month through the use of the next components:
=COUNT(Clients Who Bought In Final Month) / COUNT(Complete Clients)
Or, you can create a calculated subject that reveals the share of gross sales that had been made by the net retailer through the use of the next components:
=SUM(On-line Gross sales) / SUM(Complete Gross sales)
These are only a few examples of how you need to use calculated fields to calculate percentages. With calculated fields, you’ll be able to calculate percentages for any subject in your pivot desk, no matter whether or not or not it’s already displayed within the pivot desk.
Calculating percentages is a good way to check completely different information units and establish developments. With calculated fields, you’ll be able to simply calculate percentages for any subject in your pivot desk, with out having to create a brand new components in every cell.
Mix information fields
Calculated fields can be used to mix information from a number of fields. This may be helpful for creating new fields that present extra insights into your information. For instance, you can create a calculated subject that reveals the complete title of a buyer by combining the primary title and final title fields.
-
Mix textual content fields
You should use calculated fields to mix textual content fields from a number of columns right into a single subject. For instance, you can create a calculated subject that reveals the complete title of a buyer by combining the primary title and final title fields.
-
Mix numeric fields
You should use calculated fields to mix numeric fields from a number of columns right into a single subject. For instance, you can create a calculated subject that reveals the whole gross sales for every product by combining the gross sales figures from a number of product classes.
-
Mix date fields
You should use calculated fields to mix date fields from a number of columns right into a single subject. For instance, you can create a calculated subject that reveals the beginning and finish dates of a challenge by combining the beginning date and finish date fields.
-
Mix different information sorts
You can even use calculated fields to mix information fields of various information sorts. For instance, you can create a calculated subject that reveals the standing of a buyer order by combining the order date and the transport date.
Combining information fields is a good way to create new fields that present extra insights into your information. With calculated fields, you’ll be able to mix information from any subject in your pivot desk, no matter whether or not or not it’s already displayed within the pivot desk.
Enhance information evaluation
Calculated fields can be utilized to enhance information evaluation in a lot of methods. For instance, calculated fields can be utilized to:
-
Determine developments and patterns
Calculated fields can be utilized to establish developments and patterns in your information. For instance, you can create a calculated subject that reveals the typical gross sales for every product over time. This is able to let you see how gross sales are trending and establish merchandise which are performing effectively or poorly.
-
Examine completely different information units
Calculated fields can be utilized to check completely different information units. For instance, you can create a calculated subject that reveals the gross sales for every product in two completely different areas. This is able to let you see which merchandise are promoting effectively in every area and establish alternatives for development.
-
Create new insights
Calculated fields can be utilized to create new insights into your information. For instance, you can create a calculated subject that reveals the profitability of every product. This is able to let you see which merchandise are most worthwhile and establish alternatives to extend profitability.
-
Make higher selections
Calculated fields can be utilized to make higher selections. For instance, you can create a calculated subject that reveals the ROI of every advertising marketing campaign. This is able to let you see which advertising campaigns are handiest and establish alternatives to enhance your advertising ROI.
These are only a few examples of how calculated fields can be utilized to enhance information evaluation. With calculated fields, you’ll be able to achieve new insights into your information and make higher selections.
Simplify advanced formulation
Calculated fields can be used to simplify advanced formulation. For instance, as an example you might have a components that calculates the typical gross sales for every product over time. This components may look one thing like this:
=AVERAGE(Gross sales) / COUNT(Dates)
In case you needed to make use of this components in a pivot desk, you would need to create a brand new calculated subject for every product. This might be loads of work, particularly when you’ve got loads of merchandise.
Nonetheless, you need to use a calculated subject to simplify this course of. You possibly can create a single calculated subject that calculates the typical gross sales for all merchandise. This components would look one thing like this:
=AVERAGE(Gross sales) / COUNTROWS(Merchandise)
You possibly can then use this calculated subject in your pivot desk to see the typical gross sales for every product. That is a lot simpler than creating a brand new calculated subject for every product.
Calculated fields can be used to simplify formulation that use a number of standards. For instance, as an example you might have a components that calculates the whole gross sales for merchandise which are bought in a selected area and which have a worth larger than $100. This components may look one thing like this:
=SUMIF(Area, “North America”, Gross sales)
In case you needed to make use of this components in a pivot desk, you would need to create a brand new calculated subject for every area. This might be loads of work, particularly when you’ve got loads of areas.
Nonetheless, you need to use a calculated subject to simplify this course of. You possibly can create a single calculated subject that calculates the whole gross sales for all merchandise which are bought in a selected area and which have a worth larger than $100. This components would look one thing like this:
=SUMIF(AND(Area = “North America”, Worth > 100), Gross sales)
You possibly can then use this calculated subject in your pivot desk to see the whole gross sales for every area and product worth vary.
Calculated fields are a robust software that can be utilized to simplify advanced formulation and make information evaluation simpler.
Improve pivot desk experiences
Calculated fields can be utilized to boost pivot desk experiences in a lot of methods. For instance, calculated fields can be utilized to:
-
Add new insights
Calculated fields can be utilized so as to add new insights to pivot desk experiences. For instance, you can create a calculated subject that reveals the profitability of every product. This is able to let you see which merchandise are most worthwhile and establish alternatives to extend profitability.
-
Enhance information visualization
Calculated fields can be utilized to enhance the info visualization in pivot desk experiences. For instance, you can create a calculated subject that reveals the gross sales for every product as a share of complete gross sales. This is able to let you see which merchandise are contributing essentially the most to gross sales.
-
Make experiences extra interactive
Calculated fields can be utilized to make pivot desk experiences extra interactive. For instance, you can create a calculated subject that permits customers to filter the info by product class. This is able to enable customers to see the info for a selected product class with out having to create a brand new pivot desk.
-
Simplify advanced experiences
Calculated fields can be utilized to simplify advanced pivot desk experiences. For instance, you can create a calculated subject that summarizes the info for a number of merchandise. This is able to let you see the general efficiency of a gaggle of merchandise with out having to have a look at the info for every product individually.
These are only a few examples of how calculated fields can be utilized to boost pivot desk experiences. With calculated fields, you’ll be able to create extra informative, visually interesting, and interactive experiences.
FAQ
This part supplies solutions to incessantly requested questions on utilizing calculated fields in pivot tables.
Query 1: What are calculated fields?
Calculated fields let you create new fields in a pivot desk based mostly on present fields. This lets you carry out calculations, mix information, and create new insights with out having to create a brand new components in every cell.
Query 2: How do I create a calculated subject?
To create a calculated subject, click on on the “Analyze” tab within the ribbon after which click on on the “Fields, Gadgets, & Units” group. Then, click on on the “Calculated Discipline” button. Within the dialog field that seems, enter a reputation for the sector after which enter the components that you just wish to use to calculate the sector.
Query 3: What sorts of calculations can I carry out utilizing calculated fields?
You should use calculated fields to carry out quite a lot of calculations, together with:
- Including totals and subtotals
- Calculating percentages and ratios
- Combining information from completely different fields
- Creating new fields based mostly on present fields
Query 4: Can I take advantage of calculated fields in a number of pivot tables?
Sure, you need to use calculated fields in a number of pivot tables. Nonetheless, the calculated subject should be outlined within the information supply for every pivot desk.
Query 5: How can I enhance the efficiency of my pivot desk when utilizing calculated fields?
There are some things you are able to do to enhance the efficiency of your pivot desk when utilizing calculated fields:
- Use environment friendly formulation
- Keep away from utilizing unstable capabilities
- Use a knowledge mannequin
Query 6: The place can I be taught extra about calculated fields?
There are various assets obtainable on-line that may train you extra about calculated fields. Some good locations to start out embody the Microsoft Assist web site and the Excel Assist Middle.
Query 7: Can I take advantage of calculated fields in different spreadsheet applications?
Sure, calculated fields are additionally obtainable in different spreadsheet applications equivalent to Google Sheets and LibreOffice Calc.
Concluding paragraph:
Calculated fields are a robust software that can be utilized to enhance the evaluation and presentation of information in pivot tables. By understanding use calculated fields, you’ll be able to create extra informative and insightful pivot desk experiences.
Along with utilizing calculated fields, there are a selection of different suggestions that you need to use to enhance your pivot desk experiences. The following tips might be mentioned within the subsequent part.
Ideas
Listed here are 4 sensible suggestions for utilizing calculated fields in pivot tables:
Tip 1: Use significant names in your calculated fields
While you create a calculated subject, it is very important give it a significant title. This may make it simpler to establish the sector within the PivotTable Fields pane and within the components bar.
Tip 2: Use environment friendly formulation
When creating calculated fields, it is very important use environment friendly formulation. This may assist to enhance the efficiency of your pivot desk. Some suggestions for writing environment friendly formulation embody:
- Use the shortest components potential.
- Keep away from utilizing nested formulation.
- Use vary references as a substitute of particular person cell references.
Tip 3: Keep away from utilizing unstable capabilities
Unstable capabilities are capabilities that may change their worth even when the underlying information has not modified. This will trigger your pivot desk to recalculate unnecessarily, which might decelerate efficiency. Some frequent unstable capabilities embody:
- TODAY()
- NOW()
- RAND()
Tip 4: Use a knowledge mannequin
If you’re working with a big information set, you’ll be able to enhance the efficiency of your pivot desk through the use of a knowledge mannequin. An information mannequin is a group of tables which are associated to one another. Through the use of a knowledge mannequin, you’ll be able to create pivot tables which are based mostly on a number of tables with out having to create a number of copies of the info.
Closing paragraph:
By following the following tips, you need to use calculated fields to create extra informative and insightful pivot desk experiences.
Calculated fields are a robust software that can be utilized to enhance the evaluation and presentation of information in pivot tables. By understanding use calculated fields and by following the guidelines on this article, you’ll be able to create pivot desk experiences which are each informative and insightful.
Conclusion
Calculated fields are a robust software that can be utilized to enhance the evaluation and presentation of information in pivot tables. By understanding use calculated fields, you’ll be able to create pivot desk experiences which are extra informative, visually interesting, and interactive.
On this article, we’ve got mentioned the next details:
- What are calculated fields and create them
- Tips on how to use calculated fields to carry out calculations, mix information, and create new insights
- Tips on how to improve pivot desk experiences utilizing calculated fields
- Ideas for utilizing calculated fields successfully
By following the guidelines and recommendation on this article, you need to use calculated fields to create pivot desk experiences which are each informative and insightful.
Closing Message:
Calculated fields are a invaluable software for anybody who desires to get essentially the most out of pivot tables. By taking the time to learn to use calculated fields, you’ll be able to create pivot desk experiences which are extra informative, visually interesting, and interactive.
