Hello! I am Elizabeth Oyer, President of EvalSolutions, Inc. where I provide program evaluation and business consulting services. My projects come in all shapes and sizes and require different tools depending on the complexity and scale. One very familiar tool I use to create data summaries for smaller scale, simpler projects is the Microsoft ExcelTM pivot table. Now, pivot tables are not new and anyone knocking around in Excel regularly has learned how to generate one. In recent years, I learned two Excel tricks that improved my ability to manage data.
Hot Tip
Creating a “Table” in Excel with your rows and columns of data makes filtering, sorting, cleaning features easier to implement. Users can insert a table using the menu; however, a simpler keystroke is to simply put your cursor in the top corner cell and select, “CTRL-T” to create the table. Once you have your data in a table, you get easy access to filtering, searching, and adding formulas that populate the entire column. Data that have been converted to a table appear as blue and white.
Cool Trick
In addition to easier data manipulation in tables, I have learned a cool trick for calculating unique counts of a variable with data in the long form (e.g., multiple rows for the same person). When creating a pivot table, simply add the data to the data model. This gives you access to a new value field setting called, “Distinct Count” in the value field section. Note in the example, Count for blue = 2, Distinct Count = 1.
The American Evaluation Association is hosting Behavioral Health (BH) TIG Week with our colleagues in Behavioral Health Topical Interest Group. The contributions all this week to aea365 come from our BH TIG members. Do you have questions, concerns, kudos, or content to extend this aea365 contribution? Please add them in the comments section for this post on the aea365 webpage so that we may enrich our community of practice. Would you like to submit an aea365 Tip? Please send a note of interest to aea365@eval.org. aea365 is sponsored by the American Evaluation Association and provides a Tip-a-Day by and for evaluators. The views and opinions expressed on the AEA365 blog are solely those of the original authors and other contributors. These views and opinions do not necessarily represent those of the American Evaluation Association, and/or any/all contributors to this site.