Excel, I choose you!

August 22, 2008 – 3:38 pm by JP

---

    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.

    Download a sample workbook containing all of the charts and PivotTables.

Enjoy,
JP

 Print This Post  |   Email This Post  |  Permalink  |   Subscribe now!

Filed Under: Excel
Tags: Excel, pivot table, Pokemon

This post has 107 views since August 22, 2008 – 3:38 pm.