Microsoft Excel

INTRODUCTION

Microsoft Excel is a window based spreadsheet package and a product of Microsoft Corporation. While doing calculations on plane paper if any data is changed or if the formula is changed, then the entire calculations are required to be done again. This problem of redoing the entire work can be avoided if a spreadsheet package is used. The advantage of a spreadsheet package is that it automatically recalculates the entire worksheet if any change is made in any data or formula. Spreadsheet package can be used to maintain details of bank passbook, tax, inventory, purchase and sales etc. some of the most popular spreadsheet packages are lotus 1-2-3, symphony, MS Excel etc.

To start MS-Excel go to Start button then Programs and then click Microsoft Excel.

Components of MS-Excel

Title bar: - The blue bar on the top of the window displays “Microsoft Excel- “ , file name and various window controls like minimize button, Maximize button and close button.

Menu bar: - This bar is created to select different options.

Standard Toolbar: - Displayed by default allows giving common commands like saving the file, opening a file etc.

Formatting Toolbar: - Allows the user to give commands related to formatting cells and cell contents like Bold, Underline, Font size etc.

Name box: - displays the address of the current cell.

Formula bar: - displays the cell content.

Current cell: - Current cell will be the active cell.

Row headers: - There are 148576 rows numbered as 1,2…148576. First row number is 1 and last number is 65,536.

Column Headers: - There are 16384 columns numbered as A, B, C…. AA, AB…IV.

Scroll bars: - Used to scroll through different parts of the current cell.

Sheet tabs: - Displays the sheet names. Each worksheet is named as sheet1, sheet2, sheet3 etc.

Status bar: - Displays on the left side various modes like ready or edit mode. The status of num lock, caps lock and scroll lock keys on the keyboard on the right side.

Components of a workbook

Excel documents better known as workbooks, contain 3 worksheets by default. Each sheet named uniquely like sheet1, sheet2, sheet3 etc. which is displayed on the sheet tab. A workbook can also contain chart sheets, which are named as chart1, chart2. Excel loads automatically a new workbook named Book1 (with extension .XLS). The main part of the excel screen is the worksheet area that is a grid of rows and columns. User can insert 255 no of worksheet into the active workbook.

Row, Column and Cell

The worksheet is more like a table. The vertical divisions are known as columns and the horizontal sections are called rows. The intersection of a column and a row is called a cell. Each cell has a name or a cell address. The cell address consists of the column letter and a row number. The first cell in first row and first column has address as A1, where column name is A and the row number is 1. Similarly the last cell address is IV65536( column IV and row 65536). Total cells in the worksheets are 256*65536. An active cell is that where the insertion point is available. At nay time one cell can be active and the address of the active cell is displayed in the name box. A thick black border indicates the active cell. Any cell can be made active by clicking on that cell.

Creating a Workbook

1. On the file menu Click New.

To create a new blank workbook, click the General tab, and then double – click the workbook icon.

Opening a workbook

Workbook can be opened in the following way:

Click open Icon from standard Toolbar.
In the Look in box, click the drive, folder, or Internet location that contains the workbook.
In the folder list, double- click folders until you open the folder that contains the workbook you want, if you can’t find the workbook in the folder list, you can search for it.
Select the particular file that you want to open.
Click Open.

Saving a Workbook

Workbooks can be saved in the following way:

Click save icon from standard toolbar.
In the look in box, click the drive, folder, or Internet location that would contain the workbook.
In the folder list, double- click folders until you open the folder that would contain the workbook you want.
Specify the file name that you want to save.
Click save.

Working with Workbooks and Worksheets

In Microsoft Excel, a workbook is the file, in which you work and store your data. Because each workbook can contain many sheets, you can organize various kinds of related information in a single file.

You can enter and edit data on several worksheets simultaneously and perform calculations based on data from multiple worksheets. When you create a chart, you can place the chart on the worksheet with its related data or on a separate chart sheet.

The names of the sheets appear on tabs at the bottom of the workbook window. To move from sheet to sheet, click the sheet tabs.

To insert a new worksheet, do the following.

1. To add a single worksheet, click worksheet on the insert menu.

to add multiple worksheets, hold down SHIFT, and then click the number of worksheet tabs you want to add in the open workbook. Then click worksheet on the insert menu.

To rename a sheet,

Double click the sheet tab.
Type a new name over the current name

You can hide workbooks and sheets to reduce the number of windows and sheets on the screen and to prevent unwanted changes. For example, you can hide sheets that contain sensitive data. The hidden workbook or sheet remains open, and other documents can use their information. You can also hide selected rows and columns of data that you are not using or that you do not want others to see.

To prevent other from displaying hidden sheets, rows, or columns you can protect the workbook or sheet with a password. To hide a workbook the steps are:

Open the workbook.
On the Window menu, click Hide.

To unhide a hidden workbook select the Unhide option from the window menu.

Editing worksheet data

Double – click the cell that contains the data you want to edit.
Make any changes to the cell contents.
to enter your changes, press ENTER, to cancel your change, press ESC.

To DELETE cells, rows and columns the steps are

