Pivot tables are highly effective instruments in Microsoft Excel that can help you summarize and analyze massive datasets. Some of the helpful options of pivot tables is the power so as to add calculated fields. Calculated fields are new fields which you can create based mostly on current 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, resembling:
- Including totals or subtotals
- Calculating percentages or ratios
- Combining information from completely different fields
- Creating new fields based mostly on current fields
On this article, we’ll present you how one can add a calculated area to a pivot desk. We may even present some examples of how calculated fields can be utilized to enhance your information evaluation.
Including Calculated Area in Pivot Desk
Calculated fields improve information evaluation and supply priceless insights.
- Create new fields
- Carry out calculations
- Add totals/subtotals
- Calculate percentages
- Mix information fields
- Enhance information evaluation
- Simplify complicated formulation
- Improve pivot desk reviews
Unlock the facility of calculated fields to remodel your information evaluation.
Create new fields
Some of the helpful options of calculated fields is the power to create new fields that don’t exist within the unique information supply. This may be executed by combining information from a number of fields, performing calculations, or extracting particular components of a area.
For instance, as an instance you’ve gotten a pivot desk that reveals gross sales information for various merchandise and areas. You can create a brand new area that reveals the overall gross sales for every product class by utilizing the next components:
=SUM(Gross sales)
You can additionally create a brand new area that reveals the typical gross sales worth for every product by utilizing the next components:
=AVERAGE(Gross sales) / COUNTROWS(Product)
Or, you may create a brand new area that reveals the share of gross sales for every product class by utilizing the next components:
=SUM(Gross sales) / SUM(Complete Gross sales)
These are only a few examples of how you should utilize calculated fields to create new fields that present priceless insights into your information.
With calculated fields, you aren’t restricted to the info that’s already out there in your information supply. You’ll be able to create new fields which can be 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 resembling:
-
Calculating totals and subtotals
You need to use calculated fields so as to add totals and subtotals to your pivot desk. This may also help you to rapidly and simply see the general efficiency of your enterprise or group.
-
Calculating percentages and ratios
You need to use calculated fields to calculate percentages and ratios. This may be helpful for evaluating completely different information units or for figuring out tendencies.
-
Calculating operating totals
You need to use calculated fields to calculate operating totals. This may be helpful for monitoring the progress of a mission or marketing campaign.
-
Calculating variations between values
You need to 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 which you can carry out utilizing calculated fields. With calculated fields, you’re solely restricted by your creativeness.
Add totals/subtotals
Some of the frequent makes use of of calculated fields is so as to add totals and subtotals to a pivot desk. This may also help you to rapidly and simply see the general efficiency of your enterprise or group.
-
Add a grand complete
So as to add a grand complete to your pivot desk, merely drag the sector that you simply need to summarize to the Values space. The grand complete shall 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 simply need to group by to the Rows space or the Columns space. The subtotals shall be displayed for every group.
-
Add a number of ranges of subtotals
You’ll be able to 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’ll be able to customise the best way that totals and subtotals are calculated by utilizing the Summarize Values By dialog field. To open the Summarize Values By dialog field, right-click on the sector that you simply need to summarize and choose Summarize Values By.
Including totals and subtotals to a pivot desk is a good way to rapidly and simply summarize your information. With calculated fields, you possibly can add totals and subtotals to any area 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 tendencies. For instance, you may create a calculated area that reveals the share of gross sales for every product class by utilizing the next components:
=SUM(Gross sales) / SUM(Complete Gross sales)
You can additionally create a calculated area that reveals the share of shoppers who’ve made a purchase order within the final month by utilizing the next components:
=COUNT(Prospects Who Bought In Final Month) / COUNT(Complete Prospects)
Or, you may create a calculated area that reveals the share of gross sales that have been made via the web retailer by utilizing the next components:
=SUM(On-line Gross sales) / SUM(Complete Gross sales)
These are only a few examples of how you should utilize calculated fields to calculate percentages. With calculated fields, you possibly can calculate percentages for any area 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 match completely different information units and determine tendencies. With calculated fields, you possibly can simply calculate percentages for any area 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 may create a calculated area that reveals the complete title of a buyer by combining the primary title and final title fields.
-
Mix textual content fields
You need to use calculated fields to mix textual content fields from a number of columns right into a single area. For instance, you may create a calculated area that reveals the complete title of a buyer by combining the primary title and final title fields.
-
Mix numeric fields
You need to use calculated fields to mix numeric fields from a number of columns right into a single area. For instance, you may create a calculated area that reveals the overall gross sales for every product by combining the gross sales figures from a number of product classes.
-
Mix date fields
You need to use calculated fields to mix date fields from a number of columns right into a single area. For instance, you may create a calculated area that reveals the beginning and finish dates of a mission by combining the beginning date and finish date fields.
-
Mix different information varieties
It’s also possible to use calculated fields to mix information fields of various information varieties. For instance, you may create a calculated area 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 possibly can mix information from any area 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:
-
Establish tendencies and patterns
Calculated fields can be utilized to determine tendencies and patterns in your information. For instance, you may create a calculated area that reveals the typical gross sales for every product over time. This may can help you see how gross sales are trending and determine merchandise which can be performing effectively or poorly.
-
Evaluate completely different information units
Calculated fields can be utilized to match completely different information units. For instance, you may create a calculated area that reveals the gross sales for every product in two completely different areas. This may can help you see which merchandise are promoting effectively in every area and determine alternatives for development.
-
Create new insights
Calculated fields can be utilized to create new insights into your information. For instance, you may create a calculated area that reveals the profitability of every product. This may can help you see which merchandise are most worthwhile and determine alternatives to extend profitability.
-
Make higher selections
Calculated fields can be utilized to make higher selections. For instance, you may create a calculated area that reveals the ROI of every advertising marketing campaign. This may can help you see which advertising campaigns are best and determine 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 possibly can acquire new insights into your information and make higher selections.
Simplify complicated formulation
Calculated fields can be used to simplify complicated formulation. For instance, as an instance you’ve gotten a components that calculates the typical gross sales for every product over time. This components would possibly look one thing like this:
=AVERAGE(Gross sales) / COUNT(Dates)
In the event you needed to make use of this components in a pivot desk, you would need to create a brand new calculated area for every product. This might be a variety of work, particularly you probably have a variety of merchandise.
Nevertheless, you should utilize a calculated area to simplify this course of. You’ll be able to create a single calculated area that calculates the typical gross sales for all merchandise. This components would look one thing like this:
=AVERAGE(Gross sales) / COUNTROWS(Merchandise)
You’ll be able to then use this calculated area in your pivot desk to see the typical gross sales for every product. That is a lot simpler than creating a brand new calculated area for every product.
Calculated fields can be used to simplify formulation that use a number of standards. For instance, as an instance you’ve gotten a components that calculates the overall gross sales for merchandise which can be offered in a selected area and which have a worth higher than $100. This components would possibly look one thing like this:
=SUMIF(Area, “North America”, Gross sales)
In the event you needed to make use of this components in a pivot desk, you would need to create a brand new calculated area for every area. This might be a variety of work, particularly you probably have a variety of areas.
Nevertheless, you should utilize a calculated area to simplify this course of. You’ll be able to create a single calculated area that calculates the overall gross sales for all merchandise which can be offered in a selected area and which have a worth higher than $100. This components would look one thing like this:
=SUMIF(AND(Area = “North America”, Value > 100), Gross sales)
You’ll be able to then use this calculated area in your pivot desk to see the overall gross sales for every area and product worth vary.
Calculated fields are a strong device that can be utilized to simplify complicated formulation and make information evaluation simpler.
Improve pivot desk reviews
Calculated fields can be utilized to reinforce pivot desk reviews 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 reviews. For instance, you may create a calculated area that reveals the profitability of every product. This may can help you see which merchandise are most worthwhile and determine alternatives to extend profitability.
-
Enhance information visualization
Calculated fields can be utilized to enhance the info visualization in pivot desk reviews. For instance, you may create a calculated area that reveals the gross sales for every product as a share of complete gross sales. This may can help you see which merchandise are contributing essentially the most to gross sales.
-
Make reviews extra interactive
Calculated fields can be utilized to make pivot desk reviews extra interactive. For instance, you may create a calculated area that permits customers to filter the info by product class. This may permit customers to see the info for a selected product class with out having to create a brand new pivot desk.
-
Simplify complicated reviews
Calculated fields can be utilized to simplify complicated pivot desk reviews. For instance, you may create a calculated area that summarizes the info for a number of merchandise. This may can help you see the general efficiency of a bunch 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 reinforce pivot desk reviews. With calculated fields, you possibly can create extra informative, visually interesting, and interactive reviews.
FAQ
This part offers solutions to ceaselessly requested questions on utilizing calculated fields in pivot tables.
Query 1: What are calculated fields?
Calculated fields can help you create new fields in a pivot desk based mostly on current 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 area?
To create a calculated area, 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 Area” button. Within the dialog field that seems, enter a reputation for the sector after which enter the components that you simply need to use to calculate the sector.
Query 3: What sorts of calculations can I carry out utilizing calculated fields?
You need to 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 current fields
Query 4: Can I exploit calculated fields in a number of pivot tables?
Sure, you should utilize calculated fields in a number of pivot tables. Nevertheless, the calculated area have to 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 an information mannequin
Query 6: The place can I be taught extra about calculated fields?
There are lots of sources out there on-line that may train you extra about calculated fields. Some good locations to begin embody the Microsoft Assist web site and the Excel Assist Middle.
Query 7: Can I exploit calculated fields in different spreadsheet applications?
Sure, calculated fields are additionally out there in different spreadsheet applications resembling Google Sheets and LibreOffice Calc.
Concluding paragraph:
Calculated fields are a strong device that can be utilized to enhance the evaluation and presentation of knowledge in pivot tables. By understanding how one can use calculated fields, you possibly can create extra informative and insightful pivot desk reviews.
Along with utilizing calculated fields, there are a selection of different ideas that you should utilize to enhance your pivot desk reviews. The following tips shall be mentioned within the subsequent part.
Suggestions
Listed below are 4 sensible ideas for utilizing calculated fields in pivot tables:
Tip 1: Use significant names in your calculated fields
Whenever you create a calculated area, it is very important give it a significant title. It will make it simpler to determine 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. It will assist to enhance the efficiency of your pivot desk. Some ideas for writing environment friendly formulation embody:
- Use the shortest components attainable.
- Keep away from utilizing nested formulation.
- Use vary references as an alternative of particular person cell references.
Tip 3: Keep away from utilizing unstable capabilities
Risky capabilities are capabilities that may change their worth even when the underlying information has not modified. This could trigger your pivot desk to recalculate unnecessarily, which may decelerate efficiency. Some frequent unstable capabilities embody:
- TODAY()
- NOW()
- RAND()
Tip 4: Use an information mannequin
In case you are working with a big information set, you possibly can enhance the efficiency of your pivot desk by utilizing an information mannequin. An information mannequin is a group of tables which can be associated to one another. Through the use of an information mannequin, you possibly can create pivot tables which can be 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 should utilize calculated fields to create extra informative and insightful pivot desk reviews.
Calculated fields are a strong device that can be utilized to enhance the evaluation and presentation of knowledge in pivot tables. By understanding how one can use calculated fields and by following the guidelines on this article, you possibly can create pivot desk reviews which can be each informative and insightful.
Conclusion
Calculated fields are a strong device that can be utilized to enhance the evaluation and presentation of knowledge in pivot tables. By understanding how one can use calculated fields, you possibly can create pivot desk reviews which can be extra informative, visually interesting, and interactive.
On this article, now we have mentioned the next details:
- What are calculated fields and how one can create them
- The way to use calculated fields to carry out calculations, mix information, and create new insights
- The way to improve pivot desk reviews utilizing calculated fields
- Suggestions for utilizing calculated fields successfully
By following the guidelines and recommendation on this article, you should utilize calculated fields to create pivot desk reviews which can be each informative and insightful.
Closing Message:
Calculated fields are a priceless device for anybody who desires to get essentially the most out of pivot tables. By taking the time to learn to use calculated fields, you possibly can create pivot desk reviews which can be extra informative, visually interesting, and interactive.
