INTRODUCTION TO MICROSOFT EXCEL

Spreadsheet Basics

Discussion

Once accountants want to sum up accounting data of a firm, they use an arrangement, which allows them to work on data presented in a format of rows and columns. Known as a worksheet this arrangement enables them to easily present the information in summary form and perform simple calculations.

Highly interactive computer programs are available that allow you to present and work with DATA in an arrangement of rows and columns. Known as Spreadsheets, these programs started out as electronic versions of accounting worksheets with one major purpose - simple row and column arithmetic.

Microsoft Excel

Discussion

Microsoft Excel is a spreadsheet that belongs to the category of computer programs described above. MS Excel runs under MS Windows and shares the common interface of other Windows particularly MS Office applications

Some of the important applications which spreadsheets are used for include:

·         Financial Modeling: You can create financial models, which allow you to work out "what if" situations. In a spreadsheet changing variables within the model yield differing results. Data can be consolidated automatically to yield summaries at various levels.

·         Excel provides many statistical, analytic and scientific functions, which allow you to analyze numerical data and present findings.

·         Excel provides powerful and flexible presentation tools including charts and graphs.

·         As a data management tool Excel provides an interface which allows easy entry, editing, sorting and filtering of data.

·         Excel can access data in a wide variety of formats and is an ideal tool to analyze and manipulate data.

Discussion

You can start Excel the same way you can start other windows programs from the Start menu on the windows taskbar.

Procedure

1.    Click the Start button on the Windows taskbar.

2.    Point to Programs.

3.    Select Microsoft Excel

The MS Excel Environment

Discussion

As is common with other windows applications Excel provides you with and environment or interface to do your work. When Excel starts what you see on the screen is a rectangular box or window with different parts to it. This is the MS Excel window. It consists of the application window and the workbook window.

the application window

The application window contains the menu bar, toolbars, status bar, and the workbook window. The bars allow you to navigate your way within Excel to perform various tasks

the workbook window

The workbook window opens within the application window when you start Excel, open an existing file, or create a new workbook.

The workbook window contains worksheets, tab scrolling buttons and scroll bars, which allow you to move quickly through a workbook and the Worksheet Area where you enter your data and perform your calculations.

Several windows may be open at the same time.

The Mouse Pointer usually appearing on the screen as an arrowhead is the mouse device used to select commands or actions to be executed. Within the Worksheet area it changes shape within the worksheet area appearing as a cross.

Workbooks and Worksheets

Discussion

Workbooks are files that Excel uses to store data.

Worksheets which are the constructs within workbooks that Excel provides for you to present and work on data

Navigating Excel

Discussion

The bars within the Excel screen/window allow you to access the various tools Excel provides to execute specific actions. It is essential to understand the use of these bars, as they will enable you to work smoothly Navigating within Excel via these bars is a simple task and you can do it easily using the mouse.

title bar

The title bar appears at the top of the application window and contains the name MS Excel and the name of the workbook file you're working on.

menu bar

Right below the title bar is the menu bar showing the command options available as a choice of items. Each item has varying levels of sub menus within it from which you can select actions to carry out.

The menu bar provides access to the many features of Excel. Each menu contains commands grouped by function. Some menu commands are grayed or dimmed, which indicates that the command is not available for the current task.

When you select an item on the menu bar, the corresponding menu appears from which you can select the desired command.

When a menu command is followed by an ellipsis (...), selecting it opens a dialog box in which additional information is entered.

In addition to the standard menus on the menu bar, Excel also contains shortcut menus that may be accessed by clicking the right mouse button. Shortcut menus contain commonly used commands and are context-sensitive. Therefore, the options available on the shortcut menu vary, depending on the area of the window or the object selected.

Procedures

1.    Point to and click an item on the menu bar.

2.    Click on a command to execute it.

toolbars

Next are the Toolbars, which contain shortcuts to the commands. These are displayed as buttons with icons or pictures of the action or item of choice. Each button executes a specific menu command.

