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.
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.
Multiple Entries in an Excel Filtered List
Ctrl key to the rescue
When a list is filtered you are only seeing the rows that match the filter. The other rows are still there, just hidden. If you want to make the same entry in a group of filtered cells you can’t use the fill handle to drag and copy as you will overwrite the hidden rows. There is an easy way to do it.
Switching Rows and Columns in a PivotChart
Finding the hidden option
Switching between rows and columns in a normal chart is easy via a button on the Design tab. But how do you do it with a PivotChart?
Excel has Icons
New feature
If you have the subscription version of Excel, check out the Insert tab – you have Icons!
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.
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.
Moving Images in Excel
Make the Shift
When you are moving images, charts or other objects that float above the grid you can use the Shift key to make it easier.
Entering Dates in Excel
Stop the full stop
There are only two characters Excel recognises when separating numeric days, months and years in dates. They are the / and – characters.
Please don’t use the full stop as Excel won’t recognise it as a date.
Below you can see examples of using / and – in dates.
When you use the full stop Excel won’t recognise it as a date – see below.
Its left aligned and will be treated a text.
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.
Pasting or inserting with a Formatted Table
When a little icon makes all the difference
When you need to paste new rows to the bottom of a formatted table, or insert blank rows at the bottom, one way is much quicker than others when working with large, complex models.