Reapply a Filter

Let’s say you have applied a filter to a list.

The list is dynamic and the values have been updated by a refresh – the filter is out of date.

You don’t have to go into the filter drop down to re-apply the filter.

You can use a keyboard shortcut whilst in any cell in the table.

Ctrl + Alt + L

 

reapply


Ron DeBruin

Ron DeBruin is an Excel MVP and has been for a long time.

His site has lots of excellent macro code and free Add-ins.

He has tips and macro examples for the Mac as well as Windows.

I have used his pdf creator code to create pdfs of certain sheets in a file.

If you want to learn about modifying the ribbon he has some great tips and examples.

He also has code examples for amending Excel’s pop-up menus.

 

Calculation Issue

There are two reasons your Excel file won’t calculate.

The first, and easiest to fix, is that Calculation is turned off – see a previous tip on that issue.

The other reason is that there is a Circular Reference in the file. Circular References can stop calculation.

You can confirm if there is a Circular Reference in a file by looking at the bottom left-hand corner of the screen – see image below.

circ

A Circular Reference means that a cell is referring to itself in a calculation. A simple example is shown in the image above, where cell A1 has a formula =A1.

Excel cannot resolve this calculation, so zero is displayed.

In the Formulas tab, on the right-hand side, the Error Checking icon drop down will help you identify the Circular References in the file.

circ_2


Learning Excel’s Function Arguments

When you start to use a function it can take some time to learn the arguments required and understand what Excel expects for each argument. Eg should it be a cell or a range or either?

When you have the start of a function in the formula bar, you can either press Ctrl + a or click the fx symbol on the left of the formula bar – see image below.

function_1

In the image above, the argument in square brackets [range_lookup] is optional. Square brackets around an argument mean it is optional.

This will display a dialog with a listing of the arguments required by a function. The bold names are required, the non-bold names are optional.

function_2

This listing provides a lot more detail on what Excel is expecting for each function argument. This helps you learn more about how to create and use the function.


Related Posts

A great talk discussing how we typically teach in our schools and how it impacts learning skills that build on previous skills.

Its about 11 minutes long – worth a watch.

Mastery is worth striving for before you move on to the next topic.