1. Select the cells, rows, or columns you want to delete.

On the Edit menu, click Delete.

Applying Borders, Shading and Background Patterns

To apply border to cells,

Select the cells you want to add borders to.
To apply the most recently selected border style, click borders.
To apply a different border style, click the arrow next to border, and then click a border on the palette.
To apply additional border styles, click cells on the format menu, and then click the border tab. Click the line style you want, and then click a button to indicate the border placement.
To change the line style of an existing border, select the cells on which the border is displayed. On the Border tab (Cells dialog box, Format menu), click the new line style in the Style box, and click the border you want to change in the cell diagram under Border.

To shade cells with solid colors

Select the cells you want to apply shading to.
To apply the most recently selected color, click Fill color.
To apply a different color, click the arrow next to Fill color, and then click a color on the palette.

Delete sheet

This option will remove the active sheet permanently from the workbook. So, while selecting this option the user has to very careful otherwise the sheet can’t be retrieved again.

Delete

This option will display four- sub option:-

1) Shift cell left

2) Shift cell up

3) Delete entire row.

4) Delete entire column.

Now placing the cell printer at a respective cell thereby, choosing shift cell lift and shift cell up it will not only erase the contents of the active cell but also moves the contents of the right most cell or just bottom cell on to the active cell. Similarly delete entire row and column will remove the contents of the active row and active column respectively.

Clear

While selecting this option it will display four – sub option:-

1) Clear all

2) Clear comment

3) Clear content

4) Clear formats.

Clear all and clear content will erase the contents of the selected cell and a particular cell where as clear format will erase the current format of the selected cell and make it back to previous one. Similarly clear contents will erase the comment of a cell if enter by the user.

Move or copy sheet

User can move or copy a particular sheet on to the other area. When user will select the respective sheet area from the window. In copy, it will simply create a duplicate sheet for the active one.

Formula bar :-

User can hide or display the formula bar from the screen. When user will make the respective option from view menu OFF and ON.

Comments:-

If the user wants to place some comments to a particular cell, user has not only place the pointer at the respective cell but also click the comment option from the view so as to see the comments; Now the user will find the comment box from the respective cell on the screen to make it OFF again, user click the comment option from the view menu.

Chart: -

This is a graphical representation of the data in the spreadsheet. So by working with this option user has to click the chart option form the start menu then by selecting the range of the database it will display the various types of charts and chart sub types. After completing each step, i.e. (chart titles, x-axis, y-axis, data labels and data location) user has to click finish button so that user will find a small view of a chart on the particular location of the sheet. The chart can be dragged to make into any shape as per user’s requirement.

Function: -

User can calculate any functional value at a respective cell. While user take the help of function option from insert menu, user can do this by clicking the next button and defined the range for the respective function.

You can use the logical functions either to see whether a condition is true or false or to check for multiple conditions. For example, you can see the IF function to determine whether a condition is true or false: One value is returned if the condition is true, and a different value is returned if the condition is false

Cells :-

This option will display six-sub option: -

a) Number b) Alignment c) Font d) Border e) Shading f) Protection

What ever the changes will make onto the selected cell by the help of this six option it will be affected onto the area of the sheet, where the user wants to apply it.

Row: - This option will display four-sub option, which is

a) Row height

b) Row hides

c) Row unhide

d) Row auto fit correction

The standard height of row is 12.75.

Column: - This option will display four-sub option, which are

i) Column width

ii) Column hide

iii) Column unhide

iv) Auto fit selection

v) Standard width

The standard height of row is 8.43.

Auto format:-With the help of this option user can automatically format the selected portion of the sheet onto the various style for which user not only get the list of the samples but not also he get the preview of the cell. Now user has to select any particular style of the format then by clicking the ok button, selected area will be automatically formatted.

Goal seek: User can seek a particular goal for a formula, which is referred by some cell reference when this option will be clicked. So that it will display three sub option, which are set cell, to value and change cell.

In the cell box the user can give the cell reference of a formula, into value box user has to type the seek value no. and in the change, cell box user has to give the cell address for another data. Then by clicking the ‘ok’ button the old value will be store.

Sort:-This option will sort the database by taking the corresponding fields, in which the user will define. User has to select the mode of sorting in ascending or discending order.

Filter:-This option will automatically filter the data’s from the worksheet. In which the user specifies the condition when clicking the auto filter option from data menu it will drop the arrow into the right of each field. So , while clicking the particular field it displays a common window from the user can specify the condition. Then by clicking the ‘ok’ button the database will display the data’s which satisfy the condition.

Form:- This option will help the user not only to modify the database but also to expand the save. So working with this option the user has to click on this option from data menu, so it will display the first record of the database where the fields data menu, so it will display the first record of the database where the fields are in row wise. Now it depends upon the user to modify the data for the particular record. As the record is display in the form, so the user can expand it by click the new record option from it.

Pivot table report: - Pivot table is an interactive table that summarizes and analyses data from existing lists, tables and data bases. Use the pivot table report and pivot chart wizard to specify the source data that you want to use. After you create a pivot table report, you can display and organise the data by dragging the fields and items.

Search This Blog