How-to create an Excel Tracker that is elegant, fun & user-friendly - Tutorial » Chandoo.org (2024)

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

Someone wiseTweet

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:

Step 1: Create a table with below columns.

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

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

Now, we will create adynamic named rangethat 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:

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

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.

Here are few more rules.

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

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.

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

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

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

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.

• Goal tracker
• To-do list template
• Meeting Agenda Template