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.

Generate Random Numbers in Excel

Do you feel need for inserting random/dummy numbers in excel cells?

Well, lot of times while making new reports or making new model in Excel I don't have actual data to work with. Sometimes back-end team is still working to get the data, sometimes it is dependent on someone else or sometimes you just have to create blank template and send it to business users who will enter the details and model should give the results based on the entered value by the end user etc.

Being the Excel users you all will know how difficult it is to check the formula without any values.

One can insert numbers manually in the cells. However, it will be very tedious task especially when you have lots of cells to fill.

Better alternative is to use formula. You can use below mentioned formulas to get the random numbers...

1. RAND
Syntex: =RAND()

It gives any random numbers from 0 to 1. One can use it with combination of ROUND function to get the rounded number up to specified decimals. Given below is the formula for getting random number from 0 to 1 having 2 decimals...
=ROUND(RAND(),2)

2. RANDBETWEEN
This function is similar to the RAND function, only difference is that you get the option of adding upper and lower limit within which the random numbers are returned by Excel.

Syntex: =RANDBETWEEN(lower limit, upper limit)

Example: =RANDBETWEEN(1000,10000) will return random number between 1000 to 10000

Note: Whenever there is change in any value of any cell, Excel recalculates values in all the cells having formula. This causes changes in the values having RAND or RANDBETWEEN formulas. If you don't want these values to get changed, remove the formula by pasting values immediately after getting random numbers.

Summing cells having Years and Months stored as years.month format in Excel

Yesterday, one of my friend asked me a question. It was quite simple, he had a data stored in specific way, and he wanted to do additions of few columns….

Question:

He had an Excel file, where data is stored in Years.Month format (i.e. 5 years 8 months is stored as 5.08) in two columns. He wanted to add these columns. Returned value should be in Years.Month format only. For e.g. 5.08 + 8.05 (i.e 5 Years and 8 Months + 8 years and 5 Months) the answer should be 14.01 (i.e. 14 Years and 1 Months). A simple addition formula in Excel will return 13.13 as Excel considers these values as normal numeric values.  Hence he wanted some way to do these additions.

I gave him the solution by writing below mentioned formula. Above mentioned situation may be common for many of us hence thought of sharing it here for others.

Solution:

We can achieve desired output by using below mentioned formula.  Assuming that 2 data items are stored in cell A2 and Cell B2, formula will be…

=IF(IF(MOD(A2,1)=0,0,VALUE(MID(A2&"0",FIND(".",A2)+1,2))) + IF(MOD(B2,1)=0,0,VALUE(MID(B2&"0",FIND(".",B2)+1,2)))>=12,

VALUE(1+INT(A2)+INT(B2) & "." & RIGHT(0&IF(MOD(A2,1)=0,0,VALUE(MID(A2&"0",FIND(".",A2)+1,2))) + IF(MOD(B2,1)=0,0,VALUE(MID(B2&"0",FIND(".",B2)+1,2))) - 12,2)),

VALUE(INT(A2)+INT(B2) & "." & RIGHT("0"&IF(MOD(A2,1)=0,0,VALUE(MID(A2&"0",FIND(".",A2)+1,2))) + IF(MOD(B2,1)=0,0,VALUE(MID(B2&"0",FIND(".",B2)+1,2))),2)))

Explanation:

Logic: We are adding Numeric portion (i.e. the years) and the Decimals portion (i.e. the months) separately. If summation of decimal portion is greater than 12 then 1 is added into numeric summation (i.e. 1 is added into the summation of years as 12 months is equivalent to 1 year) and at the same time decimal summation (i.e. summation of month) is reduced by 12.

Finally after addition, both numeric and decimal portion are concatenated with a dot (decimal sign) and shown as numerical value using the VALUE function.

