Create a Dynamic Month List for Reports or KPIs

In this tutorial, we will learn how to create a dynamic list of months in Excel using various functions including TAKE, EOMONTH, and SEQUENCE. This list can be used for reporting or pulling key performance indicators (KPIs) with lookup functions such as XLOOKUP. If you create the same basic report each month, and would like Excel to automatically update the periods used, this post can help.

Video

Step-by-step Narrative

We’ll work through three exercises to understand the functions and techniques we can use.

Exercise 1: Using the TAKE function to pull the values from the last several rows

In this first exercise, we’ll assume that you keep appending the next month to the end of a Table, and you would like to write a function that retrieves the last 3, 6, or 12 months from the table. That way, you’ll always have the most recent periods in your report.

For example, you just keep adding one new row for each period to this table (Table1):

And when you add a new row, you’d like Excel to automatically update your report, which looks something like this:

Assuming you aren’t concerned about the sort order of the data table, and you always want the last X rows, we can use the TAKE function.

We write the following formula in B8:

=TAKE(Table1, -3)

• Table1 is the name of the data table
• -3 means the last three rows (3 would be the first three rows; positive numbers return from the beginning of the range and negative numbers return from the end)

When you add a new period to the end of the table, like for the next month, the report will automatically update the values. Nice!

In this simple case, let’s pretend you are the one updating the workbook each month. As such, you understand that the if you change the sort order of the table, the TAKE function won’t necessarily give you the desired values. So, what if you want to be more specific about which months to retrieve? For that, we’ll need a different approach which leads us to the next exercise.

Exercise 2: Return the required number of periods

Let’s say that you want to retrieve values for specific months (not simply the ones at the end of the table). This will ensure the formula works even if the sort order changes, and even when your report should reflect prior periods.

We can use the EOMONTH/SEQUENCE combo to generate the list of periods. Let’s check it out.

We’d like to be able to enter the number of periods in our report, and the starting period like this:

We can use the EOMONTH function to generate the last day of a month, and use the SEQUENCE function to generate the number of months desired.

In B12, we write the following:

=EOMONTH(C7, SEQUENCE(C6,,0))

• EOMONTH computes the last day of the month starting with the date in C7
• SEQUENCE generates a list of X numbers (the quantity of months in C6), starting at 0

When we hit Enter, bam:

Now, we can change the starting date in C7, and the number of months in C6, and Excel will dynamically create the month list accordingly. Now all that remains is retrieving the corresponding KPI or report values from our data table. And we’ll tackle that in the next exercise.

Exercise 3: Retrieving the report values

With the list of desired periods created, we now need to use some type of lookup function or conditional summing function to retrieve the report or KPI values. In this case, our data table has one and only one value for each month so we can use any lookup function. In this example, I’ll use XLOOKUP but you could just as easily use VLOOKUP or INDEX/MATCH. Had the data contained multiple values per period that needed to be summed, we would use a conditional summing function such as SUMIFS instead. Or, if we wanted to compute the average of multiple values, we could use AVERAGEIFS.

In this case, we’ll use XLOOKUP since we only have one row per month, and we’ll write this in C11:

=XLOOKUP(B11#, Table1[Period], Table1[KPI])

• B11# is the period in the cell to the left, followed by the spill operator # so that it includes all periods
• Table1[Period] is the column we are searching
• Table1[KPI] is the column that has the value we want returned

We hit Enter, and bam:

This is a very flexible approach because the sort order of the data doesn’t matter. In other words, nothing breaks if you sort the data descending by date instead of ascending. Plus, you can change the start date or number of periods and the formulas automatically populate the report 🙂

Conclusion

By following the exercises outlined in this tutorial, you can generate a flexible and reliable list of months for your reporting or KPI tracking needs. We used TAKE when our needs were simply retrieving the last X values from a data table. We used EOMONTH/SEQUENCE to generate a specific list of periods, and then used XLOOKUP to retrieve the corresponding values.

Remember, these formulas can be customized based on your specific requirements, allowing you to create a dynamic Excel solution that suits your data analysis needs. For example, if your data table contained multiple rows per period instead of a single row, you could aggregate the values with SUMIFS. Enjoy exploring the possibilities and harnessing the power of Excel!

If you have any suggestions, enhancements, or questions, please post a comment below.

Sample file

FAQ Section

Q: What is the advantage of using the TAKE function?

A: The TAKE function allows you to easily pull the last several rows of data without manually specifying the periods. It is a simple and dynamic way to retrieve the desired values when the sort order doesn’t change.

Q: What if the sort order may change?

A: The approach shown in Exercise 3, which uses the EOMONTH, SEQUENCE, and XLOOKUP functions, is more reliable because it allows the user to enter the desired number of periods and the start date. It is not affected by changes in the sort order, making it more consistent for reporting purposes.

Q: Can I modify the number of periods returned?

A: Yes, you can modify the number of periods by changing the cell value referenced in the SEQUENCE function.

Q: How can I format the date values correctly?

A: To format the date values, select the cells with the date values, right-click, and choose the desired date format from the formatting options. This will ensure the dates are displayed correctly.