Essential Guide

Ultimate Excel finance guide

A comprehensive collection of articles, videos and more, hand-picked by our editors
Q

Adding tick marks in Excel

Microsoft Excel consultant Bill Jelen, AKA MrExcel, shows readers how to add tick marks in Excel step by step.

FROM THE ESSENTIAL GUIDE:

Ultimate Excel finance guide

+ Show More

Accountants and auditors frequently have to add tick marks to rows in Microsoft Excel worksheets. Whether you have to add tick marks in Excel to mark cleared checks or tick marks to indicate which records you have to audit, this article shows one easy way to proceed.

Check register
Figure 1

To start, choose a couple of cells and enter the number "1," as shown in Figure 2.

modified check register
Figure 2

Select the range that should contain tick marks (be sure not to include the heading cell). On the Home tab, change the Font to Wingdings 2. Note that there are four common fonts that include symbols, and three of them have similar names. Be careful to choose Wingdings 2 instead of Webdings, Wingdings or Wingdings 3.

Wingdings 2
Figure 3

With the tick marks range still selected, press Ctrl+1 to display the Format Cells dialog box. Click the Number tab across the top. In the Category list box on the left, choose "Custom." In the Type box, enter this custom number format:

"P";;;

This format will display any positive number (such as 1) as the letter P. Negative, Zero, Blank and Text cells will appear blank.

Custom format cells
Figure 4

Once you've finished these steps, simply enter a "1" in the tick mark column and the value will appear as a checkmark.

By now, you might be wondering why you have to go through the hassle of using a custom number format. Wouldn't it be just as easy to use a Capital P and display the column in Wingdings 2?

Unfortunately, it is not. If you have a desktop computer with a numeric keypad, it is far easier to type 1 and press Enter than to press Shift+P. Plus, by using a 1 in the tick mark column, you can do some impressive math.

To count the number of records with tick marks, use =SUM() on the column, as shown in Figure 5.

check register with tick marks
Figure 5

To add the amounts associated with each record that has a tick mark, use =SUMPRODUCT as shown in Figure 6.

=SUMPRODUCT function
Figure 6

This method for displaying tick marks whenever you enter the number 1 allows for easy entry of tick marks in Excel and also for easy math.

About the author:
Bill Jelen, AKA MrExcel, has been a Microsoft Excel consultant for over twenty-five years. Read more of his expert Excel tips and tricks at his website, MrExcel.com.

This was first published in December 2013

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Essential Guide

Ultimate Excel finance guide

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchManufacturingERP

SearchSAP

SearchOracle

SearchBusinessAnalytics

SearchContentManagement

SearchDataManagement

SearchCRM

Close