[Solved] Excel Table - filter messing

[Nikhil Patki]

I am using a table to maintain fuel purchase and consumption data for every month
I'm using a few formulas and Excel built-in features to make it simple for data entry as well as results as per requirements.
1. if / subtotal formula to have dynamic / auto serial number generation
2. dependable dropdown (data validation - List) in a couple of columns to enter the branch, select veh no, fuel type, type of filling, vendor
3. basic formula in amount column - rate*qty
4. Slicers for branch, date and veh no

But when I try to filter the selective data
I get results having non-selected categories also.
usually, these results are the last row entered or sometimes the last 2 to 4 rows data, irrespective of the category filtered

btw I input data once a week for purchase
and at the end of the month, I input consumption data, basically which is used from storage
storage entries being updated at the end of the month after it's done I use the sort option by date
as these entries have mixed dates and have been entered in the last rows.

I've tried plenty of methods but the results are not as they should be - only selected category

 

[Seemanto Saha]

I am using a table to maintain fuel purchase and consumption data for every month
I'm using a few formulas and Excel built-in features to make it simple for data entry as well as results as per requirements.
1. if / subtotal formula to have dynamic / auto serial number generation
2. dependable dropdown (data validation - List) in a couple of columns to enter the branch, select veh no, fuel type, type of filling, vendor
3. basic formula in amount column - rate*qty
4. Slicers for branch, date and veh no

But when I try to filter the selective data
I get results having non-selected categories also.
usually, these results are the last row entered or sometimes the last 2 to 4 rows data, irrespective of the category filtered

btw I input data once a week for purchase
and at the end of the month, I input consumption data, basically which is used from storage
storage entries being updated at the end of the month after it's done I use the sort option by date
as these entries have mixed dates and have been entered in the last rows.

I've tried plenty of methods but the results are not as they should be - only selected category

Dear Nikhil Patki,

Thanks for sharing your problem with us. Based on your description of the datasheet, I created the following variables:​

Using these variables, I created the following dataset and inserted slicers. When I click on specific slicers, the filtering works as expected.​

​

But in your case, out-of-selected category data are also appearing. There can be multiple reasons for such incidents, such as:

• Check for Pivot Tables:
  Although you have mentioned using a general table, you should check for Pivot Tables in your workbook. If multiple Pivot Tables are connected with the same slicers, then out-of-selected category data can appear.​
• Review Table Structure:
  Verify the structure of your table. Make sure that the column you want to filter is part of the table and is correctly identified as a header.​
• Check Slicer Connections:
  Right-click on the slicer and select Slicer Settings. Ensure that the slicer is connected to the correct table, and the field selected is the one you want to filter.​

If you don’t find any issues after the above-mentioned troubleshooting steps, then please share a sample workbook with us. We may need to take a deeper look at the dataset and slicers to find the root of your problem.

You can also try to recreate the dataset from scratch and then insert slicers again. To learn more about slicers in Excel, read the following two articles:​

• Insert Slicer in Excel
• Slicer with Multiple Columns

Regards,
Seemanto Saha
ExcelDemy