In October 2019 I re-ran my Formatting Tips session. The detailed pdf manual and example file can downloaded by using the button below.
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
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.
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.
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.
There is a complicated way to extract the entries from a slicer but there is also an easy way to do it.
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.
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.
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.
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.
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.
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.
I have found a keyboard shortcut combination to one on my favourite right click options and its quicker to use.
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.
I have blogged before about using the Advanced Filter technique to extract unique entries from a list. Well a PivotTable can do it it too.
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.
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.
In my training sessions I sometimes get asked about summing cells based on their colour. A SUMIF based on colour.
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.
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.