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.
What I want to do is the following:
- Figure out the ratio of RSS:Page views
- Add a percentile column
- Figure out the rank for each month
- Figure 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â.
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.
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]
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.
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.`
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:
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).
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.
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:
Note, 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.
Hopefully that was somewhat interesting. Tomorrow, I will try and put together another conditional formatting example.