Forum Discussion
Multi Filter issue in Online Excel
Hi
I am facing an problem in filtering the value in online excel (o365).
I have 10000 rows of data which will a series of numbers like A/AR/24/0001 and there will be duplicate and also will be something like A/AR/23/0001so sometimes i need to filter multiple values in excel. When i try filtering since there are lot of individual value in a single column. I need to search for the value and add to the sheet. when i try to select multiple value and clicked that (add to selection list) but it took the last value which i have selected. so every time i need to select a value > add to current selection > apply then proceed for next filter. Is there any way to avoid this
4 Replies
- have 10000 rows of data
When you open this large sheet,do you feel lag or slow?
If so,I guess you need a database to manage so many records. The issue you are facing in Excel Online (Office 365) occurs because the web version of Excel handles filtering differently compared to the desktop version. When you want to select multiple values, it seems to replace the previous selection with the new one instead of adding to it.
Unfortunately, Excel Online has some limitations in filtering large datasets compared to Excel Desktop. Here are some workarounds that may help you filter multiple values more efficiently:
Workaround 1: Use Text Filters
Instead of selecting multiple values manually, you can use Text Filters to search for multiple criteria. Here's how:
- Click the filter drop-down in the column you want to filter.
- Select Text Filters > Custom Filter.
- In the Custom AutoFilter dialog box, you can specify multiple conditions.
- For example, use the "contains" option to filter values like A/AR/24/0001 or A/AR/23/0001.
- Add multiple filter conditions by clicking on the "Or" option, and enter the next part of the series (you can add several values this way).
This approach lets you specify multiple values or patterns instead of manually selecting individual items.
Workaround 2: Filter by Color or Flags
If you are dealing with many unique values and want to filter in steps:
- Flag or highlight the rows that meet your filter criteria using a helper column.
- Add a column to your dataset and use this to mark the rows that match certain criteria, for example using 1 for rows you want to filter on.
- Then, filter by this helper column rather than selecting individual values manually.
Workaround 3: Use Desktop Excel
If possible, consider using the Excel Desktop version for handling larger datasets and applying multiple filters at once. The desktop version allows you to select multiple items in a filter list without the issue you are encountering in Excel Online. You can open your file in the desktop app directly from Excel Online by:
- Clicking "Open in Desktop App" (in the ribbon) if you have the desktop version installed.
- Apply the filter in the desktop version, save the file, and then reopen it in Excel Online if necessary.
Workaround 4: Advanced Filter
If you need to perform more complex filtering, you can use the Advanced Filter option (available in the desktop version of Excel). This allows you to filter based on criteria in a separate range of cells. The Workarounds was edit with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.
IMHO, filtering on Web version works exactly the same way as on Desktop one:
Web
Desktop (Windows)
- Sergei, thank you for the correction. With so many Excel versions, changes and new functions, you "sometimes" lose track :-).
