# Monthly Archives: April 2015

## Excel – Creating a Basic Waterfall Chart

Waterfall charts are used to show how you got from one figure, let’s say at the start of the year, to how you ended up at the figure at the end of the year, but instead of showing a total value for each period, it will show you the change in value from one period to another.

Let’s take a simple example…

Standard column chart

Above we can see the cash amount left over at the end of each month. If we chart this with a basic column chart it shows those values as solid columns. We can see that there is a change from month to month but we can’t see by how much unless we work it out in our heads.

The waterfall chart (sometimes called a flying bricks, bridge or Mario chart) will display the change in value whether positive or negative as a floating bar. This type of chart is used in a number of ways but examples might include showing cash flow over time, or changes to inventory or staff levels.

Instead of a standard column chart we want to create this;

Waterfall chart

There are add-ins you can buy to create a variety of waterfall charts and I will mention this again later. If all you want though is a basic waterfall chart then it can be achieved relatively simply using a handful of formulas and a bit of formatting.

First we need to modify our data table.

Insert four new columns. You can call them anything you like. In this example I have named them;

• Invisible
• Increase
• Decrease
• Variance

We also need to add a couple of new rows to the table. We need an opening balance (i.e. how much had in the bank at the end of December 2014) and a closing balance at the end of the year.

This will now give us the following layout;

Modified table required to create the waterfall data

Next, calculate the change from one period to another.

Simple bit of maths, one cell minus another

=F3 – F2

…and copy down.

Variance calculation

Now we need to create what will become the floating bars in our waterfall chart. We need one calculation to identify positive values and one for negative values.

In D3 I need the following formula;

=IF(E3>0,E3,0)

This tests the value in the VARIANCE column. As we are looking for positive variances we test for values greater than 0. If the value is negative display a 0 (zero).

Increases calculated

Now for the negative values. In cell E3 enter the following formula;

=IF(E3<=0,ABS(E3),0)

This time show which values are negative, but as we need these values to be displayed as floating bars, the values need to be converted to positive ones, hence the use of the ABS (absolute) function.

Decreases calculated

As a quick check, in each pair of cells representing an INCREASE and a DECREASE, you should find one cell with the value 0 and the other with a positive value in it.

The final calculation we have to do now is work out the INVISIBLE value. We need this to create a value to calculate the start value of our DECREASE/INCREASE bars.. The close up below of the bars in the final waterfall chart shows what we are trying to calculate.

Calculating where bars have to start from

To work these out we need to add the INVISBLE value to the INCREASE minus the DECREASE. Copy across the OPEN and CLOSE values into your INCREASE column and enter the formula as shown below.

Calculating the height of the supporting invisible series

Looking at the numbers, the sum of the INVISBLE and the DECREASE values in one line should add up to the INVISBLE value in the line above.

Quick check to ensure values are correct

Copy your formula down and we now have a full set of data from which to build the chart.

Select cells A1 to the bottom of your INCREASE column.

Selecting your data for the chart

Go to the INSERT tab and select the 2-D STACKED COLUMN chart.

All we need now is a bit of formatting to get the final effect.

Select the INVISIBLE series and format the bars to have NO FILL. Then remove INVISBLE from the LEGEND as it is no longer required.

If you want, colour code the INCREASE/DECREASE bars. In this example I will make INCREASE bars green and DECREASE bars red.

To differentiate your OPEN and CLOSE balances from the variance bars, colour these differently. You may have to click twice on the end bars to select them separately from the rest of the series. Pick any colour you like.

As a finishing touch, you can add the variance values to the floating bars using Rob Bovey’s free add-in – X Y CHART LABELER from www.appspro.com

Completed waterfall chart

And there we have our final chart.

