Free Online Computer Learning Courses: Microsoft Excel

Microsoft Excel is a Spreadsheet software developed by Microsoft. Spreadsheets are a great tool for doing math related jobs. Excel is widely used for small scale accounting related work.

Spreadsheet software is used to store information in columns and rows which can then be organized and/or processed. Spreadsheets are designed to work well with numbers but often include text. Sometimes text in a spreadsheet is called a label, because it is labeling columns and rows of numbers.

Numbers are called values sometimes, and can include numbers for counts or measurements, dates, times, and calculations from numbers. Spreadsheets can help organize information, like alphabetizing a list of names or other text or reordering records according to a numeric field.

It is impossible to give a complete listing of applications that can be done in spreadsheets, but they include budgeting displays, checkbook registers, enrollment records, inventories, coded surveys, field and laboratory research data, and financial and accounting applications.

Introduction to MS-Excel

Introduction

Microsoft Excel is Spreadsheet software developed by Microsoft. Spreadsheets are a great tool for doing math related jobs. Excel is widely used for small scale accounting related work.

A Spreadsheet is similar to the sheet used to store account related details on paper. They are intersection of rows and columns used to store account (and other such details) in a more convenient way. In this IT age, Spreadsheet term is used to refer (often) Electronic Spreadsheet ().

A Spreadsheet is a rectangular table (or grid) of information, often financial information. The compound word "spread-sheet" came to mean the format used to present bookkeeping ledgers—with columns for categories of expenditures across the top, invoices listed down the left margin, and the amount of each payment in the cell where its row and column intersect—which were traditionally a "spread" across facing pages of a bound ledger (book for keeping accounting records) or on oversized sheets of paper ruled into rows and columns in that format and approximately twice as wide as ordinary paper.

Microsoft Excel is one of the most popular, most widely used spreadsheet software available in the market.

Some other popular spreadsheet software are :

• Lotus 1-2-3

• VisiCalc

• GNumeric

• Quattro Pro

• Open Office Calc

Features of MS-Excel 2000

Excel allows you to create spreadsheets much like paper ledgers that can perform automatic calculations. Each Excel file is a workbook that can hold many worksheets. The worksheet is a grid of columns (designated by letters) and rows (designated by numbers). The letters and numbers of the columns and rows (called labels) are displayed in gray buttons across the top and left side of the worksheet.

Features of MS Excel include:

• Windows Based Application

• Worksheet & Workbook

• Large Scale Data Management Capacity

• Drag and drop

• Powerful Data Analysis Tools

• Build-in Functions

• Web enabled Information Sharing

Windows Based Application

Unlike older versions of Lotus 1-2-3 and VisiCalc that runs under DOS, Microsoft Excel is Windows based software (like other software’s of Microsoft Office Package) and Since it is windows based a user willing ot work on it doesn’t require to remember any command. It has menu like other windows based applications and since it is easy to learn and use.

Worksheet & Workbook

In MS Excel a file is known Workbook and a workbook contain multiple worksheets. Worksheet (or Spreadsheet or simply Sheet) is workspace in MS-Excel.
Thus we can say that workbook is made of worksheets.

Large Scale Data Management Capacity

One of most powerful Excel Features is its “Large Scale Data Management Capacity”. In MS-Excel 2000 you can manage very large volume of data.

As we know a workbook in excel contains multiple worksheet.

In a worksheet in Excel 2000 there are :-

65536 rows

225 columns

That means there are :

65536 x 256 cell in a worksheet (since intersection of a row and column forms a Cell).

A cell can contain a maximum of 32767 characters.

That means we can store 65536 x 256 x 32767 character in (or 524272 megabytes) a worksheet. A workbook can contain up to 255 worksheet that means a Excel file can store a maximum of 65536 x 256 x 32767 x 255 character. (or 524272 x 255 megabytes. )

Drag & Drop

In MS-Excel 2000 you can drag and drop of table data from a Web browser directly into Excel worksheet for analyzing the data.

Powerful Data Analysis Tools

MS-Excel provides you powerful tools for data analysis. You can create Chart based on your data or use Pivot Table Report to summarize your data.

Built-in Functions

