Excel Auto-Adjusting Drop-Down List
If you need to have a drop-down list automatically adjust and remove items as they are selected, here’s one technique.
(more…)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.
(more…)Clear the Filter in One Column Only in Excel
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.
(more…)Filter Issue with Excel
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.
(more…)Removing Greyed-Out Slicer Options in Excel
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.
(more…)Excel Slicers Trick
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. (more…)
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.
(more…)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.
(more…)
Free Webinar Recording – Excel Formatting Tips
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. Download Materials 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. https://vimeo.com/366395923 This session covers: a format to avoid and the one to use in its place keyboard and mouse…
Hiding Zero Rows in Excel with No Macros
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. (more…)
Multiple Entries in an Excel Filtered List
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.
(more…)Extracting entries from a slicer
There is a complicated way to extract the entries from a slicer but there is also an easy way to do it. (more…)
Excel VBA to Remove Filters
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.
(more…)Sequential numbers in a filtered list
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. (more…)
Select Visible Cells Only Hack
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. (more…)
Find the first and last dates in a filtered list
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. (more…)
Hide and Seek with Excel Charts
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. (more…)
Excel Filtering for Blanks or Zeroes
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. (more…)
Filter by Cell Value Keyboard Shortcut
I have found a keyboard shortcut combination to one on my favourite right click options and its quicker to use. (more…)
Excel Filter Trick
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. (more…)
Getting a unique list in Excel
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. (more…)
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. (more…)
Summing Coloured Cells in Excel
In my training sessions I sometimes get asked about summing cells based on their colour. A SUMIF based on colour. (more…)
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. (more…)
Slice and dice in Excel
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. (more…)