Excel Dynamic Conditional Formatting Tricks

avatar
(Edited)

Conditional Formatting in Excel allows you to format cells based on the value of the cell. So, most conditional formatting in Excel is Dynamic Conditional formatting. This is because the formatting will change as the values of the cells change. Excel's conditional formatting option has many built-in rules.  These rules allow you to format cells based on values such as the  Top or Bottom value to name a few.  And you can also set the formatting to different icon sets too. Conditional Formatting is often a finishing touch.  It can give a spreadsheet or model that professional look. It can also be used to highlight important data that needs to stand out on your reports. In the video below you will learn how to apply conditional formatting by using a formula.  When you use a formula to determine conditional formatting, this formula must return a true or false result. So the formula must be some sort of logical function.   When the conditions of the formula are found to be true, the formatting will be applied to the cells.  When the conditions are found to be false, formatting will not be applied. 


Learn and Earn Activity


Now that you have watched the video, its time to practice what you have learned.  I cant say often enough if you don't practice what you learn, you wont remember it in a months time.

So to encourage you and help you become your office Excel Ninja you can earn tokens for completing this activity.  Learning Excel has never been more rewarding.

To carry out the learn and earn activity - answer the questions below in the comments section of this post.  Rewards will only be given to those that answer the questions here on the blog.  No rewards will be given to those that leave comments on Facebook or Youtube.

You can read more about our Learn and Earn Activities here.

Dynamic Conditional Formatting Activity


Copy and paste the following table into Excel

Invoice # Company Invoice Date Invoice Total
Inv587 2 - Company 2 30/09/2019 2380.05
Inv588 3 - Company 3 01/09/2019 3.36
Inv589 5 - Company 5 01/10/2019 929.06
Inv590 2 - Company 2 01/10/2019 7.8
Inv591 2 - Company 2 01/10/2019 102.58
Inv592 5 - Company 5 14/10/2019 1346.85
Inv593 11 - Company 11 15/10/2019 474.37
Inv594 11 - Company 11 16/10/2019 600
Inv595 9 - Company 9 04/10/2019 146.52
Inv596 2 - Company 2 10/11/2019 837.15
Inv597 2 - Company 2 10/11/2019 4.22
Inv598 1 - Company 1 10/11/2019 106.37
Inv599 4 - Company 4 16/11/2019 88.88
Inv600 8 - Company 8 18/11/2019 3513.48
Inv601 11 - Company 11 18/11/2019 2490.75
Cr500 11 - Company 11 18/11/2019 -123
Inv602 12 - Company 12 01/12/2019 12300
Inv603 6 - Company 6 01/12/2019 12300
Create a formula to sum the values of the Invoice Total Column.

Set up conditional formatting to fill every second row of data.

Set up conditional formatting that will put boarders on the cell containing the formula to total the invoice column.

In the comments section below answer the following questions.

  • What formula did you use to apply conditional formatting to fill every second row?
  • What formula did you use to apply conditional formatting to apply boarders around the cell with the total invoice value?
 Do you want to start collecting rewards quickly for learning Excel?  Then you should try:

10+ Excel Learn and Earn Activities YOU can do Today

SIGN UP FOR OUR NEWSLETTER TODAY – GET EXCEL TIPS TRICKS AND LEARN AND EARN ACTIVITIES TO YOUR INBOX


SIGN UP

Take A FREE course with us Today!


FREE beginner excel training

The Ultimate Excel Formulas Course

  • includes XLOOKUP and will soon include Dynamic Arrays



Become a Power Pivot Hero

Power Pivot online training course

GET and TRANSFORM DATA like a PRO

Power Query Excel 365



Learn DAX for Power Pivot and Power BI

DAX for powerpivot course
 
Best Value Excel and Excel Power Tool Learning.  Access All Areas, Unlimited Learning Subscription

 


Cross posted from my blog with SteemPress : https://theexcelclub.com/excel-dynamic-conditional-formatting-tricks/



0
0
0.000
17 comments
avatar

Hello,

Your post has been manually curated by a @stem.curate curator.

FA8866FD-F2C3-43B3-A5A5-E0324BA4BB47.jpeg
Supporting Steemians on STEMGeeks

We are dedicated to supporting great content, like yours on the STEMGeeks tribe.

If you like what we are doing, please show your support as well by following our Steem Auto curation trail.

Please join us on discord.

0
0
0.000
avatar

Congratulations @theexcelclub! You have completed the following achievement on the Steem blockchain and have been rewarded with new badge(s) :

You distributed more than 18000 upvotes. Your next target is to reach 19000 upvotes.

You can view your badges on your Steem Board and compare to others on the Steem Ranking
If you no longer want to receive notifications, reply to this comment with the word STOP