There are many toolbars providing a grouping of related commands and word gives you the option of choosing which you want displayed.

When Excel starts the Standard and Formatting toolbars appear by default. The Standard toolbar contains buttons used for many general Excel functions, whereas the Formatting toolbar contains buttons and pull-down lists used to enhance the appearance of text and paragraphs.

You can choose to display one, several, or all the toolbars at any given time, or you can hide all the toolbars. You use the Toolbar submenu on the View menu to display and hide toolbars.

Procedures

1.    Point to and click on the View command on the menu bar

2.    Select and click on the Toolbars command

3.    Select a toolbar to display by checking the box to the left of its name.

Procedures

1.    Point to a blank part of the Formatting toolbar (anywhere away from a button, for example below any button) and double click.

2.    Try moving the floating toolbar across the screen by clicking and dragging the Formatting toolbar's Title Bar.

3.    Anchor the Formatting toolbar to the bottom of the screen by dragging the floating toolbar all the way to the bottom edge of the screen, then release the mouse button.

Procedures

1.    Point to a button on the toolbar and note the ScreenTips

2.    Click on a button to execute the associated toolbar command.

status bar

At the bottom of the Excel window you will find the status bar which gives you information about your location in the workbook.

scroll bars

There are two scroll bars - the vertical scroll bar at the right and the horizontal scroll bar at the bottom of the workbook window. These allow you allow you to quickly move around in a worksheet window. They allow you to move up and down and right and left in the worksheet continuously or scroll through the worksheet.

Exiting Excel

Discussion

When you have finished using Excel, you should exit the application properly, since Excel performs necessary housekeeping before it closes.

If the current document has been modified but not saved, Excel prompts you to save the changes before exiting.

Procedures

1.    Point to and click on the File menu.

2.    Select and click on the Exit command.

USING EXCEL

Workbooks

An Excel document is called a workbook. Workbooks are files that Excel uses to store your data.

Here are some things to keep in mind:

1.    Think of a workbook as a binder.

2.    Think of every worksheet as a page in the binder.

Creating A New Workbook

When you start Excel, a workbook named Book 1 is automatically displayed. Book 1 is a new, unsaved workbook that is ready for input.

There are several ways to create a new workbook.

Procedure

1.    Click on the New button on the Standard toolbar

2.    Hold down the Ctrl key and press N

3.   You can also create a new workbook with the New command from the File menu.

Procedure

1.    Select the File menu

2.    Select the New command

3.    Select General tab

4.    Select Workbook

5.    Click OK

If the first thing you do is open an existing workbook, Book 1 is closed automatically - there's no need to close it on your own.

Worksheets

Discussion

Worksheets are the areas or space in an Excel file or workbook where you enter your data and perform your calculations. A workbook or Excel file can contain many worksheets though the default number is set at three.

cells

The area or space at which a row and column intersects is known as a cell. The cell is the basic element of a worksheet and it is where you enter your data and/or perform various operations on the data.

cell address

You keep track of and reference a cell by its cell address, which consists of the column letter followed by the row number. E.g. the cell address D10 indicates the cell formed at the intersection of column D and row 10 in the worksheet.

active cell

At any time one cell in the worksheet is the Active Cell. A dark border around it identifies the active cell. The active cell is where anything you type will be entered. Its name or cell reference appears in the name box.

You can select any cell in the worksheet to be the active cell by clicking on it. In an Excel worksheet the mouse pointer appears as a cross symbol.

Procedure

1.    Select a cell within the visible area of the worksheet to be the active cell by moving the mouse pointer to that cell, then

2.    Click on the cell you have placed the mouse pointer in.

Moving within a Worksheet

Procedure

1.    To move the visible part of the sheet up or down Click and drag on the vertical scroll bar

2.    To move the visible part of  the sheet to the right or left Click and drag the horizontal scroll bar

