Microsoft Excel 2003 Tutorial

What is Microsoft Excel ?

Microsoft Excel is an application software developed by Microsoft. Excel is a spreadsheet package which is used for data automation. Excel is also used for creating graphs, which gives us a pictorial view of a company or an organisation. Excel features calculation, graphing tools, pivot tables etc.

What is Workbook and Worksheet ?

A workbook is a multiple pages Excel document. Each page of a workbook is called a worksheet, and the active worksheet is displayed in the document window.

What is Column, Row and Cell ?

Each spreadsheet is divided into columns, rows and cells separated by gridlines.

Columns :: These are vertical cells which can contains information. The first column is A and letter A is appears in the column heading. Each worksheet has 256 columns.
Rows :: There are the horizontal cells which can contains information. Each worksheet has 65,536 rows.
Cells :: A cell is the intersection of a row and a column. Each cell has a unique address composed of the cell’s column and row.

How do you Open MS Excel ?

To open MS Excel follow the steps below :

Go to Start > All Programms / Programms > Microsoft Office > Microsoft Office 2003/2007/2010.

How do you create a new file ?

Open Microsoft Excel first > go to File > click on New or Press Ctrl + N on the keyboard

How do you setup your page ?

Go to File > click on Page setup > select the Orientation, paper size, click on margin to change the margin of the pages > click O.K

How do you Save your file ?

Go to File > click on Save As > or Press Ctrl + S on the keyboard > Type the file name > click on Save

How do you Open an existing Microsoft file ?

Go to File > click on Open or Ctrl + O > go to the location of your existing file > select your file > click on Open

How do you make contents Bold / Italic / Underline ?

It is similar to Microsoft Word. Select contents > Press B / I / U on the Formating Toolbar or Ctrl + B / Ctrl + I / Ctrl + U

How do you change alignment ?

select the contents > click on the alignment buttons on the Formating Toolbar

How do Copy and Cut and Paste ?

Select cells / rows / columns / worksheet > Place the cursor where you want to paste
OR

Ctrl + C = Copy (for copy)
Ctrl + X = Cut (for cut)
Ctrl + V = Paste (for paste)

How do you insert Column / Row / Worksheet / Symbol ?

Place the cursor where you want to insert > go to Insert > select Rows / Column / Worksheet / Symbol whatever you want to insert > then follow the steps as your requirements

How do you delete Rows / Columns / Worksheet ?

Select the rows / columns / worksheet > Press the delete button on key board
OR
Right click on selected rows / columns / worksheet > Select delete

Editing & Formatting Cells

Select the cell and double click on the cell to edit it.

Select the cell > click on Format on the Menu bar > Do the needful > O.K

How do insert Border ?

Select the cell / rows / columns > Format > cells / rows / columns > Click on Border > select Outline and Inside > O.K

How do you merge cells ?

To merge cells follow the steps below :

Select the cells > Format > Cells > Click on Alignment > Select check box of Merge Cells > O.K

How do you make Summations of cells ?

Click on a blank cell > type the formula =sum( column and row number of first cell : column and row number of final cell) > press Enter

How do you make Subtraction ?

Click on a blank cell > type the formula =column and row number of first cell -column and row number of final cell > press Enter

How do you make Multiplication ?

Click on a blank cell > type =product(column and row number of first cell : column and row number of final cell) > press Enter

How do you make divide ?

Click on a blank cell > type =column and row number of first cell / column and row number of final cell > press Enter

Summation of multiple sheets

cleck on a blank cell on the final worksheet and type the formula given below :

=sum(sheet1:sheet5!column and row number of first cell of 1st worksheet : column and row number of final cell of final worksheet) > Press Enter on keyboard

What is Goal Seek ?

Goal Seek is a procedure to find a specific result for a cell containing formula by adjusting the value of one other cell.

Procedures

Tools > Goal Seek > Set cell name (cell containing the total estimated cost) > write the “To Value” (i.e., target value) > mention the cell for which you want to change the expenditure > OK > OK

Creating Chart / Graph

Select the data > Insert > Chart > Select Chart type > Next > Next > Enter the Chart Title, Category (X) Axis and Value (Y) Axis > As object in or As new sheet > Finish

Autofilter/Sorting/Subtotal

Select the data   >   Data   > Filter > Autofilter > Custom on any field   > OK
Select the data   >   Data   > Sort > Select the field name at sort by box > OK
Select the data   > Sort the data on the field for which Sub-Total is needed>   Data   > Subtotal > Select the field at each change in box > OK

What is Scenario ?

A scenarios is a set of values that Microsoft Excel saves and can substitute automatically in your worksheet. You can use scenarios to forecast the outcome of a worksheet model. You can create and save different groups of values on a worksheet and then switch to any of these new scenarios to view different results. You can edit your scenario also.

Procedure

Select the data > go to Tools in the menu bar > click on Scenario > Add > Type Scenario name > OK > Cell range for changing data > OK > Enter the new value > OK > Scenario Manager dialog box opens click on Summary > Result Cells > OK

Edit Scenario

Tools > Scenarios > Click the name of the scenarios you want to edit, and then click Edit > Make the changes you want > In the Scenarios Values dialog box, type the values you want for the changing cells > To save the changes click OK.

To return to the Scenarios Manager dialog box without changing the current scenarios, click Cancel.

What is Macro ?

A Macro can be defined as a program that instructs Excel to perform action. In other words they are programs run by the application for which they are created. MS-Excel poses a feature which can record and play back command Macro. When you record a series of steps, Excel notches the steps and converts them into instructions.

Procedure

Tools > Macro > Record New Macro (Record Macro dialog box appears) > Type the Macro name, also the shortcut key > OK (then a step recording dialog box appears) > Write down the structure that you want to be recorded. > After completing it click stop recoding button. > Open another blank sheet where you want the same sheet to be written. Press the Ctrl + and the shortcut key together to run the Macro.