If you need to have a drop-down list automatically adjust and remove items as they are selected, here’s one technique.
Tag Archives: filter
FILTER Function Technique
An application I use recently updated it’s filtering options to allow you to filter by any filters or all filters. This was a useful addition to the software and I thought that I could apply the same idea to Excel’s FILTER function.
Clear the Filter in One Column Only in Excel
Keyboard shortcut
When you have multiple filters across columns you may want to clear just the filter in one column. There is a keyboard technique to do that.
Filter Issue with Excel
Be careful with formatted tables
When you have a filter in place in Excel you typically only affect the visible cells when you edit multiple cells. There is a case when you are affecting all cells not just the visible ones.
Removing Greyed-Out Slicer Options in Excel
How to tell the Slicer to forget
Sometimes Slicers seem to have a long memory and list entries that are no longer in the current data set. There is a setting to fix this.
Excel Slicers Trick
Controlling multiple reports hack
Slicers can control multiple pivot table reports in Excel. The problem is that if you use a slicer on one sheet to filter a report on another sheet it is difficult to see that a filter is in place. This technique also comes with a warning – see bottom of post.
Adding columns to slicers in Excel
Slicers are a great filter interface. Sometimes, due to layout restrictions, you prefer the slicer to go across the sheet rather than down the sheet. Here’s how you do that.
Filter by Cells Value Excel Hack
Excel has a right click Filter option that speeds up filtering by a single value. You can hack that shortcut to do a little bit more.
Free Webinar Recording – Excel Formatting Tips
Feedback score 94% based on 70 responses
In October 2019 I re-ran my Formatting Tips session. The detailed pdf manual and example file can downloaded by using the button below. Content listed below the video.
CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.
This session covers:
- a format to avoid and the one to use in its place
- keyboard and mouse shortcuts
- how to use and create customer number formats
- about Styles and how they can make your formatting more consistent
- that colours can be used to filter, sort and even find things in your sheets
- how to stop zeroes displaying plus other general formatting tips
- a quick demo of Flash Fill
Hiding Zero Rows in Excel with No Macros
Filter technique
Over the years I have had regular requests for a technique to hide zero rows in reports. You can use macros but you can also use filters. Let’s see how you can implement a filter solution.
Multiple Entries in an Excel Filtered List
Ctrl key to the rescue
When a list is filtered you are only seeing the rows that match the filter. The other rows are still there, just hidden. If you want to make the same entry in a group of filtered cells you can’t use the fill handle to drag and copy as you will overwrite the hidden rows. There is an easy way to do it.
Extracting entries from a slicer
A simple hack
There is a complicated way to extract the entries from a slicer but there is also an easy way to do it.
Excel VBA to Remove Filters
Its a one liner
When creating macros that work with filters it is a good idea to remove filters at the beginning of the macro code. Here is how you do that.
Sequential numbers in a filtered list
A formula solution
Let’s say you have a filtered list and in each of the filtered cells you want to enter a sequential number, but in the hidden rows you don’t want to enter anything. There is a way, but it takes a few steps.
Select Visible Cells Only Hack
Who knew what a filter could do?
When you only want to copy the visible cells Excel has a special option that allows you to select visible cells only before copying. But there a way to make that the default option.
Find the first and last dates in a filtered list
Super power to the rescue
Let’s assume you have a large table that you are filtering. Based on the current filter you want to work out the earliest date and the latest date. You may be surprised to learn the SUBTOTAL function can help you.
Hide and Seek with Excel Charts
Hiding rows and columns
Charts have a behaviour that many people don’t realise. That behaviour can also be turned off. If you hide a row or column in the data range used by a chart, the values will also be hidden on the chart.
Excel Filtering for Blanks or Zeroes
Making the filter work across multiple columns
If you want to filter by blanks across multiple columns the standard Filter feature can’t help you. You can use the Advanced Filter but that takes time to set up and most users don’t know how to use Advanced Filter.
Filter by Cell Value Keyboard Shortcut
Its quicker to use the keyboard
I have found a keyboard shortcut combination to one on my favourite right click options and its quicker to use.
Excel Filter Trick
Saves a few mouse clicks
Do you use the “Filter by Selected Cell’s Value” option? If you do then you will be pleased to know there is a Quick Access Toolbar icon that applies it in one click.
Getting a unique list in Excel
A PivotTable trick
I have blogged before about using the Advanced Filter technique to extract unique entries from a list. Well a PivotTable can do it too.
Filtering Tricks in Excel
Excel’s Filtering feature (formally called AutoFilter) is quick and easy to use, but it doesn’t always provide enough solutions to your filtering needs.
The New Slicer in Excel 2013
Slicers are a graphic filtering tool added in Excel 2010. They allow you to filter Pivot Tables. Excel 2013 added a new slicer that makes filtering by dates a lot easier.
Summing Coloured Cells in Excel
A SUBTOTAL trick worth learning
In my training sessions I sometimes get asked about summing cells based on their colour. A SUMIF based on colour.
Excel 2010 Search Filter Option
When filtering in Excel you will be shown all the entries in a field. Unfortunately, if there are hundreds of unique items this can mean a lot scrolling to select the correct item to filter by.
Slice and dice in Excel
Excel 2010 has Slicers
Excel 2010 introduced a new filter interface for Pivot Tables called Slicers. This feature improved the filtering process for Pivot Tables and made filtered selections more visible.