A small box appears which tells you the column or row you scroll to. However scrolling does not change the active cell, it simply scrolls the visible area of the worksheet. To change the active cell you must click on a new cell.

Entering and Editing Data

types of worksheet data

Excel allows you to store and work with data in a variety of formats. The cells in the worksheet are where you enter the data. Worksheet cells can contain three distinct types of data - Text, Values and Formulas.

Text consists of any combination of alphanumeric characters i.e. letters, numbers, and other symbols that you can type in at the keyboard. A cell can contain upto 32,000 characters. Text entries are also referred to as labels.

All formulas begin with an = sign

A cell displays the value resulting from the formula entered.

The actual formula is displayed in the formula bar when the cell containing the formula is selected.

Entering Data

Discussion

Entering data is simple. Just select the cell you wish to enter data to and start typing. When finished press Enter.

Procedure

1.    Select the desired cell by clicking on it, i.e. making it the active cell

2.    Type numbers, text or a combination of both to make the cell entry.

3.    To accept your entry press the Enter, the Tab key or click on the check button shown on the Formula Bar.

Editing Data

modifying cell contents

If you make an error in an entry you can easily correct it. You can edit cell contents or entries within the cell or on the Formula Bar.

Procedure

1.    Double click on the cell whose contents you wish to modify or select it and press F2. The insertion point is placed at the end of the cell contents.

2.    Select the cell and then click on the formula bar. The insertion point is placed in the formula bar.

3.    Edit the cell contents as required using basic text editing techniques. Use the Backspace key to delete characters to the left of the insertion point or the Delete key to delete characters to the right of the insertion point.

4.    To accept your entry press the Enter key, the Tab key or click on the check button shown on the Formula Bar.

5.    To abandon your entry press Esc or click on the cross button shown on the formula Bar.

erasing/deleting cell contents

To erase the entry made in a cell, activate it and press Delete.

You can also delete cell contents in the following way.

Procedure

1.    Select the required cell.

2.    Select the Edit, Clear, content menu option.

3.    Select Delete.

replacing cell contents

To replace the contents of a cell with something else just activate the cell and make your new entry. It replaces the previous entry. Formatting however, is not removed.

            undo and repeat

If you make an error during or after an entry and wish to change your entry you can use the Undo feature to reverse it before proceeding.

Procedure

1.    To reverse your last action Select the Edit menu

2.    Select the Undo command.

On the other hand if you would like to repeat the last action you performed, you can use the Edit, Repeat command.

Procedure

1.    Select the Edit menu

2.    Select the Repeat option.

3.    Select F4 or Click on the Redo button on the Standard  toolbar

using autofill

Saving a Workbook

To keep your workbook on disk for future use, you must save it by giving it a unique file name.

save and  save as

Procedure

§  Select the File, Save menu option.

§  Press Ctrl+S, or click on the Save button on the Standard toolbar.

§  Excel will display the Save As dialogue box.

§  Enter the workbook file name into the File name text box.

§  You can set other options like the drive, the folder to save to the right location.

§  Once all save options have been selected, click OK.

If the directory you require is not currently selected, move to the correct directory (folder) by double clicking on the directory name shown in the Directories list box.

 If the directory you are looking for is not in the list, double click on the root directory c:\ to move to the top of the directory tree. Then scroll through and double click on the required directory name(s).

·         Enter a password into the Protection Password text box if you would like to prevent anyone from opening the worksheet unless they know this password.

·         Enter a password into the Write Reservation Password text box if you would like to prevent anyone from saving changes to the worksheet unless they know this password.

WORKING WITH WORKBOOKS

Opening Existing Workbooks 

To perform any operation on an existing workbook i.e. one that has been previously saved to disk you must first open it.

·         You can open an existing workbook by clicking on the Open button on the Standard toolbar

Procedure

1.    Select the File menu.

2.    Select the Open command. This opens the Open dialog box

3.    Select the file you want to open.

4.    Click OK.

The open dialog box gives you many options most of which deal with the location of files.

