tip #4: include a dynamic "n" count in your interactive dashboards

Okay...the following is an actual conversation (recreated from memory!) that I had with a client, and underscores the need to include a dynamic "n" count in your interactive dashboards...

​Early one morning, I received a frantic call from a client:

• Client: "Hi, Shelly, ummm...I know it's early, but I've been playing with your dashboard and I noticed that ALL of our female students are dissatisfied with our program. Am I reading this accurately? What is going on with our program? Did we fail our female students somehow?"
• Me: "Good morning! Hmmm...ok...bear with me as I open the dashboard. [Long pause] Okay! Got it open!
• Client: "Thanks, Shelly. I clicked on "Astronomy" majors and "Females" in the slicer menus. When I do that, it looks like 100% of participants said that they are dissatisfied with the overall program. What the heck?"
• Me: "Hmmm...I'm not seeing that. Did you, by chance, filter the data by race/ethnicity using the Race/Ethnicity" slicer?" 
• Client: "Yes...I think I selected 'Latinx/Hispanic.' "
• Me: "So, the total number of females who are Astronomy majors AND identify as Latinx is 4. So, we're actually only talking about 4 students here. Astronomy is the smallest major in the college, and there are very few Latinx students in the department. 
• Client: Oh...only 4 students?!?! I wish that was clearer in the dashboard.

​Noted!

Without knowing the denominator, my client freaked out over 4 students. I can't blame them. Context makes a BIG difference. Often times, dashboards fail to relay this "context" to the user. I would argue that interactive dashboards, in particular, require context to ensure proper interpretation of the data. 

Adding a dynamic "n" count to your dashboards is critical, but it's a little tricky to create in Excel. ​Here are the steps....

Step 1. Insert a pivot table and add a demographic or ID variable to the "value" field

First, select your data...go to your "Insert" ribbon...and click on Pivot Tables.

Once your pivot table has been created, drag and drop a demographic variable or, better yet, an ID variable into the "Values" field. You, basically, want to use a data variable that is "complete." I almost always have an "ID #" column in all of my data sheets. Sometimes the ID #s reflect actual student IDs. But, often, I just make up ID numbers for the purposes of creating a dynamic n count for my dashboards. Sneaky, right? 

Step 2. Change the value from a "sum" to a "count"

By default, Excel will try to "sum" your ID #s. Don't let it! Change the "sums" to "count" by going to the Analyze ribbon and selecting "Field Settings." Within "Field Settings," you'll want to summarize your value field by "count." In other words, select "count." Simple enough? 
​​

Step 3. Insert a Slicer (or make sure that your pivot table is connected to your slicers)

As I've described in Tip #1, Excel slicers make the dream work. Slicers will allow you to slice and dice your dashboards. Insert your slicer by going to the "Insert" ribbon and selecting "Slicer." Once selected, you will be asked to select the variables that will serve as your filters. I usually select demographic variables like gender, race/ethnicity, etc.

Step 4. Insert a text box

We are going to use a text box to feature our new "n" value. Then, we're going to place this text box somewhere in our dashboard to give the user context. Insert a text box by going to the "Insert" ribbon and selecting "text box." Draw your text box to size.

Step 5. Add the dynamic "n" count to your text box.

All right, folks. This is where a lot of people trip up! Excel is finicky sometimes and plays by its own set of rules. To add the dynamic count to your text box, you first need to reference the n count in a cell. In this example, below, I've referenced the n count in cell B66. See that?

The reason why we need to create a referenced cell is because an error message pops up when we try to directly reference a number from a pivot table in a text box. It's a strange thing, I know! Let's just go with it... 

Once you have your referenced cell, then simply select your text box and select your referenced cell. In this example below, I selected my text box and then inserted the formula "=B66" to reference the number in cell B66.
(Place $ signs around your formula to further fix the value: "=$B$66")
​​

The final step is to move your text box to your dashboard. Try it out! Did your n count change as you select the slicers? If not, you might need to fix the formula in your text box to refer to the correct cell. 

These steps are not easy to master...but, including a dynamic n count is going to help you avoid frantic morning calls from clients. ;-)
​​