Chart Suggestions
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.
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
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
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?
(more…)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…
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…
VLOOKUP and COLUMN Function Warning
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. (more…)
Insert a New Sheet
To insert a new blank sheet press Shift + Alt + F1.
Publishing a Power BI Report
Well after getting the data and creating a report and then a chart, let’s get the report onto the web. (more…)
The Handy Functions in Excel – LEFT and RIGHT
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? (more…)