Current Version: Version 2.0
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
  • Add Custom Tick Buttons
  • Linking Tick Icons

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, horizontally or diagonally 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

Tick Overview.png

[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

  • Green Check - Auditor confirmation
  • Red X - Wrong, Error, etc...
  • Question mark - Follow-up Questions
  • Blue Check - Manager/Lead confirmation
  • Yellow Caution - Something looks weird
  • Green Sigma - Indicate where figures foot

[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
  3. Pause Incremental Ticks - By toggling this button on/off you can pause the increments of the Incremental Tick icons
  4. Custom Tick Manager - This button brings up a userform that will allow you to create/modify/delete custom tick icons (version 2.0 feature)

Add Your Own Custom Tick Icons

As of version 2.0 of the Tickmark Add-in, you have the ability to create up to 30 over your very own tickmark icons! This means any design, color, and size....all inside of Excel.

To upload your own tick icon, you will first need to create your shape inside of Excel. You can do this by either drawing a shape, inserting an Excel Shape, or importing a picture into Excel from an outside source.

Once you have your icon designed, you will need to do the following:

  1. Select your icon
  2. Open the More drop down menu and click the Custom Tick Manager button
  3. Select a [New Custom Tick] placeholder from the Select Tick drop down menu
  4. Give your Tick icon a name and determine how you want the Ribbon button to look on the Excel Ribbon (Mirror, Generic, None)
  5. Click Create

There are also a couple optional options within the Custom Tick Manager that allow you to display the Tick Icon name in the Ribbon interface or add a custom description to the Ribbon icon's Screentip (shown when you hover your mouse over a custom tick in your Excel Ribbon)

After following the above steps, you should see your Tickmark Excel Ribbon change to include your newly created Tick icon. Clicking the custom Tick button will insert that particular Tick icon on your spreadsheet based on which cell you currently have selected.

Custom Tick Icon Settings

Within the Add Tick Settings userform there is a dedicated section to further customize how you want your custom icons to be created on your spreadsheet. These perform exactly how the Abbreviated Tick Settings section performs.

Linking Tick Icons

The Tickmark add-in gives you the ability to directly link to tick icons together. This is done by creating a hyperlink between the two icons based on the location of the nearest spreadsheet cell. Links can be within the same spreadsheet or across different spreadsheets.

Link

To create a hyperlink between two tick icons, select both of the icons you wish to link by holding down the ctrl key and clicking each icon. Then click the Link button and the icons will be linked together. 

Note: if you move the linked Tick icons to a different location you will need to use the Re-Link button to update the hyperlinks.

Re-Link

The Re-Link button with go through your spreadsheet and update all icons that have been linked with the Tickmark add-in to their current locations within your workbook. There is no need to select anything, just click the button.

De-Link

If you wish to remove a link from your Tick icons, simple select one of the Tick Icons and click the De-Link button. The Tickmark add-in will automatically find both linked icons and remove the hyperlink from them.