To make mathematical and other calculation easy and less time taking MS – Excel provides us a lot of build in functions for nearly every type of calculation from mathematical to statistical and from text to logical types of functions. In order to use these Excel functions we just need to supply value required by this function to complete the calculation and produce the result.

Web-Enabled Information sharing

What if you want to publish some account data you have stored in a excel file on Internet, in this case you should know HTML to create and share Web Document. Using Excel 2000 we can create and share rich Web documents. This means that your Excel 2000 content is universally viewable by anyone with a browser.

List AutoFill

Excel 2000 automatically extends formatting and formulas in lists, simplifying this common task and helping you work more efficiently.

Starting MS-Excel

To start follow these steps:

Click on Start

Then Go to Programs

Then Click on Microsoft Excel

MS-Excel Application Window

When you start MS- Excel You will get a window as shown in the picture.

Element of excel Application Window

Title Bar

Displays the filename of the currently active Excel file and Restore, Maximize/Minimize and Close Buttons.

Menu bar

The Microsoft Excel menu bar contains all the commands required to create, work with, print, and save workbooks. To access these commands, click the name of the menu to open it, and then click the command you want to access.

Each menu groups together related commands, sometimes using submenus to further group commands:

• File

Create, open, save, print, and close workbooks.

• Edit

Perform editing functions on your workbook, including copying, cutting, and pasting data and objects, moving to various locations in a workbook, and undoing and redoing actions.

• View

Change the workbook's display size and style.

• Insert

Insert new worksheets, or additional space, such as new cells, columns, or rows; also insert charts, comments, functions, hyperlinks and other objects into your workbooks.

• Format

Apply formatting to worksheets and their contents.

• Tools

Use Excel's tools, such as the spell and grammar checker, macro recorder, and protection and collaboration features, and set your Microsoft Excel preferences.

• Data

Sort, filter, and otherwise arrange the data on your worksheets.

• Window

Switch between open workbook windows, or split the window of the current workbook.

• Help

Access Microsoft Excel's online help.

Toolbars

By default MS Excel display two toolbars :

• Standard
Display buttons for frequently used commands such New, Open, Save, Cut, Copy Paste etc.
• Formatting
Display buttons for frequently used Formatting commands such as Font, Font Size, Alignment buttons etc.

Other toolbars available in Excel are:

• Chart

Create and work with charts.

• Clipboard

View and select the contents of the clipboard.

• Control Toolbox

Insert ActiveX controls into a form.

• Drawing

Insert and format drawing objects.

• External Data

Work with data imported from external sources.

• Forms

Insert form objects.

• Picture

Insert and format pictures.

• Pivot Table

Create and work with pivot tables, interactive tables for large amounts of data.

• Reviewing

Insert, edit, and delete comments for or by reviewers.

• Visual Basic

Record and run macros, and work with Microsoft Word Visual Basic code.

• Web

Navigate a Web document.

• WordArt

Insert and format WordArt.

Formula bar

The formula bar displays the contents of the active, or selected, cell. If the cell contains a formula, the formula will appear in the formula bar, while the value resulting from the formula is displayed in the cell itself.

You also use the formula to enter formulas. Clicking the Edit Formula

button next to the formula bar opens the Formula Palette, which helps you select and construct functions. Functions are formulas that are built into Microsoft Excel, and allow you to perform a variety of calculations.

Worksheet/Workspace

Workspace of MS Excel is divided in rows and columns

Sheet tab

Status bar

The status bar at the bottom of the Microsoft Excel window displays the current status of operations being performed on the open workbook, such as the progress as the workbook is saved.

It also includes the AutoCalculate feature, which displays a selected calculation for any range (or set) of cells you select, automatically. By default, it displays the sum of the values found in the cells.

MS-Excel Basic

Creating and saving a Workbook

What is a Workbook & Worksheet?

A workbook is the Microsoft Excel file that stores your data. A workbook can contain many worksheets, which you use to enter and work with your data. You can enter and display data in multiple worksheets in a workbook, and perform calculations using data found across all the different worksheets.

Worksheets are composed of cells, active areas in which you input data or formulas for calculation. Cells are aligned along columns, labeled with the letters A through IV, and rows, labeled with the numbers 1 through 65536.

