How to best display year over year sales growth

**manning457** · 05-20-2015, 11:40 AM

Hi everyone,

I've worked with excel for awhile now so I'm fairly familiar with it, but I'm stumped on a recent project I've been given. We have weekly sales numbers from about 30 stores, and my goal is to create an aesthetically pleasing and simple to understand chart/pivottable/graph etc. that displays the year over year growth for comparable weeks each year (the data organizes weeks sunday to Saturday, so it wont match exactly on the calendar year to year but itll mainly account for seasonal variances.)

I've considered doing a database format, where I use values for the stores location, weekly sales amount, the week (numerically listed 1-52), the year, etc, and create an individual entry for each store, for each week. Then I could make a pivottable to display the sales numbers, and I could change the "value field setting" to display values as a "percentage of", "year", and "previous", but this is kind of data overload with this many stores, and also I don't think the values it gives are necessarily the growth of, for example, week 3 of this year compared to week 3 of last year. In fact, I really don't know what it is using as the base value, I think it takes the average of all the values for one year, and compares each number for next year against that value. Also, I think it holds the first year as the base value, and compares each other year to that, which isn't what I'm looking for.

I also need this to be scalable into the future, so that in 2016 it will be able to display the growth from 2014 to 2015, and 2015 to 2016, for every store, again, without being totally unreadable.

Does anyone have any ideas? Like I said, I know how to work with excel reasonably well, but I'm at a loss for which direction to go here. I think a Pivottable is a good option because with the amount of data I'm working with, it would be nice to manipulate it easily to show only certain things at a time, in case there is no way to show this amount of data simplistically.

If you need any more information, feel free to ask.

Thanks for your help,

manning457

**mikeTRON** · 05-20-2015, 01:17 PM

So it seems you have a lot of data, are they wanting to see each store individually, or the aggregate, or either/or?

I have done things like this in the past, and I like creating a pivot like you said with week 1-52 so then you can stack them easily by year. Then I use slicers on the chart to filter and control what is visible on the chart.

It all depends on what you are trying to see exactly.

I actually created something similar (but for 500+ locations and for 6 years) that allowed the user (my boss) to select the two years, and the location(s) and it would display each year as a line and the locations as ONE aggregate. so you can select a specific location, or a division (group of specific locations), a region ( a bigger group of locations) or the total company.

I basically had a pivot table layout already completed and a simple dashboard to pull the data into the CHARTDATA area and the rest was just vlookup driven. Very simple and Chandoo approved.

**manning457** · 05-20-2015, 01:22 PM

The point of this chart is just going to be to give a snapshot of the growth for every store in a given period, if further information is needed obviously we have the actual weekly data already entered elsewhere, and that could be looked at separately. Like I said earlier, I think I know a way to display it, but it doesn't look very good, and I am really trying to impress with this one (one of my first projects for a new boss), so I want something that is easy to read and attractive.

Could you take a screenshot of your pivottable with all the weeks, if there isn't any proprietary information in it? I'm just having trouble visualizing how it will look, and before I take the time to manually enter in every one of these data points I want to know that it's going to work out.

Thanks, manning457

**mikeTRON** · 05-20-2015, 01:30 PM

MANUALLY enter in the data?

Just use the ranbetween formula to prepopulate data to then start playing with charts, if you dont have the real data.
I teased out a column for WeekNum, and Year and Month each, so I could have any of those on the X Axis.

My file has entirely too much sensitive data and is probably 20MBs with all the data in it lol