FREE Webinar – Financial Functions

Excel has many functions dedicated to financial calculations. These functions take three or more arguments and 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 will cover the following functions

  • PV – Present Value
  • PMT – loan payments
  • CUMIPMT – cumulative interest for loan schedules
  • RATE – interest rate
  • FV – future value
  • NPER – number of period

The session finishes with a loan model that calculates the “missing” value for a loan scenario.

These sessions fill up very quickly, so please register early to secure your place.

Date: February 26, 2016
Time: 1:00 PM AEDST
Event: FREE Webinar - Excel Financial Functions
Topic: Financial Functions
Public: Public
Registration: Click here to register.

Open Filter the Drop Down

If you have filters turned on and you are in the heading row of the table you can press Alt + down arrow to open the filter drop down.

You can then use the arrow keys to move up and down.

Related Posts

 

FREE Webinar – Excel Yourself 2018

My annual review webinar where I examine four of my Excel Yourself articles from the monthly INTHEBLACK magazine from the past year.

Includes extra content not included in the magazine. Content applies to all Excel versions.

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.

Date: December 13, 2018
Time: 1:00 PM AEDST
Event: FREE Webinar - Excel Yourself 2018
Topic: FREE - Excel Yourself 2018
Public: Public
Registration: Click here to register.

FREE Webinar – Excel Yourself 2018

My annual review webinar where I examine four of my Excel Yourself articles from the monthly INTHEBLACK magazine from the past year.

Includes extra content not included in the magazine. Content applies to all Excel versions.

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.

Date: December 12, 2018
Time: 1:00 PM AEDST
Event: FREE Webinar - Excel Yourself 2018
Topic: FREE - Excel Yourself 2018
Public: Public
Registration: Click here to register.

Chapter 13 – Validations

Price: $25 incl GST or free if you own the book

Duration: 1.0 hour

The 20th in my series of webinars based on my book and the last free one.

The book is the manual.

We are up to page 337 in the printed book. We will start and finish Chapter 13 which covers report validation techniques.

The session covers

– Centralised validations
– Rounding Tolerances
– Error tracking and debugging
– Precedents and dependents
– Identifying new codes
– Conditional formats

If you own the book you can use the answer to a question as the voucher code to attend for FREE.

Date: November 29, 2018
Time: 1:00 PM AEDST
Event: Book Webinar - Chapter 13 - Validations
Topic: Book Chapter 13
Public: Public
Registration: Click here to register.

Bold Your Headings

Apparently this is not widely known, but you should always bold the headings in your tables.

Then when you use Format as Table (Ctrl + t) on the Home ribbon tab the header row will be correctly identified.

This also applies to the Ctrl + Shift + L shortcut to insert the filter drop downs.

It also applies to the ranges used for charts.

In general ALWAYS BOLD your headings – it is something Excel looks for.

Ctrl + b is the bold shortcut.

Related Posts

Webinar – Macros 6 – Workshop 1

Price: $25 incl GST

Duration: 1 hour

Learn how to bring together many of the techniques from previous sessions to solve real world problems.

A variety of macros are covered showing you different ways to achieve similar outcomes. Lots of tips and tricks along the way.

The four macros covered are

  • conditionally hide rows (different macros shown)
  • conditionally hide columns (also run via an event)
  • clear input cells (different macros shown)
  • convert a MYOB report in to a data table
Date: November 27, 2018
Time: 1:00 PM AEDST
Event: Webinar - Macros 6 - Workshop 1
Topic: Macros
Public: Public
Registration: Click here to register.

Webinar – Macros 5 – Function Macros

Price: $25 incl GST

Duration: 1 hour

Excel Function macros allow you to create your own cell-based functions, just like Excel’s built-in functions. These are called User Defined Functions (UDFs) and can be used to simplify complex calculations.

Macro Functions also allow you to create re-useable code which can centralise your business logic and make code maintenance easier.

This session will examine a number of Functions to explain how, why and when to use them.

This session will cover

  • the SUMBOLD UDF – add up bold values
  • the JOIN UDF – joins text togther from a range
  • functions for sheet, file and user names
  • alternatives for the SEARCH and FIND functions
  • the SUMCOLOUR UDF – adds up specific values in cells with a certain fill colour
  • an “Are You Sure?” function
  • a function to identify if a one of a list of words is used in a cell
Date: November 26, 2018
Time: 1:00 PM AEDST
Event: Webinar - Macros 5 - Function Macros
Topic: Macros
Public: Public
Registration: Click here to register.

Webinar – Excel Data Validation

Price: $25.00 incl GST

Duration: 1.5 hours

Excel has a built-in Data Validation feature that you can use to limit and control the entries that go into certain cells. You can use this to make sure users comply with your input requirements.

Unfortunately the feature has one failing. Learn what it is and how to handle it.

This session will take you through some useful techniques to create flexible data validations including in-cell drop down lists, as well as how to cope with the major failing of the system.

