The House That A Girl Called Johnny Built: Part 5: Tracking Daily XP in your Runescape Excel Hiscores Tracker

Welcome to my series on how to create a Runescape Hiscores Tracker in Microsoft Office Excel!

Yesterday, I hunted penguins, then spent my points on Summoning XP. Ths involved teleporting all over Gielinor. I also converted some charms into pouches, before chipping away at some gems with my chisel. In the middle of all of this, I caught an impling or two, as they flew by me. Finally I couldn't put off the dreaded thing hanging over me in-game. I had to have another attempt at killing Vanstrom Klause.

I tooled up in Edgeville Bank, where someone optimistically asked if anyone had 97 Construction to assist him with his effigy. Yes, I did. I helped him out. Then it was off to Darkmeyer, where I accidentally hit a vyrewatch, before fleeing to the safety of the bank. I fought Vanstrom with range. He turned into mist and I threw the Holy Water. But he wasn't lured onto it and it missed. He killed me.

Once the dust had settled and the nerves salved, the next question was how much XP did all of that gain me? The answer was in my Excel Runescape Hiscores Tracker. Glancing down the XP Gained column retold the story of my in-game activities in cold, hard facts. I'd netted 128,921 total XP, split up across eight skills.

This is the table that I'll guide you through creating for yourself.

Runescape Excel Hiscores Tracker: The Story So Far

This guide will be written in the assumption that your workbook looks exactly like mine. For that to be the case, then you will have had to follow the previous entries. All of the data referred to and the tables created are outlined in them.

Part 1: How to Make a Runescape Hiscores Tracker in Microsoft Office Excel - Collecting data ready to feed your tables and charts.

Part 2: Averages Table in your Runescape Excel Hiscores Tracker - Creating the first table, which worked out your XP averages from highest to lowest skill. There was also a bonus section answering some formatting questions, mostly from those new to Excel.

Part 3: Calculating XP to Next Level in your Runescape Excel Hiscores Tracker - Creating a table to calculate the XP needed until your next level.

Part 4: Adding Activity Logs and More to your Runescape Excel Hiscores Tracker - Using the contents of websites to add data into Excel, notably a couple of ways to add your Runescape Activity Logs.

Let's get on with creating a daily XP tracker!

Building Your Table to Track Daily Runescape Experience

I want to chart my daily XP on the same page as I have my XP to Next Level and Hiscore Averages tables. There's still enough room there to see it all in one place. Before I can do that, I need to move those tables along. Skip this part, if you want to place your table in a different worksheet. Part four described how to do that.

Open up the worksheet with your tables in it.
Highlight the first column by clicking on the 'A'.
Right-click anywhere in that column.
Select 'insert' from the pull-down menu.
Repeat that until the 'Skill' column of the XP to Next Level table is in column N.
Click in cell B1,then drag your cursor to cell L1.
Colour and format them to match your other headers.
In cell B1, type 'Skill'.
In cell C1, type 'Previous Rank'.
In cell D1, type 'Previous Level'.
In cell E1, type 'Previous Total XP'.
In cell F1, type 'Current Rank'.
In cell G1, type 'Rank Change'.
In cell H1, type 'Current Level'.
In cell I1, type 'Level Gained'.
In cell J1, type 'Current Total XP'.
In cell K1, type 'XP Gained'.
In cell L1, type 'XP to Next Level'.
Widen any column necessary to display your headers.

Yes, this is going to be a huge table! But three of those columns will likely end up hidden by the end of it. My worksheet currently looks like this:

Please click the image for a full-sized view.

Actually, I'm going to delete the background for a moment, just to make it easier to work with. Just skip this step, if you're fine.

Select the 'Page Layout' tab from the top toolbar.
Click 'Delete Background'.
If you've removed the gridlines, select the 'View' tab from the top toolbar.
Tick the box beside 'Gridlines', in the 'Show/Hide' category.

I can now see what I'm doing, so it's time to add some formulas.

Inserting Existing Data into your Runescape Daily XP Tracker Table

This is a big table, but you already know how to populate most of those columns. The data is in your datasheets and it's simply a case of putting in the address to display them.

