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.
Tag Archives: formula
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.
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.
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.
Free Excel Webinar Recording – Conditional Formatting
Feedback score 94%
My free Excel webinar for August 2018 covered Conditional Formats. 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.
This session will take you through the basics, as well as an introduction to formula-based formats.
- Data bars
- Creating a progress bar using a Data bar
- Colour scales – traffic light colours
- Amending the default settings – getting the result you want
- Icon sets – icons can be better for colour blind people
- Cell-based rules – make the most of built-in features
- Working with dates automatically
- Formula-based rules – use formulas and functions to gain total control over conditions
Depreciation using the INDEX function
Alternative to OFFSET
Recently Liam Bastick (Excel MVP) wrote an article about using the OFFSET function to calculate depreciation in financial models. You can check out the full article here.
Copying Without Affecting Relative References
Works for Excel 2013 onwards
There is an easy technique to copy a single formula and paste it without affecting relative references but what if you wanted to paste lots of formulas?
An Easier Step Chart
A Line chart solution
Here’s another way to create a Step Chart. This one is quicker. I wrote previously about using a scatter plot and error bars but it required a lot of chart changes. This one hacks a line chart and requires no chart changes.
Sequential numbers in a filtered list
A formula solution
Let’s say you have a filtered list and in each of the filtered cells you want to enter a sequential number, but in the hidden rows you don’t want to enter anything. There is a way, but it takes a few steps.
Validating Checkbox Linked Cells
Data Validation and formula options
When you create a checkbox you need to link it to a cell on a sheet to be able to use its result. The user could overwrite that linked cell with a value or text and affect formulas that are using the checkbox linked cell. You can add a validation to make sure the linked cell only contains TRUE or FALSE.
Vote No for Gauge Charts
They take up too much space
Gauge charts are not a standard chart in Excel and are quite complex to create. Gauge charts should be avoided for dashboards.
Free Excel Webinar Recording – Copy and Paste Tips and Tricks
Feedback score 93%
My free Excel webinar for June 2018 covered Copy and Paste Tips and Tricks. 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.
The session focuses on the Paste Special dialog plus a little known pasting feature that is great for dashboards.
It covers the hows and whys of
- Paste Values, Paste Formulas, Paste Formats
- Converting negative to positives
- Fixing Text numbers in-situ
- Applying a Factor to a range
- Paste Link – how and why to use it
- Transpose (switching rows to columns and visa-versa)
- Paste Picture Link (great for dashboards)
The session includes lots of keyboard shortcuts. As always, I shared a few other tips during the session.
Free Excel Webinar Recording – Text Functions explained and demonstrated
How to text safely in Excel
My free Excel webinar for May 2018 covered Text functions. Download the materials using the button below and watch the video.
You know how well Excel handles numbers, but not everyone knows that Excel has built-in functions and features to work with text as well. This session covers Excel’s text functions and features, in it you will learn
- the different techniques to split text
- techniques to extract text from text
- how to easily join text
- techniques for tweaking text for dates, numbers, upper and lower case
- the formulas for extracting sheet and file names
- two new Excel 2016 functions for combining text from ranges
As always, I will be sharing a few other tips during the session.
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.
Download Text Function materials
Excel and Outliers
New functions make it easier to find them
Malcolm Gladwell’s book Outliers is a great read – I reviewed it here. Its premise is that some outliers (events that are far outside “normal” expectations) have causes and hence are worthy of investigation. Excel have some functions that can help identify outliers in your data.
Identify Milestone Years in Excel
MOD function to the rescue
Recently a client wanted help in summarising a large data list of employees. They wanted to identify the years of service in terms of 5, 10, 15 years and other milestone years based on a start date.
Excel Test For One of Two Conditions
A shorter alternative
In Excel it is quite common to test a cell for either a zero or a blank. If either of these two entries are found then you do a particular calculation. There is an easy way to handle this.
Excel Formula to Extract the Domain
Using the SUBSTITUTE function
I recently read a blog post about using Excel for SEO (Search Engine Optimisation). It mentioned a function to extract a domain from a URL. The function was from Google docs, not Excel. So I wrote an Excel formula to extract the domain from their list of URLs.
Checking Rounded Totals in Excel
Another SUMPRODUCT technique
Rounded values in Excel can pose a few issues. There is a formula you can use that can round a range of values and then SUM the results. This can be used as a check total for rounded values.
SUM or COUNT based on code length in Excel
SUMPRODUCT solution
Let’s say you have codes that have differing numbers of characters and you need to analyse them based on how many characters a code has. There is one function that can SUM and COUNT based on the number of characters in a code.
Another date solution
Formula to the rescue
Getting dates into order is usually a job for Power Query, but not everyone has it or uses it so I still get requests for formulas to fix text dates.
Towards a Simpler IF Function
New function in Excel 2016
Excel 2016 has introduced a new type of IF function to simplify handling multiple conditions. It is called IFS.