Creating a Workbook

To create a simple workbook, you need to start with a blank workbook. Within the Excel window

Choose New from the File menu
Select Blank Workbook option from the dialog box and click on OK.

Saving a Workbook

To save a workbook follow these steps:

Click on File-->Save

The save dialog box will be shown type the file name .xls (extension for excel file) and click on save to save the workbook.

Securing Your Workbook with Password

Click on File-->Save [Choose Save As if you have already saved your file].

then click on Tools and select General Options from the drop down list.

Type the Password to Open and/or modify and click on OK it will prompt you to re-type the password ,Re-type the password and click on OK and then click on SAVE .

Rows, Columns and Cells

What is a Row ?

In a spreadsheet the ROW is defined as the horizontal space that is going across the window. Numbers are used to designate each ROW'S location.

In the above diagram the ROW labeled 2 is highlighted.

What is a COLUMN ?

In a spreadsheet the COLUMN is defined as the vertical space that is going up and down the window. Letters are used to designate each COLUMN'S location.

In the above diagram the COLUMN labeled A is highlighted.

What is Cell?

In a spreadsheet the CELL is defined as the space where a specified row and column intersect.

Each CELL is assigned a name according to its COLUMN letter and ROW number cell name is known as CELL REFERENCE.

Cell Reference of highlighted cell is D5(column D & Row 5 ).

In Excel, this location is called a reference, and is used to point to values you want to use in a formula. References let you calculate data contained in different parts of your workbook, or in different workbooks altogether.

What is a Cell Pointer?

Cell Pointer in Excel is a Rectangular box that highlights the currently active Cell. For example in picture given below you can find the cell pointer on cell E3.

Entering Data

Types of Data

In a spreadsheet there are three basic types of data that can be entered.

• labels - (text with no numerical value)
• constants - (just a number -- constant value)
• formulas - (a mathematical equation used to calculate)

Data Types	Examples	Descriptions
LABEL	Name or Wage or Days	anything that is just text
CONSTANT	29 or .09 or 1984	any number
FORMULA	=8+1 or = 5*3+8	math equation

Entering Data into cell

Select the cell where you want to enter your data ,type in your data and press Enter or Tab to move next cell .

Remember ALL formulas MUST begin with an equal sign (=). Without an = sign they are treated as text and does not produce any result like a formula do.

Editing Data

To edit value of a cell, first select the whose data you want to modify then Double Click or Press F2 modify the value of the cell and press enter and/or tab to move next cell.

Selecting Cell(s)

To select a cell(s) use shift + arrow key(left, right, ip, down). Like if you want to select cells downward from current position use Shift+ (down arrow) etc.

Cells to select	Mouse action
One cell	click once in the cell
Entire row	click the row label
Entire column	click the column label
Entire worksheet	click the whole sheet button
Cluster of cells	drag mouse over the cells or hold down the SHIFT key while using the arrow keys

Creating List using Auto-fill

MS-Excel also helps its user to easily create list or series. Suppose you are creating a list of goods/commodities to be purchased for you’re your home. If you need to write serial number for this you don’t need type it one by one. You just need to do one thing type the first two numbers for example if your serial number starts from 1 and second value is 2(1 increment in each step), just write 1 in one cell and 2 in other, select the two cells use fill handler and drag to fill the series.

To create list of month, day, numbers etc you don’t need to type each and every value required for the list just type in one or two starting values and use the fill option to complete your series. For series of numbers ie Serial number, table you need to type first two values first value is called start value and second value is called step value and if you are creating series of month or days you don’t need to provide the second.

Creating a Series

Type the first two in cells select the cells where you want t fill your series and then click Edit menu and select Fill option under the Fill option click on Series and from Series dialog box select the AutoFill option and click on OK to complete your series.

Excel fill complete ur series according to your preference.

To create a series you can also use fill handle which is a more easy way to create series of anything.

Creating a Series of Month/Day using Fill handle

Type the month(or day) name from where you want to start your series Right click(or Click) on Fill Handle and drag to fill ur series. It will display a context menu (if you are dragging the fill handle using right mouse button) select the Fill Month(or Fill days or Fill Weekdays for Day)option from context menu. Excel will complete your series.

