#EXCEL FOR MAC NAME A TABLE UPDATE#

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.

No macros - I donвЂ™t have anything against macros, but they introduce privacy concerns, version compatibility, odd little warnings, and, in this case, arenвЂ™t needed.

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).

#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

Excel for mac name a table