Pivot Table?

B

Brig Siton

Hello,

I have an Excel Sheet that contains a full year of data of about 22k rows.
This excel sheets is dynamically being updated by our billing system for
reporting purposes. (Please do not ask me why we don't run the reports
directly from the database itself as I am trying to figure it out myself.)

Anyway, the sheet consists of about 10 columns:

1. FName
2. LName
3. City
4. State
5. LocationCode
6. ReceivedDate
7. ProcessedDate
8. BilledAmount
9. DiscountAmount
10. TotalDue

Now we have a Month to Month Report that tracks how many cases we Received
within that month.

I can do this through autofilters or even manually by sorting and cutting
the sheets.

However, what we want to achieve is some type of automation that the report
we will create will automatically calculate how many cases we receive per
month without disecting and breaking the whole sheet apart into monthly
data.

We are thinking about Pivot Table but how can I limit the query to select
only Jan within the whole list, or Feb, or Mar and so on.

Thank you very much in advance.

Brig Siton
 
P

Paul Lautman

Brig said:
Hello,

I have an Excel Sheet that contains a full year of data of about 22k
rows. This excel sheets is dynamically being updated by our billing
system for reporting purposes. (Please do not ask me why we don't
run the reports directly from the database itself as I am trying to
figure it out myself.)

Anyway, the sheet consists of about 10 columns:

1. FName
2. LName
3. City
4. State
5. LocationCode
6. ReceivedDate
7. ProcessedDate
8. BilledAmount
9. DiscountAmount
10. TotalDue

Now we have a Month to Month Report that tracks how many cases we
Received within that month.

I can do this through autofilters or even manually by sorting and
cutting the sheets.

However, what we want to achieve is some type of automation that the
report we will create will automatically calculate how many cases we
receive per month without disecting and breaking the whole sheet
apart into monthly data.

We are thinking about Pivot Table but how can I limit the query to
select only Jan within the whole list, or Feb, or Mar and so on.

Thank you very much in advance.

Brig Siton
Click to expand...

Make the month a page field.
 
B

Brig Siton

Thank you very much for your prompt response.

Could you tell me how tomake the month a page field?

Brigham
 
R

Roger Govier

Hi

Assuming your date field is one of true Excel dates and not text
representations, and assuming there are no blank fields in the list, you
can drag the Date field to the row area, right click on the field name
and choose Group and Show Detail.
Choose Group and Select Month.

Once the data has been grouped, drag the Date field to the Page Area of
the PT instead of the Row area.
You need to go through the process in the order outlined, as Excel will
not permit Grouping of a Page field item, but grouping it first then
dragging to the Page area will work.

For more help on Pivot Tables take a look at Debra Dalgleish's site
http://www.contextures.com/xlPivot07.html
 
Top