0

It seems so silly, but I am trying to set up my pivot table, and it doesnt seem to be my strongest side.

The following picture demonstrates what I am after:

enter image description here

... so basically in the data there is a currency string, however it seems as if I cant get this string to be represented in the matix - only in either the row labels or columns labels, which makes the table very unstructured. Is there a way to match the security (row label) with its denominated currency ?

this part of my source table:

enter image description here

... where CCY is the currency column I would like within the pivot table and not as a pivot row or column label.

/ Phillip

Improve this question
2
  • "sext string" meaning "text string", or? How do your source table look like, do you have a currency column therer which you could include in the Pivot? Commented Sep 7, 2016 at 12:03
  • Hi @MatsLind Yeah sorry for the typo - just to get someones attention :) I have updated my question with a snip of my data (source) table. Commented Sep 7, 2016 at 12:22

5 Answers 5

Reset to default
1

Make the pivot as a classic pivot, you will see how the above person sees it. Classic pivot is your answer of putting in text strings inside pivot. It somehow aligns better.

Right click on pivot --> Pivot table option --> Display --> Check the box which says "Classic Pivot table layout".

Take a backup of the excel copy before you switch to classic pivot. Some say it's irreversible

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Hi @Siddharth Sheshadri This was good help thanks. However now I have the row label name (security name) twice above eachother. One regular and the next "security name" TOTAL - how do I get rid of this ?
:).. Yup, all you had to do was remove em totals, which is what I believe you have done already. Glad I could help. Cheers!
0

Include the currency-field in the Pivot table, like this:

enter image description here

Improve this answer

Comments

0

This is exactly what I am after @MatsLind however this is what I receive when doing this:

enter image description here

so my question is why is our results different ?

Improve this answer

1 Comment

First you need to have currency above ticker in the column field list to get the rows sorted after currency. Then you need to sort on the currency. Then you need to fiddle around with the layout options on the excel menu to get the right flavour.
0

Please let me know if I am totally off, but end goal should preferably be the following image: enter image description here .. however with this sorting (1.Security , 2.CCY) I am not allowed to sort the value within the pivot table. If I make the follwing sorting (1.CCY, 2.Security): enter image description here

then I am allowed to sort the data within the pivot table, but I would prefer the former layout.

Improve this answer

1 Comment

@SS97 due you have an idea for this ?
0

Can't add comments due to lack of reputation but this ain't an answer exactly.. Maybe..

From what I see in the below image, it looks like you have the row CCY on top and then security. If you want it like the top image, change the hierarchy.

Go to field list and under rows, drag and place the Security on top of CCY, youre pivot will look like above picture.

But you're speaking of sorting, it looks like the above picture has been sorted with the grand total column.. I didn't follow you completely on the sorting peice..

Edit: I understood the problem now, what values exactly are you trying to sort?

Improve this answer

4 Comments

Hi @SS97 Ultimately I am trying to sort the Grand Total column. As is now both pivot tables are sorted from largest to smallest amount. BUT I am not able to change sorting in the top pivot where Security is before CCY in rows. But in the second pivot, where CCY is before Security I AM allowed to sort ?? Thanks alot, Phillip
It is technically not plausible. Both should allow you to sort. What happens when you are trying to sort when security is before CCY? Nothing? Edit: Also I see that both are sorted largest to smallest!! Are you trying a different sort? If so, what?
I am trying to sort smallest to largest. Everything is possible in the last pivot, but not in the first. Should I try to re-create the pivot table - could that help ? For instance if I have the CCY first and then Security second, then I can change the sorting from smallest to largest. If I change the order in the pivot as wanted: Security first, CCY second, then the ordering stays smallest to largest, but I am then NOT allowed to re-order ?
? Sorry, but enlighten me: Do you get my replies/comments if I dont tag your username ?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.