DYNAMIC NAMED RANGE IN EXCEL UPDATE
DYNAMIC NAMED RANGE IN EXCEL SOFTWARE

Define a new range with a Workbook scope that encompasses all the columns and all of the rows of data (starting at row 3): LetвЂ™s call this MainData and define it by going to Formulas В» Name Manager and clicking on New (this is Excel 2007 - itвЂ™s somewhere else easier to find in Excel 2003). The screen capture below shows this being done for the cell where the current value for Orders will go, but this needs to be done for Revenue and Web Traffic as well (I just remove the space for Web Traffic - WebTraffic_Current).ДЄnd, weвЂ™re definitely going to want to have the whole range of data on the tab available to us. ItвЂ™s important to have consistent naming conventions, so weвЂ™ll go with _Current for this (it works out to have the metric identified first, with the qualifier/type after - just trust me!). Name each cell by clicking on the cell, then clicking in the cell address at the top left and typing in the cell name.

WeвЂ™ll name the cell in the first row of each metric column as the current value for that metric (the cells donвЂ™t have to be named cells, but it makes for easier, safer updating of the dashboard as the complexity grows). While weвЂ™re on this tab, we should go ahead and defined some named cells and some named ranges. That leaves us with a Data tab that looks like this: This approach can be scaled to include dozens of metrics, but three should illustrate the point. And, in this example, letвЂ™s say weвЂ™ve got three different metrics that weвЂ™re updating: Revenue, Orders, and Web Visits. I like to use a simple shading schema to clearly denote which cells will get updated with data and which ones never really need to be touched. LetвЂ™s leave the first row alone - this is where we will populate the вЂњcurrent value,вЂќ which weвЂ™ll get to later. For the purposes of this example, weвЂ™ll go with months. On that worksheet, weвЂ™ll use the first column to list our dates - these could be days, weeks, months, whatever (they can be changed at any time and the whole approach still works). This is a slightly iterative process that starts with the setup of the Data tab. See An Excel Dashboard Widget for a look at my thoughts on dashboard visualization.) Data Tab Setup - Part 1 This basic approach is one of the core components in the dashboards I work on every day, and it can be applied to a much more robust visualization of data than is represented here. ( Note: I abhor many, many things about ExcelвЂ™s default settings, but, to keep the example as familiar as possible, IвЂ™m going to leave those alone. I like to just have the first worksheet as the presentation layer - letвЂ™s name it Dashboard - and the second worksheets as the data layer - letвЂ™s call that Data. One key here is to separate the presentation layer from the data layer. The example shown here is pretty basic, but the approach scales really well. No macros used - I donвЂ™t have anything against macros, but they introduce privacy concerns, version compatibility, odd little warnings, and, in this case, arenвЂ™t needed.

DYNAMIC NAMED RANGE IN EXCEL SOFTWARE

No use of third-party plug-ins - one of these days, IвЂ™ll get around to playing with the various Excel add-ons like those offered by Tableau Software and XLCubed (or even the Peltier Tech add-ins, which are targeted but made by one of the top 3 most authoritative Excel resources on the вЂnet), but that adds just the slightest of barriers and, again, isnвЂ™t needed for this exercise.

Reporting of the most recent value (think sparklines, where you want to show the last x data values in a small chart, and then report the last value explicitly as a number).

DYNAMIC NAMED RANGE IN EXCEL UPDATE

Single date/range selection to update multiple charts at once.

User-selectable range of data to include in the chart.

Here are the criteria I was working against: This post describes (and includes a downloadable file of the example) a technique that we use extensively to make short work of updating recurring reports. My favorite on that front is Jon PeltierвЂ™s (if you get intrigued by this post, hop over and peruse a slew of other ways to have charts dynamically update). There are lots of blogs devoted entirely to Excel tips and tricks. This blog isnвЂ™t really focussed on all of the myriad ways that Excel can be contorted to represent data effectively, but IвЂ™m a big believer in using tools as effectively as possible to remove as much rote report generation as possible. IвЂ™ve had a pretty good run of theoretical posts about the nature of marketing measurement of late, so it seemed like I was due for a more down-in-the-weeds-Excel-efficiency-tactics write-up. Originally written by Analytics Demystified on August 17, 2010

Dynamic named range in excel