July 7, 2016
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.
If 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.
Wishing you another great day of Excelling,
Francis Hayes (The Excel Addict)
If you missed my last newsletter, you can click here to view it online.
If you have a favourite quote, send it to me and I may post it in my newsletter.
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...;
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...
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;
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.
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.
|"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