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
In Excel you can us Save As to save a file as a pdf, but it isn’t quite as effective in Excel as it is for MS Word. Often you only want to save a single sheet or a few sheets to pdf. Try this.
Another nice email
Always great to start the day with an email like this.
This one followed a free webinar I ran in October 2016.
You can right click a sheet tab and select Hide, but it is just as easy to Unhide the sheet. What if you want to make it harder to unhide the sheet?
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.
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.
Turn on Calculation
The shortcut to turn on automatic calculation – pressed in sequence – is
Alt m x a
To turn it off
Alt m x m
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.
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.
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.
I saw a technique demonstrated recently with VLOOKUP that I hadn’t seen used before and thought at the time, that’s handy. Upon reflection however, I thought that’s a bit dangerous.
Insert a New Sheet
To insert a new blank sheet press Shift + Alt + F1.
September 29, 2016
Well after getting the data and creating a report and then a chart, let’s get the report onto the web.
September 30, 2016
The LEFT and RIGHT functions are great for extracting leading or trailing characters from a text string. Did you know their default setting is handy too?
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.