Custom List

Excel fill number series according to the Start and Step value given by us. That is a simple math according to our Step value increment value of each step is decided.

Suppose you are working some where as data entry operator where you have to create a list of certain commodities every day, using auto fill you can fill serial number day/month etc but you have to type in name of commodities every time you are creating the list or the alternate option is copy and paste the name of goods from previous record.

The batter way to solve this problem is a Custom list. A custom list in excel is same as any other list except that values are not pre stored (like month or day list). We need to provide the values for list once and after that we can use these values like we use day or month in our list.

Creating a Custom List

To create a custom list Click on Tools menu select Options option from the drop down list and from the options Dialog box select the Custom List tab Click on NEW LIST option from the custom list and type your entries in the List Entries area separated by comma after you have finished with your entries click on Add button to add entries to custom list.

You can also import values directly from your worksheet by clicking on the red white button

And then select the cells where you’ve your values stored

after you have selected the cells that contains value for the list click again on that button and then click on Import button to Import value of these cells and then click on add and Click on OK to close the dialog box.

Using Formula in Excel

The unique attribute of a spreadsheet program such as Excel is that it allows us to create mathematical formulas as well as execute functions. Otherwise, it is not much more than a large table for displaying text. This page will show you how to create these calculations.

Formula

A Formula is entered in the worksheet cell and must begin with an equal sign "=". The formula then includes the addresses of the cells whose values will be manipulated with appropriate operands placed in between. After the formula is typed into the cell, the calculation executes immediately and the formula itself is visible in the formula bar. See the example, the formula multiplies the quantity and price of each Item and adds all subtotal and produces the grand total.

General Syntax for using a formula in excel is :

=value1 operator value2

here value1 & value2 are values and operator depends upon kind of calculation you want to perform.

Operators used in MS Excel

Operation	Operator
Exponentiation	^
Multiplication	*
Division	/
Addition	+
Subtraction	-
Concatenation (putting 2 strings together, like EBiz & LTD.)	&
Equal To	=
Greater Than	>
Less Than	<

Operators are what connects the elements of a formula. Some familiar operators are: addition (+), subtraction (-), multiplication (*), and division (/). There is an order of operations when you are evaluating a formula. Formulas are evaluated from left to right, with expressions enclosed in parentheses evaluated first, then exponents, multiplication, division, addition, and subtraction. Excel has many more operators, but we will work with the operators listed above for now.

Here is an example of how the order of operations works:

If you have the following formula within a cell

The first operation would be the sum of 5 and 4 and then 100 would be divided by that sum.

We also learn how to use cell references in a formula in later chapter.

Copy & Move Data

When you copy or cut something, it goes in a temporary storage area where it will wait for you to send it somewhere else, an action called pasting. The storage area where the cut or copied data is sent is called the clipboard.

In Microsoft Excel 97, you can store only one item at a time in the clipboard. If you cut or copy something, it will replace the content of the clipboard with the new selection. In Microsoft Excel 2000, the clipboard can contain up to twelve items. When the Clipboard toolbar is functional, you can select copied or cut items from its list of buttons. If you use more than 12 stored items, the toolbar functions on a first-in first-out basis.

Copy data of Cell(s)

To copy content from one cell to another first select the Cell(s) and then select Copy option from Edit menu.

A blinking dashed border appears around the cell.

Select the cell where you want to put this content and select Paste option from Edit menu.

We can also do this with the help of mouse by using Ctrl+drag

Moving a Cell

To move content from one cell to another first select the Cell(s) and then select Cut option from Edit menu. Select the cell where you want to put this content and select Paste option from Edit menu.

We can also do this with the help of mouse by dragging the selected Cell(s) to Destination cell.

To finish pasting and remove the dashed outline from the original cell, press Enter.

Using Cell Reference in Formula

We have learned in previous chapters about how to use formula in MS Excel, but have you noticed in previous that using value in formula isn’t good enough idea because for example if user now wants to buy 4 Keyboard and 4 Mouse in such case our Total in column E will not update automatically, we have to modify value in formula manually. This can be vary time taking.

