Highline Excel Class 21: Conditional Formatting 12 Examples

[shootmanager1]

Hi,

Can you use colour formating to use the IF function. I am trying to sort sales figures into confirmed and provisional sales and want to colour code the amounts. 

[ExcelIsFun]

I do not understand your question. You might try posting your question to THE best Excel question site:

mrexcel[dot]com/forum

This site is great because you can have a dialog to get you an Excel solution (YouTube is not very good for dialogs and posting pictures of Excel spreadsheets).

[SoldierOfMahdi123]

Sorry but when you hit F9 I couldn't understand that what happened when you pressed F9?

[ExcelIsFun]

If you have part of a formula highlighted and you hit F9, it evaluates formula (convert formula to hard coded formula result). However, this trick is only to see what the formula part evaluates, you usually want to immediately hit Ctrl + Z to undo. An example, A1:A3 contain 2,2,2, if you highlight the formula =SUM(A1:A3), it would show the formula answer 6; but immediately undo to get back to =SUM(A1:A3).

[acemaj]

How do I conditional format so that I can alternating color patterns for a data set whenever it changes in value? In other words, I want a rule that makes a column with the first set of values white, then next set of values that are the same blue, then white again, etc. Thanks!

[ExcelIsFun]

Try this video title:

Excel Magic Trick 685: Conditional Format For Alternative Rows

But then use the columns function instead of rows.

[acemaj]

Good to know but the pattern I am looking for is more sporadic. In other words, my first 12 rows might be $5000, then 12 - 46 $8000, then 47-60 $16,000. However, this pattern might change for a different spreadsheet. Is there a word to automate formatting with alternating colors this way? It's pretty much the same as conditional formatting, but I want alternating colors instead of a "rainbow pattern"

Thanks!

[ExcelIsFun]

I do not know. Try this site:

mrexcel..com/forum

[4abregas]

u are amazing! i learnt something every time, after watching your videos!

[ExcelIsFun]

I am glad that you liked the video!!

[tmkm130]

@ExcelIsFun You truly are a great teacher. I hope you are gathering a collection of these videos so that they never disappear from YouTube! I thought this was a great video. Unfortunately, it did not answer my question. I would like to know if there is a way that conditional formatting can be applied to a large list of data that contains values that match another list (range)?

[ExcelIsFun]

Yes, it is possible, but it is not possible in the comment section to communicate on a topic like this. This is THE best site for asking Excel questions:

mrexcel[dot]com/forum

Please post to this site and then send me the link (excelisfun at gmail) and I will post an answer for you.

[stres3639]

your videos are the best tutorial ive ever seen !

is there any chance u'll teach excel programming?

[ExcelIsFun]

I am glad that they are helpful.

I do not teach VBA.

[jaltok]

Another great tutorial

[ExcelIsFun]

I am glad that you liked it!

[secretarul]

Fantastic !

[ExcelIsFun]

I am glad that you like it!!!

[GeneralApathyNonFan]

(15:40) I think that in Excel 2003 you have to input the conditional formats in the opposite order. In your example the "top" layer is in Condition 3 but in 2003 my "top" layer, the condition that supersedes all previous conditions, is in Condition 1. I have to apply the logicals in reverse order to match your output.

[ExcelIsFun]

Thanks for that hot tip!

I am not at my computer with 2003, but I will check it out on Monday!

[ExcelIsFun]

GeneralApathyNonFan has a good point:

2007: Condition 1 is listed at bottom

2003: Condition 1 is listed at top

[blackhand1818]

Excellent. But suppose we have a table that has a range or values. Is there a way to cond format the highest to lowest values 9matching a color range? Which means that if move a scroll bar and the values change in a table, the color range changes as well to match the new numberic values???

[ExcelIsFun]

Dear blackhand1818,

I am not sure that I understand exactly what you want. Highest to lowest would give you all the values, right?

If you are talking about highlighting a row when there is not an exact match, then the video on LOOKUP that I have coming out tomorrow will cover that.

--excelisfun

[jhfried]

That means always when you repost a video, are some changes?

Do you plan to do videos for Microsoft access (a good idea)?

[ExcelIsFun]

Dear jhfried,

Yes. Usually, I make a mistake or someone has a much better idea and so I re-make it. I don't re-make all videos where I make mistakes, but the Series (like the Highline Class or Lookup Series), I tend to re-shot so you don't have to go searching for the correction.

I have no Access videos at YouTube.

--excelisfun

[jhfried]

Was this posted yesterday? I seen this a few days ago, is there any changes?

[ExcelIsFun]

Dear jhfried,

Yes, I re-posted with two changes. The LARGE conditional Formatting (at 6:43) and the Intersection Formatting (at 14:03) are better than before. Not only that, but later this week I will post a video on the LOOKUP function that will show conditional Formatting for the intersection when you do not have exact matches.

--excelisfun