Creating Advanced Waterfall graph - Values moving from Positive to negative and vice-versa

Waterfall graph is a floating bar graph used to pictorially depict movement of any parameter or how various parts contribute to a whole parameter. For e.g. how the Headcount or profit has moved over last week, last month etc. OR various components of Net profit and so on. Given below is the example of a sample waterfall graph.

Please note that in the above graph values moves from Positive to negative and vice-versa, which makes process of making this graph little complicated. If your data is not going to cross 0 axis (i.e. not going to move from positive to negative) then please click HERE

 to see steps of making simple waterfall graph.

Steps to make waterfall graph

Step 1: Make the data table.

1. Create the data table for base values and movements in the below mentioned format.
   












2. Since the values moves from positive to negative and negative to positive, we will have to create another table linked to the above table
   


• In the table above, I have used two dummy columns (cells with gray background) which are not used in graph, but are used for calculating other values 
• Before – Running total before the current row value
• Formula – =SUM($B$2:C2)
• After – Running total after the current row value
• Formula – =SUM($B$2:C3)
• Axis Label – Self explanatory
•  Base Value – For Start and end points
• Formula – =IF(B2<>0,B2,0)
• Blank – For a hidden series for adjustment
• Formula – =IF(J2*K2>0,IF(J2*C2>0,J2,K2),0)
•  Positive Bars – Used for depicting bars above 0 (Horizontal axis)
• Formula – =IF(J2>0,IF(K2>J2,C2,IF(K2<0,J2,ABS(C2))),IF(K2>0,K2,0))
• Negative Bars – Used for depicting bars below 0 (Horizontal axis)
• Formula – =IF(J2<0,IF(K2<0,-ABS(C2),J2),IF(K2<0,K2,0))

Step 2: Make the graph

1. Select the second data table (Only upto Negative Bar column) and Click Insert --> Column Chart --> Stacked column (this will insert stack bar graph as shown in the screenshot below)
   


2. Select the legend and press delete to remove legend
3. Select adjustment series and hide it by selecting No shape fill and no shape outline from format menu.
   


4. Apply same Shape colour to Positive and Negative Bars. So that it appears as a single bar while crossing the Horizontal axis.
   


5. Change the Axis label setting to Move the axis label below the negative values. Right click on Axis --> Format Axis --> Change Axis Label property to “Low”
   


6. Make other formatting changes like Border, background colour, font, Gridlines etc.

Finally it should look similar to the screenshot below.

You can download the Excel file for above example from HERE.

Creating Trend Graph / bar graph with scroll bar in Excel - (Adding Scroll bar in Excel Graph)

Purpose:
To add scroll bar in the graph to scroll through the available values. If you fit too many data points in any trend or bar graph then it looks very cluttered and shabby. Look at the sales trend graph below...

In the above charts, it's very difficult to make out anything as it contains lot of information with too many data points and hence they are overlapping and very difficult understand.

Question is then what should we do to represent such large data in limited available space?

How about adding scroll bar for to the graph and display only few number of data points so that the chart is understandable and more readable? One can scroll through to see more data points. See the example below...

How to insert scroll bar in the graph:

1. Make your data ready for all the values of the X axis. For our example, we need to make data for all the days starting from 1-Jan to 31-Mar.
2. Copy few data points to a new location from the base data and make the chart only with few data points.(click here to see steps for making graph)



3. Insert scroll bar object from developer ribbon. (if developer ribbon is not available refer enable developer ribbon in excel 2010 or enable developer ribbon in excel 2007)



4. Set the below properties of the scroll bar (enable Designed Mode to select the Scroll bar)
• LinkedCell: The cell in which Excel stores the value of the scroll bar. In our example we will give M1 for this property.
• Min - This the value of the scroll bar when it's at lowest point i.e. at the left most point. For our example we will give this value as 0.
• Max - This is the value of the scroll bar when it's at highest point. i.e. at the right most point. In our example we will give this value as 77. (the difference between the highest and lowest point of X axis of the all data minus number of values visible on the graph i.e. difference between 31-Mar and 1-Jan minus 12)
• Large Change & Small change: It is the increment value for every point change in the scroll bar. In our example, we will increment value by one since on each left or right arrow click of scroll bar, we want the period of the graph to be shifted by one day.
  



