Jump to content


Photo

Customize your fields order

moving your fields

  • Please log in to reply
3 replies to this topic

#1 JPK1066

JPK1066

    Advanced Member

  • Members
  • PipPipPip
  • 83 posts

Posted 26 January 2017 - 11:22 PM

I have a pivot table with row headers from the field that are in the order below and I would like the ability to move them in the order of the second row below.

(0-0) (0-1) (0-2) (1-0) (1-1) (1-2) (2-0) (2-1) (2-2) (3-0) (3-1) (3-2) Grand Total

 

I would like to change it to:

 

0-2 1-2 2-2 3-2 0-0 0-1 1-0 2-0 1-1 2-1 3-1 3-0

 

I would also like to be able to customize the order data is displayed when putting it in a column.

 

Can you help?

 

Thank You

 

JPK1066



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 843 posts

Posted 27 January 2017 - 02:05 PM

Hi JPK,

 

It's possible to manually rearrange pivot table rows into any order you need. You can see how to do this in the video lesson: Expert Skills Lesson 5-6 Apply a simple filter and sort to a pivot table.

 

You should be able to sort pivot table columns in the same way.

 

It's also possible to sort pivot tables alphabetically and numerically (also covered in the video lesson above). If you wanted to do this with your data, you'd need to add another column with numbers in the correct order.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#3 JPK1066

JPK1066

    Advanced Member

  • Members
  • PipPipPip
  • 83 posts

Posted 27 January 2017 - 05:17 PM

I tried to follow lesson 5-6 but the row of data does not let me drag any of the fields. Here's a copy of the page in question:\

 

Count of Column1 Column Labels                         Row Labels (0-0) (0-1) (0-2) (1-0) (1-1) (1-2) (2-0) (2-1) (2-2) (3-0) (3-1) (3-2) Grand Total

 

Excel is telling me that it cannot change this part of the pivot table. What different approach can I take to access movement amongst the fields?



#4 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 843 posts

Posted 27 January 2017 - 06:03 PM

Hi JPK,

 

It sounds likely that you're using an OLAP pivot table rather than an ordinary pivot table, and this is why you're not able to manually change the sort order.

 

You can still apply an alphabetical or numeric sort to your data, so the best approach might be to append a number to the beginning of each of your row headers, indicating which order you wish them to appear in, like:

1 0-2
2 1-2
3 2-2
4 3-2
5 0-0

 

...etc.

 

You could also add a separate column with these numbers and use it for sorting purposes. Apply an A-Z sort should work with an OLAP pivot table.

 

You can see more about the differences between standard pivot tables and OLAP pivot tables in the video lesson: Expert Skills Lesson 6-7 Understand OLAP pivot table limitations.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users