This is the first post in a series that shows how to transform a monthly report from being updated manually to automatically. Along the way, we’ll see how classic Excel features like tables and SUMIFS can help somewhat, and then how modern Excel features like Power Query and the data model can help even more. Each post in the series provides the next step in the automation sequence.
I named this series “Stop Wasting Time” because when we spend more time than is necessary to do a task, we are wasting time. We tend to update our Excel workbooks using manual steps, just like last month. And we feel too busy to learn a more efficient way. So we just keep doing the same manual steps every month. Meanwhile, Excel is just sitting there, waiting to help!
If you like sports, imagine you are the coach and Excel is sitting there on the bench. Excel is like your star player, and you aren’t putting Excel in the game. Excel is sitting on the bench, waiving its arms saying “coach, coach … please put me in the game!”
This series is designed to demonstrate how to get Excel into the game. Throughout the series, we’ll discover Excel knowledge that will help us move our workbooks from manual to automated. After all, if we are busy, we certainly wouldn’t want to spend more time doing something than is needed. So, that is the big picture for the series, and I hope you enjoy it!
Before we get too far, let’s take a look at our sample report. Since I have an accounting background, I’ll illustrate this process with a classic financial statement. However, the steps and process can be applied broadly to many other types of reports.
Here is the report that we’ll improve over the next few posts:
And, here is the source data, which changes every month:
Our goal is to automatically get the new monthly data into the report.
We’ll start off easy, and assume the data is already cleaned up and in Excel. But as the series progresses, I’ll increase the complexity of the illustration so we can learn just how powerful the modern Excel tools are.
Since we are going to show the full progression of this report, from manual to automated, we’ll start by reviewing the manual update process.
All Excel users are on a journey, and we are at different places along the path. But, we all start at the beginning. We all open Excel for the very first time and see the grid. We get in and start playing around and figuring things out. Early on in our journey, we end up doing a lot of things manually. Why? Just because we haven’t learned more efficient options. As we learn more, we are able to automate more. And our journey continues like this … learning and improving as we go.
But, sometimes, we can get to a certain point and feel comfortable enough to stop improving our workbooks. Perhaps we get busy at work and decide the workbooks are good enough. We know enough to update the monthly reports. Even though it takes many manual steps, at least we know how to get them done. Sure, there may be a bunch of manual steps, and sure we may have to work after hours and weekends. But, at least we are able to get them done.
But here’s the thing: we can delegate A LOT of manual tasks to Excel. That is, we can automate a lot of stuff with Excel. I like to think about it like this. At the very beginning of our Excel journey, we do things manually. But our goal is to get to the place where our workbooks are automated. And the thing that moves us from “manual” to “automated” is Excel knowledge. I visualize it like this:
Along our journey of Excel knowledge, we learn lots of cool things that help us work faster. At the beginning of our journey, we may update our report by using Excel like a digital 10-key. Perhaps we’d use formulas like this:
And that approach allows us to finish the report. So, we use it … for a while. Until we discover that each time the data changes, we have to literally rewrite every formula. Trying to figure out a better way, we decide on a different approach. A more efficient approach. One that doesn’t require us to rewrite every formula when the values change. We use direct-cell references, like this:
This is an improvement because as the cell values change, they flow into the report automatically and we don’t have the rewrite formulas.
But, then we discover that this approach is fragile, and breaks easily. For example, our formulas break when the data values occupy different cells, the data is sorted in a different order, or a new account is added. When the formulas break, we have to rewrite them…manually.
So, we set out to improve the workbook with some classic Excel features.
Classic Excel to the Rescue
Our current approach has some issues … basically, changing the sort order breaks the formulas and adding new accounts requires us to rewrite formulas. So, let’s take them one at a time. We’ll address the sort order with SUMIFS and adding new accounts with Tables. Let’s start by converting our ordinary data range to a Table.
When we have a data range that may expand, for example new accounts or transactions being added, it is a good idea to store that data in a Table rather than an ordinary range. Why? Because tables auto-expand to include the new transactions. And we can refer to the data in a table with structured table references rather than A1-style range references.
To convert our ordinary range into a table, start by clicking any cell in the data range, like this:
Then select the Insert > Table command. Excel displays a confirmation dialog. We click OK and our table is created:
Tables have many advantages. One advantage is that they auto-expand. When you type or paste new values immediately under (or right), the table will automatically expand to include it. This means if we enter a new account, for example Payroll Checking which maps to the Cash and Cash Equivalents FS Line, it will be automatically included in the table. But, we need to understand how to reference table ranges in our formulas. So, let’s talk about names.
Tables have names. You can view/set the name with the TableTools > Table Name field. Our table is named Table1, and we can use that name in our formulas to reference the table data. But, we can also reference a single table column by using a structured table reference. To reference a specific column, we use the table’s name and then the column name in [square brackets], like this: Table1[Amount] or Table1[FS Line].
Now, let’s see how we can use SUMIFS to summarize the data in our new table.
SUMIFS is a wonderful function, and it is designed to add up numbers in a column based on matching row labels. The first argument identifies the column of numbers to add, and the remaining arguments come in pairs. Each pair defines a condition. Officially, it looks like this:
=SUMIFS(sum_range, criteria_range1, criteria_value1, ...)
But, I’ve come up with a narrative I use to help me remember the arguments. Each bold word in the narrative represents an argument:
“Add up this column of numbers, but only include those rows where this column is equal to this value.”
For example, if we wanted to add up the table’s amount column, but only include those rows where the FS Line column matches our report’s label, we could use something like this:
We fill the formula down to compute the remaining report values:
Same report as before, but this approach is more efficient to update each month because we aren’t rewriting formulas when the sort order changes. Yay!
We started our journey by performing manual updates. To improve our report, we added a table so that as new rows are added to the table they are automatically referenced by the formulas. We then used the SUMIFS function to compute the report values.
We moved the report to the right a bit … from being manually updated to being more automated:
We still have many improvements to make, and we’ll cover them in the upcoming posts in the series … stay tuned!