This means that every time you visit this website you will need to enable or disable cookies again. As a result, a Subtotal dialog box will appear in front of you. And then, sum the list values excluding the subtotals with this formula: =SUM (B2:B21)/2, enter this . Once you enter the =SUBTOTAL (, Excel automatically shows you suggestions for the operations that you want to perform on the data. And, dont forget to share your thoughts. If you want to display only the Grand Total, just click over the 1 (located on the upper-left side as depicted in the following image). Subtotal button in Data > Outline tools. Use UNIQUE to remove any duplicates in your list. Initially, select the dataset or keep the cursor over any cell within the dataset. I firmly believe that the above-discussed method will help you to make subtotal and grand total in Excel. Finally, you will see results like the picture below. This is Mursalin. This is where you will use the Subtotal formula. On the Data tab, in the Outline group, click Subtotal. So, the formula for the F8 cell will be like the following-. Note that the selected cells are highlighted with a dotted line. Once you sort and arrange your subtotal data, use the "Outline" section in the "Data" tab and select the column that you want to subtotal. 80%, Convert Between Cells Content and Comments, Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier, This comment was minimized by the moderator on the site, Sum values without subtotals with Sum function in Excel, Sum value without subtotals with Subtotal function in Excel. The subtotal function. You can also sum the amounts for each item category by using the Subtotal tool of Excel. For summing values without the subtotals in Excel, what would you do? The Subtotal command will appear grayed out if you are working with an Excel table. Select the range that you want to insert the category subtotal. function onCatChange() { In this instructive session, Ill demonstrate 4 methods on how to make subtotal and grand total in Excel. @kalyani: The formula does actually works exactly as shown. Note that Excel pastes the formatting as well as the formula when you use Ctrl-V. The default value is True. Then, press OK (keep the default options as these show). Note that this will remove all table functionality from the data except table formatting. Increases your productivity by Read More: How to Subtotal Multiple Columns in Excel Pivot Table (with Easy Steps). Instead of just typing "1" as the first argument, you can also link to the cell defining the operation. Hi there! In the first method, we will determine the subtotal of the sales amount for each product. As you can see from the following menu, there are multiple functions to choose from: Once the function is selected it's . This is the way we can use subtotalfunction in Microsoft Excel for different calculations for the subtotal. Lets introduce todays dataset (which belongs to the B4:F13 cell range) as shown in the following screenshot. ExcelDemy is a place where you can learn Excel, and get solutions to your Excel & Excel VBA-related problems, Data Analysis with Excel, etc. First of all, select any cell in the dataset. Add Different Subtotals in Same Column. Another simple way to make subtotal and the grand total is the application of the SUM function. So, without any delay, lets jump to the steps.