Tip #5: Include t-tests (and other inferential statistics) into your interactive dashboards

Do you work with a lot of pre/post data? Yeah...so do I! As evaluators, we often assess program efficacy by investigating gains over time. To assess pre/post gains, I like to use the retrospective pre/post survey because it efficiently and "accurately" captures change from before an intervention to after an intervention. 

One of the most time-saving techniques that I figured out over the past few years is how to integrate paired samples t-tests into my interactive dashboard. Think about it: By including t-tests, my dashboards are basically calculating scores and scores of paired samples t-tests behind the scenes!

​For instance: Say you want to know if females, in particular, showed statistically significant gains? Boom!...Click on the slicers in my interactive dashboard, and you'll get your answer in seconds! What about females who are underrepresented minority students and who are majoring in Physics? Yep...my dashboard will calculate that for you in an instant!

Want to know the steps?  

To include t-tests, I essentially took the PARCS method and kinda, sorta bent it. In other words, I flipped the script on the PARCS method! Here are the steps:

​Step 1. Insert a pivot table

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

Helpful Hint: Notice, in the screenshot below, that my raw data includes ID #s for each student in column A, Pre and Post data in columns B through M, and demographic data (major, gender, and race/ethnicity) in columns N, O, and P.  

Step 2. Recreate your raw data within a pivot table

Here's where things get a little flipped-y flipped! We're going to, essentially, recreate our raw data table within a pivot table. To do this, drag "ID" into the "Rows" field. Then, drag your Pre and Post items into the "Values" field. (For demonstration purposes, I'm using one of my pre/post items: Write scientific reports or papers.) 

By default, Excel made all of my pre/post values "Sum of" which is fine by me! Because we are recreating the data table, "sum" works just fine. 

Helpful Hint: Because I tend to err on the side of caution, I like to compare my pivot table to my raw data table. Fore example, it looks like ID #2 rated their Pre ability to write scientific report or papers as a 1; at Post, they rated themselves a 5. (Likert scale: 1, Poor to 5, Excellent). See if that checks out in your raw data. If it does, you're doing Step 2 correctly! 

Step 3. Insert Slicers

Slicers make the dream work...when it comes to interactive dashboards! Slicers function as dynamic filters which allow you to slice-and-dice your data. To insert a Slicer, place your cursor anywhere in the Pivot Table. Then, go to the "Insert" ribbon and select "Slicer." Once you select Slicer, a dialogue box will pop-up. Select your slicers from this dialogue box by checking off which variables you want to filter the data. In my case, I selected "Gender" and "Race/Ethnicity."

Once you click "Ok," you should see 2 slicer menus. Start playing with them by clicking on various combinations. If you did this correctly, you will notice that your pivot table is being modified in real time. So, if you selected "Female" in your Gender slicer, the pivot table will showcase only participants who are female. So cool, right?

***Essential: Before we can run calculations, did you notice that there is a "Grand Total" in your Pivot Table? It appears right at the bottom of your Pivot Table. See it? Ummm...we need to get rid of that, like, pronto because it will mess up all of our calculations! To quickly remove "Grand Total," simply right click where it says "Grand Total" and select "Remove Grand Total." Easy fix!

Step 4. Create calculations

Now, we get to calculate statistics! You knew it was coming, and here it is!
We're going to create a summary statistics table with descriptive statistics and t-test formulas.
Create this summary table either on a separate sheet or in the columns to the right of your pivot table (i.e., next to your pivot table). You DO NOT want to create your summary table underneath the pivot table! Bad...very bad!

In the screenshot, below, I created my summary table in column E. For the summary table, I calculated the following:

• N
• Mean
• Standard Deviation
• T-test  

Here are all of the formulas that I used for my particular dashboard:

• Pre N: =Count (B:B)
• Pre Mean: =Average (B:B)
• Pre Std. Dev: =Stdev(B:B)
• T-test: = TTEST(B:B,C:C,1,2)
• Significant?: =IF(F11<0.05, "p<.05","not significant")

Go ahead and click on the slicers and watch your summary table change before your eyes! (I've done this hundreds of times and, trust me, it never loses its magic!)
​

​Step 5. Insert a Pivot Chart to display your pre/post data and add the values!

Let's bring it all together! Create a chart to showcase your pre/post data by going to the Insert ribbon and clicking on PivotChart. Select your favorite chart.
Here, I went with a slope chart---------------------->

---

After fiddling with my pre/post chart, I then cut and pasted my chart to a new sheet and called this sheet "Dashboard." I also cut and pasted my two slicers to this new sheet. Once I placed the chart on the dashboard, I then included the t-test results by referencing them from my pivot sheet. 

And, here's the finished product! I added icons and created a cohesive color scheme derived directly from my clients logo. 

Once you have created one set of formulas in your summary table, it's pretty easy to drag your formulas across columns to add more pre/post items. 

Feel free to download a copy of this dashboard: 

prepost_dashboard.xlsx
File Size:	205 kb
File Type:	xlsx

Download File

---

Need help with a particular step? Please contact me!