How do I get slicers to work with a pivot table with multiple value tables, and also displayed in an easily legible way?
Our take
In the realm of data management, the ability to filter and analyze information efficiently is paramount, especially when dealing with complex datasets like the one presented in the article on pivot tables and slicers. The user is grappling with the challenge of effectively displaying and manipulating data for fifty staff members, each with multiple criteria. This situation reflects a broader issue many face when attempting to harness the full potential of pivot tables in Microsoft 365. As highlighted in previous articles, such as Pivot Table top bar date filter doesn't update in chronological order and Setting up pivot tables properly for inventory tracking purposes, the complexities can often lead to frustration rather than clarity.
The user's undertaking to create a pivot table with slicers is a commendable one, as slicers provide a user-friendly way to filter data visually. However, the struggle to get all slicers to function with the name field alone highlights a common pitfall: the intricacies of managing multiple value fields in pivot tables. While the initial steps taken to normalize data and establish relationships are sound, the challenge arises when the pivot table becomes unwieldy, turning a concise list of names into an overwhelming array of rows. This underscores a crucial point in data management: clarity should never be sacrificed for complexity. The ability to filter by multiple criteria is essential, but it must be balanced with the need for legibility and usability.
Moreover, the question of whether a pivot table is the best tool for this scenario is worth exploring. While pivot tables are powerful, they are not always the most intuitive for users who may be new to data manipulation. Alternatives such as dynamic charts or interactive dashboards could provide a more accessible and visually appealing method for filtering and displaying data. These solutions not only enhance user experience but also align with a progressive vision for data management, one that prioritizes user outcomes over technical specifications. Encouraging users to explore these alternatives can empower them to find solutions that fit their unique needs.
As we look to the future of data management, the emphasis should be on making technology more intuitive and user-friendly. The challenge presented in the article serves as a reminder that while tools like pivot tables are valuable, they should not create barriers for users seeking to make sense of their data. Instead, the focus should shift toward innovative solutions that simplify these processes, making data analysis accessible to all. As organizations continue to evolve in their data practices, we should ask ourselves: how can we further empower users to take control of their data without being bogged down by technical complexities? The path forward lies in continual exploration and adaptation, ensuring that data management not only meets but exceeds user expectations.
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.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Pivot Table top bar date filter doesn't update in chronological orderI have a an excel file which I had been building from scratch from a downloaded report every month and am trying to better automate since I do the same type of data filtering each month. I decided to create a table with the data that is linked to a pivot table which sorts and filters it into the format that I need as long as I manually adjust a couple things. However, whenever I add new data to the table, the date filter at the top of the pivot table does not show in chronological order. (Accountant working on April data and need to remove anything that was posted in May.) Theoretically I could use the timeline feature, but I'd rather not. I have resorted the source table in chronological order; I have removed the filter and re-added it in various formats. I've changed the data source, played with various settings. Is there a quick "fix" for this without manually creating the pivot table each time? I have several of these pivot tables on a sheet extracting different data sets sourced from the same table. In one of them, I need to see ONLY the items with a May date, so having to scroll through to check or uncheck the correct dates if they are out of order could lead to errors. Additionally, assuming that this can be corrected. Is there a way for an excel sheet to automatically add or remove row (by hiding or deleting, don't care which) as a pivot table gets longer or shorter so I don't have to guess how many rows I need to add before it cuts into other data below it? Sometimes I need 70+ rows and others only 30. I'm sure there's an overall better way to do what I'm doing. Thanks in advance. https://preview.redd.it/56optogx0qzg1.png?width=406&format=png&auto=webp&s=551180d29effbcfedb7038f60612e33f4e897d3d submitted by /u/inez6453 [link] [comments]
- Setting up pivot tables properly for inventory tracking purposesA bit of background: I’m former military, and currently I’m the head of inventory at a fabrication company. While I was in the military, our inventory was kept mostly on paper, and everything that was annotated in excel was done using basic formulas and a single sheet. I am using Excel on a work laptop and I would consider myself somewhere between beginner and intermediate level. My problem currently comes from the fact that this company I work at never had an actual inventory system before, which I’m currently building from the ground up by myself. Since I don’t have extensive Excel experience, I struggle to materialize my plan and thoughts into this spreadsheet. Currently I have a master list, which I separated by location, category, item ID, and amount in stock and I’m trying to make a separate sheet with a pivot table that can show each shop independently whenever I click a dropdown menu. The pivot table I did make is a bit hard to look at and I can’t figure out how to make it do what I need it to do. Additionally, whenever I update a number on the master list, which is where the pivot table is sourced from, the numbers on the pivot table don’t update with the information. I should also mention that we have 3 different “shops” that we supply within our company, which is the main purpose for me wanting to figure out the pivot table. I’ve looked up some tutorials on YouTube on how to do these seemingly advanced things, but I can’t seem to find one that fits my specific needs or that explains how to properly do this. If any of you have trouble understanding the issue, please let me know, I’m not very experienced with excel so I don’t know all the terms for everything To summarize this wall of text: Need pivot tables that I can separate individual shops through and update each shop’s stock individually. I would love some help on this. Thank you in advance submitted by /u/PhastasFlames [link] [comments]
- Pivot Table Show Details not working, any idea?Hi there, I am facing with a quite unique issue I think. There are some pivots in an Excel file that we use for internal reports for the AP team. The issue is that for the pivots there are several filters applied on, like Intercompany filter or "is the amount negative" etc. but there is the most important one is the Vendor filter, there are numerous vendors excluded, and I was like okay let me just recreate the pivot from the ground, but that would take a bit too much due to the vendor filter. The main problem is that the show details is not working, if I execute a double click on the pivot's total cells then it is loading for a 0.1 second and then nothing happens. If I try it after I close and reopen the file, then it loads for 2 seconds, with the botom right text saying "Reading Data" but then nothing, same results. It is a connection based pivot table, I tried to copy and paste into a new sheet the Pivot, didn't work, I tried to Save & Repair the file, it didn't work. Any idea? submitted by /u/Strange_Cell1142 [link] [comments]