Flexible Excel Reporting Templates

Subscribe to our monthly newsletter to get the latest updates in your inbox
Often times reporting in Excel—especially when you are creating charts—is a non-flexible task. Each time you run the report with any kind of change in data requires a change in formulas. This process greatly limits automation. Solving the problem requires a bit of initial setup, but allows for a lot more flexibility down the road.

Named Ranges

The key to flexibility is named ranges. If you have ever used tables you have taken advantage of this feature, possibly without even knowing it. For even more power, we are going to take named ranges one step further.

Tables as a Named Range

Let's start by examining how a table works as a named range. If you are familiar with tables and how named ranges work, skip this section. If you don’t know what I am talking about, stick around and we will get you comfortable quickly. When you are working with formulas you may use something like =SUMIFS(D2:D27,B2:B27,"twitter"). This is fine, except when you decide that you want to have a bigger data set and now you need to go beyond 27 rows. A common solution is to put the data into a table. By creating a table you are now able to reference named ranges, changing your formula to something like =SUMIFS(Table1[visits],Table1[source],"twitter"). In this example Table1 is your table name and [visits] and [source] are column headers. You may want to rename your table to hint at what data is contained within the table. Once you have created a table you will find your first named range in the name manager. To access the name manager go to Formulas→ Name Manager.

Named Ranges Outside of Tables

Named ranges are not limited to tables. You can create a named range that points to any cell, set of cells, or formula. The key here is that a named range can refer to a formula! All that is needed to create a named range is a name and what that name applies to. Let's say you want a named range called start_date that refers to the minimum date within a data table. The name would be start_date and the refers to would look like =MIN(Sheet1!$A:$A) where Sheet1 is the sheet your date column resides and A is the column with the date.
This can be very helpful in keeping formulas clean down the line. Give it a try! In an empty cell you can now type =start_date which will result in the numeric date value for excel. A little bit of cell formatting and you have a nice clean, dynamic cell that gives you the first date from column a, no matter how many dates you put in the column. Beyond using the named range as a single value, you can use it within formulas. Let's look at an 'advanced beginner' level example. Say you like to put your reporting date range at the top of every page. Now we have our start date, lets grab our end date (=MAX (Sheet1!$A:$A) and save it as a name end_date. You now have the two named ranges. Next step, combine the two and format them. A formula like =CONCATENATE(TEXT(start_date,"mm/dd/yyyy")," - ",TEXT(end_date,"mm/dd/yyyy")) does this nicely. Going a step further, you could make the whole thing a single named range!
Getting comfortable with named range? Starting to see the power?

Kicking Named Ranges Up a Notch

So, you understand what named ranges are capable of, and how they can really clean up formulas down the road. This alone is great when you share formulas with others and you are trying to explain how they are working. Ever tried to explain a formula to an executive or management group that is long and has a bunch of sheet and cell references? Good luck! Using named ranges with plain English names makes this a much easier task.

Named Ranges for Charts

Let's start by looking at the basic anatomy of a line chart.
You have two items to work with: Legend Entries (series) and Horizontal (category) Axis Labels. Let's explore the horizontal axis first. If we edit this axis we get a single entry box asking for the Axis label range.
Are you thinking named range? You should! This box is looking for the set of dates we want to use for our horizontal axis for this chart data. Let's make that range dynamic! I don’t always want to be forced to run a monthly or weekly report. Maybe I want to come in and look at 12 days of data—rebellious, I know! Being the rebel that I am, it is important to have the date range be dynamic. Let's make it happen.

Dynamic Date Range for Horizontal Axis

We will begin by building the formula, then we can name it and setup our chart to use the name. To build out the formula I first examine what I am trying to accomplish. I want to look at a column, find all of the cells with data in them, and make that data into my horizontal axis for my chart. Easy enough. The first catch is that if I select the whole column, my chart has lots of empty horizontal labels. So, let's fix that. We are going to use two formulas to get the task accomplished. The first formula is offset, the second counta. We can start with counta and work backwards to our final goal. Counta counts all of the cells in a given range that have something in them. So, something like =COUNTA(Sheet1!A:A) will return a numeric value counting all of the cells with stuff in them in column A of Sheet 1. Now that we know how many cells are in our data set we can start with the offset formula. We are going to use offset to limit the size of our series. So, while the name of the formula would imply we are moving the data, really were just going to use the ability of offset to adjust the height and width of a selection. The offset formula is setup with the following parameters: =Offset(Data, rows to move, columns to move, height, width) Since we are not moving the data we can set these to 0, we now know the formula to calculate the height (COUNTA(Sheet1!$A:$A)) and we only want 1 column wide. All that is left is our data set, which should be the same as the data range we are counting the height for (Sheet1!$A:$A). The result is something that looks like: =OFFSET(Sheet1!$A:$A,0,0,COUNTA(Sheet1!$A:$A),1) While the above formula will work (as long as you don’t have header rows), it can start to get very slow to have Excel counting the thousands of rows each time only to know most are empty. Use your own best judgment on your data set to determine a maximum height. A sample formula with a max height of 721 cells would look something like this: =OFFSET(Sheet1!$A$2:$A$721,0,0,COUNTA(Sheet1!$A$2:$A$721),1)

Note: I often use 721 as a maximum as when pulling the data and summing by date. This allows for a full year over year comparison plus a header row. Also note, both the columns and rows are locked references, this is an important step, forgetting to lock ($) both will result in some screwy results.

Input this formula as a named range and remember the name for later (I used chart_date). We will input this named range into the chart once we finish building our named ranges.

Dynamic Values for a Legend Entries (series)

This process is very similar to the one we follow for the horizontal axis. Let's do the same thing we did with the date but for our first set of metrics: =OFFSET(Sheet1!$B$2:$B$721,0,0,COUNTA(Sheet1!$B$2:$B$721),1) and save it as a named range(chart_visits).

Creating the Chart

Now that we have our data setup, let's build the chart. I am using Visits and Date as my metrics and Dimensions for this chart, since its data over time; I'll use a line chart. Start by inserting an empty chart, right click the chart and choose select data. Now select Add Legend Entries
The Series name should point to the column header (Visits). The Series values are where we use our named range. There is a small trick here; you need to add your worksheet name before your named range. It should look something like =Book1.xlx!chart_visits. When done correctly you will see excel highlight your data series.
Now edit the horizontal axis.
Similar to the values for the last set of data, enter your book name followed by the named range. You should now have a simple dynamic chart! You can see mine here.
Couple this technique with a tool that hooks into the Google Analytics API and you are on your way to custom flexible reporting templates.