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.
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.
To switch to Full Screen mode – great for presentations – use
Ctrl + Shift + F1
Press it again to revert to the normal view.
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.
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.
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|
|Interactive Excel Chart||Adv||Recording||Materials|
Level Beg = Beginner, Int = Intermediate, Adv = Advanced
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.
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.