When data is filtered, only rows that meet the filter criteria will display and other rows will be hidden. With filtered data, you can then copy, format, print, etc., your data, without having to sort or move it first.
![]() ![]() Follow these steps to create the type of formats shown in the example above. This can be done automatically and is a great way to easily identify your high-priority tasks. Conditional Formats for the Priority ColumnIn the example above you will see that the values in the Priority column have been highlighted differently. In the Source field enter a comma-delimited list such as High,Medium,Low 2. It's very handy to use an Excel drop down list for columns like these.To create a simple drop-down list, follow these steps: Radiculopathy after blood patchYou can see this demonstrated in the Simple Task Tracker below. Conditional Formats for Numeric PriorityIf 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. Enter the word high and choose the "Light Red Fill with Dark Red Text" optionThe image below shows how to get to the correct option from the Home tab.3. Go to Home > Conditional Formatting > Text That Contains Get A Pick List Filter On Excel Software Does ItIn the example below, I've used this technique plus a small square ascii character (□,√).Another approach that I really like is to use custom Icon Sets via Conditional Formatting. If you don't see the Developer tab, go to File > Excel Options > Customize Ribbon and find and check the Developer tab.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.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.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 (√) that looks like a check mark in some fonts. 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.The form field checkbox is found in the Developer tab shown in the image below. If you like to sort and delete and insert rows, form fields get all messed up. The image below shows you how to modify the settings for this rule.I don't like this method. Go to Home > Conditional Formatting > Icon Sets > More Rules Create a drop-down list with the options 1,0,-1 Select the cells you want to use for the check boxes The simple Task Tracker Template shows an example of this: Go to Home > Conditional Formatting > Data Bars > More Rules Select the cells in the % Complete column Conditional formatting comes in handy yet again: Now you'll learn how to do it. With those cells still selected, go to Home > Conditional Formatting > Manage Rules and find the rule you just created and edit it to create a custom icon set with the setting shown in the following image.In some of the examples above, you've already seen progress bars in the "% Complete" column. Progress Bar via SPARKLINE in Google SheetsThe new SPARKLINE function allows you to create a progress bar in Google Sheets very easily. That's a pretty old trick for Excel users, but it's something that will work in Google Sheets, too. You can change the color of the bar by just changing the font color. In cell A1 enter the % Complete and then in the cell to the right of it you can use the formula =REPT("█",ROUND(A1*10,0)). ![]() Create a Gantt ChartAlthough 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. 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 tab and click on the Filter button. Autofilter and SortingThe little arrows that show up in the header of an Excel table or list are a result of turning on the Filter Button feature. You can do that with a simple conditional formatting rule shown in the example below.You can see an example of this in the Homework To Do List shown at the very top of this article. Highlighting Overdue DatesWhen you have a Due Date, you may want to highlight the date when it is overdue. This will allow you to select either a checkmark or the current date as shown in the image below.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. To do this with data validation, create a list somewhere in the worksheet with the first cell containing a check mark unicode character ✔ and the next cell containing the formula =TODAY().Then, use data validation to create a drop-down list referencing those two cells. Drop-Down with Current DateUpdate - I recently created a new wedding checklist where a user requested the ability to enter either a checkmark or the current date. Visit my Task List Templates page to find an example that uses a chart object and try the free Gantt Chart Template to see the conditional formatting technique in action.
0 Comments
Leave a Reply. |
AuthorFelicia ArchivesCategories |