To support your work, I also upvoted your post!

Vote for @Steemitboard as a witness to get one more award and increased upvotes!
0
0
0.000
avatar

A lots of good ideas

0
0
0.000
avatar

Thanks for the video. It is really helpful

0
0
0.000
avatar
(Edited)

For filling every other row: =AND(ISEVEN(MOD(ROW(A2:D2);2));NOT(ISBLANK(A2)))

For dynamic sum cell border: =AND(NOT(ISBLANK(D2));ISBLANK(D3))
- Branislav

This comment was made by a guest account using @steempress. Rewards will be sent to the user once they have a Steem account

0
0
0.000
avatar

Hi Acidyo!

Honestly, these global changes in coaches create intrigue, Manchester made a good statement about himself in the last match with PSG, I did not expect that everything would be so extravagant if you were honest, although what can I say, after Ajax crushed Real Madrid, I’m not surprised at anything . I have been a Juve fan for 18 years, and to be honest, I believe that they will be able to give an adequate response to Atletico in the next match, in any case, I am for beautiful, honest football, and the outcome of the Champions League matches is completely unpredictable!

There are also a lot of intrigues in the Premier League, and this is very good!

I live in Russia, in the South, near Krasnodar, and I am going to the match Krasnodar - Vlensiya which will take place on March 14, I hope to see a decent game, and I think I will tell about the match, about the stadium mood and about the game as a whole.

Hug.

0
0
0.000
avatar

Hi Acidyo!

Honestly, these global changes in coaches create intrigue, Manchester made a good statement about himself in the last match with PSG, I did not expect that everything would be so extravagant if you were honest, although what can I say, after Ajax crushed Real Madrid, I’m not surprised at anything . I have been a Juve fan for 18 years, and to be honest, I believe that they will be able to give an adequate response to Atletico in the next match, in any case, I am for beautiful, honest football, and the outcome of the Champions League matches is completely unpredictable!

There are also a lot of intrigues in the Premier League, and this is very good!

I live in Russia, in the South, near Krasnodar, and I am going to the match Krasnodar - Vlensiya which will take place on March 14, I hope to see a decent game, and I think I will tell about the match, about the stadium mood and about the game as a whole.

Hug.

0
0
0.000
avatar

Hi Acidyo!

Honestly, these global changes in coaches create intrigue, Manchester made a good statement about himself in the last match with PSG, I did not expect that everything would be so extravagant if you were honest, although what can I say, after Ajax crushed Real Madrid, I’m not surprised at anything . I have been a Juve fan for 18 years, and to be honest, I believe that they will be able to give an adequate response to Atletico in the next match, in any case, I am for beautiful, honest football, and the outcome of the Champions League matches is completely unpredictable!

There are also a lot of intrigues in the Premier League, and this is very good!

I live in Russia, in the South, near Krasnodar, and I am going to the match Krasnodar - Vlensiya which will take place on March 14, I hope to see a decent game, and I think I will tell about the match, about the stadium mood and about the game as a whole.

Hug.

0
0
0.000
avatar

Hi Acidyo!

Honestly, these global changes in coaches create intrigue, Manchester made a good statement about himself in the last match with PSG, I did not expect that everything would be so extravagant if you were honest, although what can I say, after Ajax crushed Real Madrid, I’m not surprised at anything . I have been a Juve fan for 18 years, and to be honest, I believe that they will be able to give an adequate response to Atletico in the next match, in any case, I am for beautiful, honest football, and the outcome of the Champions League matches is completely unpredictable!

There are also a lot of intrigues in the Premier League, and this is very good!

I live in Russia, in the South, near Krasnodar, and I am going to the match Krasnodar - Vlensiya which will take place on March 14, I hope to see a decent game, and I think I will tell about the match, about the stadium mood and about the game as a whole.

Hug.

0
0
0.000
avatar

Hi Acidyo!

Honestly, these global changes in coaches create intrigue, Manchester made a good statement about himself in the last match with PSG, I did not expect that everything would be so extravagant if you were honest, although what can I say, after Ajax crushed Real Madrid, I’m not surprised at anything . I have been a Juve fan for 18 years, and to be honest, I believe that they will be able to give an adequate response to Atletico in the next match, in any case, I am for beautiful, honest football, and the outcome of the Champions League matches is completely unpredictable!

There are also a lot of intrigues in the Premier League, and this is very good!

I live in Russia, in the South, near Krasnodar, and I am going to the match Krasnodar - Vlensiya which will take place on March 14, I hope to see a decent game, and I think I will tell about the match, about the stadium mood and about the game as a whole.

Hug.

0
0
0.000