Learning center directors often find themselves needing to analyze and report on large amounts of data, but may not have extensive training in Excel. In our most recent moderated discussion, Talia shared several helpful tips to make this process easier and more efficient.
Click HERE for the recording. Here are five key takeaways:
Navigate quickly with keyboard shortcuts
Instead of scrolling and clicking, use Ctrl + arrow keys (or Cmd, if on a Mac) to jump to the edges of your data range. This lets you quickly select all data in a column (Ctrl + Shift + Down/Up) or row (Ctrl + Shift + Right/Left).
Create a unique list of values
To get a list of unique students or other values, copy the data into a new sheet, then use Data > Remove Duplicates. This saves time compared to manually scanning for unique entries.
Use AutoFill to quickly populate a series
If you have a pattern of values (e.g. 1, 2, 4, 5, 6) or a formula, select multiple cells with that pattern and double-click the small square in the bottom right corner. Excel will intelligently fill in the rest of the series for you.
Summarize data with SUMIF(S) and COUNTIF(S)
To sum or count data meeting certain criteria, use the SUMIF(S) and COUNTIF(S) functions. For example, to get the total time for sessions with Status=Completed and Attendance=Present:
=SUMIFS(session_length_column, status_column,"Completed", attendance_column,"Present")
Remember to lock the ranges using $ to enable easy copying of formulas.
Get quick totals with the status bar
Select a range of data and look at the bottom right of your window to get a quick sum, average, and count of the selected values. This is an easy way to spot-check totals.
Other directors shared how they've used similar techniques to create demographic breakdowns, track usage over time, and identify top subjects and courses. By automating these analyses, they can spend more time acting on the data to improve their programs.
The group also discussed how ChatGPT and other AI tools can help with analysis - you can paste in a formula and ask the AI to explain what it does or suggest improvements. This can be a quick way to learn and troubleshoot.
Learning Excel can seem daunting, but just take it bit by bit and you will be a master in no time!
