Pivot tables are highly effective instruments in Microsoft Excel that let you summarize and analyze giant datasets. Some of the helpful options of pivot tables is the power so as to add calculated fields. Calculated fields are new fields that you may create primarily based on present fields within the pivot desk. This lets you carry out calculations in your information with out having to create a brand new formulation 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 primarily based on present fields
On this article, we’ll present you add a calculated discipline to a pivot desk. We can even 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 helpful insights.
- Create new fields
- Carry out calculations
- Add totals/subtotals
- Calculate percentages
- Mix information fields
- Enhance information evaluation
- Simplify advanced formulation
- Improve pivot desk studies
Unlock the facility of calculated fields to rework 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 authentic information supply. This may be achieved by combining information from a number of fields, performing calculations, or extracting particular elements of a discipline.
For instance, for instance you’ve got a pivot desk that reveals gross sales information for various merchandise and areas. You would create a brand new discipline that reveals the overall gross sales for every product class by utilizing the next formulation:
=SUM(Gross sales)
You would additionally create a brand new discipline that reveals the typical gross sales worth for every product by utilizing the next formulation:
=AVERAGE(Gross sales) / COUNTROWS(Product)
Or, you might create a brand new discipline that reveals the share of gross sales for every product class by utilizing the next formulation:
=SUM(Gross sales) / SUM(Whole Gross sales)
These are only a few examples of how you should utilize calculated fields to create new fields that present helpful insights into your information.
With calculated fields, you aren’t restricted to the information that’s already out there in your information supply. You may create new fields which might be tailor-made to your particular wants and necessities.
Carry out calculations
Calculated fields will also 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 might help you to shortly and simply see the general efficiency of your enterprise 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 tendencies.
-
Calculating operating totals
You should 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 should use calculated fields to calculate the distinction between two values. This may be helpful for figuring out outliers or for monitoring adjustments over time.
These are only a few examples of the numerous calculations that you may carry out utilizing calculated fields. With calculated fields, you might be solely restricted by your creativeness.
Add totals/subtotals
Some of the widespread makes use of of calculated fields is so as to add totals and subtotals to a pivot desk. This might help you to shortly 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 sphere that you just wish to summarize to the Values space. The grand complete can be displayed on the backside of the pivot desk.
-
Add subtotals
So as to add subtotals to your pivot desk, drag the sphere that you just wish to group by to the Rows space or the Columns space. The subtotals can be displayed for every group.
-
Add a number of ranges of subtotals
You may 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 may 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 sphere that you just wish to summarize and choose Summarize Values By.
Including totals and subtotals to a pivot desk is an effective way to shortly and simply summarize your information. 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 will also be used to calculate percentages. This may be helpful for evaluating completely different information units or for figuring out tendencies. For instance, you might create a calculated discipline that reveals the share of gross sales for every product class by utilizing the next formulation:
=SUM(Gross sales) / SUM(Whole Gross sales)
You would additionally create a calculated discipline that reveals the share of consumers who’ve made a purchase order within the final month by utilizing the next formulation:
=COUNT(Clients Who Bought In Final Month) / COUNT(Whole Clients)
Or, you might create a calculated discipline that reveals the share of gross sales that had been made by the web retailer by utilizing the next formulation:
=SUM(On-line Gross sales) / SUM(Whole Gross sales)
These are only a few examples of how you should utilize 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 information units and determine tendencies. 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 formulation in every cell.
Mix information fields
Calculated fields will also 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 might create a calculated discipline 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 discipline. For instance, you might create a calculated discipline 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 discipline. For instance, you might create a calculated discipline 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 should use calculated fields to mix date fields from a number of columns right into a single discipline. For instance, you might create a calculated discipline that reveals the beginning and finish dates of a mission by combining the beginning date and finish date fields.
-
Mix different information sorts
You may as well use calculated fields to mix information fields of various information sorts. For instance, you might create a calculated discipline that reveals the standing of a buyer order by combining the order date and the delivery date.
Combining information fields is an effective way to create new fields that present extra insights into your information. With calculated fields, you’ll be able to mix information from any discipline 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 various 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 might create a calculated discipline that reveals the typical gross sales for every product over time. This may let you see how gross sales are trending and determine merchandise which might be performing properly or poorly.
-
Evaluate completely different information units
Calculated fields can be utilized to match completely different information units. For instance, you might create a calculated discipline that reveals the gross sales for every product in two completely different areas. This may let you see which merchandise are promoting properly in every area and determine alternatives for progress.
-
Create new insights
Calculated fields can be utilized to create new insights into your information. For instance, you might create a calculated discipline that reveals the profitability of every product. This may let 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 might create a calculated discipline that reveals the ROI of every advertising and marketing marketing campaign. This may let you see which advertising and marketing campaigns are handiest and determine alternatives to enhance your advertising and marketing 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 acquire new insights into your information and make higher choices.
Simplify advanced formulation
Calculated fields will also be used to simplify advanced formulation. For instance, for instance you’ve got a formulation that calculates the typical gross sales for every product over time. This formulation may look one thing like this:
=AVERAGE(Gross sales) / COUNT(Dates)
If you happen to wished to make use of this formulation in a pivot desk, you would need to create a brand new calculated discipline for every product. This could possibly be a number of work, particularly if in case you have a number of merchandise.
Nevertheless, you should utilize a calculated discipline to simplify this course of. You may create a single calculated discipline that calculates the typical gross sales for all merchandise. This formulation would look one thing like this:
=AVERAGE(Gross sales) / COUNTROWS(Merchandise)
You may 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 will also be used to simplify formulation that use a number of standards. For instance, for instance you’ve got a formulation that calculates the overall gross sales for merchandise which might be offered in a particular area and which have a worth larger than $100. This formulation may look one thing like this:
=SUMIF(Area, “North America”, Gross sales)
If you happen to wished to make use of this formulation in a pivot desk, you would need to create a brand new calculated discipline for every area. This could possibly be a number of work, particularly if in case you have a number of areas.
Nevertheless, you should utilize a calculated discipline to simplify this course of. You may create a single calculated discipline that calculates the overall gross sales for all merchandise which might be offered in a particular area and which have a worth larger than $100. This formulation would look one thing like this:
=SUMIF(AND(Area = “North America”, Worth > 100), Gross sales)
You may then use this calculated discipline 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 advanced formulation and make information evaluation simpler.
Improve pivot desk studies
Calculated fields can be utilized to boost pivot desk studies in various 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 might create a calculated discipline that reveals the profitability of every product. This may let you see which merchandise are most worthwhile and determine alternatives to extend profitability.
-
Enhance information visualization
Calculated fields can be utilized to enhance the information visualization in pivot desk studies. For instance, you might create a calculated discipline that reveals the gross sales for every product as a proportion of complete gross sales. This may let 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 might create a calculated discipline that permits customers to filter the information by product class. This may enable customers to see the information for a particular product class with out having to create a brand new pivot desk.
-
Simplify advanced studies
Calculated fields can be utilized to simplify advanced pivot desk studies. For instance, you might create a calculated discipline that summarizes the information for a number of merchandise. This may let you see the general efficiency of a gaggle of merchandise with out having to have a look at the information for every product individually.
These are only a few examples of how calculated fields can be utilized to boost pivot desk studies. With calculated fields, you’ll be able to create extra informative, visually interesting, and interactive studies.
FAQ
This part gives solutions to steadily 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 primarily based on present fields. This lets you carry out calculations, mix information, and create new insights with out having to create a brand new formulation 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 sphere after which enter the formulation that you just wish to use to calculate the sphere.
Query 3: What kinds 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 primarily based on present 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 discipline 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 risky capabilities
- Use an information mannequin
Query 6: The place can I study extra about calculated fields?
There are a lot of sources out there 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 exploit calculated fields in different spreadsheet applications?
Sure, calculated fields are additionally out there in different spreadsheet applications equivalent to 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 use calculated fields, you’ll be able to create extra informative and insightful pivot desk studies.
Along with utilizing calculated fields, there are a selection of different suggestions that you should utilize to enhance your pivot desk studies. The following tips can 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 on your calculated fields
Once you create a calculated discipline, you will need to give it a significant title. It will make it simpler to determine the sphere within the PivotTable Fields pane and within the formulation bar.
Tip 2: Use environment friendly formulation
When creating calculated fields, you will need to use environment friendly formulation. It will assist to enhance the efficiency of your pivot desk. Some suggestions for writing environment friendly formulation embody:
- Use the shortest formulation attainable.
- Keep away from utilizing nested formulation.
- Use vary references as a substitute 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 information has not modified. This may trigger your pivot desk to recalculate unnecessarily, which might decelerate efficiency. Some widespread risky capabilities embody:
- TODAY()
- NOW()
- RAND()
Tip 4: Use an information mannequin
If you’re working with a big information set, you’ll be able to enhance the efficiency of your pivot desk by utilizing an information mannequin. A knowledge mannequin is a set of tables which might be associated to one another. Through the use of an information mannequin, you’ll be able to create pivot tables which might be primarily based on a number of tables with out having to create a number of copies of the information.
Closing paragraph:
By following the following pointers, you should utilize calculated fields to create extra informative and insightful pivot desk studies.
Calculated fields are a strong device that can be utilized to enhance the evaluation and presentation of knowledge in pivot tables. By understanding 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 strong device that can be utilized to enhance the evaluation and presentation of knowledge in pivot tables. By understanding 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 create them
- How you can use calculated fields to carry out calculations, mix information, and create new insights
- How you can improve pivot desk studies utilizing calculated fields
- Ideas for utilizing calculated fields successfully
By following the ideas and recommendation on this article, you should utilize calculated fields to create pivot desk studies which might be each informative and insightful.
Closing Message:
Calculated fields are a helpful device for anybody who desires to get probably the most out of pivot tables. By taking the time to discover ways to use calculated fields, you’ll be able to create pivot desk studies which might be extra informative, visually interesting, and interactive.