In cell B2, copy and paste the following formula: =Data!B2 (NB: Change Data to whatever you called your data worksheet.)
Drag the formula down to cell B26.
In cell B29, type Total. (Rows 27 and 28 were hidden under the averages table. If you didn't hide them, then all that I say for row 29 replace with row 27.)
In cell F2, copy and paste the following formula: =Data!G2
Drag the formula down to cell F26.
In cell F29 (or F27), copy and paste the following formula: = Data!G1.
In cell H2, copy and paste the following formula: =Data!C2
Drag the formula down to cell H26.
In cell H29 (or H27), copy and paste the following formula: =Data!C1
In cell J2, copy and paste the following formula: =Data!F2
Drag the formula down to cell J26.
In cell J29 (or J27), copy and paste the following formula: =Data!F1
In cell L2, copy and paste the following formula: =Data!J2
Drag the formula down to cell L26.
In cell L29 (or L27), copy and paste the following formula: =SUM(L2:L26)

My table now looks like this:

Please click on the image for a full-sized view.

Basic Mathematics in Excel Spreadsheets

The mathematically minded amongst you will now be flashing a little smile, when you learn that some of these columns are going to include just basic subtraction.

In cell G2, copy and paste the following formula: =C2 - F2
Drag the formula all the way down to the end of your table. (It will all be in minus numbers at the moment, because we haven't got anything in column C2.)
In cell I2, copy and paste the following formula: =H2 - D2
Drag the formula all the way down to the end of your table.
In cell K2, copy and paste the following formula: =J2 - E2
Drag the formula all the way down to the end of your table.

That really was taking the number in one cell and subtracting it from the number in another.

This is how my table looks now:

Please click on the image for a full-sized view.

Inserting the Previous Day's Data into an Excel Worksheet

Our Runescape Daily XP Tracker is now poised and ready, but for three important columns of data. There are three ways of doing this, so it's a matter of personal choice which one you choose.

Option One: Manually Inserting the Data

Highlight cells H2 to H29 (H27).
Right-click and select 'copy'.
Open your XP Tracker worksheet.
Click cell D2.
Right-click and select 'paste special'.
Select 'Values'.
Press 'Ok'.

Repeat for C2, copying the values from F2 to F29 (F27).
Repeat for E2, copying the values from J2 to J29 (F27).

This is now ready to use, give or take a little colouring in. The values in columns C, D and E won't change until you repeat the above steps. The rest of it will continue to automatically update. As you gain XP and levels, this will be recorded in your table.

Please click the image for a full-sized view.

There is an obvious limitation here. You would have to remember to transfer your data before you started playing. The second two options will do it automatically.

Option two: Stealing the Data from Elsewhere.

The internet is full of websites which track your Runescape Hiscores data on-line. Part four was all about taking data off the internet and inserting it into your Excel spreadsheet.

Your latest quest is to find a website that tracks your daily history. One such site is Runetrack. You could set up an account there and then insert the information off that webpage into your Excel data sheet.

Once you have the data, then it's merely a case of asking your table to automatically update. You can do that by inserting the formula: =[nameofyourdataworksheet]![celltoread]

The benefits are obvious. The whole table again automatically updates, so if you forget to tranfer your data, it doesn't matter. Your previous stats update, whenever the previous stats on the website update.

However, this isn't really fair on the individuals, like the coders and owners of sites like Runetrack. They've put in the hard work and you're reaping the benefits daily, without going near their site again.

Also should Runetrack's Sword Kill11 (or one of his counterparts from another Runescape hiscores tracking site) decide to close down, your Excel tracker will simply stop working.

Option three is to use a macro to automatically copy your data at a set time every day. I'll be coming to macros in a later guide, so I'll see you there for this one.

Finishing off your Runescape Daily XP Tracker in Excel

Once you have the data in there, and it's all working nicely, then all that remains is to make it pretty.

Highlight columns C, D and E by clicking the letter at the top.
Right-click anywhere in the highlighted columns.
Select 'hide'.
Colour and format the remaining table.

Because we have some moving data here, we can spruce it up with some more icon sets. I walked you through this in part three. But I'll show you how I'm going to add some to this table too.

Highlight cells G2 to G29 (G27).
Select 'Conditional Formatting' from the 'Home' tab at the top of your Excel workbook.
Select 'Icon Sets' from the menu.
Select '3 Arrows (Coloured)' from the options.