Current Version: Version 3.0
Compatibility: PC Only | Excel 2007 or later (including Office 365 Desktop) | Works on 32-bit or 64-bit
License: 1 per user

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 set up in this instructional post:

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

For Updates

If you are updating to a new version of the Tickmark add-in, you simply need to replace your current Tickmark.xlam file with the new one while your Excel Application is closed.

You’ll notice that your custom ticks (if you have any) will be gone when you initially start Excel. Simply click any button on the Tickmark Add-in and it will automatically go into your Tickmark Backup file and pull in your custom ticks and any default settings you may have changed. After this process is complete, you will see a message display stating your settings were restored into the updated add-in file.

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 Excel 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

01. Investigate Settings

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.

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

02. Add Ticks Settings

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.

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

Investigate Buttons

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 oftentimes 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.

Ghost Finder

The Ghost Finder allows you to search for cells that have values with a format consisting of white font and white background. This is obviously a practice that is not recommended yet many Excel users do this in their spreadsheets. These “Ghost” cells can end up causing major issues/confusion when forgotten about or not known by other users.

To use select a range you wish to search or leave a single cell selected to search the entire worksheet. A dialog will pop up, allowing you to tweak search settings.

Data Organizer

What Is The 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

The Data Organizer will go through and format each one of these categories in a particular way so you can easily see how the data in your cells are populating.

Determine Which Formats To Use

Determine Which Sheet To Format

Once you click the Data Organizer button, you will be prompted to decide if you would like to run it on the current ActiveSheet or if you would like it to make a copy of the ActiveSheet and apply formatting to the copy. The applied formatting is not recorded in Excel’s history, so it cannot be undone.

The Data Organizer will only apply formatting based on the selected cell range. If only one cell is selected, it will apply formatting to the entire sheet.

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!

Convert To Text/Number

• To Number – This button allows you to convert selected cells from text to numeric values
• To Text – This button allows you to convert selected cells from numeric to text

Swap Cells

The Swap Cells button allows you to swap the values/formulas of two selected ranges. This can be very useful with reordering items in a list that can’t necessarily be sorted or reorganizing the layout of your spreadsheet.

Unhide Tabs & More!

This is a split button with quite a few functionalities relating to hiding/revealing data in your spreadsheet.

Main Button – Unhide all hidden and very hidden tabs in your spreadsheet.

Group Hidden Rows – Loop through all hidden rows on the selected sheet(s) and add outline groupings to them (leaves them collapsed after finished)

Group Hidden Columns – Loop through all hidden columns on the selected sheet(s) and add outline groupings to them (leaves them collapsed after finished)

Unhide All Rows/Columns – Provides various options to unhide rows and columns

Remove All Groupings – Provides various options to remove all outline groupings

Expand All Groups – Provides various options to expand all outline groupings

Collapse All Groups – Provides various options to collapse all outline groupings

Outside References

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.

Mark-Up Buttons

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.

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.

Box/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.

Insert Comment Bubbles

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!

Insert Legend Callout

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, let’s 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

Furthermore, the Legend button can read the format of a selected cell and automatically color the box accordingly. It first checks for a cell fill color and then a text color. If the cell appears to have a white fill and black font, the default grey color will be used.

Add Tab

Insert Table Of Contents Tab

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.

Also in the 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.

Insert Instructions Tab

This button will insert a generic instructions tab at the beginning of your spreadsheet to provide guidance to your user on how to operate the spreadsheet.

Available Settings

Within the Investigate Settings userform, you have a couple of settings you can modify:

Inserting Tick Buttons

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.

Symbol/Letter Ticks

• Minus Sign – Subtraction occurring
• Red X – Wrong, Error, etc…
• Question mark – Follow-up Questions

• Addition Sign – Addition occurring
• Yellow Caution – Something looks weird
• Green Sigma – Indicate where figures foot

• 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

More Tick Features

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 the 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 of 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 of 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.

For more detailed instructions and best practices, watch the below tutorial video.

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. 

If you need to link ticks across two different spreadsheet tabs, select one tickmark and you will be prompted to select the second one within your workbook (see below). After you select the second tick, click the Link button and the hyperlink will be created.

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, simply 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.

Chris Newman

Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.