Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

I have a data set that I used the sub-total function to sum a group of numbers. That works fine but, I want to change the font of the rows where the totals are. I use the conditional formatting function->new rule->use formula to determine which cells to format.

The column that I totaled is F and in the cell at the bottom of the grouping it say "1253 Total" and the rest of the row shows the summary of "1253" I use the formula =RIGHT($F1,5)="Total"

This usually highlights the a row but it's not always the right row, sometimes I need to adjust the formula to =RIGHT($F2,5)="Total or something like that until the right row is formatted. Can someone explain why the $F1 seems to be that way or is the a help video that I can't find out there?

The column that I totaled is F and in the cell at the bottom of the grouping it say "1253 Total" and the rest of the row shows the summary of "1253" I use the formula =RIGHT($F1,5)="Total"

This usually highlights the a row but it's not always the right row, sometimes I need to adjust the formula to =RIGHT($F2,5)="Total or something like that until the right row is formatted. Can someone explain why the $F1 seems to be that way or is the a help video that I can't find out there?

Let's say I've got three cells, A4, A5 and A6, and the conditional formatting is =$a4="a"

If I select A4 and select down to A6, then A4 is the cell in white, and the others are in dark. Then the conditional formatting of $a4 is being put into the cell A4, the same row.So the conditional formatting in cell a4 is =$a4="a".

If however I select A6 and select up to A4, then A6 is the cell in white, and the others are in dark. Then the conditional formatting of $a4 is being put into cell A6, i.e. two rows adrift. So the conditional formatting in cell a4 is =$a2="a".

Your initial formula says $F1 so would assume that the Subtotal summary row is above the detail data, it can't be below because this is row 1. This option (Summary above details) can be achieved depending on your Subtotal function settings. However, you then say that the cell at the bottom of the grouping says "1253 Total". This would suggest that the Summary row is at the bottom of each group and not at the top, unless this is now the subtotal heading for the group below.

You have subtotalled column F. Are the values and the SUBTOTAL calculation actually in column F? The heading for the subtotal for a particular group is normally then in one column to the left of the column subtotalled so your RIGHT function needs to look at column E.

Thanks

Rob H

If "A1:E10" is selected (It will be applied range) and "B3" is active and you write "=C3" in your formula, it will mean to check 1 cell to the right for each cell in applied range.

When you done with defining, excel converts your formula for "first cell" of applied range.

So your formula will be "=B1" and this will be applied to all cells in applied range.

It is better to activate the "first cell" of applied range when defining conditional format and build formula for this cell.

Excel relatively adapts your formula for each cell in conditional format applied range. (Like copying formula in one cell to another cell)

You may give absolute references in your formulas using $ character before row number, column name or both.

You may look here for relative and absolute cell references.

The formula shown is using an absolute reference on column E (using the dollar sign in front) and a relative reference on the row. This conditional formula starts in row 2, hence the "2" here is ensuring that one is testing values

Here is the attachment

DataTable.xlsx

DataTable.xlsx

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

So if you want "A2" to be formatted, last 5 characters in cell "$E2" must be equal to "Total".

If you want "C93" to be formatted, last 5 characters in cell "$E93" must be equal to "Total".

$E is not changed because your

columnreference isabsolute.2 is changed because you made your

rowreferencerelativeto first cell; "A2".Excel evaluates all cells in range one by one.

When evaluating your first contition for a cell, say "Z95",

Your =RIGHT($E2,5)="Total" formula becomes, =RIGHT($E95,5)="Total".

If this condition is true, "Z95" will be formatted as you wish.