[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Pivot table excel

Posted on 2012-08-19
8
Medium Priority
?
383 Views
Last Modified: 2012-08-20
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
0
Comment
Question by:Josh2442
  • 4
  • 3
8 Comments
 
LVL 50
ID: 38309464
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
 

Author Comment

by:Josh2442
ID: 38309604
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
 

Author Comment

by:Josh2442
ID: 38309608
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 8

Expert Comment

by:Elton Pascua
ID: 38309636
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
 

Author Comment

by:Josh2442
ID: 38309679
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
 
LVL 8

Accepted Solution

by:
Elton Pascua earned 1200 total points
ID: 38309740
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
 

Author Comment

by:Josh2442
ID: 38309963
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
 
LVL 8

Expert Comment

by:Elton Pascua
ID: 38310140
- 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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question