VLOOKUP Function

VLOOKUP in excel stands for vertical lookup. This simple function is extremely useful if you have large lists of data. With this function, you can easily search through a list of data and match specific criteria. In the example below, we will use this function to match a batting average with a specific player.

Syntax

To start off, lets take a look at the VLOOKUP syntax.

=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

lookup _value:
This is the value you want to match. The lookup _value can be a text, a logical value (TRUE or FALSE only), a number, or a cell that references a value.

table_array:

This is the range of data range that our function uses to perform our search. The array used in VLOOKUP must contain at least two columns. The first column of data contains the lookup_values.

* It is best to use an absolute cell reference for the table_array. By using “$” in the range, this keeps a cell reference fixed on a certain cell, or cells.

col_index_num:
Enter the column number of the table_array from which you want data returned from. For example:

• if the col_index_num is 1, it returns a value from the first column in the table_array;
• if the col_index_num is 2, it returns a value from the second column in the table_array.

range_lookup:
True or False. The range_lookup must be a logical value.

• If TRUE or if this argument is omitted, VLOOKUP will use an approximate match if it cannot find an exact match to the lookup_value. If an exact match is not found, VLOOKUP uses the next largest lookup_value.
• If FALSE, VLOOKUP will only use an exact match to the lookup_value. If there are two or more values in the first column of table_array that match the lookup_value, the first value found is used. If an exact match is not found, an #N/A error is returned.
  

HLOOKUP Function

HLOOKUP stands for horizontal lookup. The difference between HLOOKUP and VLOOKUP is that HLOOKUP searches for data in rows instead of columns.

Syntax

To start off, lets take a look at the HLOOKUP syntax.

=HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

lookup _value:
This is the value you want to match. The lookup _value can be a text, a logical value (TRUE or FALSE only), a number, or a cell that references a value.

table_array:

This is the range of data range that our function uses to perform our search. The array used in HLOOKUP must contain at least two rows. The first row of data contains the lookup_values.

* It is best to use an absolute cell reference for the table_array. By using “$” in the range, this keeps a cell reference fixed on a certain cell, or cells.

row_index_num:
Enter the row number of the table_array from which you want data returned from. For example:

• if the row_index_num is 1, it returns a value from the first row in the table_array;
• if the row_index_num is 2, it returns a value from the second row in the table_array.

range_lookup:
True or False. The range_lookup must be a logical value.

• If TRUE or if this argument is omitted, HLOOKUP will use an approximate match if it cannot find an exact match to the lookup_value. If an exact match is not found, HLOOKUP uses the next largest lookup_value.
• If FALSE, HLOOKUP will only use an exact match to the hookup_value. If there are two or more values in the first column of table_array that match the lookup_value, the first value found is used. If an exact match is not found, an #N/A error is returned.

HLOOKUP Example

In the image below, we are telling Excel in Cell B1 to look at the range B3:E4 and find to Ty Cobb. Once Ty Cobb is found, we want HLOOKUP to show Ty’s batting average.

SUMIF function

The SUMIF function adds cells specified in range by a certain condition or criteria.

The Syntax for SumIF Function:

=sumif(range,criteria,[sum_range])

Range: A range is a range of cells you want to apply a specific criteria against.

Criteria: The criteria determines which cells to add.

Sum_range: Cells to sum.

Example:

The following example shows how to properly use the SumIf Function. Basically, we are using the SumIf function in Cell D3 to determine how many runs the Tigers scored in Home. The example uses the range A3:A12. The SumIf function matches the word “Home” in the range A3:A12 then adds the runs in the Sum_range B3:B12. Since our criteria is “Home,” the SumIf function ignores all of the away games.

Other Examples:

=SUMIF(A3:A12,C3,B3:B12) —– The SumIf Function returns: 20

=SUMIF(A3:A12,”Home”,B3:B12) —– The SumIf Function returns: 20

=SUMIF(A3:A12,”Away”,B3:B12) —– The SumIf Function returns: 13

Excel text functions – part 2

Now let’s consider a slightly more difficult situation. In the following example we have a description and an amount in the same cell, but the two are always separated by a hyphen:

Because neither the length of the text or the figure are necessarily consistent, we can’t use Left, Right or Mid. However, we can instead use the hyphen to work out where the description ends and the number begins. To do this we must first identify how many characters from the left there are before the hyphen.

To do this we use the ‘Search’ function. Here is the function screen for Search:

