Updating excel 2016 data labels
The sheet should now look like this: Because 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).While we’re on this tab, we should go ahead and defined some named cells and some named ranges.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.This is a slightly iterative process that starts with the setup of the Data tab.(Read that last sentence again if it didn’t sink in — it’s a nifty little way of ensuring the robustness of the report) This little bit of cleverness is really just a setup for the next step, which is setting up the data We’ll do this by defining a named range called Revenue_Range (note how this has a similar naming convention to Revenue_Current, the name we gave the cell with the single value — this comes in handy for keeping track of things when setting up the dashboard).We can’t use VLOOKUP, because that function doesn’t really work with arrays and ranges of data.Microsoft is conducting an online survey to understand your opinion of the Technet Web site.
Click in cell C3, select Data » Data Validation, choose List, and then enter the different values you want as options (I’ve used 3, 6, 9, and 12 here, but any list of integers will work): And, let’s name cell Report Range: Does this seem like a lot of work?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).For the purposes of this example, we’ll go with months.And, in this example, let’s say we’ve got three different metrics that we’re updating: Revenue, Orders, and Web Visits.
This approach can be scaled to include dozens of metrics, but three should illustrate the point.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! 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 — Web Traffic_Current).And, of course, we’ll actually need data — this would come later, but I’ve gone ahead and dropped some fictitious stuff in there: That’s it for the Data tab for now…but we’ll be back!Let’s leave the first row alone — this is where we will populate the “current value,” which we’ll get to later.