{"id":3354,"date":"2017-01-11T16:21:57","date_gmt":"2017-01-11T22:21:57","guid":{"rendered":"https:\/\/www.vertex42.com\/blog\/?p=3354"},"modified":"2020-09-03T12:31:00","modified_gmt":"2020-09-03T18:31:00","slug":"add-cool-features-to-your-to-do-lists-in-excel","status":"publish","type":"post","link":"https:\/\/www.vertex42.com\/blog\/help\/excel-help\/add-cool-features-to-your-to-do-lists-in-excel.html","title":{"rendered":"Add Cool Features to Your To Do Lists in Excel"},"content":{"rendered":"

One of the best ways to learn new techniques in Excel is to see them in action. This post demonstrates how to add some fun and useful features to simple to do lists including drop-down lists, check boxes, progress bars, and more. The images show Excel 2016, but instructions are similar for Excel 2010 and Excel 2013.<\/p>\n

\n

This Page (contents):<\/p>\n

    \n
  1. Simple Drop-Down Lists via Data Validation<\/a><\/li>\n
  2. Conditional Formats for the Priority Column<\/a><\/li>\n
  3. Conditional Formats for Numeric Priority<\/a><\/li>\n
  4. Checkboxes using Form Fields<\/a><\/li>\n
  5. Checkboxes via Data Validation<\/a><\/li>\n
  6. Progress Bar for % Completed<\/a><\/li>\n
  7. Gray-Out Tasks When They are Complete<\/a><\/li>\n
  8. Highlighting Overdue Dates<\/a><\/li>\n
  9. Autofilter and Sorting<\/a><\/li>\n
  10. Create a Gantt Chart<\/a><\/li>\n
  11. Drop-Down with Current Date<\/a><\/li>\n<\/ol>\n<\/div>\n

    1. Simple Drop-Down Lists via Data Validation<\/h2>\n

    Many task lists include a Priority<\/strong> or Status<\/strong> column, such as the Homework To Do List<\/a> shown below. It's very handy to use an Excel drop down list<\/a> for columns like these.<\/p>\n

    \"Example<\/p>\n

    To create a simple drop-down list, follow these steps:<\/p>\n

      \n
    1. Select the cells you want to edit<\/li>\n
    2. Go to Data > Data Validation<\/strong><\/li>\n
    3. Choose \"List\" in the Allow<\/em> field<\/li>\n
    4. In the Source<\/em> field enter a comma-delimited list such as High,Medium,Low<\/span>\n<\/ol>\n

      2. Conditional Formats for the Priority Column<\/h2>\n

      In the example above you will see that the values in the Priority column have been highlighted differently. This can be done automatically and is a great way to easily identify your high-priority tasks. Follow these steps to create the type of formats shown in the example above.<\/p>\n

        \n
      1. Select the cells in the Priority column<\/li>\n
      2. Go to Home > Conditional Formatting > Text That Contains<\/strong><\/li>\n
      3. Enter the word high<\/em> and choose the \"Light Red Fill with Dark Red Text\" option<\/li>\n<\/ol>\n

        The image below shows how to get to the correct option from the Home tab.<\/p>\n

        \"Screenshot<\/p>\n

        <\/div>\n

        3. Conditional Formats for Numeric Priority<\/h2>\n

        If you want to use a numeric priority like 0-4, then you can use Icon Sets to display images instead of (or in addition to) the numeric value. You can see this demonstrated in the Simple Task Tracker<\/a> below.<\/p>\n

        \"Custom<\/p>\n

          \n
        1. Select the cells in the Priority column<\/li>\n
        2. Create a drop-down list with the options 4,3,2,1<\/li>\n
        3. Go to Home > Conditional Formatting > Icon Sets > More Rules<\/strong><\/li>\n
        4. The image below shows you how to modify the settings for this rule.<\/li>\n<\/ol>\n

          \"Custom<\/p>\n

          4. Checkboxes using Form Fields<\/h2>\n

          I don't like this method. If you like to sort and delete and insert rows, form fields get all messed up. They may be nice for a spreadsheet layout that is not meant to be modified, but so far I haven't found a to do list that I haven't wanted to modify frequently.<\/p>\n

          The form field checkbox is found in the Developer tab shown in the image below. If you don't see the Developer tab, go to File > Excel Options > Customize Ribbon and find and check the Developer tab.<\/p>\n

          \"Checkbox<\/p>\n

          5. Checkboxes via Data Validation<\/h2>\n

          I wish Microsoft would add an in-cell checkbox feature (Apple's Numbers software does it), but until they do that we have to come up with clever alternatives.<\/p>\n

          One method I like is using a data validation drop-down list because it works pretty well in Excel on touch-enabled devices, and it is also compatible with most versions of Excel and OpenOffice and Google Sheets.<\/p>\n

          The simplest checkbox to make using a drop-down list is probably just a list with a single character (x), or you could use a special character like the square root sign (\u221a) that looks like a check mark in some fonts. In the example below, I've used this technique plus a small square ascii character (\u25a1,\u221a).<\/p>\n

          \"Checkbox<\/p>\n

          Another approach that I really like is to use custom Icon Sets via Conditional Formatting. This isn't as compatible with other spreadsheet programs (like Google Sheets) but it looks good. The simple Task Tracker Template<\/a> shows an example of this:<\/p>\n

          \"Checkbox<\/p>\n

            \n
          1. Select the cells you want to use for the check boxes<\/li>\n
          2. Create a drop-down list with the options 1,0,-1<\/li>\n
          3. Go to Home > Conditional Formatting > Icon Sets<\/strong> and select any set you like<\/li>\n
          4. With those cells still selected, go to Home > Conditional Formatting > Manage Rules<\/strong> and find the rule you just created and edit it to create a custom icon set with the setting shown in the following image.<\/li>\n<\/ol>\n

            \"Custom<\/p>\n

            6. Progress Bar for % Completed<\/h2>\n

            In some of the examples above, you've already seen progress bars in the \"% Complete\" column. Now you'll learn how to do it. Conditional formatting comes in handy yet again:<\/p>\n

              \n
            1. Select the cells in the % Complete column<\/li>\n
            2. Go to Home > Conditional Formatting > Data Bars > More Rules<\/strong><\/li>\n
            3. Modify the bar based on the settings shown in the image below<\/li>\n<\/ol>\n

              \"Progress<\/p>\n

              Want a Progress Bar in Google Sheets?<\/strong> No problem. In cell A1 enter the % Complete and then in the cell to the right of it you can use the formula =REPT("\u2588",ROUND(A1*10,0))<\/span>. You can change the color of the bar by just changing the font color. That's a pretty old trick for Excel users, but it's something that will work in Google Sheets, too.<\/p>\n

              Progress Bar via SPARKLINE in Google Sheets<\/h3>\n

              The new SPARKLINE function allows you to create a progress bar in Google Sheets very easily. Enter a percent complete in cell A1 and the following SPARKLINE function for the bar chart in cell A2.<\/p>\n

              \r\nA1=87.2%<\/span>\r\nA2=SPARKLINE<\/span>(A1<\/span>,{\"charttype\",\"bar\";\"color1\",\"blue\";\"max\",1;\"min\",0})\r\n<\/pre>\n
              See the Simple Gantt Chart<\/a> (Google Sheets version) for an example of how the SPARKLINE function can be used for progress bars.<\/p>\n
              For more ways to modify the color and look of the in-cell progress bar, see the SPARKLINE function documentation<\/a>.<\/p>\n
              <\/p>\n
              7. Gray-Out Tasks When They are Complete<\/h2>\n
              If you like the effect of seeing your completed tasks crossed out<\/span> or grayed out<\/span> or both<\/span>, you can do that fairly easily using conditional formatting.<\/p>\n
              In the example below, the first rule is applied when column A is equal to the special square root character. The placement of the dollar sign in the $A4<\/strong> reference is very important in this formula because we want all the columns in the table to reference column A.<\/p>\n
              \"Conditional<\/p>\n
              Also note that the first rule has the \"Stop if True\" box checked. That is why you don't see the priority cell highlighted red or the % Complete showing a green bar in the example. When the task is marked as complete, I don't want to be distracted by formatting that no longer matters to me. So I'm using the rule order<\/em> to prevent the following rules from being applied if the task has been marked as done.<\/p>\n
              8. Highlighting Overdue Dates<\/h2>\n
              When you have a Due Date, you may want to highlight the date when it is overdue. You can do that with a simple conditional formatting rule shown in the example below.<\/p>\n
              \"Highlighting<\/p>\n
              You can see an example of this in the Homework To Do List shown at the very top of this article.<\/p>\n
              9. Autofilter and Sorting<\/h2>\n
              The little arrows that show up in the header of an Excel table or list are a result of turning on the Filter Button feature. If you don't see the little arrows in the header row already, select a cell in your table (or the entire table) and go to the Data<\/strong> tab and click on the Filter<\/strong> button.<\/p>\n
              10. Create a Gantt Chart<\/h2>\n
              Although a Gantt chart is a great visualization and management tool for projects, creating one from scratch is not nearly as simple as the other ideas shared in this article. The two most common ways to create a Gantt chart in Excel are (1) using a stacked bar graph chart object and (2) using conditional formatting. Visit my Task List Templates<\/b><\/a> page to find an example that uses a chart object and try the free Gantt Chart Template<\/b><\/a> to see the conditional formatting technique in action.<\/p>\n
              11. Drop-Down with Current Date<\/h2>\n
              Update 10\/9\/2018<\/em> - I recently created a new wedding checklist where a user requested the ability to enter either a checkmark or the current date. To do this with data validation, create a list somewhere in the worksheet with the first cell containing a check mark unicode character<\/a> ✔ and the next cell containing the formula =TODAY()<\/strong>.<\/p>\n
              Then, use data validation to create a drop-down list referencing those two cells. This will allow you to select either a checkmark or the current date as shown in the image below.<\/p>\n
              <\/p>\n
              To avoid having Excel show warnings when cells contain older dates, make sure to turn off the warnings and errors when setting up the data validation.<\/p>\n","protected":false},"excerpt":{"rendered":"
              One of the best ways to learn new techniques in Excel is to see them in action. This post demonstrates how to add some fun and useful features to simple to do lists, including drop-down lists, check boxes, and progress bars.<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":"","_links_to":"","_links_to_target":""},"categories":[37,78],"tags":[],"class_list":{"0":"post-3354","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-excel-help","7":"category-excel-tips"},"_links":{"self":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/posts\/3354","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/comments?post=3354"}],"version-history":[{"count":0,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/posts\/3354\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/media?parent=3354"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/categories?post=3354"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/tags?post=3354"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}