The most common type of Data Validation in Excel is a drop down list. In the example below I allow the user to select a year, then a month (using a drop down) and then enter a valid day in the month.
Tag Archives: functions
Free Webinar Recording – Excel Yourself 2019
Feedback score 93% based on 41 responses
In December 2019 I reviewed four of my articles from 2019 plus some new content.
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 year’s webinar includes
- a new logic function IFS
- how to do budget allocations across months using the MOD function
- how to do complex monthly budget allocations
- using helper cells to achieve advanced conditional formats
As always there will be other tips and tricks shared along the way.
Counting Duplicates in an Excel Range
Another SUMPRODUCT solution
The Duplicates option under conditional formatting is useful to identify when there are duplicate entries within a range. This requires you to review the range to see if there are any duplicates. You can use a formula to identify ranges that contain duplicates.
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.
Extracting initials in Excel
Handling a space or a comma separator
If you have a system that uses initials to identify people then being able to extract initials from a first name and last name combination can be handy. A formula can automate the process and there is also a quick, manual way to do it.
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
- 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
Excel Macro to Insert a Formula
When I am creating a file for my training or for my blog or other articles that I write I regularly use the FORMULATEXT function to display the formula in a cell on the right of the actual formula. To save time I created a macro to do the work for me.
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.
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.
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 – 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.
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.
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.
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.
Excel Formula Week Starting with a Monday
The WEEKDAY function has options
Let’s say you have a weekly roster. You have a date and you need to identify the Monday start date for the week that contains that date. The formula is fairly easy.
Sort by value ignoring the sign
An absolutely simple function to the rescue
I recently received an unusual request about sorting. They wanted to sort in ascending order but they wanted to ignore the sign of the values. So -44 would be next to 44.
Free Excel Webinar Recording – Excel Yourself 2018
Feedback score 89%
In December 2018 I reviewed four of my articles from the INTHEBLACK magazine from 2018. 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.
Includes extra content not included in the magazine.
This year’s smorgasbord webinar includes
- how to handle validations
- dynamically highlighting the current row in a table (uses a macro)
- centralising logic – how and why to do it – alternatives to the IF function
- creating a slope chart (includes a macro to speed up the process)
As always there will be a few more tips and tricks shared in the session.
Free Excel Webinar Recording – Keyboard Shortcuts
Feedback score 93%
When I ran some face to face training sessions recently I was reminded how much people LOVE keyboard shortcuts. So I decided to update my keyboard shortcut webinar.
In October 2018 I shared lots of keyboard shortcuts. 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.
Using the mouse isn’t always the quickest way to perform tasks in Excel. Keyboard shortcuts can speed up your work and save you hunting through screens and dialogs. Some of the keyboard shortcuts you will learn in this session are:
- wrap text and other useful formats
- applying row and column grouping
- selecting a table quickly
- copying visible cells only
- apply and remove Freeze Panes
- returning after following a hyperlink
- how to avoid an annoying feature of formula and reference dialogs when you press an arrow key to move around
- get the most out of the Tab key
There will be lots of other shortcuts as well. Even if you prefer using the mouse you might learn a few useful new techniques.
Running Total in a Formatted Table
Using the SUM function
Formatted Tables allow you to create formulas that automatically copy down as the table expands. To create a running total in a column you have a couple of options.
Free Excel Webinar Recording – What If Techniques
Feedback score 92%
My free Excel webinar for September 2018 covered What If Techniques. 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.
Content applies to Excel 2010 and later versions. You will need to install the Solver Add-in – instructions in the manual and video.
- Goal Seek – simple what-if changes
- Solver – advanced what-if analysis
- Scenario Manager – handling different sets of inputs
- Data Tables – single and double variable sensitivity analysis
- NEW – Forecast sheet
Auto Format Formulas in Excel
Excel 2013 onwards
Would you like to change the format of all your formula cells so they have a different fill colour or font? There is a way in Excel 2013 onwards.