Click Format to display the Format Cells dialog box.In the formula box, enter the following formula: =ISNUMBER(FIND("Total",$A1)).In the Select a Rule Type area at the top of the dialog box, choose Use a Formula to Determine Which Cells to Format.Excel again displays the New Formatting Rule dialog box. The rule you just created now appears in the Conditional Formatting Rules Manager dialog box. Click OK to dismiss the New Formatting Rule dialog box.Click OK to dismiss the Format Cells dialog box.Using the controls in the dialog box, set the formatting as you want it applied to the Grand Total row.In the formula space, enter the following formula: =ISNUMBER(FIND("Grand Total",$A1)).Excel changes the appearance of the New Formatting Rule dialog box. Excel displays the New Formatting Rule dialog box. Excel displays the Conditional Formatting Rules Manager dialog box. Click the Conditional Formatting tool.
Make sure the Home tab of the ribbon is displayed.Before applying your subtotals, select your entire data table.If you will be repeatedly adding and removing subtotals to the same data table, you may be interested in using conditional formatting to apply the desired subtotal formatting. Now, only the visible subtotal rows are selected. Select the Visible Cells Only option button.Click Special to display the Go To Special dialog box.Press F5 to display the Go To dialog box.Using the Outline area at the left of the screen, collapse the detail in your worksheet so that only the subtotals are showing.Select the entire data table, including the subtotals.If you use subtotals sparingly, and only want to apply a different format for one or two worksheets, you can follow these general steps: You, however, may want to have some different type of formatting for the subtotals, such as shading them in yellow or a different color. That is after all the best way to learn new things, experiment with your data.When you add subtotals to a worksheet, Excel automatically formats the subtotals using a bold font. Play around with some different data and functions to see what you come up with. I am not going into these in this blog, but this should give you some ideas to start experimenting with. There are other functions that you can use within the Subtotal Function such as: Max, Min, Product, Count Numbers, Standard Deviation, Standard Deviation for the Population, Variance, and Variance for the Population. By clicking the numbers in the top left I can reduce my file so that all I am looking at is my subtotals, I have highlighted all of my subtotals to make it easy to see what I have done. Here is what we get (the newest is highlighted in blue.)Īs you can see all of my subtotals are here for easy access and use.
We will repeat our steps for setting up a subtotal, I am adding in the average of the weight, destination code and discount. Now let's add a subtotal for another column without deleting our existing subtotals.
I now have the totals of the costs and the number of Packages in each shipment. I highlighted the first Subtotal results in green the Count Subtotal I just completed in yellow.