•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
How to sort months in a slicer? (Or format Date in slicer to only be months?)
Our take
Sorting months in a slicer can be challenging, especially when they appear in alphabetical order rather than chronological order. In this scenario, where you've created a "Name of Month" column in Power Query but still face sorting issues, there are a couple of approaches you can take. First, ensure that the slicer settings are configured correctly to sort by your source data. Alternatively, consider using the original date format, which allows pivot tables to automatically group data by months.
I have a slicer for a pivot table with months (from a PQ where I created a column from date using "Name of Month"). It's showing the months in alphabetical order, instead of month order. In slicer settings, I have sort by order source data selected, and on the PQ table they're custom sorted the way I want, but it's still showing alphabetically on the slicer. What am I doing wrong here?
Another option is to work from the date itself - in other pivot tables, the date (formatted as date in my PQ) will automatically chunk into months, years, etc. Here I can't seem to get that option.
Either solution would work for me, as long as I can eventually have the months in the slicer in the proper order. Thanks in advance.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Sort a Pivot Table slicer by another columnI’ve got a relatively simple question compared to some of the other content here: For a project I’m working on I’ve had to convert from using Power Pivot and a Data Model to normal Tables/Pivot Tables without Power Pivot to accommodate some Mac users in my org. When rebuilding some of the pivot tables and slicers, one of my most used ones is a Fiscal Quarter & Year slicer/column which contains data formatted “FQ1’26”, “FQ2’26”, etc. The result of this is that the slicer orders the entries “FQ1’26”, “FQ1’27”, etc. I have what we can call an index column that is “20261”, “20262”, etc that was used in Power Pivot to sort the Fiscal Quarter & Year column - however I’ve struggled to find a way to replicate the functionality outside of Power Pivot. Is there a way in a normal Table/Slicer to sort the Slicer column by another data column? submitted by /u/gnartung [link] [comments]
- Sorting pivot table column headingsHey fellow Excellians! Been having some major frustrations trying to keep my pivot table column headings in the right order. Column headings are weekbands: 0–2 Weeks 2–4 Weeks 4–9 Weeks 9–13 Weeks 13–21 Weeks 21–26 Weeks 26–36 Weeks 36–52 Weeks 52–62 Weeks 62–78 Weeks 78+ Weeks Total 2 Weeks 9 Weeks 13 Weeks 52 Weeks So far I’ve tried: Using custom sort lists Sort by > Column in the Data Model Protecting the worksheet Regardless, any time I apply a filter from a slicer (which will sometimes remove columns from the pivot as there is no data), all of the column headings get jumbled. Not sure if there is something simple I’m missing. I also want to “lock” the columns/rows in place even when no data is returned from slices applied so that the table structure remains the same, but thought that fixing the sort issue would come first. Maybe I’m wrong? Thanks! submitted by /u/irish_shitlord [link] [comments]
- how to make pivot table sort dates properly when they are the columns?dates are not in order, i made sure to make the column date data type and all but still https://preview.redd.it/ig0tg3jo5rtg1.png?width=1460&format=png&auto=webp&s=15194d5b47010d38e1ff3d5bebe731cdea4b5dd8 submitted by /u/PurpleDurian7220 [link] [comments]
- Unable to Group by Month on Pivot Tables.I have a weird situation that I can't find a solution to online. If I export my Power Query* query into a table then make a pivot table out of that, the option to group by months automatically comes out. But if I export my query directly into pivot table, Excel won't let me group my dates into months - group field, group selection, all those don't work. Any idea why this is happening? It can't be the format cause query>table>pivot table works. The second pivot table is loaded into the data model.* Could this be the reason why? *edited Edit2: using DATEVALUE() on a Pivot Table date gives a #VALUE error. Is there any info how Pivot Table formats dates? Wrapping the date in a TEXT(,"m-dd-yyyy") fixes it. The left indent on the right PT could be a clue but I don't know how to interpret that. submitted by /u/monxstar [link] [comments]