Hello, AEA365 community! Liz DiLuzio here, Lead Curator of the blog. This week is Individuals Week, which means we take a break from our themed weeks and spotlight the Hot Tips, Cool Tricks, Rad Resources and Lessons Learned from any evaluator interested in sharing. Would you like to contribute to future individuals weeks? Email me at AEA365@eval.org with an idea or a draft and we will make it happen.
Greetings, AEA365 community! My name is Liz DiLuzio. When I’m not curating the blog, I can be found teaching anything from one-day workshops to semester-long courses on tons of topics related to data analysis.
My friends over at GBEN, AEA’s affiliate group in the Boston area, recently reminded me of a post I wrote in 2023 about the top Excel skills every evaluator should know. Upon rereading, I was amazed to see how much my Excel practice has developed in a little over one year. If my student had asked me today what topics I thought she should know, here’s what I would tell her:
Hot Tips:
- The top Excel trick to beat all others: yeah… it’s still PivotTables. It continues to be difficult to convince me of any other single feature in Excel that is as high-leverage.
- 2023’s recommendation: aggregation formulas such as SUM and SUMIF, COUNT and COUNTIF. To up the ante, check out SUMIFS and COUNTIFS. More flexibility, more power!
- When cleaning data, CONCAT/LEFT/RIGHT/MID continue to be helpful in key moments, but how have I only recently learned about TRIM?? If you’ve ever had your formulas thrown off my stray spaces within cells, TRIM is the solution to your data cleaning woes.
- Speaking of more flexibility and power, try combining SUMIF/COUNTIF with Excel’s wildcard feature. It’s the single most helpful combination when working with data from “select all that apply” fields.
- You probably know that you can create dropdown lists in Excel to aid with data collection, but did you know that you can create dependent dropdown lists, displaying certain values based on the selection in a prior dropdown menu?
- Somewhere between January 2023 and today I learned about XLOOKUP, and I’ve never looked back. (Sorry, VLOOKUP.)
Rad Resources:
Interested to know more? I’m teaching a flexible attendance* Advanced Excel workshop online June 17th and 19th that will cover all of the items above and more.
*Flexible attendance means you can attend synchronously or, if you can’t (or prefer not to learn live), you can receive a recording of the workshop afterwards so that you can digest the information at your own pace.
Can’t make it, but want to be notified about upcoming workshops in Excel, Tableau, R, Python, and QGIS? Sign up for our newsletter here.
Want to read some other Excel must-have lists? Check these out:
- 7 Essential Excel Tricks Every Office Worker Needs to Know
- 11 Advanced Excel Skills That Will Make You Look Like a Spreadsheet Pro
- 13 Tips to Make You a Microsoft Excel Power User
Your Turn:
Are there any Excel skills I didn’t name that you think are a must-have for evaluators? Let me know in the comments!
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.