Current Version: Version 1.3
Compatibility: PC Only | Excel 2007 - 2016 | Works on 32-bit or 64-bit
License: Up To 3 of your personal devices per single license

Overview of Help Topics

  • Installing This Add-in Inside Excel
  • Entering Your License Code
  • Does Your Tickmark Tab Keep Disappearing?
  • Tickmark Add-in Settings
  • Investigate Buttons
  • Add Ticks Buttons

Installing This Add-in Inside Excel

The Tickmark Excel add-in installs just like any normal Excel add-in (.xlam) file. If this is your first time installing an Excel add-in you can view step-by-step instructions on how to get the file setup in this instructional post:

How to Install and Uninstall A VBA Add-in File For Microsoft Office

Entering Your License Code

  1. Click the Settings Dialog Launcher button
  2. Select the Key icon near the top right of the Settings Userform
  3. Enter in the License Code provided to you via your download email
  4. Read the End User License Agreement and click the I Accept & Activate button

Does Your Tickmark Tab Keep Disappearing?

Due to a security update released by Microsoft in July 2016, add-in files you download from the internet are "blocked" by default. To unblock the file or create a "safe" folder location for your add-ins, please read my article to keep your add-ins showing in your PowerPoint Ribbon (it's a pain, but is fast and easy to do!).

Prevent Microsoft Office Add-ins From Disappearing From The Ribbon Interface

Tickmark Add-in Settings

Investigate Settings (click to enlarge)

The settings userform for the Investigate section of the Tickmark add-in can be opened by clicking the dialog launcher button in the bottom right-hand corner of the group.

Launch Investigate Settings.png

Here is an overview of all the settings available to customize to your preference (click image to enlarge)

Add Ticks Settings (click to enlarge)

The settings userform for the Add Ticks section of the Tickmark add-in can be opened by clicking the dialog launcher button in the bottom right-hand corner of the group.

Launch Add Ticks Settings.png

Here is an overview of all the settings available to customize to your preference (click image to enlarge)

Investigate Buttons

[a] Plug Finder

Adding a plug can be common practice if you need your number to visually foot or if you have a one-off reason to modify a calculation.  There are typically two ways to make such adjustments (1) add the plug off to the side of your analysis and modify your formula to point to the "Plug Cell" or (2) just add the number directly into the formula itself.  

Plugs directly inside formulas can be detrimental because they can be easily forgotten about, they are not visible, and are often times not documented as to their reason for being there.

After running the Plug Finder on a spreadsheet tab, you instantly get a list of cells containing plugged numbers within formulas. These cells are also marked with a marker of your choosing (dot, circle, or box) in order to bring attention to the cells that were possibly tampered with. The markers are all shape objects, so they will not affect any formatting on your spreadsheets.

[b] Data Organizer

Data Organizer

With the Tickmark Add-in's Data Organizer button you can easily decipher whether the data on a spreadsheet falls into one of the following categories:

  • Hardcoded Number
  • Formula
  • Off-Sheet Referencing Formula
  • Formula with a Plug

Determine Which Formats To Use

Add A Legend For Your Organized Data

Within the Data Organizer drop-down menu, you have the option of adding either a vertical or horizontal format legend to your spreadsheet. The legend will automatically pull in the text/fill colors from your Data Organizer settings. 

Adding this legend will make it very easy for others to understand how your spreadsheet model is setup!

[c] Unhide All

This button unhides all spreadsheet tabs within your active workbook.

[d] Outside Refs

This button will provide you a list of all tab names (within your ActiveWorkbook) that are used in the ActiveSheet's cell formulas. The list provided to you essentially shows you which tabs are feeding information into your spreadsheet.

[e] Scroll Up

This button goes through every worksheet of the ActiveWorkbook and scrolls to cell A1 (Top, Left). This is a great feature to run before sending your model off to someone or when you first open up a model to ensure you are starting off at the very top of every spreadsheet you dig into.

[f] Arrow Callout

This button will create an arrow shape based on your cell selection. The first cell you select will be the starting point of the arrow and you can continue to drag vertically or horizontally to determine the direction and size of the arrow.

Once you have selected a few cells, simply click the Arrow Callout button on the Tickmark add-in ribbon and your arrow will be instantly created.

You can predetermine the line color and thickness of your arrows by going into the Investigate Settings and changing the values within the Arrow Settings section.

[g] Box  and Circle Callouts

Places a box or circle around your currently selected range of cells. This is a great way to bring attention to specific parts of your data that need attention. Through the Tickmark Settings you can even determine the default border color and thickness to make your boxes and circles align with your preferences.

[h] Comment

Add a comment bubble shape on top of the ActiveCell. You determine the direction of the spike. You can also go into the Tickmark settings and set your own default color formats and shape size!

[i] Legend

Many spreadsheet users love to color different sections of their cells to highlight a meaning or difference. This button helps you create a legend to tell users what your different spreadsheet formats mean.

For Example, lets say the following formats are applied to your spreadsheets:

  1. Gray = User Inputs
  2. Blue = Formulas Calculations
  3. Green = Data from the Accounting Department

To create a three-part legend you would:

  1. Select three cells vertically
  2. Click the Legend Button
  3. Color the boxes to match your formats
  4. Write a description to go with your formats

[j] Contents

This button allows you to insert a Table of Contents tab with hyperlinks to each one of the spreadsheet tabs in your workbook. After clicking the contents button, you will be prompted to determine how many columns you wish your table of contents to have. After you enter a number and click OK, a tab named Contents will be created with a table of contents section in it.

Via the Contents drop-down menu, you can also add "Back" buttons to each tab so you can easily flip back and forth between all the spreadsheets. There is also an option to remove the back buttons if you decide later on you do not want them.

Add Ticks Buttons

[a] Incremental Ticks

These tickmark icons will continue to progress from 1-100 or from A-Z as you add them to your spreadsheet. You can modify their color and size in the Add Tick Settings Userform.

[b] Symbol Ticks

[c] Letter Ticks

  • OK - Figure Checks out
  • V - There was a variance found
  • GL - Ties to General Ledger
  • PY - Ties with Prior Year figure
  • CY - Ties with Current Year figure
  • TB - Ties with the Trial Balance
  • M - Material discrepancy found
  • IM - Immaterial discrepancy found
  • NA - Not Applicable

[d] More

Within the More drop-down list you can do the following:

  1. Select Every Tick - Select a tickmark and click this button to select every tickmark on the spreadsheet that is the same species. This is great for changing colors or size of a particular tickmark type
  2. Remove All Ticks - Deletes all tickmarks within the ActiveSheet