If you find any post useful then, please do share with others. Thanks!

Popular Posts

Contact

Email me

Need help and that also free? I like to learn this way, in case of any question or for a small task, please feel free to email me with details and example data, if required.

Tuesday, August 25, 2015

Gantt Chart in MS Excel 2010

Gantt chart
As per Wikipedia, the Gantt chart is a type of bar chart, adapted by Karol Adamiecki in 1896, and independently by Henry Gantt in the 1910s, that illustrates a project schedule. Gantt charts illustrate the start and finish dates of the terminal elements and summary elements of a project.
We can build this chart in Microsoft Excel also. Please see the step by step illustration.
1-      First we should have a list of data i.e. task table to build the chart.

Task Start Date Duration End Date
Brain Storming 5/2 4 5/6
Listing Ideas 5/6 4 5/9
Selecting Idea 5/9 3 5/11
Estimating 5/11 2 5/12
Executing 5/11 2 5/13
Managing 5/12 2 5/14
Reporting 5/13 2 5/12
Meeting 5/13 2 5/12

2-      Make a Bar chart
From the top menu bar, select Insert then Bar and the 2D Stacked Bar. This will inert a blank chart.


3-       Have your mouse in blank Excel chart and click there and then left click -> Select Data. The source window will appear.
Click on legend entries (click add). This will open the series window.
In the series name box, select the cell reference of Start date column header. Like, if our data is in Column A to D, this would be B1.
In the series values box, select the data range of start date column i.e. b2:b9. Click ok and start dates data is in the chart
In same way add the durations.



Now, we will change the dates on the left side to list of tasks.
For this under Horizontal (Category) Axis labels, click on edit.
With mouse highlight the names of tasks and not include the name of column itself i.e A2:A9. Click ok.



Now chart looks like below.



Now Format the Gantt chart

Our task names are in reverse order to correct this, select task names and right click then
Format Axis. And then in Axis options, check the box of categories in reverse order and close.





For more space in chart, select the start date and duration legend/label with mouse and delete them.
Now hide the blue portions of the chart.
Click on any blue bar, this will select all, right click and choose Format Data series.

Ø  Click on Fill then select no fill
Ø  Now click on Border color and select No Line.
We are almost done. We just need to remove the empty extra area from the start.
Go to the first start date cell in the list, in our case it is A2. Now right click and select Format cell. In the Category section, select General. And note the number appearing, in our case it is 42126. Cancel as we are not changing anything, just need this number to use somewhere else.


In the Gantt chart select the dates appearing at top. Click there to select all and then right click and click on Format Axis.

In the Axis options change the minimum bound to the number, we noted.
Change the major unit to 2 and then press Close.

To remove most of the spaces and make the chart look nicer. Click on the top first bar and right click, select Format Data series.
Change the Gap width to 10%

We are finished, our Gantt chart look like this.

Use Mid, Find & Len function to extract substring

Use Mid, Find & Len function to extract substring

Mid function requires three arguments, first the text (could be a cell reference) to look in, the start point and number of characters to return.

What to do when the start position and number of characters to return (length) is unknown?

For example we have a list of email addresses and we want to extract the domain which is after @.

Here comes the use of Find function along Mid function. The Find function, have three arguments. First one is text to find, second is within text (could be a cell reference) and third the starting point of search is optional.

Suppose in cell A2 we have an email address amiqullah@gmail.com . With Find function, we can know the position of @. So the syntax would =FIND("@",A2,1) and the result is 10. So the domain name in this mail address is starting from 11 onward.

Now let us use the Mid function to extract the domain name. Example is given below.

