Excel Dynamic Chart Monthly and Quarterly Data
Mike.S...@gmail.com
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.
smartin
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.
Mike.S...@gmail.com
>
> - 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!!!
smartin
>
> 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!!!
Can do, it might take me a couple days though. I need to make sure what
I post is stripped of any potentially proprietary info.
smartin
Here's something to look at (just over 1 MB):
http://vfdrake.home.comcast.net/files/excel/monthly_workflow_eg.xls
Here's how it works.
The workflow is from right-to-left. Query data (widget sales by month
and state, with several years of history) is dropped on the "Data Page".
The state is converted to a "Core State", which is representative of
focused areas of sales.
The pivot table summarizes and organizes data by core state within sales
year and month.
On the chart table, the first five columns are mostly static and form
the foundation of the charting ranges. The origin date is adjusted at
the top of the table each month. The table displays exactly 37 months of
results per core state, plus a summary of all states at the bottom of
the table. Column F fetches values from the pivot table. Columns G and H
compute the values to be charted. This is set up this way so we can
create charts for each core state showing a fixed number of results,
ending with the current month. The charts point to fixed ranges on the
Chart Table and do not need to be adjusted from month to month.
This might seem like a lot of fuss to set up--it did take me a couple
hours to get everything up and running. However, once in production,
only three things need to be done to update the file every month:
Drop new data on the data page
Update the pivot
Increment the origin year and month on the chart table
That's it.
Then I fire off the widget sales charts to the VPs in PDF format. My
monthly time investment is less than 5 minutes.