Using Cell Reference in Formula & Functions

The process of using Cell Reference in a formula is same as of using Value the only difference is that while entering a formula we use the Reference of the Cell that contains the value instead of directly using the value of the cell.

For example if we 10 and 20 stored in cell B2 a& C2 respectively and we want to store the sum of two in cell D2 instead of typing formula as =10+20 in cell D2 we will type it as :

=B2+C2

in cell D2 .

typing a formula this way make sure that the result of the formula is automatically updated whenever value of any cell that is part of this formula is changed.

Using Formula Copy

Using cell reference in a formula has another advantage that we don’t need to type the formula in each cell to perform the calculation. Suppose in the above example you have entered formula =C3*D3 in cell E3 to calculate total price for Keyboard then you don’t need to type formula in cell E4 just select E3 Copy its content using copy option and Paste content to E4 or Use Auto-Fill option (from Edit --> Fill -->Series) as we have used to create series etc.

As you will paste the formula in other cell you will see that formula is displaying correct result and if you look the formula bar then you’ll find that its displaying =C4*D4 not =C3*D3.

It’s because when we copy paste data of a that contains a formula that is using reference of a cells then references are automatically adjusted in the destination cell.

This is known as Formula Copy in Excel.

Types of Cell Referencing

As we know using Cell Reference in formula is a better way since it permits automatic recalculation.

We can use reference of cell in three ways:

a. Relative reference
b. Absolute reference
c. Mixed reference

Relative Referencing

Relative referencing is most frequently used in excel for simple calculation. In Relative referencing cell reference are automatically adjusted in destination cell.

It’s because when we copy paste data of a that contains a formula that is using reference of a cells then references are automatically adjusted in the destination cell.

This type of referencing is known as Relative Referencing.

Absolute Referencing

Relative is very useful and easy and hence most frequently used in MS Excel but it not suitable for every case.

For instance, suppose you want to calculate tax payable for different amounts. You’ve stored tax rate in Cell A10 and amount in column B(from B10 to B13). In this case writing a formula using relative reference will work properly with the cell in which we have typed the formula but when we will copy the formula to another cell it will not display appropriate result just because of relative approach.

See the picture below:

That means when you will type this formula in cell C10:

=B10*A10

(where B10 contains amount and cell A10 contains tax rate)

it will work fine in cell C10 only and we will need to type a separate formula for each cell, because in Relative Referencing cell reference are automatically adjusted in destination cell accordingly.

In a case like mention above, we will need to fix cell reference of either side (like cell A10 in our example).

We will need to write above formula as :

=$A$10*B10

A dollar ($) symbol is used to fix particular row or column.

Calling cells by just their column and row labels (such as "A1") is called relative referencing. When a formula contains relative referencing and it is copied from one cell to another, Excel does not create an exact copy of the formula. It will change cell addresses relative to the row and column they are moved to. For example, if a simple addition formula in cell C10 "=(A10*B10)" is copied to cell C11, the formula would change to "=(A11*B11)" to reflect the new row. To prevent this change, cells must be called by absolute referencing and this is accomplished by placing dollar signs "$" within the cell addresses in the formula. Continuing the previous example, the formula in cell C10 would read "=($A$10*B10)" if the value of cell C10 should be the multiplication of cells A10 and B10. Both the column and row of both cells are absolute and will not change when copied.

Mixed referencing

It can be used where only the row OR column fixed.

For example, in the formula "=(A$1+$B2)", the row of cell A1 is fixed and the column of cell B2 is fixed.

Navigating & Searching a Worksheets

Navigation using Keyboard

As we know in Excel the worksheet where we perform all tasks is divided in rows and columns and to move one cell to another we can use arrow keys, tab key etc. Each key is described below in detail:

The Down Arrow Key

We can use the down arrow key to move downward one cell at a time.

1. Press the down arrow key several times.
2. Note that the cursor moves downward one cell at a time.

The Up Arrow Key

We can use the Up Arrow key to move upward one cell at a time.

1. Press the up arrow key several times.
2. Note that the cursor moves upward one cell at a time.

The Tab Key

You can use the Tab key to move across the page to the right, one cell at a time.

