Free Excel Webinar Recording – What If Techniques

Feedback score 92%

My free Excel webinar for September 2018 covered What If Techniques. Download the materials using the button below and watch the video.

Download Webinar Materials

Content applies to Excel 2010 and later versions. You will need to install the Solver Add-in – instructions in the manual and  video.

  • Goal Seek – simple what-if changes
  • Solver – advanced what-if analysis
  • Scenario Manager – handling different sets of inputs
  • Data Tables – single and double variable sensitivity analysis
  • NEW – Forecast sheet

Inserting a Blank Row Between Entries

Manual technique

Over the years I have had many requests to help people insert blank rows between entries is a list. Apparently it is for an input routine that requires blanks. My normal solution is a macro because it automates the process, but there is a manual technique that is quick and easy.

Easy Financial Year Formula

To get the Australian financial year from a date you usually use an IF function based on the month number.

I recently learned a new hack from Matt Allington of Exceleratorbi.

You can add 184 to the date and then use the YEAR function. See table and formulas below.

The formula in cell B2 is

=YEAR(A2)

The formula in cell C2 is

=YEAR(A2+184)

Both formulas have been copied down.

A simple solution to a frustrating issue. Thanks Matt.

Excel and Outliers

New functions make it easier to find them

Malcolm Gladwell’s book Outliers is a great read – I reviewed it here. Its premise is that some outliers (events that are far outside “normal” expectations) have causes and hence are worthy of investigation. Excel have some functions that can help identify outliers in your data.

Selecting a column

To quickly select a column of data in a formatted table you have a couple of options.

Keyboard

Select a cell in the column and press Ctrl + Space Bar.

This will select the column of data. If you want the heading too, press it again.

You can also select multiple columns before using the shortcut.

Mouse

This technique can take practice if your headings are in row 1.

If the heading starts in row 2 or below it is easier. See image below.

If you point to just above the heading row you will see a downward facing, black arrow. Click this once to select just the data. Click it again to include the heading.

When the heading row is in row 1 you need to do the same but make sure the column letter doesn’t highlight.

The image below is the correct arrow – this will select the column in the table only.

In the image below the arrow shown (because the column letter is highlighted) will select the whole column, not just the data in the table.