So far, we've grabbed some data, then made a chart to work out the average of the whole for each skill. In a bonus section to part two, I answered some questions about formatting.
This is written on the assumption that you've been following the series so far. Now let's grab some more data and create a new table. This one will automatically calculate how much XP is needed to your next level in every Runescape skill.
Runescape Excel Hiscores Tracker: Adding More Data
Before we can calculate the XP needed, we need to know the amount that causes you to level up. This is probably the easiest insertion of data for you, because I'm going to provide it!
- Open your Runescape Hiscores Tracker Excel Workbook.
- Select the worksheet with your raw data in it.
- Copy and paste the following in cell H1:
- Copy and paste the following in cell I1:
All done! My data sheet looks like this:
Runescape Hiscores Tracker: Naming Your Ranges
There is a formula coming up that's going to require us to name a range. We'd better do that then.
- Click on cell H1 (which should currently have a 1 in it).
- Drag your mouse down to H99 (which should currently have a 99 in it).
- Right-click anywhere in the highlighted area.
- Select 'Name a Range' from the pull down menu.
- Call it 'Lvl' and click 'OK'.
- Do the same for cells I1 to I99.
- Call it 'Exp'.
Now that our columns of information are named, we can use that in a formula that looks things up on them. This is precisely what we're going to be doing next.
Looking Up The XP to Your Next Runescape Level in Excel
The worksheet, with all of my data in it, is imaginatively called 'Data' in my Excel workbook. It's important that you note what you called your tab, because it's going to be used in the next formula.
- Click on cell J2.
- Copy and paste the following formula:
- Change Data to whatever you called this page. (Or leave it alone, if you called the worksheet 'Data'. :p)
- Press enter.
- Woot at the fact that the XP to your next Attack level should be revealed.
- Click on cell J2 again.
- Left-click on the tiny square in the bottom, right-hand corner of the cell.
- Drag it down to cell J26.
- Happy dance.
This is how my data sheet looks now:
We have our data all calculated. All that remains now is to format it into a pretty table.
Creating a Table Charting the Experience Remaining
I'm going to walk you through creating a table that looks like this:
Obviously, you might want to add your own artistry to it. Please do feel free to colour and decorate it as you wish!
Open up your main worksheet, where you last created the Averages table. Mine is huddled up in the first five columns, so I'm going to move it along a bit.
- Click the A to highlight the first column.
- Right-click and select 'insert'.
- Repeat that another three times.
- Click cell E1.
- Right-click and select 'copy'.
- Click cell B1.
- Right-click and select 'paste'.
- Drag that over to C1, using the little corner box.
- Click on C1.
- Type 'XP to Next Level'.
- Widen the C column, by clicking your cursor on the wall between C and D and dragging.
- Highlight the cells B2 to C26.
- Colour them in using the paint bucket.
This part is really formatting the cells to look pretty. I went through it this when we were making the Averages table, and added some more tips in the bonus blog, so please consult them.
I'm going for a light grey with full borders and gridlines.
Once it's all pretty, it's simply a case of adding a couple of formulas.
- Click on B2.
- Copy and paste this formula: =Data!B2.
- Drag the formula down to B26.
- Widen your B column, if necessary. (The word Dungeoneering will probably make it necessary.)
- Click on C2.
- Copy and paste this formula: =Data!J2.
- Drag the formula down to C26.
Voila! All of your information in a handy table! But I want to add a bit more decoration, just to give it a splash of colour.
- Highlight cells C2 to C26.
- Click on 'Conditional Formatting', in the styles section of the Home tab toolbar.
- Select 'Icon Sets'.
- Select 'More Rules'.
- Pull down the menu beside 'Icon Style'.
- Select '4 Traffic Lights'.
- Tick the box beside 'Reverse Icon Order'.
- Click Ok.
There are loads of icon sets in there. If this one isn't thrilling you, then try another in the same way. But before we declare this table done, there's a big decision to make about filters.
Excel Worksheets: The Limitations of the Sort Filter
You could add a sort filter, as you did with the Averages table. Unfortunately a limitation of Excel is that you can only have one of these filters on any single worksheet. There is a work-around using macros, but that's a bigger job, so a tip for another day. For now, just decide which table you wish to have the handy button filter on. The other can be sorted manually by highlighting it and using 'sort and filter' to arrange it.
NB Do not sort the columns headed 'Average' and 'Average Minus 1st/Last', else it will lose the formatting.
I've opted to put the button filter on the XP table.
Two Runescape Hiscore Tracker Tables in Excel
There we have it. You now have two lovely and useful tables, nestled side by side on your Excel worksheet.
Next time, we'll look at how to add Activity Logs and more.