Note again that we can just type the hyphen into the ‘Find_text’ box and Excel will automatically add the speech marks. Note also that there is the option to specify the character position at which you want to start the search. This is useful if you need to locate more than one similar character – once you have found the first, you can start the next search from one character position higher. Our example is a simple one that doesn’t use the ‘Start_num’ argument and, as you can see, it returns the position of the hyphen as character 6.

=SEARCH(”-”,A13)

We can now ‘nest’ the Search function within the ‘Left’ function to retrieve the description:

=LEFT(A13,SEARCH(”-”,A13)-1)

In order to exclude the hyphen itself we have subtracted 1 from the result of search. If we copy this formula down our list we can see that it achieves the desired result:

Now to deal with the amount. Whilst we can use Search to find the starting position, we don’t yet know how long the amount is. We can work this out using the ‘Len’ function. ‘Len’ is a very simple function with just one argument – the text string, or cell containing the text string, that we wish to find the length of:

=LEN(A13)

This tells us how long the text is in total, and we have already used Search once to find the position of the hyphen. By combining Len and Search we can calculate how many characters follow the hyphen:

=LEN(A13)-(SEARCH(”-”,A13))

In the case of “Sales-10000” Len will return 11, the hyphen is at position 6, so 11-6 = 5, the number of characters in the amount.

We can use this with the ‘Right’ function to pick out the amount:

=RIGHT(A13,LEN(A13)-(SEARCH(”-”,A13)))

Again we can copy this formula down the list:

As you can see above, whilst we have indeed separated out the amount characters, Excel is still treating our text as text and if we used Sum to total column C we would get zero:

We need to convert the text ‘amounts’ into proper numbers. To do this we use the function Value. We will use the value function to convert the three items in our list to numbers. Here is the formula for cell D13:

=VALUE(C13)

We can now copy this down our list and use Sum again to total our new column:

As you can see, the text values are now treated as numbers and Sum works correctly.

These two functions can be used to remove unwanted characters from text. Sometimes, if you import text from other sources, you may end up with non-printing characters, such as carriage returns – Clean will remove these. Trim can be used to get rid of extraneous spaces:

In the following example we have part of an address that includes multiple spaces between ‘High’ and ‘Street’ and a carriage return character to separate the lines of the address.

In column B we have used Trim to get rid of the extra spaces:

=TRIM(A19)

and then in column C we have used Clean on the result to remove the carriage return:

=CLEAN(B19)

Note that the Trim function leaves a single space between High and Street, but that the Clean function removes the carriage return entirely.

Excel text functions Part 1

This is the first part of a two part article that was prompted by a comparatively simple query about concatenating text. As well as dealing with that query, we’ll look at some of the simpler Excel text functions for working with text. Part 2 will follow shortly and, in it, I’ll look at some slightly more advanced functions for dealing with less predictable text entries.

First of all let’s deal with the actual query, which asked how to combine text in two separate cells into a single cell. There are two principal ways to achieve this. Perhaps the simplest is to use the ‘&’ within an Excel formula:

In our example we have typed three items of text in columns A, B and C. In cell D1 we have entered the following formula to combine all three into a single cell:

=A1 & ” ” & B1 & ” ” & C1

Alternatively, there is an Excel function that concatenates text in this way. Unsurprisingly it is the ‘Concatenate’ function.

In the following screen shot we have used ‘Insert, Function’ to enter the required details. Note that in order to include spaces between the items of text, we have included “ “ between each pair of cells. Using the Insert Function screen, you just need to enter a space in the appropriate text box – Excel will add the speech marks for you:

So far so good, now let’s see how we cope with combining numbers and text. If we just want the number without worrying about the number format, then we can use exactly the same formula as for two items of text. Here we have included some ‘literal’ text in the formula together with a number in a cell:

As you can see the format is not ideal:

In the following example we have used the ‘Text’ function to format the number in the cell:

Note that you can also use named ranges. So if we name cell B3 as ‘profit’ we could write the formula as:

