When a Young Woman's Fancy Turns to Thoughts of Excel

You might remember my Shaggy Dog story this winter about efforts to get an interactive workbook online. Even after I found a solution and did my happy dance, the project got shelved...with the caveat that we could pursue something in the spring. Well, it's spring and we're no closer to being allowed to put the project online; however, this may have been a good thing, because it has given me an opportunity to completely revisit things and derive a more elegant solution.

A couple of weeks ago, David Napoli shared this link to a Metro Style Dashboard by Erik Svensen. And while I couldn't directly use the information (I don't have Excel 2013 yet), it did get me thinking about how to update the look of my project---and more importantly, it let me know that slicers work with the Excel Web App.

Slicers, you ask? If you're new to Excel...or if you are still using a pre-2010 version...you might not have seen these. They go along with pivot tables (a piece of Excel magic that we haven't talked about in this space) to "slice" (filter) your data set into all sorts of views. It's probably easier to show you, so my example is below. It uses the same spreadsheet as the Shaggy Dog.

Start by highlighting the first cell in a table, then from the Insert tab, choose Pivot Table. When prompted, I added the pivot table to a new worksheet. This is the easiest way to wrangle your table, because it will be a dynamic item. Depending upon what you want to look at, the items showing in the table will change.

On the left is my pivot table...to the right of it is a dialog box I can use to build the pivot table. Notice that the headings from my original table (Unit, Category, Title, Description) are in the list of fields. I picked the first three for this project. Excel automatically placed them as rows, but I could drag these into any arrangement or hierarchy that I want.

I should share that this pivot table is unusual in that there are no numbers associated with it. Most people use pivot tables to look at the results of different groups...and this is very handy for teachers looking at student data, too. But for this project, I only have text. We'll talk about building pivot tables for your other data in another post.

Okay, now I can add Slicers. If your pivot table is highlighted, you can insert slicers from the Options tab on the ribbon.

Otherwise, just go to the Insert tab and choose "Slicer." You'll get a dialog box---just like the fields from the pivot table---to choose from. I picked the first two options this time, so here is what Excel gave me:

Slicers are like remote controls for your pivot tables. By clicking on any combination of buttons in the Slicers, you can call up any combination of data from the table. They also can move around in the workbook, meaning we can add them to a reporting tool. The defaults on the Slicers, like most things in Excel, are not very sexy. But, fear not, there are a lot of ways you can dress them up.

Now, I can build a report. I'm not going into the full details in this post, but you can download the workbook if you want to see the innards. With the exception of the pivot table and the interface you see below, it's all the same data set and formulas as the Shaggy Dog. I moved my Slicers and built a simple display.

 

The slicers are on top, in a two-by-two arrangement. Depending on the buttons selected, the list of items changes. Better yet, you can upload this workbook to a SkyDrive account and put it on the web. Like this:

You can click the "View Full Size Workbook" button in the lower right corner of the window if you don't want to deal with the scrolling. (When you embed workbooks, you can change the size that shows, but we have some other design constraints here based on my blog design.)

This is a very simple application, but you can certainly take it a lot further. The one I (re)built for work has a block of color to show the user how many resources are available. If you used a tool like this to get a view of what was happening across classes (or classrooms), you could also build it out with a variety of charts or other visuals.

It's spring. Get out and play!

The Back End of the Shaggy Dog

