• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member
  • Login

Return to original sorting after using the slicer|Power Pivot|Excel Forum|My Online Training Hub

You are here: Home / Return to original sorting after using the slicer|Power Pivot|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower PivotReturn to original sorting after us…
sp_PrintTopic sp_TopicIcon
Return to original sorting after using the slicer
Avatar
Victor M. Agosto
Member
Members
Level 0
Forum Posts: 6
Member Since:
September 30, 2020
sp_UserOfflineSmall Offline
1
October 27, 2020 - 10:56 pm
sp_Permalink sp_Print

Hi Mynda,

I have a pivot table and chart that is comparing data from 2019 and 2020. I have sorted the data from highest to lowest number at the year 2020 field.

Example:

                  2019   2020

Mfg               40       25

Pckg                        20

Laboratory      15      15

 

I added a slicer for the user to be able to filter their area. I came up with the following issue: if the area has data from both years, after de-selecting the filter the data goes back to sorting the 2020 year as originally stated. However, if one of the year has no data, the pivot table sorts out the data in alphabetic order. It's not going back to sorting the data as originally stated (year 20200). Is there a way to ensure that the data is kept sorted at the year I specified?

sp_AnswersTopicSeeAnswer See Answer
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4614
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
October 28, 2020 - 9:43 am
sp_Permalink sp_Print

Hi Victor,

Try setting the field to 'show items with no values' (in the field settings), so that even if there is a year with no data, it is still present in the PivotTable and hopefully it will retain the sort settings.

Mynda

Avatar
Victor M. Agosto
Member
Members
Level 0
Forum Posts: 6
Member Since:
September 30, 2020
sp_UserOfflineSmall Offline
3
October 30, 2020 - 4:51 am
sp_Permalink sp_Print

Thanks Mynda,

Unfortunately it did not work. An additional detail, I have items which have data for 2019 but not for 2020 and vice-versa. When I filter one of those is when I have the issue of the table not being able to return to its original sorting.

By the way, your videos have been a great deal of help. I have had learned a lot and have had allowed me to develop nice dashboards. Thanks a Lot!

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4614
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
4
October 30, 2020 - 7:17 am
sp_Permalink sp_Print

Hi Victor,

Can you share the file, or at least a sample file?

Mynda

Avatar
Victor M. Agosto
Member
Members
Level 0
Forum Posts: 6
Member Since:
September 30, 2020
sp_UserOfflineSmall Offline
5
October 30, 2020 - 11:16 pm
sp_Permalink sp_Print

See the file attached.

Graphs in the dashboard sheet that are showing the data sorted from largest to smallest (starting form the bottom), I want them to stay that way.

Any additional recommendation is very welcome.

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4614
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
6
October 31, 2020 - 10:16 am
sp_Permalink sp_Print

Hi Victor,

My original suggestion works: "Try setting the field to 'show items with no values' (in the field settings), so that even if there is a year with no data, it is still present in the PivotTable and hopefully it will retain the sort settings."

You need to apply it to the Year field of the PivotTable: right-click one of the Year headers in the PivotTable > Field Settings > Layout & Print tab > check the box for 'show items with no values'.

This will add two more items to your Year slicer: >9/30/2020 and <1/2/2019. These items have no data, so you can delete them from the legend by selecting them (left click twice slowly to select individual legend items), then press delete.

In any Year Slicers you can set it to 'hide items with no data' so they don't display as an option.

You'll also need to fix the colour formatting in your charts as the additional date items will mess it up.

Mynda

sp_AnswersTopicAnswer
Answers Post
Avatar
Victor M. Agosto
Member
Members
Level 0
Forum Posts: 6
Member Since:
September 30, 2020
sp_UserOfflineSmall Offline
7
November 2, 2020 - 11:24 pm
sp_Permalink sp_Print

Mynda,

 

Thank you so much. It works indeed. I was not performing your suggestion correctly.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Lakisha Hall, Ric Wade, Tracy English, David Birch, QSolutions Group
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 880
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 237
Jessica Stewart: 219
A.Maurizio: 213
Aye Mu: 201
jaryszek: 183
Newest Members:
Jennifer Rodriguez-Avila
Khaled Ibrahim
Kiran Supekar
Lisa Myers
Ronald White
Ginette Guevremont
Taryn Ambrosi
Mark Davenport
Christy Nichols
Harald Endres
Forum Stats:
Groups: 3
Forums: 24
Topics: 6530
Posts: 28602

 

Member Stats:
Guest Posters: 49
Members: 32820
Moderators: 2
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel Office Scripts
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook

Sign up to our newsletter and join over 400,000
others who learn Excel and Power BI with us.

 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate
  • Sponsor Our Newsletter

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.