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
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
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.
Or
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
=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
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.
Run or Deleting a Macro
Tools > Macro > Macros > Select Macro > Run or Delete
Practice 1. Domestic budget calculation
Practice 2. Profit and Loss Calculation
Microsoft Excel 2003 Tutorial
Reviewed by Mani
on
November 23, 2011
Rating:

No comments: