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.

FREE Webinar – Excel 2016 Conditional Formats

Conditional formats allow you to have cell formats automatically change based on the value in the cell and other cells. This is great for exception reporting. For example when you open a file all the overdue dates could have a red fill colour based on today’s date and the dates for the coming week will be coloured orange. Some of the techniques covered can also be applied to dashboards.

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

As always you may pick up a few other tips along the way.

 

 

Date: August 23, 2018
Time: 1:00 PM AEST
Event: FREE Webinar - Excel 2016 Conditional Formats
Topic: Excel Conditional Formats
Public: Public
Registration: Click here to register.

FREE Webinar – Excel 2016 Conditional Formats

Conditional formats allow you to have cell formats automatically change based on the value in the cell and other cells. This is great for exception reporting. For example when you open a file all the overdue dates could have a red fill colour based on today’s date and the dates for the coming week will be coloured orange. Some of the techniques covered can also be applied to dashboards.

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

As always you may pick up a few other tips along the way.

 

 

Date: August 22, 2018
Time: 1:00 PM AEST
Event: FREE Webinar - Excel 2016 Conditional Formats
Topic: Excel Conditional Formats
Public: Public
Registration: Click here to register.

Chapter 11 -Part 2 – Formatting

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

Duration: 1.0 hour

The 17th in my series of webinars based on my book. The book is the manual.

We will continue Chapter 11 which covers Formatting. We are up to page 279 in the printed book.

The session will cover

  • Formatting tips and tricks
  • Conditional Formats – built-in
  • Conditional Formats – changing defaults
  • Conditional Formats – formula – based
  • Printing tips

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

Date: August 30, 2018
Time: 1:00 PM AEST
Event: Book Webinar - Chapter 11 Part 2- Formatting
Topic: Book Chapter 11
Public: Private
Registration: Click here to register.

Chapter 11 – Maintenance

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

Duration: 1.0 hour

The 16th in my series of webinars based on my book. The book is the manual.

We will start Chapter 11 which covers Formatting. We are up to page 255 in the printed book.

The session will cover

  • Formatting tips and tricks
  • Custom Number Formats
  • Merged cells alternative
  • Borders
  • Styles

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

Date: July 27, 2018
Time: 1:00 PM AEST
Event: Book Webinar - Chapter 11 - Formatting
Topic: Book Chapter 11
Public: Private
Registration: Click here to register.

Printing Tip

If your sheet has a lot of colour and you want to print it on a black laser printer, one of the Page Setup options can improve the print.

On the Sheet tab of the Page Setup dialog there is a Black And White option – see image below.

This removes all the colour and prints in black only,

You can access the Page Setup dialog from the Page Layout tab. Click the small arrow on the bottom right of the Page Setup section.

 

Data entry and calculating

In some large models Excel may calculate for a few seconds after you make an entry.

In most cases you don’t have to wait for Excel to finish calculating before you make your next entry.

Type your entries as fast as you like. Excel will finish calculating once you are done.

Thank you Michael Alexander

Recently I learned about the passing of Chip Pearson an Excel legend.

He was so generous with his content and I had thanked him for his contributions years back but it got me thinking about thanking others who had helped me along the way.

The seventh on my Thank You list is Michael Alexander.

In more recent years some of my favorite Excel books have been written or co-written by Michael Alexander.

I like his style of writing plus he knows so much about so many different areas of Excel. He also knows a lot about Access.

His site is here.

I own many of Michael’s books in paperback or kindle.

Thank you for sharing your Excel knowledge in such a practical and understandable way.

My Excel skills have improved from reading your books.

Seth Godin

Seth’s website is a great resource on marketing and business in general.

He has recently updated it to WordPress and it looks impressive.

Lots of videos, podcasts and his over 7,000 blog posts (yes 7,000).

 

Thank you Nick Melchior

Recently I learned about the passing of Chip Pearson, an Excel legend.

He was so generous with his content and I had thanked him for his contributions years back but it got me thinking about thanking others who had helped me along the way.

My sixth Thank You is not Excel related.

This gentleman is in publishing and in October 2012 he approached me to see if I was interested in writing a book on Excel. I may have said “Hell Yeah!”

Nick Melchior worked for Wiley Australia at the time and because of my CPA articles he thought I could write a book.

He asked me to put a book proposal together and he submitted it and supported it. It got through and 18 months later I had a 420 page book in my hands with my name on it as the author. A life-time dream come true.

Unfortunately shortly after the book was approved Wiley wound down its Australian operations and Nick lost his job. I will always be grateful he approached me and had faith that I could write a book.

Thank you Nick – you helped me achieve a life long dream.

 

Chapter 10 – Maintenance

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

Duration: 1.0 hour

The fifteenth in my series of webinars based on my book. The book is the manual.

We will start and hopefully finish Chapter 10 which covers maintenance issues in Reporting models. We are up to page 239 in the printed book.

