Data Copy from Web Page

Are you sick of copying the same data again and again from web page and hate it like anything? 

Do you copy your equity share report from web page in every 1 hour and still don’t get all figures in one go?

Following information might help you… ...

Let’s see how we can make it simple -
-Open internet explorer and surf your equity page
-Copy the web address and open excel sheet
-Go to Data tab and Click on From Web button
-Paste the copied address in the Address Comb box and Click on Go
-Wait till the time box shows you the web Page inside the Web query box
-Then click on Import Button and your half work is done

Now you don’t need to visit the web page every time, you can open the same excel file and get your data readily available in excel.

Please Note :

While opening the excel file, Microsoft Excel will throw a Security Warning saying that data connection have been disabled, you need to click on Options and then Enable this content every time.
Now you can refresh your data in Data Tab.

 

Sort from Left to Right

This Week’s Special!

Sorting data from Left to Right is very easy

1) Select your Data

2) Go to Data TAB and click on Sort button

3) Then click on the Options...

4) click on "Sort left to right" Option

5) Then again it will go back to the Sort window where you need to select Row 1 for Field Sort

You are Done !!!

Please write for any specific information, I'd love to share if i could.

You can also write me on excelchamps@gmail.com

LEN Function - Microsoft Excel 2007

Do you want to know how this function works?

Let's have your 5 min on this....

TRANSPOSE Function - Microsoft Excel 2007

TRANSPOSE Function -

One more amazing function in Microsoft Excel 2007

To enjoy this kind of posts please follow this blog.

Remove Dupliate - Microsoft Excel 2007

It's Long time I haven't written any blog !

To start with let's talk about Microsoft Excel 2007 which has amazing features in it.

One of the wonderful features is “Remove Duplicate”

REMOVE DUPLICATE in Microsoft 2007

The best part of this feature is that you don’t have to sort the data before you go to remove the duplicate records.

Just follow some simple steps & your work is done !

Select the data & go to the "DATA" TAB

After click, Dialog Box (Remove Duplicate) will appear

Click on OK, Duplicate records will be removed

Amazing feature don't you think so !!!

Gauge chart

Gauge Chart in Excel :-

Gauge chart in Excel also known as Speedometer chart.

This chart is basically a combination of 2 charts, 1^st Doughnut chart & 2^nd Pie chart

First we will make Doughnut chart which is a base to place pie chart on it -

To make doughnut chart we requires some measures which will show the strength of area like RED area, Yellow area & the safest area which is green, we will use below mentioned table to make this chart

Performance	Measure	Color
Bad	40%	Red
Average	20%	Yellow
Good	40%	Green
Base	100%	White

We require half (100% out of 200%) of the chart to be hidden so will color this area white

To create Doughnut chart follow mentioned instructions –

·         Select Doughnut chart from Insert chart menu under other charts

·         Remove the legends & heading

·         Right click on any of the slice of the chart area and select Format Data series

·         Rotate chart by 270 degree under series options

·         Right click outside the circle and select No fill under Fill option

·         Also click on No line under border color

·         And change the slice color as per table mentioned above

Now we will make another part of Gauge chart which is Pie chart –

Pie chart will capture your actual performance %age, by changing this %age your chart will change automatically

Performance	Measure
Performance	50%

Here we are assuming 50% is your current performance which can be change manually as per the actual performance of the company

Now the below mentioned table will make Pie chart

	Data	Color
Performance - 1%	49%	White
1%  Gap to highlight	1%	Gray
	150%	White

In this table 1% is nothing but to show the line on the point of performance %age like below

To create this chart –

·         Select Pie chart from insert menu under charts

·         Remove the legends

·         Add Data Labels

·         Right Click on any slice of Pie chart and select format Data series

·         Rotate chart by 270 Degree under series options

·         Right click just outside the circle & select No Fill under Fill option

·         Also select No line under border color

·         Change the circle color as per Table mentioned

·         Remove data levels except 1%

·         Select 1%, Press equal to sign & click on performance value Cell (50%) to show performance %age

After doing this our chart will look like

Fit Pie chart over Doughnut chart & do formatting like below

·         Make a round circle to fit middle of the doughnut chart

 

·         Make a shield to protect your charts to avoid unnecessary clicks

Place circle & shield at appropriate place & your Gauge chart is ready

Please write your suggestions to make it better

Right Function

To extract the characters from right side from a text string based on the no of character specified

Syntax

RIGHT(text,num_chars)

How to extract last name where last name is having different number of letters in different cells

	B	C	D
22	Names	Last name	Formula
23	Rajeev Sharma	Sharma	=RIGHT(B23,LEN(B23)-FIND(" ",B23))
24	Rohit Bansal	Bansal	=RIGHT(B24,LEN(B24)-FIND(" ",B24))
25	Amit shrivastava	shrivastava	=RIGHT(B25,LEN(B25)-FIND(" ",B25))

Len Function returns the length of selected text and finds the position of selected characters (Space)

Now to get no of letters after space just subtract the find length from total length

Let see how we did it

Rajeev Sharma	Sharma	=RIGHT(B23,LEN(B23)-FIND(" ",B23))
Total Length	13
Length till space	7
Last name letters	13-7 =	6 (Sharma)