fbpx
Search
Close this search box.

How-to create an elegant, fun & useful Excel Tracker – Step by Step Tutorial

Share

Facebook
Twitter
LinkedIn

Do you want to create a simple, elegant and useful tracker using Excel? You can make trackers with features like tables, data validation rules and conditional formatting. In this page, I will explain the process for creating an Excel tracker. 

Demo of the Excel tracker we will be creating...

Excel tracker - quick demo

Download the tracker demoed here

Click here to download the Excel Tracker explained in this page. Use it to understand the process or change it to suit your needs.

Purpose of the tracker

You can't track what you don't know...

Let’s say you want to build a tracker to keep track of the visitors to the corporate office of Big Large Inc. You need to capture below details for compliance purpose.

  • Name
  • Type of person (Full time, Temporary or Visitor)
  • Department visiting (one of the 7 departments if the person is Full time or Temporary)
  • SOP status (Not started, read, read  & signed)

It is a fairly straight forward tracker, except for this bit:

If the person is a visitor, then no need to get “department”. 

Thanks to Colette, who emailed me with a description of this template. 

Steps for creating Excel tracker

Step 1: Create a table with below columns.

Just type the headings, select them and press CTRL+T. 

step 1 - create a table for excel tracker

Learn more about Excel Tables

Excel tables can help you build trackers, plans, lists or data. They make data analysis, charting or pivoting a breeze too. If you are new to this powerful feature, check out this getting started with tables guide.

Step 2: Set up data validation rules

This is the important bit. We don’t want garbage data in our tracker. So set up simple rules on each column.

Data validation rule for Type column:

This is rather simple. Just select the Type column, go to Data > Validation and set up the validation type as “List”. Type out the possible values – Full time, Temporary, Visitor and click ok. Here is a screenshot of the process.

data validation rules for type column

Learn more about Data Validation Drop Down List

Data validation makes it easy to set up a list of allowed values for a cell or table column. Learn more about setting up data validation list.

Data validation rule for Department column:

Now this is a tricky one. We want to show a list of departments if type  = Full time or Temp. Else we want to leave it blank.

Start by setting up a list of departments in a range and give it a name like lstDepts 

List of departments

Now, we will create a dynamic named range that will return either lstDepts or blank depending on what is picked in [@Type] (the current row’s type value).

We can use the trusty IF formula for this.

=IF(Table1[@Type]<>"Visitor",lstDepts,"NA")

Create availableDepts named range (Formulas > Define Name) like this:

step 2 - conditional named range for departments

Once the named range is created, use it as List for data validation on the Department column as shown below.

Data validation rule for department column

Data validation rule for “SOP Status” column:

This is similar to the rule for “Type” column.

Step 3: Highlight what matters with conditional formatting

Let’s say Big Large Inc. is fussy about the SOP status and want to quickly monitor anyone not starting the SOP process or half-done it.

You can use conditional formatting to easily spot these. 

Just set up rules to highlight the Status column based on what matters to you.

For example, if you want to highlight all “Read” statuses, you can use below rule.

conditional formatting rule to highlight "Read" values

Here are few more rules. 

more CF rules for excel tracker

That is all. Our tracker is ready. Go ahead and roll it out.

Learn more about Conditional Formatting

Conditional formatting is a great way to keep an eye on important bits of information. You can set up rules to highlight missed deadlines, top 5 values or values meeting a criteria. Getting started with conditional formatting.

Video - How-to create an Excel tracker

If you are still fuzzy over the details of how to create a tracker in Excel (or you just want an earful of my sweet voice) you can watch below video. I explain the process with greater detail on the data validation rules. 

You can also watch this video on Chandoo.org YouTube channel.

Play Video

Download Excel tracker - Demo file

Click here to download the Excel Tracker explained in this page. Use it to understand the process or change it to suit your needs.

Tips for creating AWESOME trackers

Trackers are a big part of spreadsheet life. Here are my top tips for creating long-lasting, friendly and useful trackers.

  • Use Tables for inputs:  Tables are natural for keeping data like this. So use the liberally.
  • Apply validation rules: to prevent unwanted data from getting in. You can use data validation to allow lists, valid dates or even complex conditions. See this demo.

Either or condition in data validation

Either or condition in data validation

  • For large trackers, create a settings tab: If you have a large tracker with several columns and rules, create a separate worksheet to maintain the rule data (like validation lists, boundaries for valid values etc.)
  • Apply conditional formats: People like to know when their inputs are right. So use conditional formatting features like icons to highlight (in)valid data entries. See this demo.

using conditional formatting to highlight valid and invalid data entries

 

Awesome data entry forms with conditional formatting + data validation

  • Consider Excel Forms instead of shared workbooks: If you need multiple people to access the tracker to update or input data, consider using Excel Forms. This online features works great for collecting data in a secure manner. Click here for more info.

 

More Excel trackers for you

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

3 Responses to “How-to create an elegant, fun & useful Excel Tracker – Step by Step Tutorial”

  1. Malani says:

    Hi Chandoo,
    I am responsible for tracking when church reports are submitted on time or not and the variations from the due date for submission.
    Here is the Scenario;
    The due date for the submission of monthly reports is on the 5th of each month. and I would like to know how many reports have been submitted on time (i.e, those that have been submitted on or before the due date) I would also want to track those reports that have been submitted after the due date has passed.
    How can I create such a tracker?

  2. Carl says:

    Hi Chandoo,
    I am a member of your excel school.
    I was trying to create SOP Tracker I follow all your steps but I keep this error below.

    The list source must be a delimited list, or a reference to a single row or cell.

    I try looking on YouTube for answer but no luck.

    can you help on this?

    thanks
    Carl.

  3. Rakesh says:

    Dear Mr. Chando,

    Rakesh, I'm working in a private company in the UAE. Recently, I'm struggling to get more details about the staff sick, annual, unpaid, and leaves. I would like to get a tracker in excel. Could you please help me in this situation?

    I also watching your videos in YouTube. i hope you can help me on this situation.

Leave a Reply