Further arguments,starting with ref2, are optional. SUBTOTAL uses up to 11 different mathematical functions (from AVERAGE to VAR.P) to find the right total value, while working within the parameters of the function (ignoring other SUBTOTAL formulas, for instance).Ī SUBTOTAL formula is easily created using the following structure:Ī formula using SUBTOTAL has at least two arguments: function_num and ref1.
The SUBTOTAL function works by calculating the total value of a cell range, based on another mathematical function. How does the SUBTOTAL function work in Microsoft Excel? If you want a SUM formula that works with filtered cells and ignores other SUBTOTAL formulas, then using SUBTOTAL is best, although a pivot table could work in a similar way. 11 different subtotal methods are available, each matching an existing Excel function. Thankfully, you can use other functions as part of a SUBTOTAL formula. SUBTOTAL is also useful for ignoring hidden values, which other functions (like SUM) can’t do. If you filter a table using a value, the SUBTOTAL formula updates, recognizing the filter and excluding cells accordingly. SUBTOTAL works with other Excel features, such as cell filtering. You can insert a formula using SUBTOTAL into a range of cells without it affecting the overall total, because SUBTOTAL ignores other cells that contain a SUBTOTAL formula. While it’s true that you don’t necessarily need to use SUBTOTAL, it makes targeted data analysis a lot easier. What is the SUBTOTAL function and what is it used for?Īt first glance, you might wonder: what is the point of the SUBTOTAL function? After all, it’s easy enough to find a subtotal from a range of cells using existing functions like SUMIF or AVERAGE.
How to use SUBTOTAL in an Excel formula: A step-by-step guideĪre you thinking about using the SUBTOTAL function to analyze your spreadsheet data? To help, we’ll explain how it works and how to use it effectively.Things to consider before using SUBTOTAL in Excel.How does the SUBTOTAL function work in Microsoft Excel?.What is the SUBTOTAL function and what is it used for?.SUBTOTAL automatically ignores any other examples of SUBTOTAL formulas, making it a better option if you’re looking to add subtotals within your dataset without impacting the data that’s reported. If you want to use these functions to find subtotals, however, you can easily do this using the SUBTOTAL function. Mathematical functions like SUM, AVERAGE, and MIN exist in their own right in Excel, allowing you to analyze data in your own way. We’ll explain how to use the SUBTOTAL function, step-by-step, in this tutorial. It allows you to use other functions, like AVERAGE or COUNT, to find custom totals from a dataset. it applies to Excel 2013 and Excel 2016.The SUBTOTAL function in Microsoft Excel returns the subtotal value from a range of cells. See Microsoft documentation for more information. Quick Analysis is a similar set of tools available in Excel 2013.
The button will open the Data Analysis dialog, which offers access to a variety of analysis tools. Go back to the first screenshot in the instructions to see how it will look. Once the add in has been successfully installed you will see data analysis when you click on the data tab (usually to the far right of the toolbar).
If you are prompted that the Analysis ToolPak is not currently installed on your computer, click Yes to install it. Tip: If Analysis ToolPak is not listed in the Add-Ins available box, click Browse to locate it.