FREE Excel Tips Newsletter from TheExcelAddict.com
Subscribe here to get more tips like this every week

The Excel Addict - Help with Excel 2013, 2010, 2007, 2003

July 7, 2016
 
Greetings from The Excel Addict
Hi fellow Excel Addict,

In my last newsletter I showed you a quick way to format your subtotal rows to make them easily distinguishable from the other data rows. Although, this is a great solution to one of Excel's 'puzzling' shortcomings, there is another way to set up your table so that the subtotal rows are formatted 'automatically'. That is the subject of today's tutorial.

Give it a try and let me know what you think.

I
f you missed my 'Excel in Seconds' newsletter on Tuesday, I showed you 'Make Your Subtotal Rows REALLY Stand Out'. You can read the tip here.

W
ishing you another great day of Excelling,
Francis Hayes (The Excel Addict)
Email:  fhayes[AT]TheExcelAddict.com



Francis Hayes (The Excel Addict)



If you missed my last newsletter, you can click here to view it online.
 

 
TheExcelAddict.com Quote of the Day

"Happiness is not having what you want, but wanting what you have."

-- Hyman Schachtel --

If you have a favourite quote, send it to me and I may post it in my newsletter.


Today's Microsoft Excel Tip

AutoFormat Your Subtotal Rows Using Conditional Formatting

When you insert subtotals (Data, Subtotal) in a list, it is difficult to distinguish the subtotal rows from all the other rows in the list.

In a previous tutorial I showed you a quick way to format your subtotal rows to make them easily distinguishable from the other data rows. However, there is another way to set up your table so that the subtotal rows are formatted 'automatically'.

This solution uses Conditional Formatting (CF). You create a CF rule that automatically applies a format when a condition you define is met. In this case, we want the formatting to be applied automatically to the entire table row if the word 'Total' is found at the end of any text value in that row. 


Here's how to set it up...

1) First select your entire data table;

2) From the Home tab click Conditional Formatting, then New Rule...;

Add New Conditional Formatting Rule in Microsoft Excel 2007 2010 2013 2016 365
3) The New Formatting Rule dialog opens. In the 'Select a rule type' section, click 'Use a formula to determine which cells to format';. The bottom of the dialog will display a section to add your formula and formatting;

4) Under 'Format values where this formula is true' , type this formula...

=COUNTIF($A5:$G5,"*Total")

New Formatting Rule in Microsoft Excel 2007 2010 2013 2016 365
For your own data, replace $A5:$G5 with the cell references for the first row of the range you have selected. Make sure you type a $ before both column references but not the row references (e.g. $A5:$G5);

5) Click the Format... button;

6) Now choose the formatting you want to be applied to the subtotal rows. In this example, from the Font tab, select Bold as the Font Style.
On the Border tab, choose the top border and from the Fill tab, select a light orange Background Color, then click OK. You will now see a preview of your chosen format in the New Formatting Rule dialog;

Apply Conditional Formatting in Microsoft Excel 2007 2010 2013 2016 365
7) Click OK on this dialog and you will be taken back to your worksheet. Nothing looks different because none of the rows match the Conditional Formatting rule yet;

8) When you insert subtotals into your table (using Data, Subtotal), the formatting will magically appear in each of the subtotal rows.

Automatically Format Subtotal Rows in Microsoft Excel 2007 2010 2013 2016 365

If you remove the subtotals and reapply them, the formatting will reappear.

Caveat: Adding more data to your list
Unlike Excel tables, when you add new data to the bottom of your list, the formatting isn't automatically copied down to the new rows. So if you add new data below your list, to apply this Conditional Formatting rule, you will need to copy the formatting from one of the rows above. To do this, select a row from the original data, click the Format Painter tool on the Home tab and drag to select the newly added rows.

I hope you find this tutorial helpful.


FREE Excel Tips Newsletter from TheExcelAddict.com
Subscribe here to get more tips like this every week

Thanks for supporting this newsletter and website

Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 10 years as someone who provides exceptional value to my readers. So I'm not willing to risk that. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are EVER not completely satisfied with anything I recommend, please let me know and you will get your money...GUARANTEED. You can't lose.
"Spreadsheets Tips From An Excel Addict" is a weekly publication of TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved.

8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630