Solved

MS excel conditional formatting

Posted on 2014-12-04
9
207 Views
Last Modified: 2015-01-28
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?
0
Comment
Question by:jakester69
9 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40480551
Here's one suggestion - It depends which cell you have focused on before you do the conditional formatting.

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".
0
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 40480554
Hi,

it depends of the Applies to  To Range

see example in B5 and C5

Regards
EE20141204.xlsx
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40480826
In addition to the comments of the two above, I might be making 2 + 2 = 5 but with the wording in the question, there area  couple of disparities.

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
0
 
LVL 5

Expert Comment

by:Hakan Yılmaz
ID: 40480839
When you are defining format for the first time, you need to pay attention to the "active cell".
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.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:jakester69
ID: 40481164
OK, thanks everyone for your help.  I kind of understand what everyone is referring to but I still don't quite understand.  I attached a zip file of the table I'm working on.  As you can see, the proper fields are formatted but one of the formulas are =RIGHT($E2,5)="Total" I understand what E represents (column) but why the 2?  if I put a 1 there I get a totally unexpected result.  I like this conditional formatting and it can be really useful for me but I need to explain it to others so I want to understand it.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40481312
Sorry, no attachment! :-)

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 on the same row as where the format is applied.
0
 

Author Comment

by:jakester69
ID: 40481606
Here is the attachment
DataTable.xlsx
0
 
LVL 5

Accepted Solution

by:
Hakan Yılmaz earned 500 total points
ID: 40481639
Because you applied your contitional format to cells in range "$A$2:$AC$329", your cell references in format condition formula like "$E2" is relative to cell "A2". The first cell of applied range.

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 column reference is absolute.
2 is changed because you made your row reference relative to 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.
0
 

Author Closing Comment

by:jakester69
ID: 40481810
Thanks, I now understand how it works.  You even answered my next question which was why the formula changes  "=RIGHT($E2,5)="Total" formula becomes, =RIGHT($E95,5)="Total".
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now