v  The email address is in cell A2. So first specify the text we are looking in =Mid(A2,
v  After that we need to tell the starting point for that use Find function and add 1 so if the Position of @ is 10, we want our search start from 11. FIND("@",A2,1)+1
v  Now the third argument is to specify the length of text that we want to extract. For that first pull the length of our email address len(a2) and then exclude the length of characters that are till @. For that use the same Find function. So complete syntax is LEN(A2)-FIND("@",A2,1).
v  And our complete formula is MID(A2,FIND("@",A2,1)+1,LEN(A2)-FIND("@",A2,1))

In case of any confusion, please feel free to contact me.

A
B
Formula in Cell B
gmail.com
MID(A2,FIND("@",A2,1)+1,LEN(A2)-FIND("@",A2,1))
shalimar.com
MID(A3,FIND("@",A3,1)+1,LEN(A3)-FIND("@",A3,1))
malikgulfaraz.com
MID(A4,FIND("@",A4,1)+1,LEN(A4)-FIND("@",A4,1))
quresh.com
MID(A5,FIND("@",A5,1)+1,LEN(A5)-FIND("@",A5,1))



Monday, August 24, 2015

Use of Excel SIGN Function

SIGN Function

The Excel sign function checks the sign of a number and gives result of 1 if the number is positive, 0 if number is zero and -1 if the number is negative.

Here we have a use of SIGN function. For example we have cost data of this year and last year and we are looking at % variance.

Please see in the below table when value is changing from -18 to -10 the normal calculation give us % change as -44%. Even though there is improvement in year over year number but the % is in negative. So we can use the SIGN function here to return the correct result each time.

A
B
C
D


Cost YE 2014
Cost YE 2015
Variance calculated with Sign function
Variance calculated by normal standard
Formula in Cell C
Formula in Cell D
-18
-10
44%
-44%
(B3-A3)/A3*SIGN(A3)
(B3-A3)/A3
10
12
20%
20%
(B4-A4)/A4*SIGN(A4)
(B4-A4)/A4
13
11
-15%
-15%
(B5-A5)/A5*SIGN(A5)
(B5-A5)/A5
-11
-10
9%
-9%
(B6-A6)/A6*SIGN(A6)
(B6-A6)/A6


Wednesday, August 19, 2015

Data Validation – Dynamic (Excel 2010) – From Table

Data Validation – Dynamic (Excel 2010) – From Table
Data validation is used so users enter the expected data only, available in a drop down. But with static data validation, if we want to add more values in our list, we will first have to update the source list and then update the source range in Data Validation.  
But there is a way to handle this issue. For example, in column A we have a few product’s listed which we want in another cell to appear in drop down. And as per our need when we add/modify another product in the list (Column A), it start appearing in the drop down.
1- For this, first create a table of the list, for this select the data A1:A9 and press Ctrl T.
2- The default table name is Table1, if you want to change then have your cursor somewhere in the table and go to design tab, under table name the name is appearing and can be changed.
3- Select all record of the table excluding the header (A2:A9) then go to Formulas tab > Define Name and give a name to highlighted range i.e. product
4- In the Refers to tab refer the table like in our case =Table1
5- Select Cell C1 and go to Data > Data Validation. In the Data Validation dialog box, settings tab, choose list under Allow: and in the source box write equal sign and the named range. Like in our example it would be =Product. Press Ok.
6- The drop down list is available in cell C1
7- Now important thing is that we can add a new value to our list, simple go to cell A10 and type another product and press ok or move out of cell and check in C1 cell, the new product is in the list.
Please refer to below snapshots for better idea.





Thursday, August 13, 2015

Excel Fill Series

Excel Fill Series

For example, we want to create a list of dates from 01/01/2015 to 12/31/2015. We can type the date 01/01/2015 in cell A1 and then drag it down until we reach 12/31/2015. It will take some time as it’s difficult to stop the mouse at exactly the right cell.
So an option is to use the fill series option. The series dialog box has many options and some of them get automatically selected as the selected cell in our case has a date. If the value in the first cell is a number then we’ll see the ‘Date unit’ options are greyed out:
As we want to fill dates down column A, so we need columns selected.
--Type as Excel detected is Date.
--It has also assumed that we want to increase series by days, as opposed to weekdays, months or years.
--And the step value or increment value is one day at a time.
--So I need to enter the stop value which is last date of my series which in our case is 12/31/2015.



Click ok and you will have a list of dates from January 1 2015 to December 31, 2015 in a few clicks.
Note: (I learned it from http://www.myonlinetraininghub.com/)