I work in tech, and people are forever referring to the "back end" of software, databases, and so on. I find the reference amusing (because inside, I'm 12), but I have to admit that the euphemism fits. The code, meta-data, and other unseen features are not the pretty face that gets shown around. So let's take a look behind the scenes, as it were, of my Shaggy Dog Story with Excel.These instructions are all about MacGuyvering a spreadsheet. While I hope that no one needs them "as is," there are a few tricks here that you might find handy for other projects.

First, I organized my resources on one spreadsheet. Then, I made it into a table. You can do this in Excel by clicking in the cell that will be the uppermost lefthand corner of the table, then using CTRL+T. My table had headers (unit, resource, title description), so I checked that box and hit ENTER. You don't have to turn the data into a table, but (1) I think it makes the formulas easier to manage and (2) you don't have issues with adding records.

Now I have something that looks like this. If you don't like the formatting Excel applies, you can go into the Table options and fuss around with it.

Then, set up your sheet where you want the results displayed. If you're using Excel offline, just use data validation. Don't bother with my "X marks the spot" strategy. However, if you're trying to webify this content, carry on. This is how I set up the demo for the blog post. I used the four column headers (in brown) on this sheet to organize the display.

There are two more small pieces before we get knee-deep with generating a list of items based on user input. First, on the sheet with the list of resources, I set up two IF statements, one for the choices underneath "Unit" and the other for "Category." I needed to tell Excel how to recognize the selections made by the user. Here is how I set up the formula for Unit:

This formula is placed on the other sheet, but since cell references are on this one, I'm showing it here.

Keep in mind that if you're building an offline version using dropdown menus, you can skip this part.

Finally, I set up a COUNTIFS statement based on the user selections. This was also placed on the page with the data set: COUNTIFS(List[Unit],G2,List[Category],G3). This tells Excel to count all of the cells in the table that matched the results of the two IF statements. I named this formula Item_Count. The one I did for work ended up with nearly 30 choices and two extra layers of IF statements...but we won't bother about that now.

An aside here...

I mentioned earlier in the post that converting the data into a table made things simpler for the formulas. Notice that in the COUNTIFS formula that I don't need to state a particular range (e.g. A1:A50). I can just tell Excel to use the table (List) and column header in square brackets (Unit, Resource).

Okay, let's go back to the sheet with the results. We have one more formula to apply...and it's a doozy. Why? We need to generate a list of results based on user input. Our old friends, INDEX and MATCH, are great at identifying the one item that fits a set of criteria...but we need a list. We need Excel to find the first one, the next one, the next one...and so on. Also, the results of each list might not be the same in number--could be one item, could be ten.  I found the basic answer here, if you want to watch a video tutorial; but here's the basic idea for cell B11 shown below. Keep in mind that this is an Array Formula, which means you need to use CTRL+SHFT+ENTER to make it work.

=IF(ROWS($B$11:B11)>Item_Count,"",INDEX(List[Title],SMALL(IF(List[Unit]&List[Resource]=List!$G$2&List!$G$3,ROW(List[Unit])-ROW(List!$A$2)+1),ROWS($B$11:B11))))

If you're hyperventilating after looking at that formula, find a paper bag and come back. We can do this.

The first part of the formula (=IF(ROWS($B$11:B11)>Item_Count,"") means that we want Excel to count the rows from B11 to B11 and compare them to the result of the COUNTIFS formula (named "Item_Count"). If there are more rows than the Item_Count, Excel should leave the cell blank. This part is important because we are going to have a variable number of results based on user input. By including the option for the blank result, we will keep error messages from showing up.

Next, we have the FALSE part of the IF statement---what Excel should do if the Item_Count is smaller than the number of rows. In this case, I want it to INDEX the Resources and display the title based on the following criteria:

• It must match both the Unit and Category as indicated by the results of the IF statements we set up earlier. (IF(List[Unit]&List[Resource]=List!$G$2&List!$G$3)
• It must be the "smallest" (first) item that matches the criteria that is equal to the number of rows we've specified +1:  SMALL(IF(List[Unit]&List[Resource]=List!$G$2&List!$G$3,ROW(List[Unit])-ROW(List!$A$2)+1),ROWS($B$11:B11))

The good news is that you only have to build this formula once. And trust me, you will do your Happy Dance when it works. Use your fill tool to copy the formula down. You can also copy and paste the formula to cell E11 for the description, then change "List[Title]" to "List[Description]" in the formula.

You can download the workbook here, or go back to The Shaggy Dog Story and see how things work in the embedded version.

See? That isn't so Ruff! Ruff!

Bonus Round
The gigantic formula shown above has a lot of different uses. Don't discount it just because it looks scary. For example, think of how you might use it to sort a list of students into different classes (i.e. take a master list and sort it onto multiple spreadsheets). Although Excel is not a database tool, this formula gives you some options to use it that way when you need to.