MS Excel: A popular choice amongst spreadsheet packages. We attempt at explaining the various features of Microsoft Excel, such as: worksheet, selecting multiple cells, entering data, editing the worksheet and creation of formulas.
Keeping in view the needs of visually impaired users, care has been taken to adopt a keyboard approach to all tasks although the mouse options have also been mentioned.
The tutorial has been divided into two parts - a basic introduction (given below) and advanced formatting, which will be unveiled in the time to come.
• The screen reading software referred to in this document is JAWS for Windows 4.0.
• The menu commands and other features explained here are as found in Excel 9.0 of Microsoft Office 2000.
• The screen reader keystrokes have been prefixed with ‘Screen reader command’ to distinguish them from the application keystrokes.
- Introduction to MS-Excel
What is an electronic spreadsheet
An electronic spreadsheet is an electronic replacement for the traditional financial modeling tools, that is, the accountant's columnar pad, pencil, and calculator. These offer dramatic improvements in creating, editing and recalculations. Among the various spreadsheet packages available Microsoft Excel is a popular choice. It is a part of a bigger package of applications called Microsoft Office.
How to start Excel
Before you start Excel, ensure that Excel is installed on your machine. Its icon is generally found in the PROGRAMS sub menu (inside the START menu). At times it may be inside the Microsoft office group inside the PROGRAMS menu. Press enter or double-click the application named Microsoft Excel.
Excel working environment
When you start Excel, two windows appear on the screen, one nested snugly within the other. The larger of these is called the application window, which frames the entire screen: you use it to communicate with the Excel program. The smaller window, called the document window, is used to create and edit Excel worksheets and charts.
Excel application window
The Excel application window is like a program manager. It provides you with tools, commands, and status messages to use with worksheets.
The elements displayed are as follows:
1. Title Bar: The bar at the top of the application window. It displays the name of the application - Microsoft Excel. Screen reader command: Insert + T, reads the title bar.
2. Menu Bar: The bar below the title bar which is used to choose commands from Excel's drop-down menu system.
The items on the menu bar are as follows:
File Edit View Insert Format Tools Data Window Help
Menu bar can be accessed by the mouse or keyboard. To access the menu bar with the keyboard activate the menu using the ALT key or F10 function key and then use the arrow keys to move to a particular menu choice. The hot keys for the different menu items can also be used. Note that the hot key for menu items is the letter underlined in their names.
3. Standard toolbar: The bar below the menu bar. You click on its tool buttons to choose frequently used commands and utilities.
4. Formatting toolbar: The bar below the standard toolbar. You click on its tool buttons to choose frequently used formatting commands.
5. Formula bar: The bar below the formatting toolbar. It displays the contents of the currently active worksheet cell.
6. Status bar: The bar at the bottom of the application window. It displays information about the currently selected command and the current status of the workspace. Screen reader command: Insert + Page Down, reads the status bar.
The Excel document window displays the currently active worksheet and the workbook in which it is contained.
The elements displayed are as follows:
1. Title bar: The bar at the top of the document window. It displays the name of the workbook (book 1).
2. Worksheet tabs: The tabs at the bottom of the document window. You use these to select a different worksheet within the current workbook. Control + Page Up or Page Down, can be used to scroll between the different worksheets in a workbook.
3. Worksheet-tab scroll buttons: The set of four boxes to the left of the worksheet tabs. You use these to scroll through the worksheet tabs.
4. Scroll bars: The vertical and the horizontal scroll bars framing the right and lower borders of the document window. You use the scroll bars, along with the scroll boxes and scroll arrows they contain, to change which portion of the worksheet is displayed in the document window.
5. Column headings: The letters at the top of the worksheet column.
6. Row headings: The number at the left of each worksheet row.
In Microsoft Excel, files are called workbooks. When you open a new workbook, you have 3 blank sheets named Sheet1, Sheet 2 and sheet 3. You can enter data in more than one sheet at a time, switch between sheets. You can switch sheets with the keyboard using, Control + Page Up, to move to previous sheet or Control + Page Down, to move to the next sheet.
An Excel worksheet is an electronic version of a paper spreadsheet. You arrange your data in rows and columns. A worksheet consists of a grid of 256 columns and 65,536 rows.
Columns headings are designated by alphabets - beginning with letter A and continue up to the letter z. After the 26th column, headings become double letters that is AA up to I V.
Rows are designated by numbers running down the left border. So the first row is called row 1 and the last row is row 65,536.
The intersection of a column and row is called a cell. For example, cell A1 is the intersection of the first column with the first row. Data is entered directly in the currently active cell. An active cell is the one that is outlined by a dark border. Only one cell can be active at a time. The name of the active cell appears in the cell-reference area on the far left of the formula bar. The screen reader reads out the address of the cell as one moves between cells using the ARROW keys. The cell address of active cell can be read by the Screen reader command: INSERT + C.
top of page
Moving around in a worksheet
Left one cell -- Left arrow Right one cell -- Right arrow Up one cell -- Up arrow Down one cell -- Down arrow Up one screen -- Page up
Down one screen -- Page down
First cell in a row that contains data -- Control + LEFT ARROW Last cell in a row that contains data -- Control + RIGHT ARROW Start of worksheet -- Control + HOME
Screen reader command for: Move to prior screen in spreadsheet Alt + Page Up Move to next screen in spreadsheet Alt + Page Down
Selecting multiple cells
You can select several cells at once by holding down the Shift key while you click the mouse on the cells.
To select several adjacent cells
• Select the first cell in the set.
• Hold down Shift key.
• Press repeatedly the arrow key in the direction of the last cell in the set to be selected or simply select the first cell and drag to the last cell with the mouse.
• For selecting entire columns and rows use Select column: Control + Spacebar Select row: Shift + Spacebar Exiting Excel
When you finish an Excel work session and want to exit Excel, you can do either of the following:
Open the File menu to display the drop-down menu. In this menu, click or press ENTER on EXIT.
2. Getting started
You can enter text, numbers, and formulas into any cell on a worksheet. You simply select a cell and type the data.
Follow these guidelines when entering the numbers. You can include commas in numbers such as 1,000. A single period in a numeric entry is treated as a decimal point. Plus sign entered before a number are ignored. Precede negative numbers with a minus sign or enclose them within parentheses.
Text can include alphabets, numbers or special characters.To enter numbers as text, precede the entry with an apostrophe as follows
:'244 entry will be treated as text and not number.
When you need to perform calculations in Excel, you use a formula. You can create formulas by preceding the entry with an equal sign (=). This will be discussed in the advanced tutorial.
Excel allows you to enter date or time. Use the following guidelines when entering date or time:
If you want to display time using the 12 hour clock, type am or pm; for example: 3.00 pm or 3, 00 am. Unless you type am or pm Excel automatically displays the time using 24 hour clock.
You can type date and time in the same cell, but do separate the date and time with a space.
To enter dates, use either a / or – as separator.
The following keyboard shortcuts can be used to insert current date or time in the default format.
Date stamp Control + ; (SEMICOLON) Time stamp Control + SHIFT+; (SEMICOLON)
To save any work sheet, you will have to save the entire workbook in which the worksheet is contained.
• Select the File menu.
• Choose the Save As option
• Type a file name (let's call it First) and save it in the desired location. Note that excel provides the same type of Save As dialog box as seen in MS Word or in any other office application.
To close the file, select the file menu and choose the Close command. The keyboard shortcut is ALT + F4.
To open an existing worksheet, Click the open button on the tool bar Or Select the open option from the file menu.
Then,Select the appropriate drive and directory from the LOOK IN combo box.Select the file name from the file list and choose OK.
3. Editing the Worksheet
Editing data is making changes to the existing data. You can edit data in two places on a sheet.
You can select the cell and edit data in the formula bar OR you can edit the data right in the cell.
For example, let us assume that the cell D6 contains the figure 26,500. To change it to 25,600; use arrow keys to select cell D6.Now there are two ways of editing data in a cell:
• The first one is the overwrite mode in which you type the complete new data and the old data in the cell gets replaced by it.
• The second method is to make desired changes in the contents of the cell by going into the Edit mode. Press F2, the selected cell then comes into edit mode and then the insertion pointer can be moved around the data in the cell. Now place the insertion point at 6. Type 5, and delete the additional 5 after
6.To edit the data using mouse, you simply double-click the cell and then move the insertion point to wherever you need it in the cell. You can then either type the new information or delete the information that is incorrect.
You can always correct mistakes that you have typed by pressing the Delete key and retyping the correct letters or numbers. But what if you select a cell and clear it or type over the contents of a cell by mistake. You can recover from such mistakes by:
Using the Undo command on the Edit menu or its keyboard command: Control + Z OR Undo button on the toolbar.
If you decide that you want to keep the change after all, you can use the Redo command on the Edit menu (Control + Y) or use the Redo button.
The name of the Undo command changes to reflect the specific action that can be undone.
In some instances, the Undo command is not available, for example after you issue a File Save command.
If data needs to be removed from a cell, you can easily delete the information and replace it with the new information. You can also remove data from a cell by selecting the cell and pressing Delete.
Data can be also erased by selecting the Clear command on the Edit menu.
When you enter data in the cells, you are not locked in to the arrangement. You can always rearrange data if you need to that is you could copy and paste
data between cells, insert and delete cells, rows and columns, and even move data between cells.
Copying and pasting data between cells
If you need to copy information to another place on the worksheet, you can either;
Use the mouse to drag the data to a new location OR Use Copy and Paste buttons on the toolbar OR Copy and Paste commands on the Edit menu.
Using copy and paste
Before copying data it is essential to select the cells which contain the data to be copied. After selecting the cells, select the Copy command from the Edit menu. A moving dotted line, the marquee, surrounds the selected cells.
You can use keyboard shortcuts for both the Copy and Paste commands.
To copy data: Hold down Control key and press C.
To paste data: Hold down Control key and press V.
If you need to copy given ranges to multiple locations in a worksheet choose the paste command or use the, Control + V combinations. The marquee remains active, signaling that you can paste the selected data in as many locations as you want. To complete the command, press Enter when you paste the data for the last time, or press Escape, to remove the marquee.
Moving data between cells
Just as you can copy data by dragging with the mouse or by using menu commands, you can also move data using the Cut and Paste commands.
Using cut and paste
Cutting and Paste with commands is much like copying and pasting with a twist: You use the Cut command instead of the copy command, and you move data rather than duplicate it. The shortcut command is Control + X.
Inserting rows and columns
Excel gives you the facility to insert blank rows or columns. To insert a new row above a row, follow these steps:
1. Select the row above which the new row is to be inserted by clicking its header or pressing Shift + Spacebar.
2. Choose the Row command from the Insert menu. The rows move down, leaving a new, blank row.
Excel makes it as easy to insert new columns as it is to insert new rows in your worksheets. The steps followed for inserting a column are the same as that for inserting a row. Instead of selecting the row header select the column header to the right of the location where you need the new column. Alternatively, press Control + Spacebar, from the keyboard. Then choose the column command from the Insert menu.
When you want to insert only a few cells, simply select the same number of cells below or to the right of the location where you need new cells. Then choose the Cells command from the Insert menu. Excel shifts other cells in the worksheet to make room for the new cells. However, you must first choose from the dialog box, that opens on giving the command between shifting the selected cells to the right and shifting the selected cells down.
Deleting rows and columns
Deleting rows and columns is as straightforward as inserting them. To delete a column:
1. Select the column header of the column to be deleted or select that column by pressing, Control + Spacebar
2. Choose the Delete command from the Edit menu.
To delete a row, follow step 2 after selecting the row.
The Delete command on the Edit menu works in much the same manner as the Insert command. To delete the cell range for example; A4: A 12, follow these steps:
• Select the range A4:A12
• Choose the Delete command from the Edit menu.
• Confirm whether to Shift Cells left or to Shift Cells Up in the dialog box that opens.
• Click OK, or press Enter.
Source: N.A.B., New Delhi