Excel, I choose you!
August 22, 2008 • JP • No Comments • Rate This Article
• Links to this article
My son and his friends are really into Pokemon. After the initial disgust wore off, I noticed that the characters are all catalogued and classed into different categories, with different levels, strengths and abilities, so I went and found this cool color chart showing the effectiveness of different attacks on different Pokemon types. What better way to make use of Excel than a few charts showing how different Pokemon fare against each other? Ok, I'm sure there are better ways to use Excel, but I was bored. Hopefully I won't get a C&D from Nintendo for this post.
I couldn't find anything similar to the effectiveness chart in Excel, so I created my own. Here's a screenshot.
- N = Low damage
- \/ = Normal damage
- Y = High damage
- X = Immune
The chart lets you visually cross-reference the attack type with the Pokemon type. So now you can see that a Fight attack is highly effective against Rock Pokemon, but Ghost Pokemon are immune to it. I'm sure you are so excited at this point!!
But how to turn this into a useful chart? We need to turn the data from a grid into a column-based table with field names at the top. Each attack is pitted against its own type, and every other type, and there are 17 types. Therefore we list each type 17 times in the same column, and then list every other type next to it, along with the effectiveness.
You can see above how the first three attacks are listed, with the Pokemon type list simply duplicated in the middle column repeatedly. I just cut-and-pasted the row and transposed it (using Paste Special) into the column; I did the same for the effectiveness column.
(Special thanks to Excel MVP Jon Peltier for informing me of the proper way to do this.)
In case you were wondering, this is how you can turn any "grid" type data into something usable for a PivotTable.
Now we can create some meaningful PivotTables to let our Pokemon battle! The first one shows the effectiveness of each attack against each Pokemon type. I select the data and then run the PT wizard to create a blank PivotTable. You should end up with something similar to the picture below.
Click and drag 'Type' to the Page field area and drag 'Attack' to the Row field area. Drag 'Effectiveness' to both the Column field and Data field area (i.e. two times). You should end up with this:
Click the dropdown arrow in the cell next to 'Type' to select various Pokemon types. Then you can see how effective each attack is against that type. Next we'll move on to the other type of PivotTable we can create: one that shows how each type of attack fares against each Pokemon type.
I'll highlight the same data from the previous PT, and run the wizard again. At the end of the wizard, Excel will notice that we selected the exact same data and ask if we want to use the same pivot cache for the new PT. I'll click 'Yes', but it really doesn't matter, since we're only creating two PivotTables. However, if you are creating several automated tables or charts at once, it is more memory efficient to use the same cache to make them all, especially if they all use the same data source or if the data is external to Excel (i.e. Access, SQL).
The PT will be exactly the same, except we swap the Attack and Type fields; the Attack field goes in the Page field area, and the Type field goes in the Row field area. Now you can select an attack type and see how it affects each Pokemon type.
Enjoy,
JP
Excel ·
Rate This Article:
(No Ratings Yet)
Loading ...What now?
Subscribe To The Blog
Email This Article
Follow Me On Twitter
My Facebook Page
Or just keep reading:
I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space if you want to learn more about VBA. Keep Reading »
Previous Post: Excel User Conference Update
Next Post: Processing multiple emails
I'm not really a fan of Pokemon (yet), but this is so cool! What an interesting way to use Excel!
Thanks for sharing
Avoid it if you can.