Take Attendance in Excel (and so much more)

In training, I need to take attendance. I can have a list in Excel that looks like the one below. But, by using Conditional Formatting, I can get a better looking list that is easy to filter/sort to what I need. Here’s how I do that.

The first step is to change how I use the “Attended” column. Instead of x’s, I need to enter 1’s and 0’s. Excel is designed around the use of numbers and this will only work if I do the same. In this instance, I use 1’s for those who have attended and 0’s for those who were absent.

1. Select the column with the values to format. In my example, I selected column D.
2. On the Home tab, Styles group, click Conditional Formatting and select the 3 Symbols (Uncircled) under the Indicators options. (It's the green check, yellow explanation point and red X.)

You will see the icon and the value listed in the column together. Now to hide the value.

1. Click Conditional Formatting again and this time select Manage Rules… at the bottom of the gallery to open the dialog box
2. Click the Edit Rule… button.
3. Select the Show Icon Only check box. Click OK to finish the rule, and OK apply.

The bonus is now you can filter/sort on this column. Turn on the Filter (on the Data tab) to view the filter arrows on each column heading. Click the filter for your column, move to Filter by Color and select to view only those who attended or get a list of those who were absent.