# Conditionally Selecting a Column in Excel

### The IF function can return a range

When creating a formula sometimes you need to conditionally choose the column to perform a calculation on. The IF function can be used, but there is a trick to shorten the formula.

# 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
• 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.

# Highest and lowest values in an Excel filtered list

### AGGREGATE function solution

If you need to find the highest or lowest three entries in a filtered list you can use the AGGREGATE function to find them.

# 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.

# Handling Exceptions in Excel

### Two ways to consider

When developing budgets and financial models you may need the same rate/value (say debtor days) for 10 months of the year but need to adjust it for 2 months. It is usually December and January as they tend to be a little different due to the Christmas / New Year period. Here’s a couple of ways to handle exceptions in Excel.

# Excel ISDATE Function

### A few solutions

Unfortunately Excel doesn’t have an ISDATE function. Excel’s macro language, VBA does, but there is no spreadsheet function that let’s you know if a cell contains a date. Well there is a partial workaround and you can also use VBA.

# 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.

# 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.

# 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.

# Free Excel Webinar Recording – Custom Number Formats

### Feedback score 92%

In April 2019 I demonstrated many of Excel’s Custom Number Formats.

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 brand new webinar we examine Custom Number Formats which hide away at the bottom of the Number Format tab. These custom made formats offer some useful techniques.

They can

• display negatives in red and with brackets
• format mobile phone numbers correctly
• display numbers and text together and still perform calculations
• hide zeroes
• display rounded numbers to thousands and millions
• display the day of the week
• create customised dates
• be saved and be available in new sheets and files using a Template
• be saved as a Style

See examples and demonstrations of many different custom number formats and learn how to create your own.

As always I will share a few other tips.

# 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.

# Free Excel Webinar Recording – Mastering Form Controls

### Feedback score 92%

In March 2019 I demonstrated how to use some of Excel’s Form Controls.

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.

Learn how to build better spreadsheet interfaces using form controls like check boxes, scroll bars, option buttons and combo boxes.

Form controls can reduce the number of input errors; simplify and speed up input plus add structure to a file.

Discover the function that you need to use to get the most out of these controls.

Find out a few tips and tricks to make creating and using form controls easier.

As always there are a few more tips and tricks shared in the session.

# Handling plurals with the IF function

### Singular or plural?

Sometimes when creating text you need to handle plurals correctly. The IF function makes it easy.

# Free Excel Webinar Recording – Format As Table Features

### Feedback score 93%

In February 2019 I demonstrated how to use the Format as Table feature in Excel, including some advanced techniques.

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.

Many of Excel’s features and functions work seamlessly with formatted tables. They can help you improve the structure and reliability of your spreadsheet files.

Formatted tables can allow you to create powerful reports like those in a relational databases.

Topics covered

• advantages and limitations of formatted tables
• keyboard shortcuts
• using formatted tables with formulas
• solutions to some of the limitations of formatted tables
• using range names with formatted tables
• using formatted tables with data validations
• creating a running total
• using PivotTables
• Relationships (Data tab)

As always there will are a few more tips and tricks shared in the session.

# Allocate based on start date and number of months

### EDATE to the rescue

Let’s say you want to allocate a value across multiple months based on a start date and how many months you want to allocate. The monthly allocation will be averaged based on the number of months. The solution isn’t that hard.

# Free Excel Webinar Recording – Budget Challenge

### Feedback score 93%

In January 2019 I presented a webinar that examined a solution to a 4 dimension budget challenge. Download the materials using the button below and watch 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.

NOTE: This is not a beginner’s session.

This webinar is based on a budget scenario which you need to read before the session starts. It is only 3 pages long and included in the materials.

Topics covered include

• using INDEX-MATCH (better alternative to VLOOKUP)
• 3-D formulas and techniques to make using them easy
• using a reporting template
• validations
• extracting sheet names
• automating reports

As always there will are a few more tips and tricks shared in the session.