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 cells in the first row of each metric column (the row labeled “Current–>” 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. The sheet should now look like this:īecause this is now a table, as you add data in additional rows, as long as they are on the rows immediately below the table, the table will automatically expand (and that new data will be included in references to Main_Data, which is critical to this whole exercise). Then, click over to the Table Tools / Design group and change the table name from “Table1” to “Main_Data” (this isn’t required, but I always like to give my tables somewhat descriptive names). Now, turn that range of data into a table by selecting the area from A2 to D19 and choosing Insert » Table. 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. 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.
Excel for mac name a table 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 when I initially implemented this approach: This post describes (and includes a downloadable file of the example) a technique that I’ve used extensively to make short work of updating recurring reports. Macs are a bit of a crap shoot, unfortunately (but you can always run Parallels, so I hear, and use Excel for Windows!). However, it should work fine on Excel 2007 for Windows, too. The content was created in Excel 2010 for Windows. This is that post - 100% plagiarized from the original when it makes sense to do so. The kicker is that I wrote that post before I’d discovered the awesomeness of Excel tables, and before Excel 2010 had really gone mainstream. I’ve been meaning to redo the original post with an example that uses tables, because it simplifies things a bit.
Apparently, through sheer volume of content on the page and some dumb luck with the post title, I consistently do well for searches for “Excel dynamic named ranges” (long live the long tail of SEO!). The single post on this blog that has, for several years now, consistently driven the most traffic to this site, is this one that I wrote almost three years ago. Originally written by Analytics Demystified on June 8, 2013
0 kommentar(er)