If you don’t fancy doing all those calculations then you might want to consider buying Jon Peltier’s PELTIER TECH CHART UTILITY add-in. The full advanced edition sells currently (April 2015) for \$99 (http://peltiertech.com/Utility20/). At that price it may not be for everyone, but if you have to build complex waterfall charts (as well as some other types included in the add-in) on a regular basis then it’s worth investing in.

## MS Project – For when you have nothing better to do…no really!

Ok, this is a totally frivolous blog. What you are about to learn is of no use whatsoever other than to pass the time, or show your colleagues a totally random and surprisingly entertaining (in a geeky way) bit of misuse of MS Project.

Create two tasks of equal duration.

Click into the FINISH cell for TASK1 and then click on COPY.

Then click into the START cell of TASK2.

Click on the arrow under PASTE and select PASTE SPECIAL.

Paste special option

Then select PASTE LINK and click on OK.

Do the same but this time starting in TASK2, pasting the FINISH date into the START cell of TASK1, making sure you use PASTE LINK again.

See the video of the whole set up and see those tasks move!

It’s amazing what geeks find entertaining!

Note that by doing this the tasks will walk to the end of time in your project plan. To stop it all, just delete the tasks.

Enjoy!

## Excel – Using the Data Form

If you find you are working a lot with long lists of data, you probably find after some time that distinguishing one line from another becomes increasingly difficult…a form of snow blindness if you like. You can format the list as a table and shows lines in alternating colours which might help but wouldn’t it be nice sometimes to be able to see only one line at a time.

Data list formatted as a table with banded rows

Well…you can, using a FORM. You can go to the length of creating a form in VBA, and adding all kinds of buttons and clever functionality, but there is actually a built in FORM creator which will build a form when required on any data as long as it is in a list format i.e. column headings only, no row headings.

In 2003 or earlier versions of Excel, the FORM was available from the standard menu. Since 2007, it has been relegated to just another button in the CUSTOMIZE THE QUICK ACCESS TOOLBAR (2007+) or CUSTOMIZE THE RIBBON (2010+) options.

You will find the FORM button under COMMANDS NOT IN THE RIBBON or ALL COMMANDS.

Finding the FORM button to add to the QAT or RIBBON

Add this to either the QAT or RIBBON and you are ready to go!

So now you have access to the FORM, what does it do and how does it work?

Click anywhere in your data table and click the FORM button.

The FORM button added to the QAT

And without any further intervention by you, or working your way through multiple steps of a wizard, Excel will create a user FORM for you.

Viewing a single record using the data form

Using the column headings, there is a separate line for each heading and a text box displaying the information held in that column for a single record.

In the top right hand corner you will see the total number of records in your list. Using the scroll bar between the data and the buttons you can move from one row to another using the arrows at the top and bottom of the scroll bar or click and drag the scroll bar itself to move quickly through the records.

Not only does the FORM allow you view records, but you can also edit the information directly in the FORM which will update the spreadsheet. There is extra functionality available using the buttons on the right hand side.

Buttons on the data form

New: To add new records to your list click here and start typing the information directly into the text box next to the heading. Sometimes, you will see some values/data in the form that does not appear in a white text box. This will be a calculated field or perhaps a look up function based on something you enter in another box. Because these cells in the worksheet are reliant on a formula you cannot enter information directly here.

A form showing both data entry boxes and un-editable formulas

One major downside to using the FORM is that if your data table has drop down lists to select values from, these are not transferred to the FORM, so you need to type values in manually.

One advantage of using a FORM to add new records, is that if your data is stored as a NAMED RANGE, any new records you add are automatically added to the NAMED RANGE so you don’t have to manually adjust the REFERS TO bit in the NAME MANAGER. If you are unsure about NAMED RANGES then refer to my blog http://wp.me/p2EAVc-99 to find out more. There is however, one caveat to this. A FORM will only add to an existing NAMED RANGE if it is called “database”. Call it anything else and the NAMED RANGE is fixed and it will not automatically expand to include new records.

Delete: Does exactly what you would imagine…it deletes the record from your table/list – permanently. Note that I said it deletes the record, not just the highlighted piece of information in the FORM.

Restore: If you make a change to anything in the FORM other than delete a record, this button becomes active and allows you restore the data back to its original value.

Criteria: I’ll jump to CRITERIA next because the FIND PREV and FIND NEXT buttons are generally used in conjunction with this button. When you click on CRITERIA it clears the FORM. This now becomes a search FORM. Type search criteria in one or more of the text boxes. You can enter whole values or you can use wildcards. Using FIND PREV and FIND NEXT then allows you to scroll through records that match your search criteria. When you click on CRITERIA you will see a CLEAR button that will clear the form of any criteria you have entered, and a FORM button that will return you the standard form.

So what wildcards can I use?

*(star/asterisk) denotes any number of characters.

H* will search for any value that begins with H followed by any number of characters.

*h will search for any value that ends in h.

? (question mark) denotes any single character

H?p will search for Hip, Hop, Hup, H3p etc.

You can then combine these together or use them in conjunction with < or >.

>h* searches for any names (for example) that begin with the letter h, I, j, k etc. and beyond.