You can also open your most recently used workbooks by selecting the file names displayed at the bottom of the File menu.

Using Workbooks

multiple workbooks/ windows

Excel allows you to have multiple workbooks open at the same time. This is useful if you want to work with or view data in several workbooks simultaneously.

Excel also allows you to open more than one copy of a workbook at the same time. This is useful when you want to simultaneously view different sets of data residing in one workbook

If you have multiple workbooks or more than one copy of a workbook open each workbook or copy of a workbook resides in an Excel window.

As with other Windows applications, Excel windows can appear on the screen in the following ways:

Maximized - only one workbook is displayed at a time.

Minimized - a small window displaying on the title bar appears

restored to a non maximized size

Navigating Workbooks and Windows

Discussion

When you have more than one workbook open at any time, to work on any or all of them you must be able to navigate between them. Excel allows you several ways of navigating and controlling the open workbook windows. This includes moving, resizing, and switching between windows.

To practice navigating workbooks, first create some empty workbooks after starting Excel.

Procedure

1.    Click once on the New button to create Book 1

2.    Click twice more on the New button to create Book 2 and Book 3.

the active workbook

When you wish to work with any open workbook you must select it first and make it the Active Workbook. You can do this by selecting the desired workbook from the Window menu, which lists the names of all open workbooks at the bottom.

Procedure

1.    Select the Window menu

2.    Click on the name of Book 1, Book 2 or Book 3 to make it the active workbook.

If more than one workbook is visible on the screen clicking anywhere in the desired workbook will make it the active workbook.

This procedure is used to move between windows of a workbook.

Arranging Windows

Discussion

You can arrange open workbooks on the screen in multiple windows so that you can view them. The Arrange option on the Windows menu allows you several different ways of doing this.

Procedure

1.    Select the Windows menu from the menu bar

2.    Select the Arrange option. The Arrange Windows dialog box with four different options of how windows can be displayed appears.

3.    Select any option to see how the windows are arranged under it.

Moving & Resizing windows

Discussion

To move a workbook window,

Procedure

1.    Click on the Title bar of the workbook you want to move and drag it.

2.    Once you have the workbook window in the desired position release the mouse button

To resize a workbook window use the mouse as follows. The mouse pointer changes shape to a double head arrow when pointed at the window borders indicating that you can click and drag to resize.

Procedure

1.   To resize a window click and drag on any of its borders.

2.   To resize a window both horizontally and vertically at the same time, click and drag on any corner.

Closing Windows

When you have finished working with a workbook you can close it to free memory it uses.

·         You can close a workbook from the Menu bar.

 Procedure

1.    Select the File menu

2.    Select the Close command

Enter numbers, text, a date, or a time

¨      Click the cell where you want to enter data.

¨      Type the data and press ENTER or TAB.

¨      Use a slash or a hyphen to separate the parts of a date; for example, type 9/5/96 or Jun-96.

¨      To enter a time based on the 12-hour clock, type a space and then a or p after the time; for example, 9:00 p

Otherwise, Microsoft Excel enters the time as AM.

Note:

            To fill in rows of data in a list, enter data in a cell in the first column, and then press TAB to move to the next cell. At the end of the row, press ENTER to move to the beginning of the next row. If the cell at the beginning of the next row doesn't become active, click Options on the Tools menu, and then click the Edit tab. Under Settings, select the Move selection after Enter check box, and then click down in the Direction box.

¨      To enter today's date, press CTRL+; (semicolon).

¨      To enter the current time, press CTRL+SHIFT+: (colon).

Enter a formula

For information about how formulas calculate values, the following are the rules.

1.         Click the cell in which you want to enter the formula.

2.         Type = (an equal sign).

If you click Edit Formula or Paste Function, Microsoft Excel inserts an equal sign for you.

3.         Enter the formula.

4.         Press ENTER.

Note:

You can enter the same formula into a range of cells by selecting the range first, typing the formula, and then pressing CTRL+ENTER.