IF(IF(MOD(A2,1)=0,0,VALUE(MID(A2&"0",FIND(".",A2)+1,2))): It returns decimal portion from the given value. E.g. for 5.05 it will return 5, for 6.10 it will return 10 and so on. If there is no decimal value then it returns 0. MOD is used to check if the given value has any decimal portion or not. If there is any decimal portion, MID function is used to take out only the decimal portion.

INT(A2): returns the Numeric portion of the given value. For e.g. for 5.05 it will return 5, for 6.10 it will return 6 and so on.

VALUE: function is used to convert text into values. All the operations are done by text functions and the final outcome is also a text. To convert this text into numeric value, Value function is used.

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.

Vlookup Formula in Excel

Vlookup is one the most important and most widely used function amongst the available lookup functions in Excel. This is the first complicated formula that I have learnt after I started working.

Purpose:
It is used for searching corresponding value of a given text from a database . For e.g. Searching name, address, telephone number from  large customer database for few customer id. There are other alternative lookup formulas like match, offset, Index etc are available. But of all, vlookup is simplest and most widely used function.

There can be two main purposes for which we can use this function. 

1. To append columns/fields in your existing data for additional information. For e.g. You have list of customers with id, names etc and you want to contact them using phone number which is available in another excel data file. You can add column for phone number in your data and use Vlookup formula to get the details for entire list
   
   
   
   
   
2. For searching details like name, address etc for given text. E.g. Employee id, customer id etc. 
   
   
   
   

How to do Vlookup:

Lets take example of case 1 above. In this case, Customer phone number is taken using using vlookup function based on the "cust id" field from "Cust Basedata" worksheet. Lets call worksheet/table where you want the data as Destination (the worksheet/table where you need to insert vlookup formula) and the worksheet/table from where this information is available ("Cust Basedata" worksheet) as Source.

Vlookup function requires 4 parameters (separated by comma sign)...Each parameter is explained below. After explanation of each parameter, formula for first row of our example (i.e. cell C2 of destination) for respective parameter is mentioned in square  bracket in blue font. 

1. Lookup_value: This the text for which you want to search corresponding value from another Excel database. In our example Customer ID in destination table is the Lookup_value. [A2]
2. Table_array (range): It is the range in which your base data is present. In the example, range A1 to C31 of source is the table_array for vlookup. Note that your table_array should always start from the column in which the lookup value is present. i.e. the first column or the left most column of the table_array should contain the lookup value. In our example Column A is the left most column of the table_array and it contains the customer ids which we are searching for. ['Cust Base Data'!$A$1:$C$31]
3. Col_index_num: This is the column number in the table_array from which the matching value is returned; starting from the left most column in the table_array. So, Col_index number 1 will return value from the 1st column, col_index_number 2 will return value from 2nd column and so on. In our example, column number for phone number field is 3, which we are trying to get in source. [3]
4. Range_lookup (Lookup type): It's an Optional parameter, a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. 
• True or 1 (default if not specified): For approximate match. If exact match is not found, then it will return value for next largest value which is lower then the search value. Personally  I feel, this option has very limited use as most of the time you would want to do vlookup for exact match only. 
• False or 0: Use this option to for searching exact match. vlookup will return #N/A error if it doesn't find the search value in table array. In our example we will use this option, since we are looking for phone number of customer id matching exactly. [False or 0]

Hence the formula for cell C2 will be =VLOOKUP(A2,'Cust Base Data'!$A$1:$C$31,3,FALSE). 
After applying drag/copy the same formula in the remaining list. i.e. cells C3 to C11 in destination table. 

Important points to remember while using vlookup:

• Vlookup function will return #N/A error if the Range_lookup (4th parameter) is False and if it doesn't find the exact match of search value.
• It will return #Ref! error when column_index (3rd parameter) supplied is greater than the number of columns in table_array. 
• It will return #Value! error if column_index is less than 1.
• It will return 0 if it finds the match and the corresponding cell for that searched value is blank. 
• If the source has more than one values then vlookup function returns first value from the top most row. Hence it becomes very important to sort your source data when there are multiple records for the same value to ensure that the relevant rows comes above the other rows. For e.g. in our example, if there are more than one phone numbers for few customers with additional field of last updated date, then data can be sorted based on descending order of last updated date to get the latest updated phone numbers. 
• Ensure that range you supply in Table_array parameter is absolute cell reference. (done by using $ sign before the column and row number)
• Vlookup can be done only from left to right and not vice-varsa

Grouping data of a text field in a pivot table using group option

Purpose:
How many time in our daily professional life we come to a situation where we want to further summarize the data/reports given by a pivot table? For e.g. Grouping all south locations in sales pivot, grouping designations in headcount summary etc

Steps:
One easy method of doing this could be to add column in your data and update it with required details for grouping. However, it is very tedious to add columns every time.  Also it will increase number of columns in your data and make your database bigger.
You can use group option in pivot table to achieve the same results without adding any extra columns in your data. Just follow few steps mentioned below...

1. Select the items in the pivot
2. Right click inside pivot table
3. Click group
4. This will group selected data in pivot and give default name like Group 1, group 2 etc.
5. Change the group name

Steps

Simple...isn't it?

How to remove grouping:
Once you apply grouping on any field, excel pivot automatically adds one more field with the similar name as the filed in which the grouping is applied.
You can remove this grouping by simply dragging this field out of pivot table. This will remove this groping from the pivot table, however it will remain in the pivot field list. You can use the same in future if required.

To delete this field from the field permanently. Right click on the field which is grouped and click group. It will remove the additional grouped field.

Additional note:
Group option gives lot of additional features to group numerical or date fields e.g. grouping date fields into months and years etc. Simply right click on date or numeric field and you can explore more options.

Enable Developer tab in Excel 2010

Developer tab is used for accessing macro/VBA related options and to add activeX controls like combo box, Command Buttons, Check Boxes etc into your file.

Followings are the steps to enable Developer ribbon in Excel 2010

Extract live data from system in Excel by connecting your Excel file to the System database - Importing data from SQL in Excel

Suppose your organization records all the transactions in an SQL server database (or any other database) using an ERP  system and you need data from this system on a regular basis for your routing activities.

For e.g. you work in HR department needing information about employees like current status, department, Designation, date of joining etc very frequently and this data is stored in SQL database.

You can use Import External data feature of Excel to connect your Excel file to this SQL server database and get updated data whenever you need it just by refreshing this data with few clicks.

Followings are the steps to import data from SQL server to Excel file.

Note: Before you start, please ensure that you have read access to this database and you have Server Name, Login name & Password

There are broadly two steps involved to achieve this…

1.       Create Data Source Name (DSN)

2.       Importing data into Excel File using this DSN

1. Creating Data Source Name (DSN)

• Go to Control Panel --> Administrative Tools --> Data Sources (ODBC)
• Click Add, Select SQL Server & Click Finish
• Enter any Name for DSN and select server name and click next
• Select the authentication mode and click next (check with the database administration team for the type of access provided to you)
• Select “With Windows NT authentication using the Network login ID” if your windows login name has been granted the access
• Select “With SQL Server Authentication using a login ID and password entered by the user” if you have the login name and password.
• Select default database and click next
• Click Finish

2. Importing data into Excel file using the DSN which we have just created

• Open new Excel file and select Data --> From Other Sources (under “Get External Data”) --> From Microsoft Query

Select the DSN which we have just created (Note: You can use this same DSN for making n number of files)

Enter Password if it asks for and click OK

In next screen, Expand the Table name from which the data is required. Select the Columns which are required. Click Next.

 

 

• If you want to filter any data then apply filters on this screen. Select the field on which filter need to be applies and select the filter type. Like Date of Joining after 1-1-2011 or Designation equal to “Grade 1” etc.

• Select the sorting order if required. You can specify 3 sorting fields and select the type of sorting i.e. Ascending or Descending,  click Next

 

• Select “Return data to Excel” to finish the data. Use edit “Edit data using Microsoft Query” option for advanced query editing. (Basic MS SQL knowledge is required for advanced Query editing)
• Click Finish

• Select the cell where you need the data.

That’s it, you should get the data in the excel sheet. Now use Right click à Refresh option every time to get refreshed data….

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.

How to clear the “Recent Documents” list

In Excel 2007, by pressing the “Office Button”, the “File” menu is displayed. On the right side of this menu, it displays the recently opened document list.

This is a great feature. However, it may not be that useful when you are working on private files on a shared computer and you don’t want others to see the files which you have opened/edited recently.

The number of “Recent Documents” to be displayed can be changed from Advanced Option (set it to “0” for disabling this option). However, there is no option in Excel to delete this list.

To delete this list, one has to delete the registry entries of these files using Registry Editor. 

Custom formula/function using VBA

Excel has lots of built-in functions which covers the requirements of almost all the standard calculations. However, these built-in functions are not sufficient. Many a times we come to a situation where we want to do some calculations which are not offered by excel built-in function.


Here I will show you how to create a custom function using VBA.