BACKGROUND IMAGE: iSTOCK/GETTY IMAGES
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.
To start, choose a couple of cells and enter the number "1," as shown in 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.
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:
This format will display any positive number (such as 1) as the letter P. Negative, Zero, Blank and Text cells will appear blank.
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.
To add the amounts associated with each record that has a tick mark, use =SUMPRODUCT as shown in 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.