The session covers

  • setting upper and lower parameters – fixed and flexible
  • creating custom error messages
  • how to validate the validations and show the user when an error has been made
  • creating in-cell drop down lists
  • creating flexible lists that automatically expand and don’t require any complicated formulas
  • how to create dependent drop downs – one drop down affects the entries in a second drop down

As usual I will throw in some useful general tips along the way.

Date: November 22, 2018
Time: 1.00 PM (AEDST)
Event: Webinar - Excel Data Validation
Topic: Excel Data Validation
Public: Public
Registration: Click here to register.

Atomic Habits (2018)

A new book that covers how you can make small incremental changes over time that lead to large results.

Atomic means small, as in atoms.

I have followed James’s Blog (link below) for a number of years and this book covers topics from the blog plus new material.

James Clear’s blog

His back story is interesting and inspiring.

It is a practical book and he provides ideas to increase your good habits and reduce your bad habits – useful strategies to do both are included.

Habits are automatic and many times unconscious, so the more automated and intentional you make your habits the more time you have for other important things.

You may need to read it a couple of times to get the most out of all his suggestions.

Highly recommend.

Excel Advanced Range Names

 Price: $25 incl GST

Duration: 1 hour

This session covers many advanced techniques. These techniques can be applied to reporting models, dashboards, budgets and forecasts.

The session covers

  • Naming conventions – what they are and how to use them to simplify formula creation
  • Non-formula uses for range names – create an unbreakable hyperlink
  • Linking between files – technique to reduce broken, or corrupted links
  • Advanced techniques – creating a sheet name range name and some advanced ways to refer to ranges
  • The INDIRECT function and range names
  • One Formula to Rule Them All – an amazing technique that can be applied to many situations – a single formula to extract Actuals, Budgets and Forecasts
  • Advanced range reference techniques

As always, I will share a few general tips along the way.

Date: November 20, 2018
Time: 1:00 PM AEDST
Event: Webinar - Excel Advanced Range Names
Topic: Excel Advanced Range Names
Public: Public
Registration: Click here to register.

Excel Range Name Essentials

 Price: $25 incl GST

Duration: 1 hour

Learn the basics, plus some powerful techniques to make your range names more dynamic. This session has everything you need to get started with using range names effectively.

Even if you have been using range names this session provides some useful shortcuts and techniques that you may not be aware of.

This session covers

  • the three ways to create a name (some are much easier than others)
  • naming rules and suggestions
  • keyboard shortcuts
  • name intersections
  • formula-based names
  • using names with formatted tables

As always, I will share a few general tips along the way.

Date: November 15, 2018
Time: 1:00 PM AEDST
Event: Webinar - Excel Range Name Essentials
Topic: Excel Range Name Essentials
Public: Public
Registration: Click here to register.

FREE Webinar – Excel 2016 Mouse Shortcuts

Following up from the keyboard shortcuts session in October, in November we will look at quick ways to do common tasks with the mouse.

Some of the topics and techniques you will learn include:

  • the various cursor shapes and how to use them correctly
  • right clicking options you may not know exist
  • copying anything quickly with the mouse, even sheets
  • drawing perfect circles and squares
  • lining up charts and other shapes perfectly
  • cool techniques with the Fill Handle
  • something you may never have tried with the right mouse button
  • using the the Office clipboard
  • a trick to use the Series dialog

This session will demonstrate lots of techniques using the mouse and also using the mouse with the keyboard.

Even if you prefer using the keyboard you might learn a few useful new techniques.

Date: November 21, 2018
Time: 1:00 PM AEDST
Event: FREE Webinar - Excel 2016 Mouse Shortcuts
Topic: Excel 2016 Mouse Shortcuts
Public: Public
Registration: Click here to register.

FREE Webinar – Excel 2016 Mouse Shortcuts

Following up from the keyboard shortcuts session in October, in November we will look at quick ways to do common tasks with the mouse.

Some of the topics and techniques you will learn include:

  • the various cursor shapes and how to use them correctly
  • right clicking options you may not know exist
  • copying anything quickly with the mouse, even sheets
  • drawing perfect circles and squares
  • lining up charts and other shapes perfectly
  • cool techniques with the Fill Handle
  • something you may never have tried with the right mouse button
  • using the the Office clipboard
  • a trick to use the Series dialog

This session will demonstrate lots of techniques using the mouse and also using the mouse with the keyboard.

Even if you prefer using the keyboard you might learn a few useful new techniques.

Date: November 13, 2018
Time: 1:00 PM AEDST
Event: FREE Webinar - Excel 2016 Mouse Shortcuts
Topic: Excel 2016 Mouse Shortcuts
Public: Public
Registration: Click here to register.

Delete a cell comment

From a question in a recent webinar I found a keyboard shortcut to delete a cell comment.

Alt r d pressed in sequence, not held down.