Pivot table excel

I created a pivot table using 2007 Ms Excel. On the column level my data is only calculated in different aggregated functions. Is there a way to turn off these aggregated functions. My date shows as count as well. How do I make this normal because even with format cells and changing the setting to date it shows absurd date.
Thanks in advance
Josh2442Asked:
Who is Participating?
 
Elton PascuaConnect With a Mentor Commented:
I used 2010 for this so I'm not sure if the options are exactly in the same place. Also, I can't make the SUM appear in the second column because it's a value field (it can't be placed between row fields).

1. Created "PivotSource" dynamic range to avoid "blanks" in the pivot and make it extensible (should you add more rows).
2. Add ID, AMT, STATEMENT_START_DATE, STATEMENT_END_DATE, PAYMENT_RECD_DATE, PAYMENT_DUE_DATE, Reccuring, PostRecurrence as rows.
3. Removed subtotals on the rows (in #2) by right-clicking and unchecking "Subtotal..."
4. Removed row subtotals (Right-click Pivot, Pivot Table Options > Totals and Filters > Uncheck "Show Grand Total for Rows"
5. Renamed the columns to match "Sheet2"
data-pivot.xls
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

date or data?? It's a bit hard to picture the scenario. This might be easier to solve if you could post a sample workbook with your source data structure (dummy data, please) and the desired pivot table.

In many standard situations, the date field would be pulled into either the row or the column area of the pivot table. The data to be aggregated would be pulled into the values area. You can then specify the aggregation type for the values, i.e. count or sum (or other aggregations). With the date in the rows (or columns) you can also right-click any date value and then select to group them, by month, quarter, year, etc.

Again, it would be much easier to see what you want to achieve if you post a sample file.

cheers, teylyn
0
 
Josh2442Author Commented:
Here is a Pivot table. Until the column name SUM of T_AMT the columns seem fine. But when the date columns are placed it doesn't let me select the normal date format. Rather it shows everything as Count. Even the Annual percentage is shown as a count. I would like  normal values in these fields without the functions being applied on it
dATA.xls
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Josh2442Author Commented:
One other thing, i would like to get rid of is the Total at the end of each ID and just have a grand total at the end
0
 
Elton PascuaCommented:
Please see attached file. Is this how you want the data to show? If not, please create a table (not pivot) showing how you want the data to look and we can work it out.

By the way, you can't use the recurrences (Monthly) as values, they're better off as filters or rows.
dATA.xls
0
 
Josh2442Author Commented:
You were pretty close on the last  one. The previous person who prepared these pivot table had not used the recurrences as Filters so i would want it in the report if possible. I have sent you the actual data in the Sheet"RPT" and desired pivot table "sheet 2" but in a regular excel format. Sheet 2 is how the pivot  table looks like. I have other things that I need to put on the filter so if possible please follow "sheet 2". Also send me the instructions of where I should plug in the values.
dATA.xls
0
 
Josh2442Author Commented:
I wonder why it is still not working. The dates were not exactly the same for each. So here is what I ended up with. This is just a sample data. My real data has about 12700 rows and I had 40 ID's and just needed 40 rows as outcome. Also how to create 'dynamic pivot source? On my real data I am ending up with around 900 rows which is not right. But it is similar to the 'Sheet1' layout that I have attached here again with Sheet 'RPT' containing actual data and 'sheet 2' containing the format it should be in.
data-pivot.xls
0
 
Elton PascuaCommented:
- Create a dynamic source by creating a dynamic range for your pivot table data source:
http://support.microsoft.com/kb/830287

Open in new window


- You are seeing more rows than the number of unique numbers in the ID column because of the dates. Your STATEMENT_END_DATE, PAYMENT_RECD_DATE, and PAYMENT_DUE_DATE columns for example, have multiple dates so they're all displayed.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.