As Residence Life staff, we are often (digitally) swimming in data. We receive and analyze data related to students, rooms, incidents, programs, duty, inspections, and more. And while many wonderful software companies have made storing and reporting on these data much easier over time – and many of us work with awesome colleagues who can create and analyze those reports with and for us – we will likely always still find ourselves looking at a spreadsheet and needing to manually do “something” with it. Before you set aside a few hours for one of those tasks, I’m going to share some tips for making some of those manual tasks just a little easier, the kind of tips that should truly up your game in Excel, Sheets, or your favorite spreadsheet application and that you’ll want to share with all your colleagues to up your department’s game too.
First a quick note on depth: This is not a “how to” article – there are too many excellent ones out there to try to make it one. I will include basic instructions for Microsoft Excel and Google Sheets but not much deeper than that. I do hope that will give you enough to search further!
Want to make those data just look nicer? Use a Table!
Ever get a dataset with no formatting and think, “well this is boring, I’m going to make it look nicer for readability and reporting”? Your favorite program will have a Table formatting tool that will get you most of the way there! Just highlight all your data, then tell your program to change it to a table:
- In Excel: Insert > Table
- In Sheets: Format > Convert to Table
Whether I am presenting data or not, I tend to always convert a dataset to a table for multiple reasons. First, I find it more visually accessible for myself as I review and work with the data. And on top of that, table formatting often comes with sorting, filtering, and column naming options that make calculations and manipulation much easier.
Want to summarize your data? Use a Pivot Table!
Another manual task we often take on with lists of hundreds (if not thousands) of residents is summarizing those datasets. Whether it’s how many residents are from a certain location, the total number of animals living in a hall or apartment complex, or even the number of programs each student staff member has planned so far in the year, summarizing data is essential to our work. And Pivot Tables automate much of the manual counting that is sometimes involved! To create your Pivot Table, first highlight your data (or select the Table you just made!), and then:
- In Excel: Insert > Pivot Table
- In Sheets: Insert > Pivot Table
You’ll need to select where you want it, but then you will have a nice pop-up widget where you can select how you want to summarize the data, including what each row, column, and value represents. Using my example “RA” data below, I can use a Pivot Table to total the number of residents by floor or the number of RAs (using their First Name column) by floor for this building.
Want to combine text? Use the Concatenate formula!
It’s not uncommon for a data set to have something close to, but not exactly, what you’re looking for. Sometimes we section communities differently than what’s in our data, sometimes we shorten or lengthen names or titles when we share data, and other times we just need a list of attributes rather than separate columns. One way we can manipulate data to get what we need is by combining text in multiple columns, and we can do this automatically through a formula in our spreadsheet tool rather than by hand. It’s called the “concatenate” formula, and it literally “strings” the text together that you tell it to.
- In Excel: Type “=concat()” or “=concatenate()” depending on your version
- In Sheets: Type “=concatenate()”
After typing the above, you will then select the cells or type in the text you want to appear together in one cell, separated by commas. I most recently used this formula to merge the list of keys for every space on campus into one cell for a mail merge (see next section), but in my example below I show you how I can use this to create a “full name” column for my example data, including adding a “ “ for a space between the two names (again, separated by commas for the formula to work).
Want to create the same e-mail message or document for rows of data? Use Mail Merge!
Okay, so a colleague recently reflected to me that this feels like a lost art in the age of much swifter and built-in e-mail tools, and this tool can be frustrating at times, but I still love the satisfying feeling of a successful mail merge that I feel the need to share it.
Imagine this: You have five to one hundred or more rows of data, and you want to use it to create individual e-mails or documents (door decs, anyone?). Now imagine that you do not need to manually copy and paste the individual row names, room numbers, or whatever you are pulling from those data into the individual documents. I hope your imagination is now seeing time saved.
To do this, you will first need to save your data in a file somewhere, then find the option in your word processor to complete a mail merge referencing that data spreadsheet. In Microsoft, you can do this by selecting Mailings > Start Mail Merge, then selecting what kind of merge you wish to complete and the Excel file to reference. I have not done this in Google and believe you would need to use Add-ons, but I have done this in Canva through their Bulk Create tool, which is rather useful for creating many decorations, certificates, or other formatted documents from your spreadsheet file.
Want to merge data sets together? Use a combination of Index and Match formulas!
To wrap-up this quick tutorial, I will share my favorite combination of formulas for bringing two data sets together. As I alluded to above, your data sometimes has gaps that another dataset can help fill. If you have the time and energy (and hopefully dual monitors), you could merge what you need manually, but if you’re like me you may occasionally realize these gaps a little too late for that. And do any of us ever feel like we have extra time?
Instead I will suggest you apply two formulas in combination with each other. You will start by opening:
- Dataset A – the set of data that’s missing something
- Dataset B – the set of data that has what you’re missing in A, but does have a similar column of information as Dataset A to link the two datasets
Now start your Index formula – this formula “indexes,” or delivers the piece of information at the intersection of a row and column, a dataset for you. This formula will need three pieces of information:
- The dataset you are referencing or pulling information from – so, Dataset B
- The row of that dataset you want information from
- The column of that dataset you want information from
Now, if you are merging two datasets, you need someway to help them compare – or match – to each other. Enter the Match formula, which can give you the row number at which a column has a given value (the information that “links” them). It needs three pieces of information as well:
- The value you are looking for – find it in Dataset A
- The column where you are looking for it – use the columnÂ
- How exact you want your match to be – use “0” here if you want an exact match
These two formulas combine by using the Match formula for the row in the Index formula (so piece of information #2). In my example below, I have the number of animals on each Section of floor in a separate table (Table 2), and I want to add it into my main table (Table 1). So I use Match to find the row of my Animal table where each Section shows up, then I ask for column 2 of the Animal table, where my number of Animals exists.
Table 1 – Where I am adding the number of Animals for Each section
Table 2 – Where I have my number of Animals by Section
The complete Index & Match combo
You can do this across multiple sheets or even separate workbooks – it’s rather powerful! I hope that this and the other tools above – and the ones you will discover as you continue reducing your manual work in spreadsheets – help save you the time and energy I believe we would all rather give to the students and staff with whom we work.
