ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

Conditional Formatting – how to create a to do list in Excel using Excel 2007 and Excel 2010

Updated on June 5, 2013

Introduction

Hi and welcome to my second hub on how best to use Conditional formatting in Excel. Conditional formatting allows you to give your spreadsheets more visual impact or appeal. You can highlight data that a user is interested in, or make parts of your spreadsheet stand out.

In today’s hub, I will be looking at enhancing an Excel to do list created using Check boxes and Combo boxes with Conditional Formatting. We will be using both to create to do lists and using colours and icons to indicate whether each item on our list is complete or not.

To create Check boxes and Combo boxes for your own list, you can find detailed instructions on my hubs below:

  • To learn more about using, creating and configuring Check boxes, my hub can be found here:

http://robbiecwilson.hubpages.com/hub/Configuring-and-Using-Check-boxes-in-Excel-2007

  • To find out more about Combo boxes, I have a hub going into much more detail here:

http://robbiecwilson.hubpages.com/hub/Configuring-and-Using-Combo-boxes-in-Excel-2007

In my first hub on Conditional Formatting, I discussed using Icon Sets and also using formulas to determine which cells should be formatted. I also discussed how to ensure that the rules you create are not overwritten. Than hub can be found here:

http://robbiecwilson.hubpages.com/hub/Conditional-Formatting-in-Excel-2007-and-Excel-2010-spreadsheets-using-Formulas-and-Icon-Sets

Use Conditional Formatting with to do lists to create visual impact in your spreadsheets in Excel 2007 and Excel 2010.
Use Conditional Formatting with to do lists to create visual impact in your spreadsheets in Excel 2007 and Excel 2010. | Source

Using Conditional Formatting with Check boxes in Excel 2007 and Excel 2010 to give them extra visual impact

Check boxes allow you to create lists such as mine below and to indicate if individual items are completed by checking their associated boxes.

A check box to do list created using Excel 2007 or Excel 2010.
A check box to do list created using Excel 2007 or Excel 2010. | Source

Before we go onto adding the additional formatting, a few points about Check boxes:

  • Each check box is linked to a cell in Excel, in my example each Check box is linked to the cell in column I next to it (I 10 to I 13 in my example)
  • The linked cell will show whether the box is ticked (True) or not (False) as shown below.

We will use the True / False data in column I for our formatting.

The Check box to do list showing the cells linked to the Check boxes in Excel 2007 or Excel 2010.
The Check box to do list showing the cells linked to the Check boxes in Excel 2007 or Excel 2010. | Source

Using Icon Sets with Check Boxes in Excel 2007 and Excel 2010

The first formatting we will use is to add icon sets to our spreadsheet. In this example, we will use the tick and cross icon set to indicate whether a task is complete or not. To begin:

  • For A10, enter in =-H10 to link it to the linked cell for the check box in that row and then do the same for each subsequent cell in the column (A11 =-H11 and so on)
  • Select the cells to the left of your list (in my example column A)
  • Click on the Conditional Formatting button in the Styles group on the Home tab
  • Choose New Rule
  • Next, select Format all cells based on their values
  • Under Format Style pick Icon Sets
  • Configure the Display each icon according to these rules and configure it to be the same as the figure below
  • Finally, choose the Icon Style that you prefer (I like 3 Symbols (Uncircled))
  • Click OK

Configuring Conditional Formatting based on a cell's value in Excel 2007 or Excel 2010.
Configuring Conditional Formatting based on a cell's value in Excel 2007 or Excel 2010. | Source

Note: the ! is not used, but Excel provided us with 3 and 4 icon sets and no 2 icon sets so we had to improvise!

You will be back at the Conditional Formatting Rules Manager which will look like the below.

Conditional Formatting Rules Manager showing configured rules in Excel 2007 and Excel 2010.
Conditional Formatting Rules Manager showing configured rules in Excel 2007 and Excel 2010. | Source

Your list will now look like mine below

Check box to do list showing Icon Sets configured using Conditional Formatting in Excel 2007 or Excel 2010.
Check box to do list showing Icon Sets configured using Conditional Formatting in Excel 2007 or Excel 2010. | Source

Using Conditional Formatting with formulas to give Check boxes visual impact in Excel 2007 and Excel 2010

As well as Icon Sets, you can use formulas with Conditional Formatting to further illustrate the status of the items in your checklist. As we saw above, a Check box returns a True if the box is ticked and a False if it is not. We can use a formula to enable us to use this output.

We begin as we began above by:

  • selecting the cells we want to format and then clicking on the Conditional Formatting button and selecting New Rule
  • This time, we select Use a formula to determine which cells to format
  • The formula I will use is =H10=FALSE
  • Next, click the Format button and select the Fill tab
  • I then choose to fill the cell with red

How to configure Conditional Formatting using a formula in Excel 2007 or Excel 2010.
How to configure Conditional Formatting using a formula in Excel 2007 or Excel 2010. | Source
Conditional Formatting Rules Manager showing rules created using formulas in Excel 2007 or Excel 2010.
Conditional Formatting Rules Manager showing rules created using formulas in Excel 2007 or Excel 2010. | Source
  • I repeat the above for the formula =H10=TRUE
  • Now you can see that our list now clearly illustrates at a glance which items in our list are complete (green) and which are not (red).

Check box to do list formatted with Conditional Formatting in Excel 2007 and Excel 2010.
Check box to do list formatted with Conditional Formatting in Excel 2007 and Excel 2010. | Source

Conditional Formatting for Combo boxes in Excel 2007 and Excel 2010

The main difference between Combo and Check boxes when using their output is that a Check Box returns True or False, but a Combo box returns a number.

The number a Combo box returns corresponds to the position of the item you selected on the list. In our example, Yes is 1 and No is 2. I have illustrated the difference below (the Combo boxes are linked to cells in column G and the Check boxes to cells in column H)

Illustration of the differences in output between Check boxes and Combo boxes in Excel 2007 and Excel 2010.
Illustration of the differences in output between Check boxes and Combo boxes in Excel 2007 and Excel 2010. | Source

In this example, if Yes is selected, the list item will be crossed out and green, for No, it will be crossed out and red. To achieve this:

  • Select the cell containing your first list item and click the Conditional Formatting button and select Create a new rule.
  • Choose Use a formula to determine which cells to format
  • In the Format values where this formula is true field I enter =G3=1
  • Click Format and then Font
  • Select Strikethrough and green as the text Color
  • Create another new rule for No and do exactly the same as above except using the formula =G3=2 in your list in the formula above and select red for the text colour
  • Once you have created these rules you should end up with something similar to the rules below.

Conditional Formatting rules created to configure a Combo box to do list in Excel 2007 or Excel 2010.
Conditional Formatting rules created to configure a Combo box to do list in Excel 2007 or Excel 2010. | Source
Completed Combo box to do list with Conditional Formatting in Excel 2007 or Excel 2010.
Completed Combo box to do list with Conditional Formatting in Excel 2007 or Excel 2010. | Source

Conclusion

Conditional formatting allows you to make your spreadsheets more visually appealing and also to highlight values or trends. In this hub, we used it to highlight items on to do lists. These lists were created with either Check boxes or Combo boxes and the highlighting changes automatically depending on whether an item is marked complete or not.

We used Icon Sets as well as changing both the font and the cell fill colour using Conditional Formatting in this hub.

I hope that you found this hub useful and informative. Please feel free to leave any comments you may have below and thanks for reading!


Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://hubpages.com/privacy-policy#gdpr

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)