One of the most common custom number formats used in Excel is the mmm-yy format. As an example this format displays all the dates in January 2019 as Jan-19. This format is used in most reports, budgets and financial models. There is quicker way to apply it than using the Format Cells dialog.
Category Archives: Tips & Tricks
Free Webinar Recording – Excel Chart Tips and Tricks
Feedback score 94.5% based on 59 responses
In November 2019 I re-ran my Excel Chart Tip sand Tricks session.
The detailed pdf manual and example file can be downloaded using the button below. Content listed below the video.
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.
This webinar is focused on showing you how to create and modify charts in Excel 2016 with a minimum of fuss.
The Chart interface changed in Excel 2013 and I take you through some of the changes.
You will see best practice design and formatting techniques demonstrated and explained.
See how to create dynamic charts that automatically change based on selections made or data added.
Learn about the feature added in Excel 2010 called Sparkline charts.
I also include some useful date-based functions.
Putting Emphasis on Words in Excel
Don't overdo the colours
Did you know that you can format individual words and letters differently within an Excel cell or text box?
Changing the Close & Load settings in a Power Query
Its a right click option
A recent attendee at a webinar posed the question, can you change the Close & Load setting on an existing query? Here is the answer.
Free Webinar Recording – Excel Formula and Function Tips
Feedback score 95.8% based on 66 responses
In October 2019 I re-ran my Excel Formula and Function Tips session.
The detailed pdf manual and example file can be downloaded by using the button below. Content listed below the video.
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.
This session covers lots of tips, tricks and techniques to speed up the formula creation process. You will learn:
- how to quickly start a formula
- the benefits of the numeric keypad
- to use AutoComplete to save typing
- how to easily insert $ signs to fix references
- about the calculation sequence
- how to use the colours Excel displays when editing formulas
- the tricks on selecting large ranges
- using formulas and Format as Table together
- a helpful technique when working with ranges in other sheets
Free Webinar Recording – Excel Formatting Tips
Feedback score 94% based on 70 responses
In October 2019 I re-ran my Formatting Tips session. The detailed pdf manual and example file can downloaded by using the button below. Content listed below the video.
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.
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
Sequential Numbers Formula
A ROWS by any other name
Getting a single formula to create a series of sequential numbers is most commonly done with the ROWS function.
Allocating Across Financial Years
Make the most of MOD
A recent question from a CPA in Canberra allowed me to combine the MOD function and helper cells to offer a solution.
Linking sheet controls to cells
Easy and quick way
Most spreadsheet controls need to link to a cell to enable interaction with the spreadsheet. There is an easy way to do that.
Some actions clear the clipboard
Some actions in Excel will clear the clipboard after you have copied or cut.
Two common ones are
- inserting a new sheet
- clearing a filter – using Clear in the Data menu (keyboard shortcut Alt a c )
So if you are going to do either of these, do them before copying or cutting.
Copying between sheets in Excel [VIDEO]
Mouse and keyboard shortcut to speed things up
Copying between sheets or files is a common task. I’d like to share a quick and easy way to do it.
Increase Font Size in VBA Window
Easy change
Yes, you can make the text in the VBA window easier to read.
It is also great for training.
(This tip may not work on 4K monitors.)
To open the VBA Window press Alt + F11.
Click the Tools menu and then Options – see image below.
Click the Editor Format tab and change the Size drop down to 14, or whatever you want – see image below.
Click OK and the font size will now be increased in the code window.
See below for a comparison between 10 and 14 point.
Much easier to read!
Windows File Explorer – New Folder
To insert a new folder in Windows File Explorer use Ctrl + Shift + N.
Unlocking coloured cells in Excel
Styles and Find solutions
When you create an Excel file that handles inputs it is best practice to colour code the input cells. The colour you choose isn’t important but making sure you use it consistently is. You may need to unlock the input cells if you plan to add sheet protection to the file. Here’s a couple of ways to do that.
Free Excel Webinar Recording – Financial Functions Part 2
Feedback score 87%
In July 2019 I explained and demonstrated a number of Excel’s financial functions – see below for more details.
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.
In July 2019 following on from the response and feedback from my June webinar, I covered more financial functions. These are more related to comparing and analysing cash flows. I have also included a couple of requested schedules.
This session will cover the following functions
- NPV – Net Present Value of regular cashflows (learn to trick to using it correctly)
- XNPV – Net Present Value of irregular cashflows
- IRR – Internal Rate of Return of regular periodic cashflows
- XIRR – Internal Rate of Return of irregular periodic cashflows
- Discounted Payback period schedule
- Flexible Loan schedule – handles lump sum payments
As with all my sessions, I will throw in a few other shortcuts along the way.
Full Screen
To switch to Full Screen mode – great for presentations – use
Ctrl + Shift + F1
Press it again to revert to the normal view.
Keyboard shortcut to insert a Text Box
A quick way to insert a Text Box is by pressing, in sequence (not held down) Alt N X.
A blank Text Box is placed in the middle of the sheet.
Default Function Arguments in Excel
What to leave out
Many of Excel’s functions have default arguments. What are arguments? Arguments are the sections within a function that you separate with commas and some of these arguments are optional.
Free Excel Webinar Recording – Financial Functions Part 1
Feedback score 93%
In June 2019 I explained and demonstrated a number of Excel’s financial functions – see below for more details.
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.
These functions take three or more arguments, but there are a few things you need to know to use them correctly. Most involve loans or calculations associated with the time value of money.
This session covered the following six functions
- PV – Present Value of future regular cash outflow
- PMT – periodic loan repayment calculation
- CUMIPMT – cumulative interest – great for loan schedules
- RATE – interest rate
- FV – future value of regular investment
- NPER – number of periods
The session finishes with a loan model that calculates the “missing” value for a loan scenario based on two out of three inputs. The IFERROR function is also discussed.
Related Posts
Format as Table Extends Data Validation
Another advantage to formatted tables
Excel’s Format as Table feature on the Home ribbon has many advantages. One advantage that isn’t mentioned much is the automatic extension of Data Validations.
Fastest way to copy an image, graphic or chart
To copy an image, graphic or chart simply have the object selected and press Ctrl + D. You can press multiple time to paste multiple times.
If you line the first one up then the others will also line up as you duplicate them.
Related Posts
Avoiding keyed in values in Excel formulas
Table solution
One of the cardinal rules of Excel is don’t key-in a value into a formula if that value could change. Tracking the value down could be problematic if you need to change the value. Tables can be the solution to avoiding keyed-in values.
Zeroing an input range in multiple cells
Range names make it easy
In some cases you may have to make manual inputs across multiple cells that are spread across a sheet. Before making the entries you need to clear the existing entries. Creating a range name can make that process much quicker.
Selecting a single word in Excel
Mouse shortcut
If you are editing a sentence or a heading and you need to replace a single word there is a quick and easy way to do it.
Excel Comments (Notes) and Freeze Panes
Row height solution
Inserting a Comment (renamed to Notes in newer versions of Excel) in the first row and then using Freeze Panes to keep that first row visible can cause issues displaying the Comment (Note). Here’s how to fix it.
Free Excel Webinar Recording – Dashboard #1 Guildelines & Techniques
Feedback score 92%
In May 2019 I examined and demonstrated some guidelines and techniques for creating charts for dashboards.
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.
In this session I focused on chart and dashboard guidelines plus some techniques used to create small charts for dashboards.
I also looked at creating a bullet chart, which is an alternative to gauge, speedo or dial chart.
The session covered
- general chart guidelines
- questions to ask yourself to develop better charts
- best charts to use
- charts and formats to avoid
- using text boxes on dashboards
- how to create small charts
- chart templates
- lining up and re-sizing charts
- useful number formats for dashboard charts
- using the #N/A error with charts
- how to create a bullet chart
As always I shared a few other tips.
Excel has Icons
New feature
If you have the subscription version of Excel, check out the Insert tab – you have Icons!
Selecting the same cell in all the sheets
It comes with a warning
Let’s say you want the active cell to be A1 in all the sheets in a file before you save the file. There is an easy and quick way to do it. It doesn’t matter how many sheets are involved, but it does come with a warning.
Automate Input Cell Colour
Use a CELL function option
It is best practice in Excel to have a consistent colour for input cells so that users know where they can and need to make changes. You can automate this process by using a Conditional Format.
Excel, Subtraction and Accuracy
Binary takes a byte
It’s not mentioned a lot, but some of Excel’s subtraction calculations are not exact. When you look at them at 15 decimal points they are slightly out. Most times this make no measurable difference to the result. But sometimes when you round, it does.