Hi Brad,
Okay, that's a good clarification. This is probably going to seem like
entirely too much work, but it's going to save you a lot of time in the long
run.
First, you need what Excel calls a list. A list in Excel has field/variable
names in its first row, and the values of those fields in its second and
subsequent rows. The list is just a matter of laying things out properly.
With the data as you describe it, your list would have five columns, and
each column would be headed by the name of a different variable. So, cell A1
would contain the name Area, B1 the name Store, C1 would contain the name
UPC, D1 the name Sold, E1 the name Date.
The way you're getting your data conforms to that structure, except for
Date, which is just on the sheet tab. You could type that date in E2, under
the name Date, and copy-and-paste that value into as many rows as you have
records for the other variables. Or if this is something that will recur
each week for many weeks, you might consider recording and saving a VBA
macro that picks up the name of the worksheet tab and copies it into column
E for all the new records you've received.
I also suspect that you want to continue your weekly data for many weeks and
do your analysis over time, rather than in one shot analyses that each look
at one week only. In that case, you're going to want what's called a dynamic
data range. Worksheet ranges, like A1:E500, can be named in various ways,
such as Insert | Range | Define. If you handle things the right way, you can
get Excel to automatically redefine a range's address according to how many
rows and columns are in it.
As you describe things, you would have five columns only, so all your
dynamic data range would have to figure out is how many rows it has. To keep
things straightforward, assume that the Area values in column A define the
number of rows in the data range: if there are 200 valid Area values, there
are 200 valid sales records. (There are ways to avoid making this
assumption.)
So, on the worksheet where you have put all your data, both the current and
the prior, you could define (you need do so once only) a name such as
SalesData, by choosing Insert | Name | Define, typing SalesData into the
Names in Workbook box, and this formula into the Refers to box:
=OFFSET($A$1,0,0,COUNTA($A:$A),5)
This range definition starts with cell A1 (argument 1) and offsets the range
by 0 rows (argument 2) and zero columns (argument 3). It offsets the range
by zero rows because you need to tell Excel the variable/field names in Row
1. Because you have one data record for each value in Column A, less 1 for
the variable name in Row 1, Excel makes the range have that many rows, but
it includes the first row for the variable names, because you have to pass
those along to your pivot table (see below). And as your data comes to you,
and after you've pasted the date into column E, the range has five columns.
Each time you get another set of data, you can copy that data and paste it
into this data range, meanwhile copying the date into the associated cells
in Column E. The range named SalesData will redefine itself when Excel
counts the newly pasted values it finds in Column A.
(BTW, one drawback with this sort of dynamic name is that the name does not
appear in the Name Box. I'm in hopes that this longtime oversight will be
corrected in Excel 12.)
The hard work is now done, and you're about to start saving time, big time.
Somewhere else -- presumably in the same workbook and, to keep things neat,
on a different worksheet, you establish a pivot table. The steps differ a
bit depending on which Excel version you're using, and I'll assume a more
recent one. Choose Data | PivotTable and PivotChart Report. In the pivot
table wizard's first step, choose Microsoft Excel List or Database, and
accept the default PivotTable report (you can change it to a chart/report
later, and this is simpler).
In step 2, type the name of the sales data range -- I suggested SalesData
above, but it can be anything that isn't smutty. Actually, it could be
smutty, but I promised my mother years ago that I'd never base a pivot table
on a data range that had a smutty name.
In step 3, indicate where you want to put the pivot table.
Then, the pivot table wizard disappears and you drag fields into the table
layout on the worksheet. You might want to drag Date into the Row area, UPC
into the Column area, and Sold into the Data area. Assuming that you have
exclusively numeric values in the Sold field, it will default to Sum, and as
I've described the table's layout you'll get the sum of units sold for each
date and each UPC.
You can have more row and column fields, though. One arrangement might have
Sold in the Data area, Date in the Column area, and both Area and Store in
the Row area. This would give you a subtotal for store within area.
BTW, you can group on the Date field to force it to show sales by something
other than week -- month, quarter, year, quarter within year, etc. This sort
of thing is why I've always called pivot tables the most powerful method of
data analysis and synthesis in Excel.
There's another kind of field in pivot tables, a Page Field. It's a way of
selecting a subset of records. If you put, say, Area into a Page field, you
can look at your table for a particular Area that you select via a dropdown.
Also, bear in mind that a pivot table does not automatically react to the
presence of new data. You'll want to right-click a cell in the table and
choose Refresh Data from the shortcut menu. Although your dynamic data range
automatically refreshes itself when new data comes along, the pivot table
doesn't, and you need to call its attention to the fact that more data has
come along. (Again, there are ways to automate that, but at the outset it's
best to keep things straightforward.)