The session will cover techniques that simplify maintaining files. These include

  • using tables
  • automating dates
  • improving the user interface with checkboxes, option buttons and combo boxes

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

Date: June 27, 2018
Time: 1:00 PM AEST
Event: Book Webinar - Chapter 10 - Maintenance
Topic: Book Chapter 10
Public: Public
Registration: Click here to register.

FREE Webinar – Excel Copy and Paste Techniques

In Excel copying and pasting is one of the most commonly performed tasks. Did you know there are many options that can control and speed up your pasting?

This session will focus on the Paste Special dialog plus a little known pasting feature that is great for dashboards.

The session will cover 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 (great for dashboards)

The session includes lots of keyboard shortcuts.

 

Date: June 14, 2018
Time: 1:00 PM AEST
Event: FREE Webinar - Excel Copy and Paste Techniques [FULL]
Topic: Excel Copy and Paste
Public: Public
Registration: Click here to register.

FREE Webinar – Excel Copy and Paste Techniques [FULL]

In Excel copying and pasting is one of the most commonly performed tasks. Did you know there are many options that can control and speed up your pasting?

This session will focus on the Paste Special dialog plus a little known pasting feature that is great for dashboards.

The session will cover 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 (great for dashboards)

The session includes lots of keyboard shortcuts.

 

Date: June 13, 2018
Time: 1:00 PM AEST
Event: FREE Webinar - Excel Copy and Paste Techniques [FULL]
Topic: Excel Copy and Paste
Public: Public
Registration: Click here to register.

Thank You CPA Australia

My first thank you messages were directed to people who helped me learn Excel.

This, my fifth Thank You, goes out to the organisation that has helped me share what I had learned with others.

Thank You CPA Australia for publishing my first Excel article, way back in May 2002. Thank you for agreeing to do a regular Excel article in late 2002. The Excel Yourself series started out with a Q & A format.

Initially the questions were based on questions I answered during training sessions. Then I started to receive questions from CPA’s from around the world. I still receive questions, but the quantity has dropped off due to Google and the many excellent online Excel forums.

As at May 2018 there have been 170 Excel Yourself articles plus 8 feature articles. Articles now appear online and each has a companion video and many have companion files.

You can see the articles here.

Over the years I have also worked with CPA Australia in creating and presenting training sessions for conferences and running training sessions in regional areas. I have recently done a few podcasts, plus I have written extra posts and done videos for the INTHEBLACK website.

Thank you CPA Australia for providing a platform to help me share my Excel skills with Australian accountants from around the world.

It all started from that first article all those years ago. By the way the first article was about the Ctrl key and its shortcuts – see image below.

Thank you Bill Jelen

Recently I learned about the passing of Chip Pearson an Excel legend.

He was so generous with his content and I had thanked him for his contributions years back but it got me thinking about thanking others who had helped me along the way.

The fourth on my Thank You list is Bill Jelen (aka Mr Excel).

Through the Mr Excel website Bill has directly and indirectly helped more people than probably anyone else in Excel. The site

I have used the site’s forum to solve many problems over the years.

The forum has many Excel experts answering questions and solving problems from people from around the world.

Bill has written many books covering most of Excel’s topics.

I own a few of his books and have read many more. He tells it like it is and is not afraid to say if he disagrees with changes made to Excel.

Thank you Bill for being so generous with the your knowledge and for your huge contribution to the Excel community.

My career is better for having read your books and used your website – thank you.

 

Waterfall charts

2018-05-15

I prefer to call them Bridge charts rather than Waterfall charts, but Waterfall is the common name.

Excel added Waterfalls in Excel 2016.

I think the name Bridge is more descriptive since a bridge takes you from one place to another which is what the chart does with values.

Waterfalls in nature only fall down, whilst a waterfall chart has measures that rise and fall.

This blog post show many examples. Most examples are not done in Excel.

Link to blog post.

 

Thank You Matthew Harris

Recently I learned about the passing of Chip Pearson an Excel legend.

He was so generous with his content and I had thanked him for his contributions years back, but it got me thinking about thanking others who had helped me along the way.

My third thank you goes out to Matthew Harris. Here is a link to his website

Back in the 90’s I taught myself VBA using his book – Teach Yourself Visual Basic For Applications in 21 Days. It has pride of place on my bookshelf.

The book really made a difference to the way I used Excel and opened my eyes to so many possibilities.I found I really enjoyed programming when working in VBA and Excel.

I have learned a lot since, but his book gave me a great grounding in VBA.

Thank you so much Matthew for your book, it has made a huge difference in my life.

Show all comments

Cell comments are useful for instructions and documentation.

If you want to make all the comments on a sheet visible, use Alt v c pressed in sequence, not held down.

Once visible this shortcut also hides all the comments in one go.

This is an old Excel 2003 shortcut that still works.