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 Webinar Recording – Recorded Macros Tips Tricks and Traps
Getting started with recorded macros
It is easy to create a recorded macro. It is not so easy to create a flexible and re-usable recorded macro. Click the materials Button below to download the pdf manual and example file.
Learn the techniques that can allow you to record effective macros that can handle different ranges and changes to sheet names.
Macros can speed up your work and reduce the time taken for tedious tasks, as well as adding functionality to Excel.
This is the first in a series of webinars dedicated to macros. Future paid sessions this month (August 2019) will expand on the techniques taught in this session. Click here to register.
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.
Automate Strike Through Format in Excel
Task done!
Who doesn’t get satisfaction from drawing a line through a completed task? That sense of achievement. Well you can do the same in Excel.
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.
Promoting Headers Issue in Power Query
Be careful if a first row name changes
Promoting headers in Power Query means using the first row as column headers. In Power Query this is a useful and common option. In some cases it is even automated. There is one time though when you don’t want to use it.
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.
Restricting column inputs based on the current month
A data validation solution
Let’s say you have an input range that covers the whole year. You only want users to make entries in the current month column. How can you limit the month entry? The answer is a custom Data Validation.
Power Query Fill Down Doesn’t Work
The fix is easy
Sometimes when working with CSV files in Power Query you may strike the situation where Fill Down doesn’t fill down. Don’t worry there is an easy fix.
Twelve months of free Excel webinars
Free monthly Excel webinars
This month celebrates 12 consecutive free monthly Excel training webinars. One a month since August 2018.
You can view the recordings and download the materials using the table below. Sorted by Skill Level.
12 hours of free, practical CPD. Remember to keep your own records of recording viewing for CPD purposes.
Please share with the your network, enjoy!
12 Months of Free Excel Webinars
| Free Courses (One hour each) | Level | Recording Link | Materials |
|---|---|---|---|
| Excel Keyboard Shortcuts | Beg | Recording | Materials |
| Excel Mouse Shortcuts | Beg | Recording | Materials |
| Excel 2016 Conditional Formats | Int | Recording | Materials |
| Excel Custom Number Formats | Int | Recording | Materials |
| Excel Financial Functions Part 1 | Int | Recording | Materials |
| Excel Financial Functions Part 2 | Int | Recording | Materials |
| Dashboard Guidelines & Techniques | Int | Recording | Materials |
| Format As Table Features | Int | Recording | Materials |
| Excel Yourself 2018 | Beg/Int/Adv | Recording | Materials |
| Mastering Excel's Form Controls | Adv | Recording | Materials |
| Excel 2016 What-If Techniques | Adv | Recording | Materials |
| Excel Budget Challenge | Adv | Recording | Materials |
| Excel Recorded Macros | Adv | Recording | Materials |
Level Beg = Beginner, Int = Intermediate, Adv = Advanced
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.
Autofit Rows with a Maximum Row Height
Follow up post
In March I wrote a post on using a macro to apply column Autofit on a sheet but with a maximum column width. This is a follow up post as someone requested the same functionality for row height.
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.
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.