Microsoft Office Enterprise 2007 Fun With Tables

A few weeks ago when I asked readers what they wanted to see in the way of content over the next few months, a few people suggested some discussion about everyday use. Accordingly, I am going to write today about some things I was doing with Tables in Excel 2007, and tomorrow I will try to write a bit about conditional formatting.
Specifically, what I want to walk through with regard to tables was using calculated columns and structured referencing. (For those that are new to the blog or who need a refresher, a post from last year on these items is located here.) In this case, I was working with two tables of data (imported from another data source) and I wanted to add some additional calculated columns and then add a lookup from one column to another – a pretty standard activity. Let’s take a look at how this can work in Excel 2007.
Here is one of the tables (note, the data is completely fabricated for the purposes of this blog entry). It contains a list of blogs I was looking up and their traffic via both RSS feeds and normal page views for the month of June. The other table, on the second sheet, is exactly the same, except that the data is for the month of May.
(Click to enlarge)
What I want to do is the following:
Figure out the ratio of RSS:Page viewsAdd a percentile columnFigure out the rank for each monthFigure out how the rank changed between the two months
The first thing I did it was to turn the table into a Table (new for Excel 2007, newer readers can find a series of posts on Tables here) using CTRL+T. I did the same thing for the table on the second tab, and named them “June_2006” and “May_2006”.
(Click to enlarge)
The next thing I did was added another column to the end of the table by typing “Ratio” beside the other table headers and pressing Enter. Excel added a column to the table and took care of the formatting.
(Click to enlarge)
To add the calculation, I simply needed to enter the names of the columns (either by typing or by using the arrow keys or mouse) thus: =[RSS Views]/[Web Views]
(Click to enlarge)
When I press Enter, Excel fills down the calculation across all the rows in the Table. So that’s it – a few keystrokes to add a calculated column to the table, formatting and all, and you can sede that Excel uses Table column names and not references in the forumula.
(Click to enlarge)
Now those of you that clicked on the above picture probably noticed that there was a #DIV/0! Error down on line 20. To address that problem, I went back and updated the formula that I just entered to use the new IFERROR function that was added in Excel 2007.`
(Click to enlarge)
Which simply says that if there is an error, replace the error value with the string “0 Web Views”.
Next up on the agenda is adding a percentile and a rank column. Since these are both pretty similar, I will only go over one, adding a percentile column. Again, the first thing to do is to add a new column in the same way I added one above. The next thing to do is to start typing the name of the function I want to use … in this case, PERCENTILERANK. Notice that as soon as I type “=”, a list of everything that I might want to enter at this point – functions, Tables, Defined Names – is brought up in a list (Formula Autocomplete – see this blog post for details). As I type more, the list is narrowed down based on what I type. If I type “=p” for example, I see this:
(Click to enlarge)
I think simply have to select the function I want (mouse, keyboard, further typing) and I can start entering arguments. In this case, I want the PERCENTRANK of the Total Views column, so I simply enter that for both the array of values as well as the specific value in question (the x value).
(Click to enlarge)
What’s going on here? Excel is clever enough to figure out the implicit intersection of the x value. When I press enter, again, the column is filled down. Adding a column to rank values is exactly the same, except I use the RANK function. Here is a picture of the table after I have added the two new columns and done a bit of formatting.
(Click to enlarge)
The final thing that I want to do with the table is figure out how the rank has changed from the previous month (I added the same to calculated columns to the other table). In this case, I am going to use a VLOOKUP function and a MATCH function to get this data, like this:
(Click to enlarge)
Note,Microsoft Office Enterprise 2007, however, that instead of using cell references, I am using references to the Tables, Table columns, and Table headers I am using. Specifically, I am looking for the value in the “Blog” column, I am looking for that value in the “May_2006” table, and I am subtracting the “Rank” column at the end of the formula to figure out the difference between the two months. In addition, I am using the MATCH function to find the position of the “Rank” header in the “May_2006” Table header (that’s what “May_2006[#Headers] returns – a reference to the headers in the specified table … you can also return references to #Totals, #Data, #All, or #ThisRow).
The net effect of all this is that my formula is a lot easier to read/audit, and a lot less fragile, since I don’t have to worry about whether more data shows up on refresh, headers being moved around, etc.
After another IFERROR function and some conditional formatting, I am pretty much done.
(Click to enlarge)
Hopefully that was somewhat interesting. Tomorrow, I will try and put together another conditional formatting example.
<div