Here, for example, is what I did yesterday:
I didn't update that sheet. It happens automatically and sits there waiting me for to consult it. It helps my gameplay. In many ways, it directs it. I can use it to find skills that are close to levelling or else would benefit from a little work. Though mostly I'm scrutinising another couple of charts for that.
Again these automatically update without any input from me, once it's all set up. So the question that I'm most frequently asked is how did I set it up?
Excel Hiscores Tracker: Finding the Data
Those charts look very pretty and compact, but they are just the front face. It's the make up or decoration, that causes it to look so pretty. But all that any one of those charts is doing is just presenting data collected from elsewhere.
What you can't see is the hidden worksheet in the background. Let me reveal what's doing all of the real work:
Suddenly it's not so pretty. But then neither is the average brain, which doesn't stop it doing all of the good stuff.
Let's make an Excel Hiscores Tracker!
Runescape Hiscore Tracker: Creating the Data Sheet
I'm using Microsoft Office Excel 2007. Your own version might be different, so please bear that in mind as you follow my instructions. (With many thanks to Paul P, who solved an issue with a query formula that was doing my head in.)
- Open up Excel. You'll find that you have a workbook with three tabs in front of you. Look at the bottom to find that you are on Sheet 1.
- Right-click where it says 'sheet 1'. Select 'rename' from the pull down menu. Call it 'Stats' or 'Data' or whatever you want to call a sheet that will ultimately be hidden from view.
- Click on the A, so that the whole column is highlighted.
- Right-click anywhere in that column, then select 'format cells' from the menu.
- On the category list, select 'text', then click 'ok'.
- Select the Data tab from the top toolbar. Select 'From Web' from the Data toolbar.
- Click on the URL for my hiscores, then replace Merch Gwyar with your own player name. Paste that URL into the 'From Web' pop up browser.
- Click on the black arrow inside the box.
- Press the 'Import' button at the bottom of the box.
- Click ok, when you are asked if you want to insert this into the existing worksheet
- Gasp in awe and wonder, as your first column of data cascades into your worksheet. This will never have to be updated again. Jagex deal with all of that.
162663,90,5439918 - Hiscore rank is 162,663; level is 90; xp to date is 5,439,918. 134310,90,5448609 - Hiscore rank is 134,310; level is 90; xp to date is 5,448,609.
Unfortunately, Excel doesn't know this until we tell it. It's time to programme in some formulas.
Runescape Hiscore Tracker: Formulas For the Win!
One major fail about the Jagex hiscores lite is that it doesn't tell us the name of the skills. We'll have to write those in ourselves.
- In the same sheet, click on square B1 (or anywhere really, as you can tell from my messy data sheet up above. I randomly started on B8 mostly because that's where my cursor landed first on the day that I did this).
- Copy the following list and paste it into B1:
- Copy and paste the following formula into square C1:
- Click on C1 and you'll see a tiny box in the right hand bottom corner.
- Click that tiny box and drag your cursor down to square C26.
- Rejoice in the fact that 99% of your levels are suddenly revealed.
- Click on D1 and repeat the above in that column, but with this formula to start:
- Click on E1 and repeat the above in that column, but with this formula to start:
- Go back to C1 and delete the formula from that cell only. Replace it with: =SUM(C2:C26)
Tomorrow, I'll walk you through making your first chart and adding more data.
More in this series:
Part 2: Averages Table in your Runescape Excel Hiscores Tracker.