=”Profit is ” & TEXT(profit,”£#,##0″)

Note that we have included a space after the ‘is’ and before the “ so that the number does not follow on immediately from the text.

To see the text functions available in Word, select Insert, Function and then choose the ‘Text’ category (note that the examples shown are from Excel XP, other versions’ screens are slightly different):

As you can see there are lots of text functions, we will look at a few in detail, but if you want to explore all of them, just scroll through the list using the down arrow key. As you select each function in turn you will see a brief description of what it does towards the bottom of the screen. For more details, click on the ‘Help on this function’ link:

This group of functions can be used to return specific sets of characters from a text string. As you would imagine, Left is used to return a certain number of characters from the beginning of a text string, Right is used to return characters from the end and Mid to return characters from anywhere within the text string.

As an example, we will look at some nominal ledger codes. We will assume that the first two characters represent the company, the next three the branch, and the last four the type of expense or income:

First of all we will use the Left function to list the first two characters in the company column. We can either use Insert, Function or just type the function in directly if we know the required syntax:

=LEFT(A7,2)

Now let’s use Right in a similar way to sort out the four characters from the end of the code:

=RIGHT(A7,4)

As you can see, the syntax of Left and Right are very similar, just referring to the cell holding the code and the number of characters. The final function that we will look at in this section is ‘Mid’ and the syntax for this one is slightly more involved because we need not only to specify the number of characters, but also from which character to start. For this reason you may find it easier to use Insert, Function:

This should create the following formula:

If we now copy the three formulae down to the end of our list, we can see how our text string has been split into the three different sections:

Round function

A less drastic method of avoiding rounding errors is to use Excel’s ROUND() function. This allows you to round a number to a specific number of decimal places for use in calculations, without changing the original number. Round takes two arguments – the number to be rounded and the number of decimal places to round to. In this case we have used =ROUND(A2,0) to round to round pounds:

The ’sum’ formula in cell C5 sums the rounded numbers in C2 to C4, so returns 99 rather than 100. If you need the total to be a particular figure, for example if the 100 was a profit share, then you could set the final rounded figure to be the total minus the sum of the other rounded figures. In this case we have entered a figure of 100 in cell E5, then rounded the calculation of the two shares in E2 andE3, with E4 being =E5-SUM(E2:E3)

More fun with rounding

As well as 0 for no decimal places or a positive number for that number of decimal places, you can also enter a negative number as the decimal place argument of the Round function. For example you could enter -3 to round to thousands:

To change the format to display numbers rounded to thousands you would use the following custom format:

#,###,

for 1,235k

#,###,k

for millions

#,###,,

for 1m

#,###,,”m”

The speech marks are necessary for the m, but not the k, because m is used in formatting to signify a month format.

IF function in Excel Part 2

A couple of days ago, I received a question from a reader asking how to use the IF function in Excel for a particular scenario he needed to do at work. I thought other people might benefit from having a short tutorial on this, so I’m providing it here, divided into a few parts.

The first part (here) will go into the basic IF statement, and subsequent parts, such as how to use nested IF functions with other logical operators, will describe how to use it in slightly more complicated scenarios or with additional features. For example, the reader’s question had to do with using an IF statement given two conditions, e.g. if a number falls between two other numbers. I address those in the separate post in the link above.

But, let’s go over the basic IF function here, first.

Let’s say that you have a series of numbers in Excel like this that represent sales figures that 10 of your salesmen have made this quarter, in column B:

Before we go further, if you’d like to work through the examples yourself, here’s the raw data you can copy into an Excel worksheet. First, open up a blank excel worksheet. Next, highlight the table below. Copy it, and then go back to your excel worksheet. Go to cell A4 (or another empty cell, if you want to put the data elsewhere), and then select “Edit” from the menu bar. Select “Paste Special” and then “Text” from the popup box. Click “OK”. The data should appear in your Excel worksheet just as it does above.

	Sales
Salesman A	87925
Salesman B	100000
Salesman C	145000
Salesman D	200750
Salesman E	178650
Salesman F	99555
Salesman G	147000
Salesman H	213450
Salesman I	122680
Salesman J	92500

Ok, now back to the tutorial.

Next, suppose that you give them a bonus commission of 12% if they’ve exceeded a threshold value, say, $100K. Rather than going through each sales figure yourself and manually checking to see if that number is greater than $100K, and if so, calculating the commissions in another column (column C in this case), you can use the IF function to do this for you.

First, let’s put the 12% commission percentage in B1 so we can change it later if we want to, and the $100K figure in B2 for the same reason:

Here’s the syntax for the IF statement in Excel. You put the function in the cell or cells where you want the result. The IF function takes three inputs:

=IF(condition to test for, what to do if the condition is true, what to do if the condition is NOT true)

The first two arguments are required, and the last one is optional. If you leave out the part about what to do if the condition isn’t true, then Excel will return “FALSE” automatically. This can be useful in certain situations. In our case, just to avoid confusion, we’ll put in a specific action for what to do when the condition is not true.

So, in our case, we’d start in cell C5, where we want to calculate the salesman’s bonus, if any. The IF statement would be written as.

=IF(B5>$B$2, B5*$B$1, “No bonus”)

Like this:

Here’s what that says in plain English: If the number in cell B4 is greater than the number in cell B2, then multiply B4 by B1. If not, then write “No bonus” in the cell instead. Once you enter in this formula, the result will be printed in the cell. In this case, Salesman A didn’t make enough sales, so “No bonus” appears in the cell.

By the way, the dollar signs by $B$2 and $B$1 are simply an ugly way that Excel uses to keep a cell “fixed”. Later on, we’ll copy and paste the IF function we wrote in cell C4 and the formula will automatically adjust for each row. For example, if we paste the formula in cell C6, the formula will automatically change to:

=IF(B6>$B$2, B6*$B$1, “No bonus”)

If we hadn’t put “$” by B2 and B1, then these would have automatically adjusted as well when we pasted the formula in another cell. Of course, you could avoid all this and put 100,000 and 12% into the IF statement directly, but if you needed to change these parameters later on, it’d be much harder to do.

After copying and pasting the IF formula down the column, here’s our result:

And voilá. It’s done. Now you can format all the numbers and alignments nicely so it’s more readable.

If you have several rows of data, the IF statement is a lot easier a tool to use than going through each case manually! In an upcoming post, I’ll try to describe some other nifty things you can do with the IF statement.

Create Graphs and Lines

Introduction
This is a brief outline of how to produce acceptable graphs using Microsoft Excel. The version used here is Excel 97. If you have an earlier version you may have less features, a newer version may have more features. This is not intended as a step by step tutorial for this reason. It is a complicated package and unless you are confident and have several hours to spare you may not achieve very good results.

Data Table

1. General appearance

There are many options for changing the appearance of the content of cells. The content can be aligned in different ways (e.g. centred) both horizontally and vertically. The font type, size and colour can also be changed. Different borders can be put around the cells. Don't waste much time with this unless you are intending to print out the table for your final report.

Column headings: titles and units

• Set the column headings, titles and units, to plain text:
  Format→Cell→Number→Category=Text

• For long titles set the text to wrap around inside the cell, rather than across the next cell.
  Format→Cell→Alignment→Text control=Wrap text

2. Data

• Set the data cells to be numbers.
  Format→Cell→Number→Category=Number

• Set the correct number of decimal places
  Format→Cell→Number→Decimal places=1
  (or however many places you want)

In this example the contents of each cell have been centred. For the middle column headings the cells in columns B, C and D have been merged (Format→Cell→Alignment→Text control=Merge cells) to make one cell. This just makes things a bit neater to look at.

3. Calculating averages

The average function can be used to calculate averages automatically.

In the example above the following text, not the number 1.13, has been typed into cell E3:

=AVERAGE(B3:D3)

This means calculate the average of cells B3, C3 and D3, and display the answer. This doesn't have to be typed in for every cell. By dragging the small square at the bottom right of selected cell E3 down the column the formula is copied into all the other cells. Saves quite a bit of time!

An alternative way to write the same formula would be:

=(B3+C3+D3)/3

Using this simple method can be quicker than trying to find if a special function exists, and then working out the correct syntax for it.

Other examples;

• =POWER(E4:3) and =E4*E4*E4 both mean E4 cubed.
• =SQRT(E4) and =POWER(E4:0.5) both calculate the square root of E4

You should be able to calculate most things using these basic maths symbols:

symbol	use
( )	brackets to group parts of a calculation together
+	plus
-	minus
*	multiply
/	divide
SQRT(A1)	square root

Line Graph

1. Creating the chart

You've got all the data into the table, now you have to draw the chart. In the above example select cells A3 to A12 with the mouse, then E3 to E12. You will have to hold down the Ctrl key as you drag the mouse to select both columns at once.

With the two columns highlighted create a new chart:

Insert→Chart→Chart type=XY(Scatter)

Make sure you select the XY(Scatter) NOT Chart type=Line

If you select all the default options your chart will look something like this:

This won't get you many marks, you will have to change just about everything on the graph to bring it up to an acceptable standard.

Below is an improved version of the graph that would be suitable if you have a colour printer. For a black and white printer get rid of the blue squares completely and make the points and line black as well.

2. Chart options

Chart → Options will allow you to set up the basic appearance, which includes the following:

• Titles: for the chart and axes
• Axes: x and y axes and numbers shown or not
• Gridlines: on or off (These are the blue background lines below)
• Data Labels/Legends: these are not normally needed.

3. Finished Graph

There are many other options that can be set up. Double clicking on part of the chart, such as the x axis, will bring up a menu allowing that part to be configured.

chart area/plot area	Keep these simple, it is best to use a plain white background.
axis scale (e.g. for x axis)	minimum=0 ; start value maximum=120 ; end value major unit=10 ; goes up in tens minor unit=1 ; not used in this example
data series (i.e. the points)	line = none ; this is a dot to line, so switch it off! marker ; set up to produce a cross "+" using, background = no colour ; style = + ; size=10
grid lines	If you use these set them to a pale colour to look like graph paper.

Finally, make sure the chart is adjusted so that it is big when printed out. Postage stamp sized graphs will not gain many marks. The graphs shown on these web pages are smaller than they would be if printed to make them easier to view on a monitor.

4. Adding mathematical symbols to Excel charts

With Excel 97 and Excel 2000 it is not obvious how to add a title such as "√length" to a graph axis. Excel allows you to change the font, but doesn't let you insert symbols as you would with Microsoft Word. One way to get round this is to format the text including symbols as you want it to look in Word. Then cut and paste the text into the correct location in Excel.

Line of Best Fit

1. Trying out a trendline

Selecting the Add trendline option will produce a menu with various options similar to the example below. Each type will try to a fit a line according to a certain mathematical function. Some of these you may not understand, but that doesn't matter, concentrate on those which make sense. Try them out and see if there is a good match. Note that some of the trends can't be calculated if you have a data point at (0,0). If this is the case remove the value from your selected data and try again.

4.2 Trend types

linear	y=mx+c ; e.g. y=2x, y=-3x+12
power	y=xn ; e.g. y=x2, y=x3, y=x½ (square root), etc
polynomial	y=a+bx+cx2+dx3+... The order gives the highest power of x
exponential	e.g. the type of curve you get for radioactive decay
logarithmic	You will either have studied logs in maths or not!

3. Options

To extend the trend line beyond the first and last points of the graph you must enter values in the Forecast Forward and Backward boxes. In the example graph above it has been extended forward by 5 (x-axis) units and backwards by 9.9. (Remember that some trend lines don't like to go back to x=0. If this is the case extend back to just greater than zero. Hence a value of 9.9 rather 10.)

The style and colour of the line may also be easily changed. You want to keep it as a solid narrow line in a colour that shows up well.

4. Display equation on chart

For the above graph with a trendline of the power type the equation produced is;

y = 0.359 x ^0.497

If we round up the numbers we get;

y = 0.4 x ^0.5, or y = 0.4 √x

Thus the data shows a convincing trend that y is proportional to the square root of x. To prove this you could plot a second graph of y against the square root of x. This will produce a straight line.

5. Words of Warning

The computer may not fit a good trendline to your graph. If you think you can draw a better trendline by hand then do so. Your brain can intuitively take account of points that may be slightly wrong, or may have larger errors than others. The computer software may not. Remember, if you make a poor job of adding a line of best fit you will not get 8 marks!

PROPER Function

The following formula easily tranforms a list of data from Upper to Lower case.

1. Start with a list of data.
   
2. Next, place your curser in Cell C1 and enter the following functions as shown. =PROPER()
   
3. Enter the cell A1 between the parenthesis. =PROPER(A1)
   
4. Now drag the formula down to convert the remaining names to lower case. Simple huh!
   

IF function in Excel Part 1

The IF statement is excel is very useful, but can become a little complicated. The following explanation will explain how to easily and properly use this function.

Overview

The IF function answers the question, “Is this true or false. There a three basic arguments used in the function.

1. Logical Test – Does Batter “A” or Batter “B” have a higher average.
2. Value if true – What is displayed if the value is true.
3. Value if false – What is displayed if the value is false.

Execution

The following example will show you how to determine which baseball player has a higher batting average.

Start by using the following fuction.

=IF(B3>C3,”John”,”Andrew”)

You can copy/drag the formula down to display the results. I’m going to assume you know how to copy a formula. If you don’t, click and hold the little fill handle that appears in the bottom right corner of the cell. You are then able to drag the formula down. You can also refer to the Auto-Fill Tutorial.

There are numerous variations to using IF statements. If you have any difficulties with any statement or function, please feel free to ask.

COUNTIF Function

There are various methods used to count cells in Excel. We will be utilizing the COUNT function.

• The COUNTIF function is used to count cells in a range given a certain criteria.

The examples listed below show how to properly use the COUNT function in Excel. In every example, the data range that will need to be changed is titled “range.”

Use of the COUNT function Examples

To count the number of cells that contain the word “buddy”:

=COUNTIF(range,"criteria")

=COUNTIF(range,"buddy")

The asterisk “*” is your friend

To count the number of cells that contain any text:

=COUNTIF(range,"*")

To count the number of cells that contain text that begins with the letter “b”:

=COUNTIF(range,"b*")

To count the number of cells that contain the letter “b”:

=COUNTIF(range,"*b*")

Count Functions with exact criteria

To count the number of rows where the cell is greater than or equal to a given number:

=COUNTIF(B1:B10,”>=10″)

To count the number of cells that contain either “yes” or “no”:

=COUNTIF(range,"yes")+COUNTIF(range,"no")

To count the number of cells that contain a value between 1 and 10:

=COUNTIF(range,">=1")-COUNTIF(range,">10")

Create Simple Worksheet

1- Open Microsoft Excel
2- Click on File menu and then click on New

3- A New window will appear on the right side of the screen, click on simple worksheet.


Selecting Cells
You just click on the cell to select it

A Cell is a Intersection of a column and row, you must select a cell to work with it. A range is selection of one or more then one selected cells. You can edit, delete format, use formula just like a singe cell. The active cell has black border, As you select a cell or range its range reference is shown in the name box

Formatting Cells

1- Select Cell which you want to format and then right click on that cell and then Format option.
OR
Select Cell which you want to format and then select Format menu above and click on Cells.
OR
Select Cell and simple press Ctrl+1 Keys

2- A window will appear at front of you:-

You can see different tabs on this window which are explain as under:-

Number Tab: In number tab you can select category of cell for which purpose you want to use the cell.

Alignment: You can set horizontal, vertical alignment in this option. Text control is also available in this option.

Wrap text: If you want text to appear on multiple lines in a cell, you can format the cell so that text wraps automatically, or you can enter a manual line break. Shrink to Fit: By selecting this option text will shrink in a single cell. Merge Cell: A single cell that is created by combining two or more selected cells.

Font: You can change font size, font style, underline, bold font and add effects.
Border: Border tab is use to draw border outside the cell.
Patterns: Use to add colors in the cells

Other Options in Format Menu:-
Click on Format Menu you will see following options in this menu. These option are explain as under

Rows: This option is use to set height, hide, unhide cell.
Column: This option is use to set width, height of a column
Sheet: This option is use to rename worksheet on which you are working, change background of the sheet. Hide and Auto format option is also available( Select number of cells and use auto option format)
Style: Different currency and other styles are available in this option.

Getting Started with Excel

When a user start Microsoft excel following window is appear.

Standard Toolbar Standard toolbar is located at the top of screen under Menu Bar New: Click to create new worksheet OR Click File->New OR Press Ctrl+N to create new worksheet. Open: Click open new worksheet OR Click File->Open OR Press Ctrl+O to open new work sheet. Save: Click to save worksheet OR Click File->Save OR Press Ctrl+S to save the worksheet. Permission: Click OR You can create content with restricted permission using Information Rights Management only in Microsoft Office Excel 2003. Start Microsoft Office Excel 2003, and create a new workbook. On the File menu, click on Permission, and then click Do Not Distribute. In the Permission dialog box, select the Restrict permission to this <file type> check box. In the Read and Change boxes, type the names or e-mail addresses of people you want to give permission to. If you want to give all users permission, click Give all users Read access to the right of the Read box, or click Give all users Change access to the right of the Change box. Click OK, save your workbook And distribute to the people with permission. E-Mail: Click to Email your worksheet. Printer: Click to Print worksheet. Preview: Click to see Preview worksheet. Spell Check: Click to check the spellings of words which is use in the worksheet. Research: Click to research icon, a window will open on the right side of the screen, you can enter a word on which you want to research. Copy: Select any text/picture/chart in the excel and then press to copy the text/picture/chart. Undo: Click to cancel the last action you performed and click the forward to cancel the Undo action. Insert Hyperlink: Select cell or text and click to insert hyperlink on the text. Auto Sum: Enter data in the any row or column in the excel worksheet and then click on to calculate the sum of numbers You will find its sum in the next cell:- Sort: This icon is use to sort data in ascending or descending order. Chart Wizard: This feature will allow you to create chart/graphs. Help: Click icon if you need any help related to Microsoft Excel. Formatting Toolbar Formatting toolbar is available on the top of screens. There are different quick shortcuts available in this toolbar, you can add more shortcuts this toolbar.