Many Excel users are not familiar with, or are intimidated by Pivot Tables, one of the most powerful features in Excel. This page was written by Harald Staff, and is included here with his kind permission.
Finally Excel asks if the table should be placed in a new worksheet. Now you have your table, and it looks very much like a sorted version of the original data list, except from the automatic subtotals.
Now comes the cool stuff: Grab the What header in the table and drag it all the way to the left.
When you drop it here, the table re-sorts and re-sums; you have a table of beer costs by person instead.
Now drag the Week header to the left and you have a weekly report.
Double-clicking the headers gives options of showing/hiding specific data (like Empty and Beer, may come in handy) and removing subtotaling for this column.
Right-clicking gives other options, among them Hide and Show Detail for reading totals only.Here comes another useful pivot, made from the same list.Select any item in the original data list and choose Pivot Table wizard again.This time, drag Who into the Row field, What into the Column field and Amount into the Data field. This table is -among other things- very useful for graphing. The only tricky thing is this: You have to update the table manually from the Data menu. If this becomes boring, here is some macro code that makes the tables update on selecting the worksheet: Sub Auto_Open() Application. On Sheet Activate = "Update It" End Sub Sub Update It() Dim i P As Integer Application.