5. Based on the scroll bar value, insert formula to get X axis values of the graph. In the given example I have entered the starting date in one cell and added the scroll bar value to that to get the starting point of the X axis. Remaining values of the X axis are derived by adding 1 to the previous value.
   



6. Use lookup formula to get the data for selected period from the entire data. Exit the Design Mode and try changing scroll bar.

Download Sample file

Further enhancing dynamic chart:
I have explained simple method of preparing dynamic chart using scroll bar in this article. In addition, you can further enhance and add more functionality like providing drop down list also. Refer to how to create dynamic chart for adding this feature.
Note: for providing scroll bar as well as drop down list in the same graph, you will have to modify your base data and the lookup formula to derive value for the graph.

Making Pyramid Graph for Headcount Distribution Representation

Headcount Pyramid graphs are used to graphically represent the grade/designation wise distribution of the total headcount for entire organization or for specific department or for specific project. It is like a pie chart which shows the distribution of various elements of the total. However, for parameters like headcount, Pyramid graph is more effective as compared to a pie chart (lower level headcount is expected to be more than the upper level headcount, thus forming a pyramid). Refer to the screenshot below for a typical headcount pyramid graph…

Steps to make a Headcount Pyramid Graph

• Make a table as shown below for grade wise headcount.

• Add a column to the data table and apply formula in this column to insert the negative values of the headcount. . (in the given example, the formula in cell C3 will be “=-B3”). After adding this column, the data table should look like the table given below

• Select the table and go to Insert --> Bars (under charts section) --> Cluster Bar Chart. It should insert graph as shown in the screen below

• Select and delete gridlines of the graph
• Now select Vertical category axis i.e. the grade appearing on the graph. And Click on format selection from Chart Tools --> Layout. Change the Axis labels from “Next to Axis” to “Low”. After doing this, grades label will get aligned to the left of the graph window.

• Select any of the data series and click on format selection from Chart Tools --> Layout. Change the values of “Series overlap” to 100% and “Gap Width” to 0%

• Remove Horizontal value axis and Legend (select axis press delete. Similarly select Legend and press delete)
• Change the color of Bars, so that both the bars looks identical.
• Again select Vertical category axis i.e. the grade appearing on the graph. And Click on format selection from Chart Tools --> Layout.  Go to “Line Color” tab and select “No Line” to remove the line appearing at the center of the bars

• Select “Headcount” data series and add data labels by selecting Chart tools --> Data Labels --> Outside end
• Finally do some formatting changes as required. Like Font size, border color, shadow effects etc.

That’s it.  The Headcount Pyramid is ready and it should look similar to the screen below.

Steps to make graph in excel 2007/2010

Below mentioned are the steps to make simple graph in Excel 2007. The example displays how to make bar graph.

1. Make the base data table as shown in the screenshot belowUse Chart tools à Layout à Data Labels option to add data labels in the graph

How to make dynamic charts

In excel, one can create a GUI, where users can select values from the dropdown lists. Based on this selection the graphs below will be updated.

Such graphs & reports are very useful while making big dashboards in which the users requires different views of the report. For e.g. City wise report or Month wise report.

You can always make multiple graphs/reports for each values (for e.g. separate graphs for each location if you are making location wise report). However, doing this will take lot of time and efforts, especially when the list is too large.

In the example below, user can select values in cell “C1” and the graph below will get updated based on the selected value.

Steps to create dynamic chart

How to make waterfall graph in excel

Waterfall graph is a floating bar graph used to pictorially depict movement of any parameter or how various parts contribute to a whole parameter. For e.g. how the Headcount or profit have moved over last week, last month etc. OR various components of Net profit and so on. In the screenshot below, example 1 depicts the headcount movement w.r.t. last month and example 2 shows the various components of Net profit.

Example 1: (Headcount movement w.r.t. last month)

Example 2: Various component of P&L

Post your questions on Excel

Hello,

If you have any doubts in excel, please post your questions on Help Forum. I will try and answer your queries.