Pivot tables are a robust software in Excel that can be utilized to summarize and analyze knowledge. One of many many issues that pivot tables can do is subtract counterparts. This may be helpful for duties reminiscent of discovering the distinction between two units of numbers, or for calculating the online earnings or loss for a enterprise. While you create a pivot desk, you’ll be able to specify which fields you need to use as rows, columns, and values. To ensure that the pivot desk to subtract counterparts, you could use a numeric area because the values area. For instance, when you have an information set that features gross sales figures for various merchandise, you could possibly create a pivot desk that reveals the full gross sales for every product. Within the values area, you would choose the gross sales figures area. After you have created the pivot desk, you should use the Summarize Values By choice to specify how the values ought to be summarized.
The Summarize Values By choice means that you can select from quite a lot of abstract features, together with sum, common, and depend. If you wish to subtract counterparts, it is best to choose the sum operate. After you have chosen the sum operate, you’ll be able to click on on the OK button to create the pivot desk. The pivot desk will show the full gross sales for every product. If there are any detrimental gross sales figures, they are going to be subtracted from the full. For instance, if one of many merchandise had complete gross sales of $100 and one other product had complete gross sales of -$50, the pivot desk would show the online gross sales as $50. You need to use the pivot desk to investigate the information and establish tendencies and patterns. The pivot desk will also be used to create charts and graphs that may assist you visualize the information.
Pivot tables are a flexible software that can be utilized for quite a lot of duties. By understanding tips on how to use the Summarize Values By choice, you should use pivot tables to subtract counterparts and carry out different calculations. This may be helpful for quite a lot of duties, reminiscent of analyzing monetary knowledge, calculating averages, and discovering variations between two units of numbers. With a little bit apply, you should use pivot tables to effectively and successfully analyze your knowledge.
Understanding Pivot Tables
Pivot tables are highly effective instruments in Microsoft Excel and Google Sheets that help you summarize and analyze knowledge effectively. They allow you to create interactive tables that may be reconfigured by dragging and dropping fields, offering a complete view of your knowledge from numerous views.
At their core, pivot tables consist of 4 most important parts: rows, columns, values, and filters. Rows and columns signify the classes or teams of information you need to analyze, whereas values present the numerical knowledge related to these teams. Filters help you slim down the information to particular subsets, specializing in the points which might be most related to your evaluation.
Pivot tables provide a flexible platform for knowledge manipulation and exploration. They permit you to carry out calculations, reminiscent of sums, averages, and counts, on the information and create clear, concise reviews that spotlight the important thing insights and patterns inside your dataset.
Subtracting Numbers
Subtracting numbers in a pivot desk is a simple course of that means that you can evaluate completely different values and establish deltas or variations. To subtract numbers in a pivot desk, observe these steps:
- Click on on the cell the place you need to show the subtraction outcome.
- Go to the “System Bar” on the prime of the window.
- Enter a system that subtracts the values of two cells. For instance, to subtract the worth in cell A2 from the worth in cell B2, enter the system “=B2-A2”.
- Press “Enter” and the outcome can be displayed within the cell.
You may as well use the “Calculated Area” function in pivot tables to create a brand new area that represents the distinction between two values. This lets you save the ensuing calculation and reuse it in different components of the pivot desk or in different calculations.
Step 1: Set Up the Pivot Desk Supply Information
2. Specify the Information Vary
Exactly choosing the information vary is essential for an correct pivot desk. To do that, make sure you spotlight all of the pertinent cells inside your dataset. Keep away from leaving out any obligatory columns or rows, as this might lead to incorrect knowledge or lacking values within the generated pivot desk. For readability, it is advisable to make use of named ranges or particular cell references inside the knowledge supply when defining the information vary.
When defining the information vary, meticulous consideration ought to be paid to the inclusion of header rows and columns. In case your dataset comprises headers, ensure that they’re included inside the chosen vary. Nonetheless, if the headers should not current, guarantee they’re excluded to keep away from potential errors within the pivot desk creation course of.
Think about the next desk for example:
| Month | Gross sales | Returns |
|---|---|---|
| January | $10,000 | $1,000 |
| February | $12,000 | $800 |
| March | $15,000 | $900 |
To outline the information vary for this instance, you’ll spotlight cells A1:C4, guaranteeing that each the header row and the information rows are included within the choice.
Step 2: Insert the Pivot Desk
Set Up the Pivot Desk
Now, let’s create the pivot desk itself. Observe these steps:
- Choose the Information: Spotlight the vary of cells containing the information you need to analyze.
- Insert the Pivot Desk: Go to the "Insert" tab and click on on "PivotTable."
- Select the Vacation spot: A pop-up window will seem. Choose the situation the place you need the pivot desk to be positioned. You may select an current worksheet or create a brand new one.
Add Fields to the Pivot Desk
As soon as the pivot desk is inserted, you should add fields to it for additional evaluation. This is how:
- Drag and Drop Fields: From the "PivotTable Fields" pane on the correct, drag fields into the "Rows," "Columns," or "Values" areas of the pivot desk.
- Select Fields: Choose the fields which might be related to your evaluation. For instance, drag the "Class" area to "Rows" and the "Quantity" area to "Values."
Configure Pivot Desk Settings
After including fields, you’ll be able to alter numerous settings within the pivot desk to customise its look and conduct:
- Area Settings: Proper-click on a area within the "Rows," "Columns," or "Values" space to entry area settings. You may change the type order, grouping, and different choices.
- PivotTable Choices: Click on on the "PivotTable Choices" button within the "PivotTable Analyze" tab to manage numerous settings, reminiscent of present gridlines, allow drill-down, and apply filters.
Step 3: Place Fields within the Pivot Desk
Drag Worth Area to “Values” Space
To indicate the variations between numbers, you should add the sphere that comprises the numerical values to the “Values” space of the Pivot Desk. This space is often positioned on the backside proper nook of the Pivot Desk. Merely drag and drop the worth area into the “Values” space.
Drag Row Label Area to “Rows” Space
The Row Labels space determines the rows in your Pivot Desk. Drag and drop the sphere that you simply need to use as row labels into the “Rows” space. This area usually offers a selected class or group for every row.
Drag Column Label Area to “Columns” Space
Just like the Row Labels, the Column Labels space determines the columns in your Pivot Desk. Drag and drop the sphere that you simply need to use as column labels into the “Columns” space. This area usually represents a unique class or group for every column.
Configure Worth Area Settings
After you have positioned the fields, right-click on the worth area within the “Values” space. Within the context menu, choose “Worth Area Settings”. This can open a dialog field the place you’ll be able to configure how the values are displayed.
Within the “Summarize worth area by” drop-down record, choose “Subtract” to show the variations between the numbers.
Further Suggestions
- You may drag a number of fields into the “Rows” or “Columns” areas to create a extra detailed Pivot Desk.
- You need to use the “Type & Filter” choices within the Pivot Desk toolbar to customise the order and filtering of the information.
- You may as well use Slicers, that are interactive filters, to dynamically filter the information within the Pivot Desk.
Step 4: Create Calculated Area for Subtraction
5. Outline the Calculation
Now, let’s outline the calculation to subtract the other numbers. Click on on the “Insert” tab within the PivotTable Instruments part, then choose “Calculated Area”. Within the “Title” area, enter a descriptive identify on your calculated area, reminiscent of “Subtracted Values.”
Within the “System” area, use the next syntax:
“`
=FieldName1 – FieldName2
“`
Change “FieldName1” with the identify of the sphere containing the optimistic numbers and “FieldName2” with the identify of the sphere containing the detrimental numbers. In our instance, the optimistic numbers are within the “Gross sales” area and the detrimental numbers are within the “Returns” area, so the system can be:
“`
=Gross sales – Returns
“`
As soon as you have entered the system, click on “OK” so as to add the calculated area to your pivot desk. The calculated area will now be displayed within the “Fields” record and may be added to the Rows, Columns, or Values sections of the pivot desk.
Instance:
To illustrate you might have the next knowledge desk:
| Month | Gross sales | Returns |
|---|---|---|
| January | $100,000 | $10,000 |
| February | $120,000 | $12,000 |
| March | $140,000 | $14,000 |
After making a calculated area utilizing the “Gross sales – Returns” system, the pivot desk would present the next values:
| Month | Gross sales | Returns | Subtracted Values |
|---|---|---|---|
| January | $100,000 | $10,000 | $90,000 |
| February | $120,000 | $12,000 | $108,000 |
| March | $140,000 | $14,000 | $126,000 |
Step 5: Subtract Corresponding Values
To subtract corresponding values in a pivot desk, you’ll need to make use of the CALCULATE operate. The CALCULATE operate means that you can carry out calculations on knowledge in a pivot desk, and it may be used to subtract values from completely different rows or columns.
To make use of the CALCULATE operate to subtract corresponding values, you’ll need to specify the next arguments:
1. Expression: The expression that you simply need to calculate. On this case, it would be best to specify the distinction between two values.
2. Filter1: The filter that you simply need to apply to the primary worth.
3. Filter2: The filter that you simply need to apply to the second worth.
For instance, the next system would subtract the worth within the “Gross sales” column for the “East” area from the worth within the “Gross sales” column for the “West” area:
“`
=CALCULATE([Sales], [Region] = “East”) – CALCULATE([Sales], [Region] = “West”)
“`
You may as well use the CALCULATE operate to subtract corresponding values from completely different rows. For instance, the next system would subtract the worth within the “Gross sales” column for the primary row from the worth within the “Gross sales” column for the second row:
“`
=CALCULATE([Sales], ROW() = 1) – CALCULATE([Sales], ROW() = 2)
“`
The CALCULATE operate is a robust software that can be utilized to carry out quite a lot of calculations on knowledge in a pivot desk. By understanding tips on how to use the CALCULATE operate, you’ll be able to create pivot tables that give you the knowledge you should make knowledgeable selections.
Subtracting Corresponding Values from Completely different Tables
In some circumstances, chances are you’ll need to subtract corresponding values from completely different tables. For instance, chances are you’ll need to subtract the gross sales from one desk from the gross sales from one other desk. To do that, you’ll need to make use of the RELATED operate. The RELATED operate means that you can retrieve knowledge from a associated desk.
To make use of the RELATED operate to subtract corresponding values from completely different tables, you’ll need to specify the next arguments:
1. Expression: The expression that you simply need to calculate. On this case, it would be best to specify the distinction between two values.
2. Desk: The desk that you simply need to retrieve knowledge from.
3. Filter: The filter that you simply need to apply to the information.
For instance, the next system would subtract the worth within the “Gross sales” column from the “Gross sales” desk from the worth within the “Gross sales” column from the “Orders” desk:
“`
=CALCULATE([Sales], RELATED([Sales Table])) – CALCULATE([Sales], RELATED([Orders Table]))
“`
The RELATED operate is a robust software that can be utilized to carry out quite a lot of calculations on knowledge from completely different tables. By understanding tips on how to use the RELATED operate, you’ll be able to create pivot tables that give you the knowledge you should make knowledgeable selections.
Step 6: Modify Pivot Desk Formatting
Cell Formatting
Customise the looks of cells by choosing them and making use of formatting choices from the “House” tab. You may change font, dimension, coloration, and background coloration. For detrimental numbers, right-click on the chosen cells, choose “Format Cells”, and set the “Quantity” format to a customized format with detrimental numbers displayed in a unique coloration or brackets.
Column Width and Row Top
Modify the width of columns and the peak of rows to make the desk simpler to learn. Choose a column or row header and drag its border to the specified dimension. You may as well double-click on the border to mechanically match the content material.
Disguise or Present Grand Totals
Grand totals may be hidden or proven as wanted. Proper-click on the grand complete cell and choose “Disguise Grand Complete” or “Present Grand Complete”.
Add Calculated Fields
Calculated fields help you carry out calculations on the information within the pivot desk. Choose the “Analyze” tab and click on on “Calculated Area”. Enter a reputation and system for the calculated area, reminiscent of “(Value1) – (Value2)” to subtract two values.
Freeze Panes
Freeze panes to maintain sure rows or columns in place whereas scrolling. Choose the rows or columns you need to freeze and go to the “View” tab. Click on on “Freeze Panes” and choose the specified choice, reminiscent of “Freeze High Row” or “Freeze First Column”.
Create a Calculated Area to Subtract Reverse Numbers
To create a calculated area that subtracts counterparts, observe these steps:
- Choose the “Analyze” tab and click on on “Calculated Area”.
- Enter a reputation for the sphere, reminiscent of “Distinction”.
- Within the system area, enter the system “IF([Value] < 0, [Value] * -1, [Value])”.
- Click on “OK” to create the calculated area.
| Worth | Distinction |
|---|---|
| 10 | 10 |
| -5 | 5 |
| 0 | 0 |
Step 7: Use Filters and Slicers for Refinement
To additional refine your pivot desk, you’ll be able to apply filters and slicers to particular knowledge fields, permitting you to deal with related subsets of data. As an illustration:
8. Make the most of Slicers for Interactive Filtering
Slicers present an interactive strategy to filter your knowledge. By clicking on particular values in a slicer, you’ll be able to immediately filter the pivot desk to show solely the information that meets the chosen standards. This lets you shortly isolate particular tendencies or patterns in your knowledge.
So as to add a slicer, choose the sphere you need to filter by from the PivotTable Fields record. Drag it to the Slicer part of the Area Record. A number of slicers may be added, enabling you to filter by a number of standards concurrently.
This is an instance of utilizing a slicer to filter a pivot desk that summarizes gross sales knowledge by area and product:
| Area | Gross sales |
|---|---|
| Central | $10,000 |
| East | $12,000 |
| West | $15,000 |
By clicking on the “Central” worth within the Area slicer, you’ll be able to isolate the gross sales knowledge for the Central area:
| Product | Gross sales |
|---|---|
| A | $3,000 |
| B | $4,000 |
| C | $3,000 |
Step 8: Customise Pivot Desk Design
After you have created your pivot desk, you’ll be able to customise its design to make it extra visually interesting and simpler to learn. To do that, choose the pivot desk and click on on the “Design” tab within the ribbon. Right here you’ll be able to change the font, coloration, and dimension of the textual content, in addition to the borders and shading of the cells.
Customizing Quantity Codecs
One necessary side of customizing your pivot desk is to format the numbers appropriately. By default, pivot tables will show numbers in a normal format, however you’ll be able to change this to a extra particular format, reminiscent of foreign money, share, or date.
To vary the quantity format, choose the column or cell that you simply need to format and click on on the “Quantity Format” drop-down menu within the “Quantity” group on the “House” tab. Right here you’ll be able to select from quite a lot of pre-defined codecs, or you’ll be able to create your personal customized format.
| Pre-defined Codecs | Description |
|---|---|
| Forex | Shows numbers as foreign money, with a foreign money image and hundreds separator. |
| Share | Shows numbers as percentages, with a % signal. |
| Date | Shows numbers as dates, with a date format that you simply specify. |
| Customized | Permits you to create your personal customized quantity format, utilizing quite a lot of placeholders and symbols. |
By customizing the quantity codecs in your pivot desk, you may make it simpler to learn and interpret the information.
Greatest Practices for Pivot Desk Subtraction
10. Deal with Destructive Values Fastidiously
Pivot tables mechanically subtract optimistic and detrimental values, however detrimental values can generally result in deceptive outcomes. To stop this, observe these greatest practices:
- Use absolute values. Convert detrimental values to their absolute values (e.g., -5 to five) earlier than performing subtraction to make sure that the result’s all the time optimistic.
- Use the SUMIFS operate. The SUMIFS operate means that you can specify a number of standards, together with whether or not a worth is optimistic or detrimental. This lets you exclude detrimental values from the calculation.
- Create a separate area for detrimental values. If you should show each optimistic and detrimental values in your pivot desk, create a separate area for detrimental values and use the SUM operate to calculate the sum.
| Methodology | Execs | Cons |
|---|---|---|
| Absolute values | Ensures optimistic outcomes | Can distort the underlying knowledge |
| SUMIFS operate | Permits for exact filtering | Could be complicated to implement |
| Separate area for detrimental values | Preserves the unique knowledge | Requires extra setup |
How one can Create a Pivot Desk that Subtracts Reverse Numbers
Making a pivot desk is a good way to summarize and analyze your knowledge. You need to use pivot tables to calculate sums, averages, counts, and different statistical measures. You may as well use pivot tables to create charts and graphs. One widespread job that you simply may must carry out in a pivot desk is to subtract counterparts.
For instance, you might need a dataset that tracks gross sales and refunds. You may need to create a pivot desk to summarize the full gross sales and refunds by product. You might then use the pivot desk to calculate the online gross sales for every product by subtracting the refunds from the gross sales.
To create a pivot desk that subtracts counterparts, you’ll need to make use of the next steps:
1. Choose the information that you simply need to summarize.
2. Click on the Insert tab.
3. Click on the PivotTable button.
4. Within the Create PivotTable dialog field, choose the vacation spot for the pivot desk.
5. Click on the OK button.
6. Drag the fields that you simply need to use as rows, columns, and values into the PivotTable Fields pane.
7. Proper-click on the worth area that you simply need to subtract.
8. Choose the Summarize Values By choice.
9. Choose the Distinction choice.
10. Click on the OK button.
Your pivot desk will now be up to date to indicate the distinction between the 2 values.
Individuals Additionally Ask
How do I create a pivot desk in Excel?
To create a pivot desk in Excel, you’ll need to observe these steps:
1. Choose the information that you simply need to summarize.
2. Click on the Insert tab.
3. Click on the PivotTable button.
4. Within the Create PivotTable dialog field, choose the vacation spot for the pivot desk.
5. Click on the OK button.
6. Drag the fields that you simply need to use as rows, columns, and values into the PivotTable Fields pane.
How do I subtract two values in a pivot desk?
To subtract two values in a pivot desk, you’ll need to observe these steps:
1. Proper-click on the worth area that you simply need to subtract.
2. Choose the Summarize Values By choice.
3. Choose the Distinction choice.
4. Click on the OK button.
How do I create a pivot desk that reveals the distinction between two values?
To create a pivot desk that reveals the distinction between two values, you’ll need to observe these steps:
1. Choose the information that you simply need to summarize.
2. Click on the Insert tab.
3. Click on the PivotTable button.
4. Within the Create PivotTable dialog field, choose the vacation spot for the pivot desk.
5. Click on the OK button.
6. Drag the fields that you simply need to use as rows, columns, and values into the PivotTable Fields pane.
7. Proper-click on the worth area that you simply need to subtract.
8. Choose the Summarize Values By choice.
9. Choose the Distinction choice.
10. Click on the OK button.
