Automatic updating graphs in excel


If you are looking for other Excel 2010-specific information that this post doesn’t cover, please leave a quick comment as to what the change/issue is that led you to the search.Thanks.] 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.All you need to do is put the So, the formula simply takes the currently selected month, finds the row with that value in the data array, and then moves over to the column that matches the current column of the formula: Slick, huh?And, because the Report Period data validation dropdown on the Dashboard worksheet is referencing the on the Data tab, the VLOOKUP will always be able to find a matching value.

And, we’re definitely going to want to have the whole range of data on the tab available to us.So, we need to make it a named cell — Report Period: Now, let’s do a similar operation for the report range — this tells the spreadsheet how many months to include in each chart.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?For the purposes of this example, we’ll go with months.

Let’s leave the first row alone — this is where we will populate the “current value,” which we’ll get to later.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.


Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>