•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Sort a Pivot Table slicer by another column
Our take
Sorting a Pivot Table slicer by another column can enhance your data analysis, especially when working with fiscal periods. If you're transitioning from Power Pivot to standard Tables and Pivot Tables, you may encounter challenges with sorting your Fiscal Quarter & Year slicer correctly. Specifically, entries like “FQ1’26” can lead to an unintended order. Fortunately, there are methods to sort your slicer by an index column, allowing for a more accurate representation of your data. Let’s explore how to achieve this effectively.
I’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?
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- 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 do I get slicers to work with a pivot table with multiple value tables, and also displayed in an easily legible way?I have an example list of fifty staff names with six different types of information for each staff member. Three of those criteria (Role, Types of Exp., and Clients) have multiple values, e.g. a staff member will have worked with multiple clients, have many types of experience, and have played different roles. My goal is to create a table of the staff that can be easily filtered according to any of the six criteria, including being able to filter by multiple values for a single criteria, e.g. search for staff members who are in the Chicago office with a bill rate of 200 or less, and who have worked with Northwind and Contoso. I thought that creating a pivot table with slicers for each criterion would be a good way to do this. Steps I followed: I took my initial set of data and converted it to a table. I then created normalized data by copying the name column and each of the multiple value columns into three new sheets, and then used From/Table Range to access Power Query and transform the data into rows as a table. I then established relationships between each individual multiple value criteria table and the original staff table, using the Name column since it is what they have in common. I then created a pivot table from the data model, and added slicers for all six criteria based on the tables where those criteria live. I'm stuck at this point. With just the Name field from the Staff table in the Rows Area of the pivot table, only the Office, Yrs. of Exp. and Bill Rate filters work correctly. If I want to get any of the multiple value filters to work, I need to add the slicer based on the table for that specific criteria, and then also add the corresponding field to the pivot table from the corresponding multi value table. Unfortunately, then the information is very difficult to read due to the way the pivot table displays and nests the information. A list of 50 names turns into over a thousand rows of data. My questions are: Is there a way to get all six slicers to work with just the name field from the Staff table in the Rows area? Is there a way to also have the other criteria displayed in the pivot table in a more legible way, similar to the actual Staff table? If a pivot table and slicers are not the best way of going about this, what would be a better way? Ideally, I'd have the Staff table itself filterable with the slicers, but I'm not sure that's possible. I am new to all of this and apologize if I am sometimes not using the correct terms. Thank you! Edit: I am using Microsoft 365 on desktop. https://preview.redd.it/68q1nmv2hmyg1.png?width=490&format=png&auto=webp&s=92c147b286e378307ce5b79440612fd1ef4417ee https://preview.redd.it/rh71o8v2hmyg1.png?width=1399&format=png&auto=webp&s=15677df44967aeab7e24d2138a34e02674332d53 https://preview.redd.it/a8id0hv2hmyg1.png?width=1284&format=png&auto=webp&s=0c8ae142adee38ea0fcbef0dfe7dd8583ef5fcc8 https://preview.redd.it/1g3pp9v2hmyg1.png?width=388&format=png&auto=webp&s=76f66fb33fd59a5dc15785e07ce7c5400888f045 https://preview.redd.it/oylhz9v2hmyg1.png?width=676&format=png&auto=webp&s=e55d776248bc373d1292778c027d1f5c544429e3 https://preview.redd.it/5ff6g9v2hmyg1.png?width=2559&format=png&auto=webp&s=ca4d12eb1a125ada20ce9d1c82f28a1de1502b41 https://preview.redd.it/dxoc49v2hmyg1.png?width=445&format=png&auto=webp&s=2805ea6ebf62853610c519ac203ad5cb4266e34b submitted by /u/ToDigAndBeDug [link] [comments]
- How to filtering additional fat sets along with a pivot tableI have a few different data sets of sales based on different time periods and sorted by salesperson. I have a pivot table based on data set 1. The other data sets are also by salesperson but different time periods. How can I get the additional data sets to filter alongside the original data set as it’s filtered by salesperson? I tried adding a slicer but couldn’t get the relationships to work because t said I have duplicates. I don’t know why any duplicates would exist because the data is pulled difectly from our customer software system. Any suggestions? submitted by /u/Gill_22 [link] [comments]
- pivot tables - exist something like filter by column?Good morning community. I don’t usually use pivot tables much, because I tend to prefer building my reports with filters and formulas, but my boss loves them. The problem is that many times he doesn’t even know exactly what he really wants. Here’s the issue. I have a table where data is divided into categories, for example: Column A – Primary Categories Column B – Secondary Categories Column C – Tertiary Categories From Column D onward… prices, with each column representing a different month. My boss wants a pivot table where he can filter by categories (I already have that done), but also be able to filter somehow by just one month, or several, or all of them, since he then wants to use that in a chart (this part is also already done). AND HE ONLY WANTS TO SEE THE CHART AND CONTROL THE DATA FROM THERE—in other words, he doesn’t want to have to go into the pivot table to edit it by adding or removing columns. So the question is… how can I (if it’s even possible directly from the pivot table) quickly change the columns I’m displaying with a button? That is, without having to manually edit the pivot table. So far, the solution I found was to create a “column” using formulas where I bring in the data with “HLOOKUP” and change the filter with a dropdown list linked to a macro that refreshes the pivot table every time its value changes. But I’d like to know if pivot tables have a more direct way to solve this. Thank you very much. submitted by /u/Potential-Aside-1712 [link] [comments]
Tagged with
#Excel alternatives for data analysis#generative AI for data analysis#big data management in spreadsheets#conversational data analysis#real-time data collaboration#intelligent data visualization#data visualization tools#enterprise data management#big data performance#data analysis tools#data cleaning solutions#rows.com#natural language processing for spreadsheets#Excel compatibility#Excel alternatives#Pivot Table#slicer#Power Pivot#Data Model#Fiscal Quarter