How to build an excel tracker
How to build an excel tracker
Embedded Files
Feel free to scroll through the page as you would normally, or use the table of contents below to jump straight to what you want to know.
Why build a tracker?
Why build a tracker?
The main point of the tracker is to keep on top of all applications you have sent out. This allows you to know where you have applied, what roles you have applied for, and to keep on top of what you need to do for different companies to ensure you stay organised. Keeping on top of 5 applications might be manageable, but keeping on top of 50 is less so!
This tracker is something you can create when you start sending off applications, and then making it a habit of updating the document every time you make an application, or receive an update email, will mean you are always on track of this.
The aim is to make it as simple as possible for you to maintain, in order to keep you motivated to update it regularly, while also keeping it full of information.
This page will have 2 guides to 2 different trackers - one simple one and one more complex one so you can tailor it to your needs and to how you want it to look. If you would like any help with any of the steps below, please get in touch via the seed@sheffield.ac.uk email and we can help you out! There are also a load of different Youtube videos that can take you through the steps.
Something to note is formatting on Google Sheets and Microsoft Excel are different - and this guide approaches it on Microsoft Word.
What to think about before starting?
What to think about before starting?
The main thing to consider is what you want this tracker to have. There's already so much information that needs to be collated, so it's important to know what is important to you and what you want to keep on top of. Some examples for potential columns are as follows:
Name of the company
Name of the role
Name of the industry you will be working in (e.g finance, investment, sales) - This could be useful if you wanted to make a graph of the different industries you have applied to
The salary expected
The location of the role
The deadline for applications / The date you applied to the role
The link to the page with the job description
The status of your application (Applied, Online Tests, Online Interview, Assessment Centre, Job Offer & Rejection being the main ones)
A column that lists your next steps, what you need to do and the deadline you need to do it by (e.g Online test - complete by MM/YY)
It helps to have an idea of what you want the tracker to look like before making it. Not all of these will need to be on everyone's trackers.
Making a simple tracker
Making a simple tracker
In order to make the easy tracker attached, you can complete this in a couple steps.
1) In Row 2, write down the columns you wish to input into the tracker. For this one, we will keep it simple and do company name, role title, location of role, current stage and date applied.
2) In Row 1 above, `merge & centre` all of the cells above the headers and write the title "Tracker"
3) If you highlight the current table, and then also drag down below the table, you can then install borders around the cells to help keep your table organised. You can do this by going Home -> Font -> Borders -> Whatever borders you want, (Shortcut Alt + h + b + whatever you want)
4) Use font sizes, header sizes, bold, colour & different borders to add variation to the table and make it as you wish.
Making a more advanced tracker
Making a more advanced tracker
The above tracker works if all you need is something simple to help keep ahold of your applications. However, if you want to slightly level it up, there are some steps below that you can take.
Using the table function
If you are going to be adding a lot of applications, it can help to be able to filter the data and to order it. These are doable through the table function. Instead of doing step 3 above, instead do the following,
3) Highlight your columns from Row 2 down, including the data as well. Then press Ctrl + T to make it a table in excel (Make sure to check the `my table has headers` box).
4) Click on the table, and then go to `Table Design`, which allows you to change the colour of the table, as well as removing the filter buttons (Table Design -> Table Style Functions -> uncheck Filter Button).
[Find here what happens when you follow the first 2 steps of easy tracker then table as above]
Using If Statements to automate the columns
An If statement can help to automate the tracker, for example you can make it so that if the an application deadline is within 3 days of now, you can turn the cell red to make it clearer to apply.
You can also use if statements to return values, for example you can make it so that if you have a `status of applications` column, then you can create an `actions` column that has a value that fills in based on whatever is in the status column.
The general form for an if statement is =IF([Cell] = "[Text Value]", "[Text if it is true]", "[Text if it is false]". To nest if statements into each other, in the [text if it is false] section, you can then write another IF and open another set of brackets for every single alternative. There is an example of this formula in the tracker below, where you can also have a go.
[Use this tracker to show you how these help]
Using Graphs to portray your applications.
Finally, if you want to visualise your applications, you can use excel graphs to visualise pretty much anything you wish. This is shown in the tracker below.
The general trick for these is to create a pivot table in another tab with a column of data, for example the role you applied for. And then you can create a graph based off the pivot table data, which will update whenever you add new data into the tracker (assuming you press Refresh Pivot).
In the example below, as well as including graphs and the pivot tables, we have also included a drop down menu for the application status to ensure our if statements from above will never give us an error. This can also be done on microsoft word under "Data Validation".
The Employability Opportunities Fund.
The Employability Opportunities Fund.
All undergraduate bursary holders can apply for up to £300 towards employability opportunities like interviews, work shadowing, training courses and assessment centres. We can reimburse expenses such as travel costs, professional clothing or technical equipment.
Further information about eligibility and how to apply is on the Discover Your Future webpage. If you’ve got any questions, please contact dyf@sheffield.ac.uk.
Still feel a bit confused? Think this page is missing something? Contact us at seed@sheffield.ac.uk, and we'll be happy to help.
Page updated
Report abuse