Excel Tip: Custom Conditional Formatting on User Input June 13th, 2013

Vinod Kumar

This blog post is an extension to our previous post on Excel Tip: Custom Conditional Formatting. I highly recommend you to read that post because we will be building on the same concept and take it to next level.

In this blog post the problem statement is simple:

  1. Have user input for Sales or Spend.
  2. If “Sales” is given, we want to highlight “Sales” values greater than 70.
  3. If “Spend” value is given, we want to highlight all “Spends” values lesser than 30.

Let us see how to achieve this step-by-step.

Initial DataSet

This is the same dataset I used before for Excel Tip: Custom Conditional Formatting. Nothing magical about the dataset :).

Creating Filter Dropdown

We will try to create an Input element, in our example below I am creating a Label of Filter and adding a cell C1. Let us next add the dropdown.

With Cursor on C1, Select Data Tab –> “Data Validation”.

This brings the Data Validation Screen. In this screen, Select Allow –> List. Under Source, we will select our sources of values in C3 and D3. This will be for values Sales and Spend.

Now click “OK” on Data Validation screen. Now you will be presented with a Dropdown on C1 cell and selecting that gives us two values as below.

Starting Conditional Formatting

Now the process for conditional formatting is similar to the post from Excel Tip: Custom Conditional Formatting. Let us select the section C4-D9 region and select Home –> Conditional Formatting and “New Rule…”.

In our Formatting rule screen, let us add a simple rule to start with. Here we have entered a Formula of =IF($C$1=”Sales”, $C4>70). Also don’t forget to change the Format color before hitting OK.

This formula shows conditional format of Sales > 70 when our Dropdown value shows Sales. A sample output is shown below.

Interestingly, we have not yet written our rule for “Spend”<30. So Goto Conditional Formatting –> Manage Rule and Edit our Rule again. Here we have used the Syntax of IF(Condition True, then, Else(If (condition) true, then)).

The actual condition looks like =IF($C$1="Sales", $C4>70, IF($C$1="Spend",$D4<30)).

Click “OK” and get back to our C1 Dropdown. Select Spend and now you will see the formatting change.

We can extend this concept to a number of formatting options. I have found this really powerful use of Conditional Formatting and works like charm in almost most of the earlier versions of Excel.

Hope you are enjoying this series of Excel Tips. Keep locked in, we have many more coming. If you liked it, please drop a comment.

Tags: , , , , , , , , , ,

This entry was posted on Thursday, June 13th, 2013 at 08:30 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.


2 Responses to “Excel Tip: Custom Conditional Formatting on User Input”

  1. Beena says:

    Really helpful function, very nicely written. Useful for beginners too.

    I would be a bonus, if you’ve added 3rd example, where you use both sales and spend criteria in a single function. For eg: If sales> 70 and spend< 30, do this.

    Just a suggestion, next time , if you add more complex example for advance level excel users.

    Great article overall, I'm strictly following your excel tips and learning from it.

    Thank you,
    Beena

Leave a Reply