1. Move to cell A1.
2. Press the Tab key several times.
3. Note that the cursor moves to the right one cell at a time.

The Shift+Tab Keys

You can hold down the Shift key and then press the Tab key to move to the left, one cell at a time.

1. Hold down the Shift-key and then press Tab.
2. Note that the cursor moves to the left one cell at a time.

The Right and Left Arrow Keys

You can use the right and left arrow keys to move right or left one cell at a time.

1. Press the right arrow key several times.
2. Note that the cursor moves to the right.
3. Press the left arrow key several times.
4. Note that the cursor moves to the left.

Page Up and Page Down

The Page Up and Page Down keys move the cursor up and down one page at a time.

Page UP

1. Press the Page Down key.
2. Note that the cursor moves down one page.

Page DOWN

1. Press the Page Up key.
2. Note that the cursor moves up one page.

The End Key

The End key, used in conjunction with the arrow keys, causes the cursor to move to the far end of the spreadsheet in the direction of the arrow.

1. Press the End key.
2. Press the right arrow key.
3. Note that the cursor moves to the outermost right area of the screen.
4. Press the END key again.
5. Press the down arrow key. Note that the cursor moves to the bottom of the screen.
6. Press the End key again.
7. Press the left arrow key. Note that the cursor moves to the uttermost left area of the screen.

Note that "END" appears on the Status bar in the lower right corner of the screen.

Note: If you have entered data into the worksheet, the End key moves you to the end of the data area.

Note : If Scroll Lock is on then arrow keys and page up/down keys will not work as described, since in scroll lock condition movement via keyboard in locked.

The Home Key

The Home key, used in conjunction with the End key, moves you to cell A1 -- or to the beginning of the data area if you have entered data.

1. Move the cursor to column D.
2. Stay in column D and move the cursor to row 15.
3. Press the End key.
4. Press Home.
5. You should now be in cell A1.

Movement	Key stroke
One cell up	up arrow key
One cell down	down arrow key or ENTER
One cell left	left arrow key
One cell right	right arrow key or TAB
Top of the worksheet (cell A1)	CTRL+HOME
End of the worksheet (last cell containing data)	CTRL+END
End of the row	CTRL+ right arrow key
End of the column	CTRL+ down arrow key
Any cell	File\|Go To menu bar command

Using Go To for Navigation.

The following are shortcuts for moving quickly from one cell to a cell in a different part of the worksheet.

Go to -- F5

The F5 function key is the "Go To" key. If you press the F5 key while in the Ready mode, you are prompted for the cell to which you wish to go. Enter the cell address, and the cursor jumps to that cell.

1. Press F5. The Go To dialog box opens.
2. Type P13.
3. Press Enter.

The cursor should move to cell P13.

Go to – Ctrl+ G

You can also use Ctrl-G to go to a specific cell.

1. Hold down the Ctrl key while you press "G" (Ctrl+ G). The Go To dialog box opens.
2. Type D14.
3. Press Enter.

You should now be in cell D14.

Searching a Worksheet

As we know each Excel worksheet contains 256 x 65536 no of cells. That means if we are looking for something by scrolling the worksheet, its going to take more than normal time.

In such a case we can use Find option from Edit menu. When you click on Find option it display a dialog box:

or you can also use Ctrl+ F from keyboard.

In the find what textbox type the text you are looking for and click on Find Next button.

You can also specify weather the text you are looking for should be searched in the current worksheet only or in all worksheets of current workbook by clicking on Options button in the Find dialog box and setting advanced options.

Using Find & Replace

As we know each Excel worksheet contains 256 x 65536 no of cells. That means if we want to search and replace something by scrolling the worksheet, its going to take more than normal time.

In such a case we can use Replace option from Edit menu.

When we click on Replace option it display a dialog box:

in the Find what textbox type the text to Find and in the Replace with textbox type the text you want to replace the searched text with and click on Find Next button.

In the find what textbox type the text you are looking for and click on Find Next button.

You can also specify weather the text you are looking for should be searched and replaced in the current worksheet only or in all worksheets of current workbook by clicking on Options button in the Find & Replace dialog box and setting advanced options.