Pivot Tables

[Isabella]

Hi,

Could someone please show me how i can perform a pivot table that will give me the same result as show in Col J-P. I want to avoid using formula if possible.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
4											2-29			30-59
5	Type	Amount	CCY	Age	Source		CCY	Rate		Source	No. of items	Value (AUD)	ABS (AUD)	No. of items	Value (AUD)	ABS (AUD)
6	SCR	8268.57	AUD	15	Team1		AUD	1		Team1	3	29,268.57	29268.57
7	SCR	20000	AUD	20	Team1		EUR	0.75581		Team2	1	6,844.94	6844.94067
8	SCR	1000	AUD	5	Team1		GBP	0.645411		Team3	0	0.00	0
9	SCR	4417.8	GBP	7	Team2		JPY	82.56223		Team4	17	29,314.60	1803231.35
10	SCR	2208.9	GBP	30	Team2		USD	1.00655
11	SCR	100000	AUD	40	Team3
12	LCR	1163.22	EUR	12	Team4
13	LDR	-3320.14	EUR	39	Team4
14	LCR	9646.51	EUR	15	Team4
15	LDR	-12618.8	EUR	40	Team4
16	LCR	476017.8	EUR	15	Team4
17	LDR	-1688137	JPY	38	Team4
18	LCR	7187517	JPY	4	Team4
19	LDR	-6303971	JPY	15	Team4
20	LCR	108431.3	USD	15	Team4
21	LCR	15652.36	USD	15	Team4
22	LDR	-280467	USD	17	Team4
23	LCR	52025.14	USD	17	Team4
24	LCR	7589.03	USD	17	Team4
25	LCR	1965.98	EUR	3	Team4
26	LDR	-98555.5	EUR	17	Team4
27	LDR	-33658.3	EUR	17	Team4
28	LDR	-19670	EUR	10	Team4
29	LDR	-247310	EUR	17	Team4
30	LDR	-2068.46	EUR	13	Team4
31	LDR	-800	EUR	28	Team4
Sheet1

 

[ManDrone]

It's not that simple. Just like formulas, pivot tables have a specific function - to merge or aggregate data that has the concept of "groups". Your data has this structure so you can get what you need (almost). However, the data has to be in the right format first (this is always a problem with data analysis - getting data in a usable format).

Pivot tables basically collapse and sum/count - think of it like sorting clothes into piles based on what they are - you could have a sock pile, a shirt pile etc. or a "white stuff" pile and a "red stuff" pile. Pivot tables tell us how many white vs red things we have when we look (pivot) one way and how many socks vs shirts when looked at another way (and it can get a lot more complex that that, of course).

All you need to do to get your data in a pivot table friendly manner is to add a column that converts your currencies to AUD. It's much more simple than using no pivot table at all and writing all the formulas out.

 

[Fazza]

hi, Isabella

The result is pretty much identical to post #64 in thread http://www.mrexcel.com/forum/showthread.php?t=517419

Just include the Age field and make it a column field - group on it as required.

FYI, ManDrone, the pivot table can combine data from multiple tables. In this case, including a calculation (currency conversion) without adding a column of formulas.

regards, Fazza

 

[Isabella]

Fazza,

I named my main source data as "MainTable" i then named the currency table as "Forex" I then opened a new workbook, clicked Insert and then Pivot Table, i clicked on Use external data source, i pressed choose connections, but all i can see was MsnMoney etc.

What is the next step?

hi, Isabella

The result is pretty much identical to post #64 in thread http://www.mrexcel.com/forum/showthread.php?t=517419

Just include the Age field and make it a column field - group on it as required.

FYI, ManDrone, the pivot table can combine data from multiple tables. In this case, including a calculation (currency conversion) without adding a column of formulas.

regards, Fazza

 

[Fazza]

Please refer post #64 of http://www.mrexcel.com/forum/showthread.php?t=517419

From a separate new file start the pivot table (ALT-D-P) wizard taking the external data option at the first step. Then Get Data, Excel files, etc. Continue to the end & take the option to edit in MS Query. Then the 'SQL' button & edit to be like,

Google should find an example, too.

 

[Isabella]

Please refer post #64 of http://www.mrexcel.com/forum/showthread.php?t=517419

Google should find an example, too.

I clicked connection and then i clicked Browse and then i selected the file that has data, but where did you see the edit part for SQL?

 

[Fazza]

Continue to the end & take the option to edit in MS Query.

 

[cnestg8r]

You could also stick to pivot tables using SQL in the background. It works nicely, but the formulas are OK if you are not familiar with SQL.

FOLLOW Fazza - he is the king of PT with SQL.

 

[Isabella]

I am using Excel 2010 and your method does not work. I am ok until I have named the ranges.

1. I open a new workbook

2. I click on Pivot table from the insert menu

3. I then click "Choose Connection"

4. When i click Connections i can see under "Connections files on this computer" Book1 Forex, Book1 Maintable.

5. When i select maintable, I then use the open button at the bottom of screen box, when i click open i get the Pivot table

I dont know how to edit

 

[Fazza]

OK, Isabella. Thanks for clarifying the steps. I don't have Excel 2010, sorry.

FWIW, prior to Excel 2007 the steps are,

• ALT-D-P to start pivot table wizard
• choose "External data source", Next
• Get Data...
• from the Databases strip, "Excel files*", OK
• then follow wizard to the end & take the option to edit in MS Query

This might help you to work out the steps in Excel 2010. Suggest you refer to the help files. Maybe google the Microsoft site?

regards

 

[Fazza]

I found this Microsoft site, it says there is a video. (Not that I see it, I guess the security settings where I am prevent me seeing it...) http://office.microsoft.com/en-au/excel-help/video-create-a-pivottable-report-VA101809910.aspx