Just because you can, doesn’t mean you should. This applies to many thing in Excel and especially to charts. With charts the “less is more” philosophy works well. Have a look at the four charts in the image below.
Monthly Archives: October 2016
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
Excel and pdf
Creating a pdf from a single sheet
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.
Disabling Sheet Unhide in Excel
How to hide a sheet and make it hard to unhide
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?
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.
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.
VLOOKUP and COLUMN Function Warning
Be very careful using these two together
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.
Seth Godin
September 29, 2016
September 29, 2016
A short read – never stop learning, reading and asking questions.
Publishing a Power BI Report
Getting it out there
Well after getting the data and creating a report and then a chart, let’s get the report onto the web.
September 30, 2016
Some great additions to the Power BI features this month.
More maps; dependencies view; show errors; Load More option to identify more columns in your data and converting a column into a list to name few.
The Handy Functions in Excel – LEFT and RIGHT
Simple Defaults
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.
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.