It is very common in everyday Excel usage that you need to calculate data from two or more different spreadsheets. For example, you may need to calculate the price difference between two months or two years, you may need to find the total time an employee has worked in an institution by calculating from the joining day worksheet and leaving date worksheet. The scenarios are endless. In this article, I am going to show you how you can subtract from different sheets in Excel.
Download Practice Workbook
You can download the workbook used to demonstrate the methods in this article below. Try it yourself while you go through the article.
2 Easy Ways to Subtract from Different Sheets in Excel
To subtract in Excel you need to use a formula with the minus sign (-) between two values. It generally looks something like =A1-B1. It subtracts the cell value of B1 from the cell value in A1 and returns the difference. But you can only use this formula when you want to take values of cells A1 and B1 from the same spreadsheet.
If you want to take the values of cells from a different spreadsheet you need something like:
=SheetName1!cell – SheetName2!cell
First, you need to put the spreadsheet name where you want to take the cell value from followed by an exclamatory mark (!). Then after that write the reference cell. Excel will take the cell values from the particular spreadsheet in this way instead of taking them from the current one.
You can input this formula by two different methods. I have included each in its sub-section. For demonstration, I have selected the following datasets.
This is the one indicating fuel prices in March in the first spreadsheet.
Here is the second one in a different spreadsheet indicating fuel prices in April.
I will use the formula to determine the price difference in different worksheets.
1. Using Manual Formula to Subtract from Different Sheets
You can manually type in the whole formula in the first place. If you are absolutely sure of where you are taking your values from, you can write them down manually to subtract in Excel from different sheets. Be sure to put the name of the sheets within apostrophes (‘).
For a more detailed guide, follow these steps.
- First, go to the spreadsheet where you want to find the subtraction of the previous two sheets. I am using a new one for this.
- Then write down the following formula using your keyboard.
='Price in April'!C5-'Price in March'!C5
- After that, press Enter on your keyboard. You will have the subtraction from different sheets.
- Finally, select the cell again. Click and drag the Fill Handle Icon to fill out the rest of the cells with the same formula.
In this way, you can substrate from different sheets in Excel.
2. Using Mouse to Select Cell Reference from Another Sheet
You can achieve the same result by using the mouse for cell references. Even though you will have to use the same formula for this too, you can use the mouse to input the different cell references. In this method, you can avoid different typing mistakes that can occur from the previous methods if you are not careful. Thus, you will avoid syntax errors.
With this in mind, follow these steps for a more detailed guide.
- First, select the cell you want to input the formula in. To subtract, I have used a different sheet here.
- In the cell, write down the equal sign (=) first using your keyboard.
- Then go to the sheet you want to take the value from and click on the cell. In this case, I have selected the sheet Price in April and cell C5 in it. The formula box will automatically fill up.
- Then type in the minus sign (-) on your keyboard.
- After that go to the sheet where the cell to be subtracted resides. For this example, I have selected the sheet Price in March and cell C5. The formula box will automatically fill up in this step too.
- Once you are done with the previous steps, press Enter on your keyboard. You will have the subtracted value from different sheets.
- Select the cell again. Finally, click and drag the Fill Handle Icon to fill out the rest of the column to replicate the formula.
As can be seen, you can have the same result with this method too.
These were the two methods you can subtract in excel from different sheets. Hope you have found this useful and informative. If you have any questions or suggestions, let us know below. For more detailed guides like this, visit Exceldemy.com.