
Got any Excel Questions? Free Excel Help
See Also: Excel Subtotals  Making the SUBTOTAL Function Dynamic  Bold Excel Subtotals Automatically  Sum Every Nth Cell  Count of Each Item in a List  Excel Pivot Tables
One issue that is often encountered when working in Excel is that the Subtotal results, via Data>Subtotals are not bolded or made easily distinguishable. This can make the resulting data very hard to read, especially if the table that we have applied Subtotals to contains many columns. This often means the resulting subtotals are over to the right, while their associated heading are often in the first column.
Consider the small example below where Subtotals have been added to a very small table of data.
Before Subtotals
A  B  
1  Quarter  Cost 
2  Quart1  $10.00 
3  Quart1  $20.00 
4  Quart1  $10.00 
5  Quart2  $10.00 
6  Quart2  $10.00 
7  Quart3  $15.00 
9  Quart3  $10.00 
10  Quart3  $25.00 
After Subtotals
A  B  
1  Quarter  Cost 
2  Quart1  $10.00 
3  Quart1  $20.00 
4  Quart1  $10.00 
5  Quart1 Total  $40.00 
6  Quart2  $10.00 
7  Quart2  $10.00 
8  Quart2 Total  $20.00 
9  Quart3  $15.00 
10  Quart3  $10.00 
11  Quart3  $25.00 
12  Quart2 Total  $50.00 
13  Grand Total  $110.00 
In the above table our Subtotal headings have been bolded by Excel, yet their associated results have not. As this table only has two columns, it is not that hard to read and pickout the Subtotal amounts. The more columns that a table has, the harder it becomes to visually pickout the Subtotals.
The Solution
The solution to this problem is to make use of Excels Conditional Formatting. Using the above table as an example try this before adding your Subtotals.
The all important part to the formula is the Absolute of the Column ($A) and the Relative reference of the Row (1). As we Start ed our selection from A1, Excel will automatically change the formula for each cell. For example, cell A2 will have a Conditional Format formula of: =RIGHT($A2,5)="Total", cell B2 will also have =RIGHT($A2,5)="Total" and cell A3 and B3 will have: =RIGHT($A3,5)="Total".
Now add your Subtotals and your Subtotals will look like:
A  B  
1  Quarter  Cost 
2  Quart1  $10.00 
3  Quart1  $20.00 
4  Quart1  $10.00 
5  Quart1 Total  $40.00 
6  Quart2  $10.00 
7  Quart2  $10.00 
8  Quart2 Total  $20.00 
9  Quart3  $15.00 
10  Quart3  $10.00 
11  Quart3  $25.00 
12  Quart2 Total  $50.00 
13  Grand Total  $110.00 
When you remove the Subtotals, the bolded font will no longer apply.
Taking It One Step Further
The only problem with the above table now is the Grand Total is bolded the same as the Subtotals. For Grand Totals I prefer to see these formatted different again. Here is how we can do this. Again the steps are based on the above examples
Your Subtotals will now look like below
A  B  
1  Quarter  Cost 
2  Quart1  $10.00 
3  Quart1  $20.00 
4  Quart1  $10.00 
5  Quart1 Total  $40.00 
6  Quart2  $10.00 
7  Quart2  $10.00 
8  Quart2 Total  $20.00 
9  Quart3  $15.00 
10  Quart3  $10.00 
11  Quart3  $25.00 
12  Quart2 Total  $50.00 
13  Grand Total  $110.00 
You can of course use any format you like to make your Subtotals easier to read.
Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money
Special! Free Choice of Complete Excel Training Course OR Excel Addins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.
Instant Download and Money Back Guarantee on Most Software
Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages  Trading In Excel  Construction Estimators  Finance Templates & Addins Bundle  CodeVBA  SmartVBA  PrintVBA  Excel Data Manipulation & Analysis  Convert MS Office Applications To......  Analyzer Excel  Downloader Excel
 MSSQL Migration
Toolkit 
Monte Carlo Addin 
Excel
Costing Templates