SPREADSHEETS - 03.07.2018

Keep track of your month-end checklist using Icon Sets

Accounting close checklist

You have a month end management accounting checklist in Excel which includes tasks such as reconciling all bank accounts, ensuring all sales invoices have been raised, checking accounts payable to supplier statements, posting depreciation, etc. Your list starts in cell A5 and extends down the worksheet for a number of rows. You would like to have a tick (checkmark) appear in column B for any tasks that are complete.

Checkmarks in Excel

Usually checkmarks are quite tricky to remember how to do in Excel.

Usual method. By using a capital P and formatting the cell with the Wingdings 2 font, you can get a tick in a cell. But unless you use checkmarks frequently, it’s difficult to remember which letter and which font is the correct checkmark combination. But there is an easier way to remember and it’s simple to create.

Icon Sets

Let’s say you would like a green tick for a task that’s been completed and an empty circle for those that aren’t. The reason for an empty circle is that when the checklist is printed, the empty circle provides a place to tick with a pen when the task is completed. We’re also going to add a yellow dash for items that should remain on the checklist but aren’t applicable to the current month.

Step 1. Add a 0 in column B next to each of your list items. When the first task is completed, replace the 0 in cell B5 with a 1. For any item that doesn’t need to be completed that month, replace the 0 with a -1.

Step 2. Select the range of 1s, 0s and -1s in column B. Go to Home ; Conditional Formatting ; Icon Sets . Out of the 20 built-in icon sets available, there are about ten that offer three icons and are appropriate for this task. To minimise the amount of set-up work, you could choose the set containing a green tick, yellow exclamation mark and red X.

Step 3. You will then see a mix of checkmarks, exclamation marks and X’s appear between each task in column A and the numbers in column B. But you don’t want the numbers, so go to Home ; Conditional Formatting ; Manage Rules and choose the Icon Set in the Manage Rules window. Click on the Edit Rule button.

Step 4. Tick the Show Icon Only box. Then for the first box under Value , type 1 and for the second box under Value , type 0. Change the Type boxes from Percentage to Number .

Step 5. Still in the Edit Rule window, open the second drop-down under Icon (the yellow exclamation mark) and choose the empty black circle. In the third drop down under Icon (the red X) select a yellow dash. Click OK to exit the editing window and OK again to close the form.

You should now see a green tick against the completed tasks, an empty circle for the uncompleted ones and a yellow dash for the tasks that aren’t relevant to this month.

Tip 1. The icons can be quite small in the default size so you might want to increase the font size to 18.

Tip 2. To use the worksheet, type a 1 in column B for any item that’s complete. The empty circle will change to a green checkmark.