Pivot tables are a strong instrument in Excel that can be utilized to summarize and analyze information. One of many many issues that pivot tables can do is subtract counterparts. This may be helpful for duties equivalent to discovering the distinction between two units of numbers, or for calculating the web revenue or loss for a enterprise. While you create a pivot desk, you possibly can specify which fields you need to use as rows, columns, and values. To ensure that the pivot desk to subtract counterparts, you will need to use a numeric subject because the values subject. For instance, you probably have a knowledge set that features gross sales figures for various merchandise, you can create a pivot desk that reveals the overall gross sales for every product. Within the values subject, you would choose the gross sales figures subject. After getting 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 possibility permits you to select from a wide range of abstract features, together with sum, common, and depend. If you wish to subtract counterparts, you must choose the sum perform. After getting chosen the sum perform, you possibly can click on on the OK button to create the pivot desk. The pivot desk will show the overall gross sales for every product. If there are any damaging gross sales figures, they are going to be subtracted from the overall. 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 web gross sales as $50. You need to use the pivot desk to investigate the info and determine tendencies and patterns. The pivot desk can be used to create charts and graphs that may assist you visualize the info.
Pivot tables are a flexible instrument that can be utilized for a wide range of duties. By understanding the way to use the Summarize Values By possibility, you should use pivot tables to subtract counterparts and carry out different calculations. This may be helpful for a wide range of duties, equivalent to analyzing monetary information, calculating averages, and discovering variations between two units of numbers. With somewhat follow, you should use pivot tables to effectively and successfully analyze your information.
Understanding Pivot Tables
Pivot tables are highly effective instruments in Microsoft Excel and Google Sheets that will let you summarize and analyze information effectively. They allow you to create interactive tables that may be reconfigured by dragging and dropping fields, offering a complete view of your information from varied views.
At their core, pivot tables consist of 4 fundamental elements: rows, columns, values, and filters. Rows and columns signify the classes or teams of knowledge you need to analyze, whereas values present the numerical information related to these teams. Filters will let you slender down the info to particular subsets, specializing in the points which can be most related to your evaluation.
Pivot tables provide a flexible platform for information manipulation and exploration. They permit you to carry out calculations, equivalent to sums, averages, and counts, on the info and create clear, concise studies 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 permits you to examine totally different values and determine deltas or variations. To subtract numbers in a pivot desk, comply with these steps:
- Click on on the cell the place you need to show the subtraction consequence.
- Go to the “System Bar” on the high of the window.
- Enter a components that subtracts the values of two cells. For instance, to subtract the worth in cell A2 from the worth in cell B2, enter the components “=B2-A2”.
- Press “Enter” and the consequence can be displayed within the cell.
You can too use the “Calculated Discipline” characteristic in pivot tables to create a brand new subject that represents the distinction between two values. This lets you save the ensuing calculation and reuse it in different elements of the pivot desk or in different calculations.
Step 1: Set Up the Pivot Desk Supply Knowledge
2. Specify the Knowledge Vary
Exactly choosing the info 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 essential columns or rows, as this might lead to incorrect information or lacking values within the generated pivot desk. For readability, it is advisable to make use of named ranges or particular cell references throughout the information supply when defining the info vary.
When defining the info vary, meticulous consideration ought to be paid to the inclusion of header rows and columns. In case your dataset comprises headers, make sure that they’re included throughout the chosen vary. Nevertheless, if the headers will not be current, guarantee they’re excluded to keep away from potential errors within the pivot desk creation course of.
Contemplate the next desk for instance:
| Month | Gross sales | Returns |
|---|---|---|
| January | $10,000 | $1,000 |
| February | $12,000 | $800 |
| March | $15,000 | $900 |
To outline the info vary for this instance, you’ll spotlight cells A1:C4, making certain that each the header row and the info 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 Knowledge: Spotlight the vary of cells containing the info 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 placement the place you need the pivot desk to be positioned. You possibly can select an present worksheet or create a brand new one.
Add Fields to the Pivot Desk
As soon as the pivot desk is inserted, you could add fields to it for additional evaluation. This is how:
- Drag and Drop Fields: From the "PivotTable Fields" pane on the proper, drag fields into the "Rows," "Columns," or "Values" areas of the pivot desk.
- Select Fields: Choose the fields which can be related to your evaluation. For instance, drag the "Class" subject to "Rows" and the "Quantity" subject to "Values."
Configure Pivot Desk Settings
After including fields, you possibly can alter varied settings within the pivot desk to customise its look and habits:
- Discipline Settings: Proper-click on a subject within the "Rows," "Columns," or "Values" space to entry subject settings. You possibly can change the type order, grouping, and different choices.
- PivotTable Choices: Click on on the "PivotTable Choices" button within the "PivotTable Analyze" tab to regulate varied settings, equivalent to present gridlines, allow drill-down, and apply filters.
Step 3: Place Fields within the Pivot Desk
Drag Worth Discipline to “Values” Space
To point out the variations between numbers, you could add the sector that comprises the numerical values to the “Values” space of the Pivot Desk. This space is usually situated on the backside proper nook of the Pivot Desk. Merely drag and drop the worth subject into the “Values” space.
Drag Row Label Discipline to “Rows” Space
The Row Labels space determines the rows in your Pivot Desk. Drag and drop the sector that you just need to use as row labels into the “Rows” space. This subject usually supplies a particular class or group for every row.
Drag Column Label Discipline to “Columns” Space
Just like the Row Labels, the Column Labels space determines the columns in your Pivot Desk. Drag and drop the sector that you just need to use as column labels into the “Columns” space. This subject usually represents a distinct class or group for every column.
Configure Worth Discipline Settings
After getting positioned the fields, right-click on the worth subject within the “Values” space. Within the context menu, choose “Worth Discipline Settings”. It will open a dialog field the place you possibly can configure how the values are displayed.
Within the “Summarize worth subject by” drop-down checklist, choose “Subtract” to show the variations between the numbers.
Extra Suggestions
- You possibly can 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 info.
- You can too use Slicers, that are interactive filters, to dynamically filter the info within the Pivot Desk.
Step 4: Create Calculated Discipline 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 Discipline”. Within the “Identify” subject, enter a descriptive identify to your calculated subject, equivalent to “Subtracted Values.”
Within the “System” subject, use the next syntax:
“`
=FieldName1 – FieldName2
“`
Exchange “FieldName1” with the identify of the sector containing the constructive numbers and “FieldName2” with the identify of the sector containing the damaging numbers. In our instance, the constructive numbers are within the “Gross sales” subject and the damaging numbers are within the “Returns” subject, so the components could be:
“`
=Gross sales – Returns
“`
As soon as you have entered the components, click on “OK” so as to add the calculated subject to your pivot desk. The calculated subject will now be displayed within the “Fields” checklist and may be added to the Rows, Columns, or Values sections of the pivot desk.
Instance:
As an instance you’ve gotten the next information desk:
| Month | Gross sales | Returns |
|---|---|---|
| January | $100,000 | $10,000 |
| February | $120,000 | $12,000 |
| March | $140,000 | $14,000 |
After making a calculated subject utilizing the “Gross sales – Returns” components, 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 will want to make use of the CALCULATE perform. The CALCULATE perform permits you to carry out calculations on information in a pivot desk, and it may be used to subtract values from totally different rows or columns.
To make use of the CALCULATE perform to subtract corresponding values, you will want to specify the next arguments:
1. Expression: The expression that you just need to calculate. On this case, it would be best to specify the distinction between two values.
2. Filter1: The filter that you just need to apply to the primary worth.
3. Filter2: The filter that you just need to apply to the second worth.
For instance, the next components 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 can too use the CALCULATE perform to subtract corresponding values from totally different rows. For instance, the next components 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 perform is a strong instrument that can be utilized to carry out a wide range of calculations on information in a pivot desk. By understanding the way to use the CALCULATE perform, you possibly can create pivot tables that give you the data you could make knowledgeable choices.
Subtracting Corresponding Values from Completely different Tables
In some instances, you could need to subtract corresponding values from totally different tables. For instance, you could need to subtract the gross sales from one desk from the gross sales from one other desk. To do that, you will want to make use of the RELATED perform. The RELATED perform permits you to retrieve information from a associated desk.
To make use of the RELATED perform to subtract corresponding values from totally different tables, you will want to specify the next arguments:
1. Expression: The expression that you just need to calculate. On this case, it would be best to specify the distinction between two values.
2. Desk: The desk that you just need to retrieve information from.
3. Filter: The filter that you just need to apply to the info.
For instance, the next components 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 perform is a strong instrument that can be utilized to carry out a wide range of calculations on information from totally different tables. By understanding the way to use the RELATED perform, you possibly can create pivot tables that give you the data you could make knowledgeable choices.
Step 6: Alter Pivot Desk Formatting
Cell Formatting
Customise the looks of cells by choosing them and making use of formatting choices from the “Dwelling” tab. You possibly can change font, dimension, colour, and background colour. For damaging numbers, right-click on the chosen cells, choose “Format Cells”, and set the “Quantity” format to a customized format with damaging numbers displayed in a distinct colour or brackets.
Column Width and Row Peak
Alter 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 can too double-click on the border to routinely match the content material.
Cover or Present Grand Totals
Grand totals may be hidden or proven as wanted. Proper-click on the grand complete cell and choose “Cover Grand Complete” or “Present Grand Complete”.
Add Calculated Fields
Calculated fields will let you carry out calculations on the info within the pivot desk. Choose the “Analyze” tab and click on on “Calculated Discipline”. Enter a reputation and components for the calculated subject, equivalent to “(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 possibility, equivalent to “Freeze Prime Row” or “Freeze First Column”.
Create a Calculated Discipline to Subtract Reverse Numbers
To create a calculated subject that subtracts counterparts, comply with these steps:
- Choose the “Analyze” tab and click on on “Calculated Discipline”.
- Enter a reputation for the sector, equivalent to “Distinction”.
- Within the components subject, enter the components “IF([Value] < 0, [Value] * -1, [Value])”.
- Click on “OK” to create the calculated subject.
| Worth | Distinction |
|---|---|
| 10 | 10 |
| -5 | 5 |
| 0 | 0 |
Step 7: Use Filters and Slicers for Refinement
To additional refine your pivot desk, you possibly can apply filters and slicers to particular information fields, permitting you to concentrate on related subsets of knowledge. As an example:
8. Make the most of Slicers for Interactive Filtering
Slicers present an interactive strategy to filter your information. By clicking on particular values in a slicer, you possibly can immediately filter the pivot desk to show solely the info that meets the chosen standards. This lets you rapidly isolate particular tendencies or patterns in your information.
So as to add a slicer, choose the sector you need to filter by from the PivotTable Fields checklist. Drag it to the Slicer part of the Discipline Listing. 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 information 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 possibly can isolate the gross sales information for the Central area:
| Product | Gross sales |
|---|---|
| A | $3,000 |
| B | $4,000 |
| C | $3,000 |
Step 8: Customise Pivot Desk Design
After getting created your pivot desk, you possibly can 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 possibly can change the font, colour, 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 accurately. By default, pivot tables will show numbers in a basic format, however you possibly can change this to a extra particular format, equivalent to forex, share, or date.
To vary the quantity format, choose the column or cell that you just need to format and click on on the “Quantity Format” drop-down menu within the “Quantity” group on the “Dwelling” tab. Right here you possibly can select from a wide range of pre-defined codecs, or you possibly can create your individual customized format.
| Pre-defined Codecs | Description |
|---|---|
| Forex | Shows numbers as forex, with a forex image and 1000’s separator. |
| Share | Shows numbers as percentages, with a % signal. |
| Date | Shows numbers as dates, with a date format that you just specify. |
| Customized | Means that you can create your individual customized quantity format, utilizing a wide range of placeholders and symbols. |
By customizing the quantity codecs in your pivot desk, you can also make it simpler to learn and interpret the info.
Finest Practices for Pivot Desk Subtraction
10. Deal with Destructive Values Rigorously
Pivot tables routinely subtract constructive and damaging values, however damaging values can generally result in deceptive outcomes. To stop this, comply with these finest practices:
- Use absolute values. Convert damaging values to their absolute values (e.g., -5 to five) earlier than performing subtraction to make sure that the result’s at all times constructive.
- Use the SUMIFS perform. The SUMIFS perform permits you to specify a number of standards, together with whether or not a price is constructive or damaging. This lets you exclude damaging values from the calculation.
- Create a separate subject for damaging values. If you could show each constructive and damaging values in your pivot desk, create a separate subject for damaging values and use the SUM perform to calculate the sum.
| Technique | Professionals | Cons |
|---|---|---|
| Absolute values | Ensures constructive outcomes | Can distort the underlying information |
| SUMIFS perform | Permits for exact filtering | Will be advanced to implement |
| Separate subject for damaging values | Preserves the unique information | Requires extra setup |
The right way to Create a Pivot Desk that Subtracts Reverse Numbers
Making a pivot desk is an effective way to summarize and analyze your information. You need to use pivot tables to calculate sums, averages, counts, and different statistical measures. You can too use pivot tables to create charts and graphs. One widespread job that you just would possibly have to carry out in a pivot desk is to subtract counterparts.
For instance, you may need a dataset that tracks gross sales and refunds. You would possibly need to create a pivot desk to summarize the overall gross sales and refunds by product. You might then use the pivot desk to calculate the web gross sales for every product by subtracting the refunds from the gross sales.
To create a pivot desk that subtracts counterparts, you will want to make use of the next steps:
1. Choose the info that you just 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 just need to use as rows, columns, and values into the PivotTable Fields pane.
7. Proper-click on the worth subject that you just need to subtract.
8. Choose the Summarize Values By possibility.
9. Choose the Distinction possibility.
10. Click on the OK button.
Your pivot desk will now be up to date to indicate the distinction between the 2 values.
Folks Additionally Ask
How do I create a pivot desk in Excel?
To create a pivot desk in Excel, you will want to comply with these steps:
1. Choose the info that you just 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 just 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 will want to comply with these steps:
1. Proper-click on the worth subject that you just need to subtract.
2. Choose the Summarize Values By possibility.
3. Choose the Distinction possibility.
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 will want to comply with these steps:
1. Choose the info that you just 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 just need to use as rows, columns, and values into the PivotTable Fields pane.
7. Proper-click on the worth subject that you just need to subtract.
8. Choose the Summarize Values By possibility.
9. Choose the Distinction possibility.
10. Click on the OK button.
