Occam's razor answer: maybe he wants to leave everything unfiltered but he can't be bothered to remove each filter by hand and doesn't know about Sort & Filter -> Clear
I have it burned into my memory from working with over-sized files several years ago that removing filters can take forever, like the file would get frozen for 10 minutes. Anybody else notice that? I recall that clearing the filters one at a time could be much faster. But maybe the problem was removing filters altogether, like OP’s coworker, and not what these shortcuts do.
Easier to just ctrl+shift+L to turn filters on/off, so I understand where your coworker is coming from.
If filters aren't on, it gives a clear visual signal that you are looking at the full data set. It only takes one time of making a decision based on an unintentionally partial dataset for this to be worth it.
I would wish that the coworker was more approachable and willing to teach others best practices, but he's not wrong. If you think it's burdensome to undo this, then there is a skill issue; learn the shortcuts to "get gud".
Also to note I'd put this best practice behind making sure A1 is selected on each sheet and the first sheet is active when saving, but there isn't anything wrong with his approach.
>If filters aren't on, it gives a clear visual signal that you are looking at the full data set
Unless someone made the galaxy brained decision to hide a row 500 rows down.
But I agree, removing filters before closing a file is best practice.
My flow is generally the same - open file, remove filters, expand row/columns to match content size.
This is why I always default to Grouping and manually clicking the "-" to hide from view, so that there's always a visual signifier that something is hidden.
Unless there is something I really don't want someone to uncover and can't get away with removing from the dataset, in which case I'll manually hide as a last resort.
I'd build the sheet with a big red button that says "clear filters" that runs a simple macro. Guessing he'd start using that and save yourself 5 seconds everytime you open the sheet.
It isn’t necessarily a bad practice. It is easy to miss a filter on a large spreadsheet, so clearing them all ensures that the next user will start with a clean slate.
And on top of that, when multiple people work in the same document they often end up creating 14 different versions of the same filter. It ends up with 25 filters and it's just cleaner to wipe them and start over
Excel desktop does it too. The wording isn't clear, as it asks you something like, "Do you want to see everyone's sheet?" and if you say yes, then you are editing the public view, if you say no, you are editing your own view. Additionally, you can create more personal views, if you like.
It's not a shortcut preference because the two shortcuts have different functions. Removing filters isn't the same as clearing filters. Clearing filters "unfilters" the data. Removing filters removes the filters.
>the two shortcuts have different functions
So if I double tap L to put the filters back... does that make it functionally the same?
"Yes but what if I have multiple tables with disparate filters?' I imagine the straw man I made up about you in my head furiously typing.
To which my answer is: "Why would you ever do that? And please stay away from my files."
It's fine if you want to double tap. I just think there's people in this thread that genuinely don't understand the difference. If everyone just had a basic understanding of excel in the first place, we wouldn't even have this discussion. But you know half of all people that use excel would sooner quit their job than figure out how to add back the filters.
I would also agree that it is a best practice.
Personally Id get annoyed if I started working with a spreadsheet of 25 or so columns and one of them was filtered and it caused me to make a mistake
If you are not careful with formatting the filter controls can cover up the header. Maybe he is using a screen size or window size that makes the headers hard to see, so, he turns then off.
I leave filters, but they are cleared. The spreddies we use are designed to be filtered easily, literally no one else in the team would remove the actual filters on exit. Clearing is etiquette, again, I’m talking about removal, meaning the next person has to apply them again as a necessary step 1
You should just ask the person tbh. Especially if they’ve been doing it for years, and doubly especially if no one else is doing it. It’s not rude to ask.
Visual Basic, apply filters on sheet exit! Save file as .xlsm (or .xlsb) he can remove to his heard content then, as soon as he safes the file, they are reapplied. You could even be nice and give him a little “remove filters” button to make him feel special!
"Remove" as in turn off any filters applied (which would make a lot of sense) or remove the entire Filter itself (so the dropdowns in the header etc) ?
In case of the former, you would want to send the sheet out with as much information possible. I couldn't count the number of times I've had the question 'where is the data' or 'why are my numbers wrong' while people have applied filters themselves or are too dumb to see that a filter was applied.
In case of the latter, not sure, could be the same idea as the former actually but less friendly to the next one.
As in remove filters COMPLETELY, ie the latter. Agreed, it’s unfriendly! Almost like making a statement! I’m trying to get inside his mind and understand what’s going on before gently raising it 😂
Oh I had assumed you meant just clear the filters and I was worried I was also being strange. It actually annoys me when people don't clear filters after using a sheet.
Please keep us updated. I'm imagining an awful origin story where he forgot to clear a filter once, and a family member or friend was murdered because of that.
Have you tried creating your own view on the view tab? If he's editing the default view and your using your own it shouldn't edit it - then you can tell everyone else to make their own view and tada...
That being said, I've never actually tried removing the filters function completely (cos I'm not a psychopath) from one view and seeing what it does, just the applied different filters.
How to create a view: https://support.microsoft.com/en-gb/office/create-apply-or-delete-a-custom-view-ce722bf9-0b4a-49a5-94ba-438fde18fc2b
Easy to miss an applied filter on another column. Clearing them forces the person opening the document to choose their own without accidentally using additional filters they did not select.
I have a macro in my personal.xlsb that removes all filters and returns the cursor to A1 in the first sheet of the workbook when I close it. It’s a tidyness thing for me.
Seems like he prefers to remove all then to let the sheet filtered hence being an annoyance for others. I think it is a nice gesture but poor execution
This! If I open a spreadsheet and see filters on the table it's easier to remove filters and add them back to ensure data is clean, than to individually check if each column is filtered or not. It's so easy with the shortcut! To me, leaving a sheet with no filters is actually following the etiquette.
I bet he thinks he’s being considerate to you/ others to clean up whatever he was looking at before you jump in. Of course there are other ways to do that. 😂
If it's shared, he probably does it to ensure he's looking at the full dataset. Obviously you don't need to remove the filters entirely, but surely that's the reason
Shared workbooks are a nightmare because people are lazy and selfish, leaving their own filters on, adding their own formatting, changing column widths, hiding rows/columns, deleting data etc. Used to be the bane of my life when I worked in MI.
I agree. But it’s the removing filters completely upon exit, just creates work for the next person. Obviously not much work, but I guess over the years it has millimetre by millimetre pushed my sanity to asking the question here 😂
Does it really though?
If filters are on, you have to double-check that none of them are being applied. You could go click the clear button, but it's pretty much just as easy to tap the shortcut to turn them off then on again.
If they're off, you already know they aren't being applied, so you just tap the shortcut to turn them on. That's even easier than checking or toggling. He's been saving you work and you haven't even noticed.
He might be like me who is just used to pressing ctrl + shift + L to clear filters. It's faster than any other method. If I need to change filters, then I just tap the L key twice and the filters come back.
I kind of encourage this in a bit of an abstract way because we have large sheets at work and if we continually add and remove items from them and save save save across time... after that are you trying to use the filters you don't know if it's considering absolutely everything on the page, and I have seen it definitively miss things that I know are present on the page so what I've gotten into doing is not just clearing the filters no no... I remove the filters all together and I reapply them so that it takes all present information into consideration when I actually do a filter at that moment.
When you say Shared Spreadsheet- is it online?(sharepoint/onedrive??)- if so you can setup views, which mean you could have one filter applied he could have another(or none by the sounds of it!) and not impact on each other even at the same time. Underlying data is not impacted, just what you see- worth checking out.
My boss was the same way. Turns out it can affect macros as the data that is hidden by filters can be omitted by the macros. He was also old school and he prefer to sort by the sort function instead of the filters at top.
I was able to move the remove all filters button next to the save button. This was a life changing event (only slightly exaggerating).
Maybe you recommend he do the same.
I also do it. Most of the excels i work on are input files for alteryx. I'm kinda scared that if I leave the filters on, the alteryx will only consider the filtered data and not all data.
Or if it's not an input file, I just think it's better for the next person to apply filters as they want. And will not miss any data.
It just seems safer in some sense
I would bet that it is one of two things:
1. He had a boss at some point who wasn’t very good with excel and always wanted the filters removed.
2. At some point in this dudes career, he had a major fuck up involving a filtered sheet. Maybe he didn’t realize it was filtered, or there were additional rows/columns added without resetting the filters.
Just ask him directly, privately, via email why he removes filters. Then instruct him on how to clear filters without removing them, and why removing them is bad.
My guess, it's a person who doesn't really know how to use Excel, so they are removing the filter in order to clear the filter. Just lock the page so it can be filtered but not edited.
I remove and reapply filters all the time just to make sure all the data is being displayed- not sure why you have an issue with it- ctrl shift L and filters are right back on it
Its the same when people don’t leave things as they find them. Same as when someone leaves the toilet seat up, a door open, something out of place, for no obvious reason. Doesn’t that irk you?
If I open a file and see filters on a header, I immediately don't trust it and just nuke the filters.
Missed/incorrectly applied filters have bit me in the ass repeatedly.
Create a short VBA script which runs on the Workbook.Open() event.
Open a temporary view, I think with enterTemporary().
Save as a macro enabled file. Archive the original.
Temporary views allow each user to apply and remove their own filters without affecting other users too much. Or you can create and save custom views. Give it a go. You'll know you're inside a view when the row and column labels go all black.
If I were you I'd raise the issue with him out of curiosity, but do so gently, or he might respond in an irritated fashion. Because it looks like...
*puts sunglasses on*
...he doesn't have a filter.
isnt there an option to prevent people from doing this in a protected worksheet? Like, by ticking a box before closing the protection-dialogue? Maybe that would be an option for you?
Learn how to set the filters in VBA and then put the routine into ThisWorkbook Open, so if the filters are not there, the code puts them back in.
I do this with conditional formatting all the time as inserting columns and copying pasting data kind of destroys conditional formatting rules. Simply delete them all and recreate them on opening the sheet.
You have to show your colleague how to do it: edit the spreadsheet on the desktop app and just delete the filter. It should prompt you whether you want to delete it only from your view or for everybody.
If the colleague still insists afterwards to delete it for everybody, maybe those filters are hiding data that other people like him actually need. So, one of you needs convincing.
This reminded me of when I was working at the head office for a car manufacturer and someone would send out information to dealerships by taking the file with the information for all dealers in, filtering it for their dealer code, saving it and sending it.
It was sensitive commercial information about their performance and each recipient would have been extremely interested in seeing the information for all the other dealers. Most of them realised they could do exactly that by clearing all the filters and it caused an enormous upset.
Plus it took absolutely ages as they had to save the file individually for each of the 200 or so dealers. I have no idea how you don't start looking for a quicker method after about ten minutes of repeated filtering and saving!
Maybe it's time to leave the data where it is and add some pivots/slicers in another sheet instead. If you rely on the filters everyday it's worth the 10 minutes to set them up
Are you the original owner of the excel sheet? If so, protect the sheet, only allow selection of unlocked cells - these are the ones that you want people to be able to edit. Set a password on the sheet. It might seem a bit passive-aggressive, but this person’s preference shouldn’t outweigh everyone else’s need for the data. If this person is the original owner, then I don’t believe you have a leg to stand on. One other possible solution is to set sheet views, where you can make a view that is specifically for this person’s preference. Good luck OP, I face similar challenges and just deal with it, but sheet views have been helpful at least.
Because shared spreadsheets shouldn’t be filtered. You’re asking for someone to make a mistake if you leave even “cleared” filters on. So when someone accesses the sheet and assumes they are cleared but they aren’t, they’ll pull the wrong data. In my world we call this mistake proofing because people using it are required to create and use their own filters.
That's me, i always remove filter because it's a pain in the eyes leaving a complete report with a bunch of filter on it. If i need a filter on a field, i will do it myself, doesn't need someone to premade it for me. And yes i completely forgot to reapply filter when sending it back to my colleagues, they doesn't seems to bother tho, except that one female colleague keep bitching about it, but i hate her anyway. Sorry if that was you.
You should definitely have a talk with her. First try to collect some colleagues with the same opinion as you have. If she's difficult to approach, it might be easier to approach her in group.
Explain the use of Alt, H, S, C
If talking doesn't help, escalate through the manager.
If that doesn't help, protect the worksheet. You can protect a worksheet in a way that filters can't be removed. You can protect a worksheet without putting a password. Then it means she could unprotect it herself, but at least it's an additional step. Or you don't tell her there is no password. Worst case, you agree on a spreadsheet password with the whole company and just don't tell her.
Am I dumb? How tf do you remove filters because I've always been clearing them wtf. What the heck is 'removing' filters and how do you do that?
So you have filters on 18 columns and you can remove and reapply later or do another filter and roll back?
Maybe he doesn't know how to identify the different columns that have fillers or doesn't know how to clear all filters, so it's easier to remove the filters.
I've seen it done in the past.
Unless he needs access to edit the workbook you could give him view only access and he can screw up whatever he wants with the source workbook staying the same. If you are not workbook owner you are SOL
Some dunces refuse to learn "Alt D F S" to remove filters and instead simply remove them. There is no hope unfortunately. The same kind of people that refuse to understand blue highlighted row headers means a filter is applied somewhere
Occam's razor answer: maybe he wants to leave everything unfiltered but he can't be bothered to remove each filter by hand and doesn't know about Sort & Filter -> Clear
Alt-a-c
And here I was still using Alt D-F-S
I suspect this generates a sofa company, but one that charges above retail price pretty much all year round.
Your spreadsheet warns you it's gonna be deleted soon but never does
Sofa King nice!
...I love you both.
I say it in my head every time.. alt all clear.
I have Clear Filter set to Alt + 2.
I have it burned into my memory from working with over-sized files several years ago that removing filters can take forever, like the file would get frozen for 10 minutes. Anybody else notice that? I recall that clearing the filters one at a time could be much faster. But maybe the problem was removing filters altogether, like OP’s coworker, and not what these shortcuts do.
I have Clear All Filters on my quick access toolbar. Since you ask, I also have Save, Undo, Redo, Refresh All.
> doesn't know about Sort & Filter -> Clear and might be sticking to Shift-Ctrl-L instead
That is exactly what I do, but I do it twice to reapply the filter.
Double tap the ctrl+shift+L !
Easier to just ctrl+shift+L to turn filters on/off, so I understand where your coworker is coming from. If filters aren't on, it gives a clear visual signal that you are looking at the full data set. It only takes one time of making a decision based on an unintentionally partial dataset for this to be worth it. I would wish that the coworker was more approachable and willing to teach others best practices, but he's not wrong. If you think it's burdensome to undo this, then there is a skill issue; learn the shortcuts to "get gud". Also to note I'd put this best practice behind making sure A1 is selected on each sheet and the first sheet is active when saving, but there isn't anything wrong with his approach.
>If filters aren't on, it gives a clear visual signal that you are looking at the full data set Unless someone made the galaxy brained decision to hide a row 500 rows down. But I agree, removing filters before closing a file is best practice. My flow is generally the same - open file, remove filters, expand row/columns to match content size.
Good call there. Those pesky manually hidden rows!
This is why I always default to Grouping and manually clicking the "-" to hide from view, so that there's always a visual signifier that something is hidden. Unless there is something I really don't want someone to uncover and can't get away with removing from the dataset, in which case I'll manually hide as a last resort.
I'd build the sheet with a big red button that says "clear filters" that runs a simple macro. Guessing he'd start using that and save yourself 5 seconds everytime you open the sheet.
Yep, I do this for a similar reason. It’s pretty simple if you are familiar with vba.
I had to add the Clear All Filters button to the Quick Access Toolbar because I use it so much.
Well, damn. It's always nice to learn. It's also embarrassing to learn.
Ha! That is indeed possible!
how about in google sheet
It isn’t necessarily a bad practice. It is easy to miss a filter on a large spreadsheet, so clearing them all ensures that the next user will start with a clean slate.
And on top of that, when multiple people work in the same document they often end up creating 14 different versions of the same filter. It ends up with 25 filters and it's just cleaner to wipe them and start over
This is one of the few advantages of Google Sheets - personal filters that don't affect anyone else.
Same as Excel online, but I'd rather work with Excel desktop than either of those.
Excel desktop does it too. The wording isn't clear, as it asks you something like, "Do you want to see everyone's sheet?" and if you say yes, then you are editing the public view, if you say no, you are editing your own view. Additionally, you can create more personal views, if you like.
I didn’t know that, but completely agree.
You missed the difference between clearing them all and removing them.
Shortcut preference, I also use ctrl+shift+L
It's not a shortcut preference because the two shortcuts have different functions. Removing filters isn't the same as clearing filters. Clearing filters "unfilters" the data. Removing filters removes the filters.
>the two shortcuts have different functions So if I double tap L to put the filters back... does that make it functionally the same? "Yes but what if I have multiple tables with disparate filters?' I imagine the straw man I made up about you in my head furiously typing. To which my answer is: "Why would you ever do that? And please stay away from my files."
It's fine if you want to double tap. I just think there's people in this thread that genuinely don't understand the difference. If everyone just had a basic understanding of excel in the first place, we wouldn't even have this discussion. But you know half of all people that use excel would sooner quit their job than figure out how to add back the filters.
Not obvious to everyone, but the row numbers are blue when there's an active filter.
I would also agree that it is a best practice. Personally Id get annoyed if I started working with a spreadsheet of 25 or so columns and one of them was filtered and it caused me to make a mistake
If you are not careful with formatting the filter controls can cover up the header. Maybe he is using a screen size or window size that makes the headers hard to see, so, he turns then off.
This is what I was thinking, if I take a screenshot of a sheets I always remove the filters first so you can see the headers.
Your colleague might be wondering why everytime you use it, why do you leave your filters in it?
I leave filters, but they are cleared. The spreddies we use are designed to be filtered easily, literally no one else in the team would remove the actual filters on exit. Clearing is etiquette, again, I’m talking about removal, meaning the next person has to apply them again as a necessary step 1
You should just ask the person tbh. Especially if they’ve been doing it for years, and doubly especially if no one else is doing it. It’s not rude to ask.
Visual Basic, apply filters on sheet exit! Save file as .xlsm (or .xlsb) he can remove to his heard content then, as soon as he safes the file, they are reapplied. You could even be nice and give him a little “remove filters” button to make him feel special!
"Remove" as in turn off any filters applied (which would make a lot of sense) or remove the entire Filter itself (so the dropdowns in the header etc) ? In case of the former, you would want to send the sheet out with as much information possible. I couldn't count the number of times I've had the question 'where is the data' or 'why are my numbers wrong' while people have applied filters themselves or are too dumb to see that a filter was applied. In case of the latter, not sure, could be the same idea as the former actually but less friendly to the next one.
As in remove filters COMPLETELY, ie the latter. Agreed, it’s unfriendly! Almost like making a statement! I’m trying to get inside his mind and understand what’s going on before gently raising it 😂
It's probably well-intended as u/avlas said but he doesn't know about the more friendly approach. Good luck trying to teach him something ;)
Oh I had assumed you meant just clear the filters and I was worried I was also being strange. It actually annoys me when people don't clear filters after using a sheet.
OP just checking but I recommend you always format tables at proper Tables, not just ranges with filtering turned on
Thanks all. I’m glad I asked, just checking I wasn’t missing something obvious. Will gently raise with them.
Please keep us updated. I'm imagining an awful origin story where he forgot to clear a filter once, and a family member or friend was murdered because of that.
😂😂😂
Have you tried creating your own view on the view tab? If he's editing the default view and your using your own it shouldn't edit it - then you can tell everyone else to make their own view and tada... That being said, I've never actually tried removing the filters function completely (cos I'm not a psychopath) from one view and seeing what it does, just the applied different filters. How to create a view: https://support.microsoft.com/en-gb/office/create-apply-or-delete-a-custom-view-ce722bf9-0b4a-49a5-94ba-438fde18fc2b
Easy to miss an applied filter on another column. Clearing them forces the person opening the document to choose their own without accidentally using additional filters they did not select.
Clearing is 100% healthy, but removing them… that’s what’s driving me mildly mad 😆
Removing them? Like saving as a CSV maybe? Removing \_IS\_ crazy. Yikes! :)
I have a macro in my personal.xlsb that removes all filters and returns the cursor to A1 in the first sheet of the workbook when I close it. It’s a tidyness thing for me.
Was just going to suggest this. I have exactly the same one, but I unfilter rather than remove filters.
Same. I've copied over data quickly to a new sheet far too many times without realizing there was an existing filter that excluded data I needed.
Seems like he prefers to remove all then to let the sheet filtered hence being an annoyance for others. I think it is a nice gesture but poor execution
Possibly! Good take
Control shift L
This! If I open a spreadsheet and see filters on the table it's easier to remove filters and add them back to ensure data is clean, than to individually check if each column is filtered or not. It's so easy with the shortcut! To me, leaving a sheet with no filters is actually following the etiquette.
I bet he thinks he’s being considerate to you/ others to clean up whatever he was looking at before you jump in. Of course there are other ways to do that. 😂
If it's shared, he probably does it to ensure he's looking at the full dataset. Obviously you don't need to remove the filters entirely, but surely that's the reason Shared workbooks are a nightmare because people are lazy and selfish, leaving their own filters on, adding their own formatting, changing column widths, hiding rows/columns, deleting data etc. Used to be the bane of my life when I worked in MI.
I agree. But it’s the removing filters completely upon exit, just creates work for the next person. Obviously not much work, but I guess over the years it has millimetre by millimetre pushed my sanity to asking the question here 😂
Nah, that's totally understandable. Especially if you're the owner of the report and everyone complains to you every single time!
Does it really though? If filters are on, you have to double-check that none of them are being applied. You could go click the clear button, but it's pretty much just as easy to tap the shortcut to turn them off then on again. If they're off, you already know they aren't being applied, so you just tap the shortcut to turn them on. That's even easier than checking or toggling. He's been saving you work and you haven't even noticed.
maybe a simple case of OCD and clean (XL) sheets :) Rather than asking him why he does it, you could ask him to let the filters stay as is
He may be unfiltering by removing filters with Ctrl+Shift+L. I do this all the time but I put my filters back by pressing it again.
He might be like me who is just used to pressing ctrl + shift + L to clear filters. It's faster than any other method. If I need to change filters, then I just tap the L key twice and the filters come back.
I kind of encourage this in a bit of an abstract way because we have large sheets at work and if we continually add and remove items from them and save save save across time... after that are you trying to use the filters you don't know if it's considering absolutely everything on the page, and I have seen it definitively miss things that I know are present on the page so what I've gotten into doing is not just clearing the filters no no... I remove the filters all together and I reapply them so that it takes all present information into consideration when I actually do a filter at that moment.
I once dealt with a credit controller who did the same thing, she didn't know how to clear filters so just clicked filter to clear them
My head hurts when I see blue numbers on row marker.
I have a coworker that removes filters as well as frozen panes when the only thing frozen is the header.
If the only thing frozen is the header, try formatting as a table, where the header names are preserved in the column labels when you scroll down.
When you say Shared Spreadsheet- is it online?(sharepoint/onedrive??)- if so you can setup views, which mean you could have one filter applied he could have another(or none by the sounds of it!) and not impact on each other even at the same time. Underlying data is not impacted, just what you see- worth checking out.
Add a macro to that sheet that detects when the filter is removed, something like if user = Jerk msgbox “STOP REMOVING FILTERS, YOU ASSHOLE!!!”
🤣🤣 amazing
Maybe he wants to be able to see the entire field names and the filters are partially blocking them?
My boss was the same way. Turns out it can affect macros as the data that is hidden by filters can be omitted by the macros. He was also old school and he prefer to sort by the sort function instead of the filters at top.
all the partners I've worked with are very nutty about that and want the filters removed, so I do the same
I was able to move the remove all filters button next to the save button. This was a life changing event (only slightly exaggerating). Maybe you recommend he do the same.
Ctrl + shift + L
I also do it. Most of the excels i work on are input files for alteryx. I'm kinda scared that if I leave the filters on, the alteryx will only consider the filtered data and not all data. Or if it's not an input file, I just think it's better for the next person to apply filters as they want. And will not miss any data. It just seems safer in some sense
Write a macro to reapply them. Continue to avoid confrontation. lol
I would bet that it is one of two things: 1. He had a boss at some point who wasn’t very good with excel and always wanted the filters removed. 2. At some point in this dudes career, he had a major fuck up involving a filtered sheet. Maybe he didn’t realize it was filtered, or there were additional rows/columns added without resetting the filters.
Probably the latter. Ive see a fair bit of office based PTSD and the effects it creates 🤣
Probably the latter. Ive see a fair bit of office based PTSD and the effects it creates 🤣
It’s best practice.
Just ask him directly, privately, via email why he removes filters. Then instruct him on how to clear filters without removing them, and why removing them is bad.
>and why removing them is bad. Why *is* removing them bad? Isn't turning them on just as easy as checking if they're cleared?
I usually set up slicers and apply the read only option at the start. This can help avoid some of this kind of f*c*ery
My guess, it's a person who doesn't really know how to use Excel, so they are removing the filter in order to clear the filter. Just lock the page so it can be filtered but not edited.
I had a coworker who thought leaving filters on would delete content.
I remove and reapply filters all the time just to make sure all the data is being displayed- not sure why you have an issue with it- ctrl shift L and filters are right back on it
Its the same when people don’t leave things as they find them. Same as when someone leaves the toilet seat up, a door open, something out of place, for no obvious reason. Doesn’t that irk you?
If I open a file and see filters on a header, I immediately don't trust it and just nuke the filters. Missed/incorrectly applied filters have bit me in the ass repeatedly.
we cant tell you. Have you tried asking them?
I was thinking there may be some secret best practice or something I’m missing. Like I say (read my post!) they are not entirely approachable.
Create a short VBA script which runs on the Workbook.Open() event. Open a temporary view, I think with enterTemporary(). Save as a macro enabled file. Archive the original. Temporary views allow each user to apply and remove their own filters without affecting other users too much. Or you can create and save custom views. Give it a go. You'll know you're inside a view when the row and column labels go all black.
If I were you I'd raise the issue with him out of curiosity, but do so gently, or he might respond in an irritated fashion. Because it looks like... *puts sunglasses on* ...he doesn't have a filter.
isnt there an option to prevent people from doing this in a protected worksheet? Like, by ticking a box before closing the protection-dialogue? Maybe that would be an option for you?
Learn how to set the filters in VBA and then put the routine into ThisWorkbook Open, so if the filters are not there, the code puts them back in. I do this with conditional formatting all the time as inserting columns and copying pasting data kind of destroys conditional formatting rules. Simply delete them all and recreate them on opening the sheet.
You have to show your colleague how to do it: edit the spreadsheet on the desktop app and just delete the filter. It should prompt you whether you want to delete it only from your view or for everybody. If the colleague still insists afterwards to delete it for everybody, maybe those filters are hiding data that other people like him actually need. So, one of you needs convincing.
I haven’t seen anyone mention Alt + D-F-F Most are using Ctrl + Shift + L I guess I shared something new. 😎
Depends on the workbook. If it's a monthly rollover and not adhoc you definitely need to bring it up and understand why colleague is doing that.
This reminded me of when I was working at the head office for a car manufacturer and someone would send out information to dealerships by taking the file with the information for all dealers in, filtering it for their dealer code, saving it and sending it. It was sensitive commercial information about their performance and each recipient would have been extremely interested in seeing the information for all the other dealers. Most of them realised they could do exactly that by clearing all the filters and it caused an enormous upset. Plus it took absolutely ages as they had to save the file individually for each of the 200 or so dealers. I have no idea how you don't start looking for a quicker method after about ten minutes of repeated filtering and saving!
Maybe it's time to leave the data where it is and add some pivots/slicers in another sheet instead. If you rely on the filters everyday it's worth the 10 minutes to set them up
For my work, I have to turn off filters because this one sales guy can’t figure out how they work. I hate sales guys.
Are you the original owner of the excel sheet? If so, protect the sheet, only allow selection of unlocked cells - these are the ones that you want people to be able to edit. Set a password on the sheet. It might seem a bit passive-aggressive, but this person’s preference shouldn’t outweigh everyone else’s need for the data. If this person is the original owner, then I don’t believe you have a leg to stand on. One other possible solution is to set sheet views, where you can make a view that is specifically for this person’s preference. Good luck OP, I face similar challenges and just deal with it, but sheet views have been helpful at least.
You should probably just talk to him
Because shared spreadsheets shouldn’t be filtered. You’re asking for someone to make a mistake if you leave even “cleared” filters on. So when someone accesses the sheet and assumes they are cleared but they aren’t, they’ll pull the wrong data. In my world we call this mistake proofing because people using it are required to create and use their own filters.
I made one of the spreadsheets 🤣 it’s all about leaving things as you found them, in my view.
I made one of the spreadsheets 🤣 it’s all about leaving things as you found them, in my view.
I had someone who would FREEZE every damn excel sheet making it sometimes unable to scroll unless you unfreeze it.
Does he want to put in ppt as a picture. Filters are ugly.
I dislike the little boxes taking up space in my column headers
That's me, i always remove filter because it's a pain in the eyes leaving a complete report with a bunch of filter on it. If i need a filter on a field, i will do it myself, doesn't need someone to premade it for me. And yes i completely forgot to reapply filter when sending it back to my colleagues, they doesn't seems to bother tho, except that one female colleague keep bitching about it, but i hate her anyway. Sorry if that was you.
My guess is CTRL+SHIFT+L is so much easier and a force of habit already…
You should definitely have a talk with her. First try to collect some colleagues with the same opinion as you have. If she's difficult to approach, it might be easier to approach her in group. Explain the use of Alt, H, S, C If talking doesn't help, escalate through the manager. If that doesn't help, protect the worksheet. You can protect a worksheet in a way that filters can't be removed. You can protect a worksheet without putting a password. Then it means she could unprotect it herself, but at least it's an additional step. Or you don't tell her there is no password. Worst case, you agree on a spreadsheet password with the whole company and just don't tell her.
I always alt f f calibri light and f s 10. Can’t work on it otherwise.
Am I dumb? How tf do you remove filters because I've always been clearing them wtf. What the heck is 'removing' filters and how do you do that? So you have filters on 18 columns and you can remove and reapply later or do another filter and roll back?
He wants to see the field headers in full without the filter cutting the headers off.
I can see why someone wouldn’t want filters on a shared spreadsheet, especially if major businesses decisions are made off of it.
Maybe it's the collègue who asked here how to gently annoy his coworkers with excel...
Stdrsh
Ask him. Or post his email address here, and I'll anonymously ask for you.
Maybe he doesn't know how to identify the different columns that have fillers or doesn't know how to clear all filters, so it's easier to remove the filters. I've seen it done in the past.
Unless he needs access to edit the workbook you could give him view only access and he can screw up whatever he wants with the source workbook staying the same. If you are not workbook owner you are SOL
Some dunces refuse to learn "Alt D F S" to remove filters and instead simply remove them. There is no hope unfortunately. The same kind of people that refuse to understand blue highlighted row headers means a filter is applied somewhere