While I don’t love the phrase, “work smarter, not harder” (there’s a great article here on why), I think that too often we find ourselves doing tasks that we can do more efficiently. My colleagues have shared their tips for working efficiently already, such as using tabs to batch task repetitive work, using text expanders to save time, and even I’ve written variations of this idea with my post on using mail merge to quickly create data-driven documents. This time around, I’m returning to my finicky, temperamental, and all-too-powerful favorite tool: Microsoft Excel.

For years, two of our primary teams were tracking data in separate places, even though each group can benefit from the other’s knowledge. With my background in research and data science, I dreamt of a way for us to share our knowledge easily in one place in a relational database. One of my favorite relational databases, Microsoft Access, simply wasn’t an option for us. (Microsoft Access is not compatible with Apple computers and doesn’t allow for multiple users to edit and work at the same time.)

After a months of searching, I settled on a cloud-based database system called Kintone. With this system in place, we were able to start moving our data into a centralized database. With each quarter at DePaul, we need to load new information from our registration system into this database, which we use to primarily track online and hybrid courses. The biggest problem we faced here was how to quickly and easily get data into our database.

Enter Microsoft Excel.

When we download the data from our registration system, it comes as an Excel file or a .csv text file. Before we can upload the data into Kintone, we have to reformat the data to follow conventions we have set in place about course naming, staff assignments, etc. The first term we had to do this data reformatting for upload, we did it by hand. This process took at least a day to reformat over 500 course listings. The next couple terms, we wrote in formulas to the spreadsheet by hand. While this was significantly faster, it still took longer and began to feel needlessly repetitive.

So I created what I’ve called, “The Magic Kintone Book.” It’s an Excel workbook with eleven sheets. The first three sheets are “data dumps.” In these sheets, data is pasted from the data we receive from the registration system. One sheet holds the data about courses, one sheet holds the data about instructors, and one sheet holds the data about course numbers in our learning management system. After these three sheets, are the “formatted data” pages. These two sheets are purely built of formulas, such as VLOOKUP, CONCATENATE, and IF functions. (If you aren’t familiar with any of these, I highly recommend checking them out; they are cool and powerful tools that can save you time and make your workbooks do more work for you.) Each cell is set up to look up and surface data from the “data dump” sheet, while also making decisions based on other data we input from the other informational sheets. For example, we can include deadlines and staff assignments to a course in the “Formatted Course Data” sheet using VLOOKUP to grab course data from “PUT COURSE DATA HERE” and use IF functions to decide which staff member to assign based on the “Assignments” sheet.

This book makes our work far less repetitive and the time we spend formatting is significantly decreased even further. All it takes to format the data for upload is to dump our data in one sheet, then copy the data that is surfaced in another. The entire process takes less than five minutes each term.

If you find yourself often doing repetitive spreadsheet or formula work, I recommend trying to make your workbooks work smarter and harder for you. The time it takes to set up this cascading sheet system can be annoying, but it makes the time you don’t have to spend later worth it. If you’re interested in seeing more about how our system works, or want to know more about building your own, feel free to reach out to me.