Pivot tables are highly effective instruments in Microsoft Excel that help you summarize and analyze giant datasets. One of the 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 current fields within the pivot desk. This lets you carry out calculations in your knowledge with out having to create a brand new components in every cell.
Calculated fields can be utilized for quite a lot of functions, akin to:
- Including totals or subtotals
- Calculating percentages or ratios
- Combining knowledge from completely different fields
- Creating new fields based mostly on current fields
On this article, we are going to present you the way to add a calculated discipline to a pivot desk. We may even present some examples of how calculated fields can be utilized to enhance your knowledge evaluation.
Including Calculated Discipline in Pivot Desk
Calculated fields improve knowledge evaluation and supply useful insights.
- Create new fields
- Carry out calculations
- Add totals/subtotals
- Calculate percentages
- Mix knowledge fields
- Enhance knowledge evaluation
- Simplify complicated formulation
- Improve pivot desk studies
Unlock the facility of calculated fields to rework your knowledge evaluation.
Create new fields
One of the helpful options of calculated fields is the flexibility to create new fields that don’t exist within the authentic knowledge supply. This may be accomplished by combining knowledge from a number of fields, performing calculations, or extracting particular elements of a discipline.
For instance, as an instance you’ve a pivot desk that exhibits gross sales knowledge for various merchandise and areas. You would create a brand new discipline that exhibits the whole gross sales for every product class through the use of the next components:
=SUM(Gross sales)
You would additionally create a brand new discipline that exhibits the typical gross sales value for every product through the use of the next components:
=AVERAGE(Gross sales) / COUNTROWS(Product)
Or, you can create a brand new discipline that exhibits the proportion of gross sales for every product class through the use of the next components:
=SUM(Gross sales) / SUM(Whole Gross sales)
These are just some examples of how you should use calculated fields to create new fields that present useful insights into your knowledge.
With calculated fields, you aren’t restricted to the info that’s already obtainable in your knowledge supply. You possibly can create new fields which might be tailor-made to your particular wants and necessities.
Carry out calculations
Calculated fields may also be used to carry out calculations in your knowledge. This may be helpful for duties akin to:
-
Calculating totals and subtotals
You should use calculated fields so as to add totals and subtotals to your pivot desk. This may help you to rapidly and simply see the general efficiency of your corporation or group.
-
Calculating percentages and ratios
You should use calculated fields to calculate percentages and ratios. This may be helpful for evaluating completely different knowledge units or for figuring out developments.
-
Calculating working totals
You should use calculated fields to calculate working 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 just some examples of the numerous 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 widespread makes use of of calculated fields is so as to add totals and subtotals to a pivot desk. This may help you to rapidly and simply see the general efficiency of your corporation or group.
-
Add a grand whole
So as to add a grand whole to your pivot desk, merely drag the sector that you just wish to summarize to the Values space. The grand whole will probably 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 will probably 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 an effective way to rapidly and simply summarize your knowledge. With calculated fields, you’ll be able to add totals and subtotals to any discipline in your pivot desk, no matter whether or not or not it’s already displayed within the pivot desk.
Calculate percentages
Calculated fields may also be used to calculate percentages. This may be helpful for evaluating completely different knowledge units or for figuring out developments. For instance, you can create a calculated discipline that exhibits the proportion of gross sales for every product class through the use of the next components:
=SUM(Gross sales) / SUM(Whole Gross sales)
You would additionally create a calculated discipline that exhibits the proportion of consumers 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(Whole Clients)
Or, you can create a calculated discipline that exhibits the proportion of gross sales that had been made by the net retailer through the use of the next components:
=SUM(On-line Gross sales) / SUM(Whole Gross sales)
These are just some examples of how you should use calculated fields to calculate percentages. With calculated fields, you’ll be able to calculate percentages for any discipline in your pivot desk, no matter whether or not or not it’s already displayed within the pivot desk.
Calculating percentages is an effective way to match completely different knowledge units and determine developments. With calculated fields, you’ll be able to simply calculate percentages for any discipline in your pivot desk, with out having to create a brand new components in every cell.
Mix knowledge fields
Calculated fields may also be used to mix knowledge from a number of fields. This may be helpful for creating new fields that present extra insights into your knowledge. For instance, you can create a calculated discipline that exhibits the total identify of a buyer by combining the primary identify and final identify fields.
-
Mix textual content fields
You should use calculated fields to mix textual content fields from a number of columns right into a single discipline. For instance, you can create a calculated discipline that exhibits the total identify of a buyer by combining the primary identify and final identify fields.
-
Mix numeric fields
You should use calculated fields to mix numeric fields from a number of columns right into a single discipline. For instance, you can create a calculated discipline that exhibits 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 discipline. For instance, you can create a calculated discipline that exhibits the beginning and finish dates of a challenge by combining the beginning date and finish date fields.
-
Mix different knowledge varieties
You may as well use calculated fields to mix knowledge fields of various knowledge varieties. For instance, you can create a calculated discipline that exhibits the standing of a buyer order by combining the order date and the transport date.
Combining knowledge fields is an effective way to create new fields that present extra insights into your knowledge. With calculated fields, you’ll be able to mix knowledge from any discipline in your pivot desk, no matter whether or not or not it’s already displayed within the pivot desk.
Enhance knowledge evaluation
Calculated fields can be utilized to enhance knowledge evaluation in a variety of methods. For instance, calculated fields can be utilized to:
-
Determine developments and patterns
Calculated fields can be utilized to determine developments and patterns in your knowledge. For instance, you can create a calculated discipline that exhibits the typical gross sales for every product over time. This could help you see how gross sales are trending and determine merchandise which might be performing properly or poorly.
-
Examine completely different knowledge units
Calculated fields can be utilized to match completely different knowledge units. For instance, you can create a calculated discipline that exhibits the gross sales for every product in two completely different areas. This could help you see which merchandise are promoting properly in every area and determine alternatives for development.
-
Create new insights
Calculated fields can be utilized to create new insights into your knowledge. For instance, you can create a calculated discipline that exhibits the profitability of every product. This could help you see which merchandise are most worthwhile and determine alternatives to extend profitability.
-
Make higher choices
Calculated fields can be utilized to make higher choices. For instance, you can create a calculated discipline that exhibits the ROI of every advertising marketing campaign. This could help you see which advertising campaigns are best and determine alternatives to enhance your advertising ROI.
These are just some examples of how calculated fields can be utilized to enhance knowledge evaluation. With calculated fields, you’ll be able to achieve new insights into your knowledge and make higher choices.
Simplify complicated formulation
Calculated fields may also be used to simplify complicated formulation. For instance, as an instance you’ve 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)
Should you needed to make use of this components in a pivot desk, you would need to create a brand new calculated discipline for every product. This may very well be quite a lot of work, particularly in case you have quite a lot of merchandise.
Nevertheless, you should use a calculated discipline to simplify this course of. You possibly can create a single calculated discipline 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 discipline in your pivot desk to see the typical gross sales for every product. That is a lot simpler than creating a brand new calculated discipline for every product.
Calculated fields may also be used to simplify formulation that use a number of standards. For instance, as an instance you’ve a components that calculates the whole gross sales for merchandise which might be offered in a selected area and which have a value larger than $100. This components may look one thing like this:
=SUMIF(Area, “North America”, Gross sales)
Should you needed to make use of this components in a pivot desk, you would need to create a brand new calculated discipline for every area. This may very well be quite a lot of work, particularly in case you have quite a lot of areas.
Nevertheless, you should use a calculated discipline to simplify this course of. You possibly can create a single calculated discipline that calculates the whole gross sales for all merchandise which might be offered in a selected area and which have a value 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 discipline in your pivot desk to see the whole gross sales for every area and product value vary.
Calculated fields are a robust instrument that can be utilized to simplify complicated formulation and make knowledge evaluation simpler.
Improve pivot desk studies
Calculated fields can be utilized to reinforce pivot desk studies in a variety 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 studies. For instance, you can create a calculated discipline that exhibits the profitability of every product. This could help you see which merchandise are most worthwhile and determine alternatives to extend profitability.
-
Enhance knowledge visualization
Calculated fields can be utilized to enhance the info visualization in pivot desk studies. For instance, you can create a calculated discipline that exhibits the gross sales for every product as a proportion of whole gross sales. This could help you see which merchandise are contributing probably the most to gross sales.
-
Make studies extra interactive
Calculated fields can be utilized to make pivot desk studies extra interactive. For instance, you can create a calculated discipline that enables customers to filter the info by product class. This could enable customers to see the info for a selected product class with out having to create a brand new pivot desk.
-
Simplify complicated studies
Calculated fields can be utilized to simplify complicated pivot desk studies. For instance, you can create a calculated discipline that summarizes the info for a number of merchandise. This could help you see the general efficiency of a gaggle of merchandise with out having to take a look at the info for every product individually.
These are just some examples of how calculated fields can be utilized to reinforce pivot desk studies. With calculated fields, you’ll be able to create extra informative, visually interesting, and interactive studies.
FAQ
This part supplies solutions to incessantly requested questions on utilizing calculated fields in pivot tables.
Query 1: What are calculated fields?
Calculated fields help you create new fields in a pivot desk based mostly on current fields. This lets you carry out calculations, mix knowledge, and create new insights with out having to create a brand new components in every cell.
Query 2: How do I create a calculated discipline?
To create a calculated discipline, 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 varieties 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 knowledge 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 use calculated fields in a number of pivot tables. Nevertheless, the calculated discipline have to be outlined within the knowledge 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 risky capabilities
- Use a knowledge mannequin
Query 6: The place can I be taught extra about calculated fields?
There are lots of assets obtainable on-line that may educate you extra about calculated fields. Some good locations to begin embody the Microsoft Help web site and the Excel Assist Heart.
Query 7: Can I exploit calculated fields in different spreadsheet applications?
Sure, calculated fields are additionally obtainable in different spreadsheet applications akin to Google Sheets and LibreOffice Calc.
Concluding paragraph:
Calculated fields are a robust instrument that can be utilized to enhance the evaluation and presentation of information in pivot tables. By understanding the way to use calculated fields, you’ll be able to create extra informative and insightful pivot desk studies.
Along with utilizing calculated fields, there are a variety of different ideas that you should use to enhance your pivot desk studies. The following pointers will probably be mentioned within the subsequent part.
Suggestions
Listed here are 4 sensible ideas for utilizing calculated fields in pivot tables:
Tip 1: Use significant names on your calculated fields
Once you create a calculated discipline, it is very important give it a significant identify. This can 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. This can 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 risky capabilities
Unstable capabilities are capabilities that may change their worth even when the underlying knowledge has not modified. This could trigger your pivot desk to recalculate unnecessarily, which may decelerate efficiency. Some widespread risky capabilities embody:
- TODAY()
- NOW()
- RAND()
Tip 4: Use a knowledge mannequin
In case you are working with a big knowledge 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 might be associated to one another. By utilizing a knowledge mannequin, you’ll be able to create pivot tables which might 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 pointers, you should use calculated fields to create extra informative and insightful pivot desk studies.
Calculated fields are a robust instrument that can be utilized to enhance the evaluation and presentation of information in pivot tables. By understanding the way to use calculated fields and by following the ideas on this article, you’ll be able to create pivot desk studies which might be each informative and insightful.
Conclusion
Calculated fields are a robust instrument that can be utilized to enhance the evaluation and presentation of information in pivot tables. By understanding the way to use calculated fields, you’ll be able to create pivot desk studies which might be extra informative, visually interesting, and interactive.
On this article, we’ve got mentioned the next details:
- What are calculated fields and the way to create them
- Easy methods to use calculated fields to carry out calculations, mix knowledge, and create new insights
- Easy methods to improve pivot desk studies utilizing calculated fields
- Suggestions for utilizing calculated fields successfully
By following the ideas and recommendation on this article, you should use calculated fields to create pivot desk studies which might be each informative and insightful.
Closing Message:
Calculated fields are a useful instrument for anybody who desires to get probably the most out of pivot tables. By taking the time to learn to use calculated fields, you’ll be able to create pivot desk studies which might be extra informative, visually interesting, and interactive.