You can also enter a formula into a range of cells by copying a formula from another cell. For more information about copying a formula, the following are the rules.

 

Enter the same data into several cells at once

·        Select the cells where you want to enter data.

·        The cells can be adjacent or nonadjacent.

·        Type the data and press CTRL+ENTER.

 

·        Enter or edit the same data on multiple worksheets

·        When you select a group of sheets, changes you make to a selection on the active sheet are reflected in the corresponding cells on all other selected sheets. Data on the other sheets may be replaced.

·        Select the worksheets where you want to enter data.

·        How?

·        Select the cell or cell ranges where you want to enter data.

·        Type or edit the data in the first selected cell.

·        Press ENTER or TAB.

Microsoft Excel automatically enters the data on all selected sheets.

Note:  If you've already entered data on one worksheet, you can quickly copy the data to the corresponding cells on other sheets. Select the sheet that contains the data and the sheets to which you want to copy the data. Then select the cells that contain the data you want to copy. On the Edit menu, point to Fill, and then click Across Worksheets.

 

Select sheets in a workbook

If you select more than one sheet, Microsoft Excel repeats the changes you make to the active sheet on all other selected sheets. These changes may replace data on other sheets.

To select          Do this

A single sheet Click the sheet tab.

Two or more adjacent

Sheets           Click the tab for the first sheet, and then hold down SHIFT and click the tab for the last sheet.

Two or more

Nonadjacent sheets Click the tab for the first sheet, and then hold down CTRL and click the tabs for the other sheets.

·        All sheets in a workbook

·        Right-click a sheet tab, and then click Select All Sheets on the shortcut menu.

 

Quickly fill in repeated entries in a column

If the first few character you type in a cell match an existing entry in that column, Microsoft Excel fills in the remaining characters for you. Microsoft Excel completes only those entries that contain text or a combination of text and numbers; entries that contain only numbers, dates, or times are not completed.

To accept the proposed entry, Press ENTER.

The completed entry exactly matches the pattern of uppercase and lowercase letters of the existing entries.

To replace the automatically entered characters, continue typing.

To delete the automatically entered characters, press BACKSPACE.

You can also select from a list of entries already in the column. To display the list, press ALT+DOWN ARROW to display the list, or right-click the cell, and then click Pick from List on the shortcut menu.

 

Excel is a spreadsheet program - a program designed to work with numbers (as opposed to a word processor, such as word, which is designed to work with words of text data) we need to work with data and enter it in cells arrayed into horizontal rows and vertical columns on a worksheet, an arrangement some what reminiscent of an accountant's ledger but far more flexible.

Copying and moving the data

For copying the data the following is the command:

Click Edit

Then select copy command

For pasting the document

Click Edit

Then paste command.

For cutting the document the same is the rule but the difference is that you will have to select the cut command from the Edit menu.

N.B. Before pasting the document, you will have to select the area i.e. insertion point to be placed there.

Keystrokes:

For copy : Ctrl+C

For paste : Ctrl+V

For cut : Ctrl+X

Find and Replace

You can find any document by selecting Find command from the Edit menu and you can replace it from the same menu. This command is helpful for updating the document you prepared. A list of texts may be replaced by using this command.

Shortcut keys:

Ctrl+F for finding the document  and for replacing the document you can press by using the keystroke Ctrl+H

Fill command :

You can fill a list of items by selecting the cell from the edit menu. You can fill the text to the right, left, down, AutoFill and according to series in different forms like linear and growth types. This command is helpful for updating the document.

Keystrokes:

For fill the document you can select the keystrokes: Ctrl +D

Clear the documents

If you feel difficult to delete the text, if the text area is formatted by Pivot table or any graphic objects like object linking and embedding ( OLE) or any complicate arises; you can delete the formatted text by using Clear command. In clear command, you will find contents, format and all.

Contents: specifies the text as selected

Format: Removes all formatted text, graphics/charts and number.

All: removes all the contents in the text area