Excel Dynamic Chart Monthly and Quarterly Data

[Mike.Schires]

I have sales data with multiple columns for different products, which
has many rows for data each month. I want a quick and easy way to
create a dashboard that shows current month sales, quarterly sales,
both of these compared to last year monthly and quarterly results, and
I want it to be dynamic.

I thought a List would be my ticket, as I can create a list from the
data, plot product sales in each product category, and use a filter to
show the month or quarter or year, but it seems I can only do one at a
time. To do a dashboard, I want all charts to show up at once, have a
filter that does monthly and another that does quarterly. Seems I'd
have to have duplicate lists of data with individual filters on each.
That is a lot of data and seems very inefficient.

I then started using named ranges with OFFSET, and this seemed to get
messy really fast with all the different product data I need to
crunch.

Any ideas? I'm using Excel 2003.

 

[Mike.Schires]

We do a lot of this sort of thing. Our "products" constitute a fairly
short list (perhaps 20 items) which we track over 13 months. The basic
idea is...

Have one worksheet to receive the raw data and with any additional
formula columns needed to facilitate summary levels. This usually
includes keys to do vlookups later.

In another worksheet, create a static column of products, with multiple
rows for as many dates as you need data for (say, 13 rows per product)
and a date column that can be adjusted easily. Vlookup the value needed
(e.g., total sales by product and month) in another column. Add an
additional column to compute year over year values or whatever, and
chart the results.

When it's time to update the charts, drop the new data in, fill down any
formulas, adjust the origin date on the worksheet that looks up the
values, and, voila.

Sometimes (rarely) I accomplish this using parallel pivot tables (e.g.,
one for current year next to one for prior year) and charting tables
(e.g., with the year over year ratios) extracted from the pivots. Yes,
it is a lot of data, and creates duplication, but the pivots make
multiple summary levels very easy as long as the underlying data
supports it.

Once in maintenance mode, just drop new rows in the raw data, refresh
the pivots, change filters as needed, and the charts are instantly updated.

VMMV, of course. I can post an example of the former.- Hide quoted text -

- Show quoted text -

Thank you. An example as you have offered might help. I'm pretty
sure I understand your process, and if I understand it correctly, I
currently do something similar, but I find it pretty manual...but I
might be doing something wrong. I greatly appreciate your help!!!