How to Master Subtraction in Microsoft Excel: The Ultimate Guide
Microsoft Excel is the world‘s most popular spreadsheet program, used by an estimated 750+ million people worldwide. And it‘s no wonder – Excel is an incredibly powerful tool for working with numbers, performing calculations, and analyzing data to inform business decisions.
One of the most fundamental things you‘ll do in Excel is basic arithmetic like adding, subtracting, multiplying and dividing numbers. In fact, a study by TechRepublic found that simple formulas and calculations are the most common things people do in spreadsheets.
While adding and subtracting might seem basic, Excel provides many tools and techniques to make these operations faster, easier, and more effective. Whether you‘re creating a budget, analyzing sales data, or forecasting trends, the ability to accurately and efficiently subtract in Excel is an essential skill.
In this comprehensive guide, we‘ll take a deep dive into everything you need to know about subtracting in Excel. From basic formulas to advanced functions, you‘ll learn tips and best practices to level up your skills. Let‘s jump in!
The Fundamentals of Formulas
Subtracting in Excel always begins with a formula. Formulas are equations that perform calculations on values in your spreadsheet. They can be as simple as 2+2 or incredibly complex, referencing multiple sheets and workbooks.
The basic structure of a subtraction formula is:
=value1-value2
The equal sign (=) at the start tells Excel that this is a formula, not just regular text. Value1 and value2 can be actual numbers that you type in, like =10-5, or they can be references to cells containing numbers.
To reference cells, simply type the column letter and row number, like A1 or B3. For example, if cell A1 contains 100 and B1 contains 25, you could subtract them with:
=A1-B1
Excel would display the result, 75, in the cell containing the formula.
One important thing to remember is that formulas automatically recalculate whenever the values they reference are changed. So if you modify A1 to 200, the result of the formula =A1-B1 will instantly update to 175 to reflect the new value. This is one of Excel‘s most powerful features!
Beyond Basic Arithmetic
Of course, you‘re not limited to just subtracting two numbers in Excel. In fact, a single formula can subtract as many values as you need. Simply list out each value separated by a minus sign:
=value1-value2-value3-value4…
For example, to subtract 10, 7, and 3 from 100, you would use:
=100-10-7-3
Excel will calculate from left to right, first subtracting 10 from 100, then 7 from that result, and finally 3, displaying the final answer of 80.
| A | B | Formula | Result | |
|---|---|---|---|---|
| 1 | 100 | |||
| 2 | 10 | =A1-A2-A3-A4 | 80 | |
| 3 | 7 | |||
| 4 | 3 |
You can also combine cell references and actual numbers in these longer formulas. For instance, if A1 is 100, you could do:
=A1-10-7-3
This would subtract 10, 7 and 3 from the value in A1 (100).
Working with Ranges
Subtracting multiple individual cells is useful, but often you‘ll want to subtract entire columns or rows of data. Excel makes this a breeze with cell ranges.
A range is a group of cells, defined by the upper left and bottom right cells with a colon in between. For example, A1:A10 would select the range of cells from A1 down to A10. You can click and drag to select a range, or type it directly into a formula.
Let‘s say you have projected and actual sales figures for each month, with projected in column B and actual in column C. To find the difference, simply enter this in D1:
=B1-C1
Then, double-click the fill handle (the small square in the lower right of the selected cell) to copy the formula down the entire column. Excel will automatically update the row numbers, so D2 will contain =B2-C2, D3 will have =B3-C3, etc.
| Month | Projected | Actual | Difference |
|---|---|---|---|
| January | $1,000 | $800 | =$B2-$C2 |
| February | $1,200 | $1,100 | =$B3-$C3 |
| March | $900 | $950 | =$B4-$C4 |
Using click and drag or the fill handle is much faster than retyping the formula for each row. This technique works horizontally as well – if you have numbers in rows instead of columns, put your formula in the first column and drag right to fill in the rest.
Absolute vs. Relative References
Cell references in formulas are relative by default. This means that when you copy a formula, Excel shifts the references by the same amount as the distance you copied.
For example, if you copy a formula from A1 to B1, any reference to A1 would change to B1, A2 would become B2, etc. This is usually what you want for ranges and columns.
However, there are times when you don‘t want a reference to change when copying. The most common example is subtracting the same number from a whole column of values.
To make an absolute reference, add dollar signs ($) before the column and/or row:
- $A$1 locks both column A and row 1
- $A1 locks column A but allows the row reference to shift
- A$1 locks the row but not the column
Imagine you have a list of prices in column A that you want to discount by a fixed amount, say $50 in cell D1. You‘d enter this formula in B1:
=A1-$D$1
Then when you drag that formula down column B, the reference to D1 will remain constant while A1 updates for each row.
| Original Price | Discounted Price |
|---|---|
| $249.99 | =A2-$D$1 |
| $179.99 | =A3-$D$1 |
| $129.99 | =A4-$D$1 |
Avoiding Common Mistakes
While subtracting in Excel is fairly straightforward, there are a few common pitfalls to watch out for:
- Operator order: Excel calculates * and / before + and -. Use parentheses to override this: =(A1+B1)-C1 will add A1 and B1 before subtracting C1.
- Circular references: If a formula refers back to its own cell, either directly or indirectly, you‘ll get a circular reference warning. Correct your formulas to remove the circularity.
- #VALUE! errors: This happens when you try to do math with non-numeric data, like subtracting a name. Ensure all referenced cells contain numbers.
- Forgetting to start with =: Without an equal sign, Excel won‘t run your formula. Double-check that all formulas begin with =.
- Relative/absolute mix-ups: Using relative references when you need absolute, or vice versa, can return unexpected results. Make sure you use $ properly for your needs.
Level Up with Functions
Beyond basic arithmetic formulas, Excel provides a number of built-in functions to help you analyze and manipulate numeric data. Here are two powerful ones to add to your subtraction toolbox:
SUMIF and SUMIFS
The SUMIF function adds up a range that meets one criteria, while SUMIFS can handle multiple criteria. By embedding them inside a SUM formula, you can use them to do selective subtraction.
The syntax is:
=SUM(range)-SUMIF(range,criteria,sum_range)
The SUMIF portion will total only the numbers in sum_range where the corresponding cells in range match the criteria. Subtracting this from the SUM of the entire range gives you a total that excludes your specified criteria.
For example, suppose you have sales data with amounts in column D, sales rep names in column E, and regions in column F. To find the total sales amount excluding those by Jim in the East region, you‘d use:
=SUM(D:D)-SUMIFS(D:D,E:E,"Jim",F:F,"East")
SUMIFS allows you to include as many criteria ranges and criteria as you need, so you can get very specific with what you want to exclude.
SUBTOTAL
The SUBTOTAL function is designed to help you create summaries from filtered lists. It has the syntax:
=SUBTOTAL(function_num,range1,[range2]…)
The function_num parameter is a code specifying the calculation to perform, like 1 for averaging or 9 for summing. Using a code from 100-109 will tell SUBTOTAL to ignore rows hidden by filtering.
Combining SUBTOTAL with SUM lets you find the difference between filtered and total data. For instance, if you have data in A1:A100 and want to see the difference between the visible total and the grand total, you‘d use:
=SUM(A1:A100)-SUBTOTAL(109,A1:A100)
This will subtract the total of visible cells (ignoring filters) from the actual total of all cells.
Subtraction Across Excel
Subtraction isn‘t limited to just one sheet or workbook in Excel. You can reference cells across multiple sheets and even separate files. This is incredibly powerful for consolidating data and creating executive summaries.
To reference another sheet, simply include the sheet name followed by an exclamation point before the cell address:
=Sheet1!A1-Sheet2!A1
This would subtract the value in cell A1 of Sheet2 from the value in A1 of Sheet1.
For separate workbooks, you‘ll also need to include the file name in square brackets:
=[Book1.xlsx]Sheet1!A1-[Book2.xlsx]Sheet1!A1
This would look in Book1 and Book2, both in the same folder as the current file, and subtract the values in A1 of each Sheet1.
If you move or rename worksheets or workbooks, be sure to update any formulas that reference them. Excel will show a #REF! error for formulas referencing missing or renamed files.
Bringing It All Together
Let‘s close with a real-world example that showcases many of the subtraction tools and techniques covered in this guide.
Imagine you work for a retail chain that sells products across several regions. You have a master worksheet that pulls in daily sales data from separate files for each region.
Management wants a report showing year-to-date sales by region and product category. They also want the totals excluding any discounted sales, and a sum comparing sales to the same period last year.
To build this in Excel, you could:
- Use links to pull detail data from the regional files into your master sheet
- Create a SUMIFS formula to total sales for each region/category combination, excluding those with "Discount" in the Promo column
- Drag that formula down and across to fill the range for all regions and categories
- Use SUBTOTAL at the bottom to get totals for each region, and at the right to subtotal each category
- On another sheet, set up a similar range for last year‘s data
- In a new column on the main sheet, reference the cells from the last year sheet and subtract them from this year‘s numbers
With a well-constructed spreadsheet like this, your sales data will be neatly organized and summarized, with discount sales excluded, and clearly compared to last year‘s performance. You can slice and dice the numbers in myriad ways to uncover insights and opportunities.
This is just scratching the surface of what you can do by combining subtraction with Excel‘s other functions and features like filters, charts, and PivotTables. The key is to build the skills and understanding to manipulate your data to meet your specific business needs.
Continuing Your Excel Education
I hope this guide has given you a solid foundation in the essentials of subtracting in Excel. Of course, there‘s always more to learn. Excel is a vast program with a staggering array of capabilities.
To further hone your skills, I recommend Microsoft‘s free Excel for Windows training, which includes video tutorials and hands-on practice for all skill levels. The /r/excel community on Reddit is also a great place to ask questions, find solutions, and see what other Excel experts are doing.
However you choose to continue your Excel journey, the most important thing is to keep practicing and experimenting. Try out new formulas, combine features in creative ways, and don‘t be afraid to make mistakes. Before long, you‘ll be subtracting like an Excel master.
