Microsoft Excel 2007 and 2010
Step by Step
Tutorial specially developed for the persons with no or low vision
Written by Ms. Bharti Kalra, Ms. Ranjana Rana and Mr. Yogesh Taneja
Produced and published by National Association for the Blind, Sec-V RK Puram, New Delhi-110022
1ST addition, August 2014
JAWS version: 13.0
For any suggestion/feedback related to this book please send mail to
Table of contents (TOC)
1. INTRODUCTION TO EXCEL
1.1 What is an Electronic Spreadsheet
1.2 How to start Excel
1.3 Excel Working Environment
1.4 Excel Application Window
1.5 The Worksheet
1.6 Moving around in a Worksheet
1.7 Selecting Multiple Cells
1.8 Exiting Excel
2. Getting Started
2.1 Entering Data
2.2 Saving a Worksheet
2.3 Closing Files
2.4 Opening Files
3. Editing the Worksheet
3.1 Editing Data
3.2 Undoing Changes
3.3 Removing Data
3.4 Copying and Pasting Data Between Cells.
3.5 Using Cut and Paste.
3.6 Inserting Rows & Columns
3.7 Deleting Rows and Columns
4. WRITING FORMULAS
4.1 Using References
4.2 Writing Formulas
4.3 Creating Simple Formulas
4.4 Order of Evaluation
4.5 Using Cell Addresses in Formulas
4.6 Copying Formulas
4.7 Totaling Rows and Columns Automatically
5. Formatting Data
5.1 Formatting Data
5.1. Increasing column width and row height
5.1.1 Adjusting Column Width
5.1.2 Adjusting Row Height
5.2 Hide and Unhide Feature:
5.2.1 Hiding Columns/ rows/sheet/particular range
5.2.2 Unhiding Columns/ rows/sheet/particular range
5.3 Renaming a Sheet
5.4 Font Dialog Box
5.4.1 Aligning Worksheet Data
5.4.2 Formatting Fonts
5.4.3 Adding Borders and Colors
5.4.4 Adding Number Formats
6. Printing & Other Topics
6.1 Previewing Your Worksheet
6.2 Page Setup
6.2.1 Adding margins
6.2.2 Choosing paper size
6.2.3 Defining print area
6.2.4 Header & Footers
6.3 Printing a Worksheet
6.4 Copying and Pasting Cells for Special Results
6.5 Creating Formulas with Relative and Fixed References
6.6. Working with Sheets
6.6.1 Removing Sheet
6.6.2 Adding Sheets
6.6.3 Renaming Sheet
7. Charting Your Data
7.1 What are Charts
7.2 Selecting the Chart type
Appendix A: Excel Application keystrokes
Appendix B: JAWS commands
Informational Keystrokes for Columns
Informational Keystrokes for Rows
Table Layer Keystrokes
Miscellaneous Informational Keystrokes
Navigation Keystrokes
Selection Keystrokes
Configuration Keystrokes
1. INTRODUCTION TO EXCEL
This chapter covers following topics;
- What is an Electronic Spreadsheet
- How to start Excel
- How to navigate through the Excel working environment.
- Components to a worksheet
- Exiting Excel
1.1 What is an Electronic Spreadsheet
An electronic spreadsheet is an electronic replacement for the traditional financial modeling tools i.e. 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. As the name suggests it is a product of MICROSOFT the maker of WINDOWS operating systems.
1.2 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
1.3 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, 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.
1.4 Excel Application Window
The Excel application window is like a program manager. It provides you with tools, commands, and status messages to use with your worksheets.
The elements displayed are as follows;
1.4.1 Title Bar: It is the bar at the top of the application window. It displays the name of the application - Microsoft Excel. INSER+T can be used to read the title bar.
1.4.2 ribbon tab:Moving a ruler down one notch from the title bar replicates the ribbon tabs. Each ribbon within Excel has a ribbon tab. Focusing on a tab opens the associated ribbon.
The items on the ribbon tab are as follows:
Home tab, insert tab, page layout tab, formulas tab, review tab, view tab.Ribbon can be accessed by both mouse and keyboard. To access the ribbon with the keyboard use ALT key or F10 key and then use the tab keys to move to a particular ribbon choice. The hot keys for the different ribbon items can also be used. Note that the hot key for ribbon items is the letter underlined in their names as shown in the name of ribbon items above.
Note: the behavior can be different if “Use virtual Ribbon” checkbox is checked from JAWS settings.
1.4.3 Quickaccess toolbar:It is the bar below the ribbon. You can click on its tool buttons to choose frequently used commands and utilities.
1.4.4 Formatting toolbar:It is the bar below the Quickaccesstoolbar. You can click on its tool buttons to choose frequently used formatting commands.
1.4.5 Formula bar: It is the bar below the formatting toolbar. It displays the contents of the currently active worksheet cell.
1.4.6 Status bar: It is the bar at the bottom of the application window. It displays information about the currently selected command and the current status of the workspace. INSERT + PAGEDOWN is used to read the status bar.
1.4.7 Document Window: The Excel document window displays the currently active worksheet and the workbook in which it is contained. The elements are displayed as follows:
- Title bar: The bar at the top of the document window. It displays the name of the workbook (book 1).
- 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.
- 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.
- 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. The keyboard options for scrolling on a given worksheet are given in the next section.
- Column headings: The letters at the top of the worksheet column.
- 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 & sheet 3. You can enter data in more than one sheet at a timeby switching between sheets. useCTRL+ PAGEUP to move to previous sheet or CTRL + PAGE DOWN to move to the next sheet.
1.5 The Worksheet
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 16,384 columns and 1,048,576 rows.
Columns headings are designated by alphabets - beginning with letter A and continue through the letter z. After the 26th column, headings become double letters that is AA through XFD.
Rows are designated by numbers running down the left border. So the first row is called row 1 and the last row is row 1,048,576.
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 command INSERT+C.
1.6 Moving around in a Worksheet
To navigate around in a sheet use below mentioned commands;
- 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
- Start of worksheet --CTRL+HOME
- Move to one screen left-- ALT+PAGEUP
- Move to one screen right-- ALT+PAGE DOWN
1.7 Selecting Multiple Cells
You can select several cells at once by holding down the Shift while you click the mouse on the cells.To select several adjacent cells do the following;
- Select the first cell in the set and 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.
- You can use CTRL+SPACEBAR and SHIFT+SPACEBAR to select a complete column/row respectively.
1.8 Exiting Excel
For closing an excel workbook, go to office button button/file tab by pressing ALT+F. By using UP/DOWNARROW locate the exit option and press enter on it to exit the excel. Alternatively ALT+F4 can be used to exit the excel.
Note: it will ask you to save changes and you have to act accordingly.
,,,,,,,,,,
2. Getting Started
This chapter deals with following topics;
- Entering data
- Saving Files
- Closing Files
- Opening Files
2.1 Entering Data
You can enter text, numbers, and formulas into any cell on a worksheet. You simply select a cell (place your cursor in a cell) and type the data.
Follow these guidelines when entering the numbers;
- commas can be included 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 will be ignored.
- Precede negative numbers with a minus sign or enclose them within parentheses.
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 separate chapter.
2.2 Saving a Worksheet
To save any worksheet , one will have to save the entire workbook which contains the worksheet.
Go to office button button/file tab.Choose Save option by UP/DOWNARROW and press enter.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 dialog box as seen in MS WORD or in any other office application.
2.3 Closing Files
To close the file, go to office button button/file tab buttonand choose the Close command. The keyboard shortcut is CTRL+F4 or CTRL+W.
2.4 Opening Files
Go to Office Button Button/File tab, select Open option by using UP/DOWNARROW and hit enter. The dialogue box with relevant fields will get opened. Tab to lookin: combo box and select the drive where you have saved your file. Then select appropriate file from the list and hit enter on open button. Alternately CTRL+O can also be used to bring open dialogue box.
,,,,,,,,,,
3. Editing the Worksheet
After learning saving and opening excel files, lets start how to enter and edit the data. This chapter covers following topics;
- Editing Data
- Undoing Changes
- Removing Data
- Copying and Pasting Data
- Moving Data
- Inserting & DeletingRows/Columns/Cells
3.1 Editing Data
Editing data means making changes to an existing data. Entering/editing data is littlebit different in Excel than entering/editing text in MS Word. An excel cell can be used in 2 modes Overwrite and edit mode. If you will simply place your cursor in a cell for example A1 and start typing, the previous text will be overwritten with the newly written text. If you want to make changes in the text of a cell, then you have to press F2 to change the mode of a cell to edit mode. You can use LEFT/RIGHTARROW to navigate in the text of a cell at the time of edit mode.
3.2 Undoing Changes
You can always correct mistakes which you are typing 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 activating undo command using the keystroke CONTROL + Z .If you decide that you want to keep the change after all, you can use the Redo command(CONTROL + Y) The name of the Undo command changes to reflect the specific action that can be undone.
Special consideration: If you have issued save command, the undo command will not work.
3.3 Removing Data
If data needs to be removed from a cell, you can easily delete the information and replace it with 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 in the home tab.When you enter data in the cells, you are not locked in to the arrangement. You can always rearrange data if you want to do. you could copy and paste data between cells, insert and delete cells, rows and columns, and even move data between cells.
3.4 Copying and Pasting Data Between Cells.
Before copying data it is essential to select the cells which contain the data to be copied. After selecting the cells, issue the copy command from home tab>Clipboard Submenu>copy.To paste the selected data reach the desired cell and issue paste command available under home tab>Clipboard Submenu.Alternately,the shortcut CTRL+C and CTRL+V can also be used to perform copy and paste commands respectably.
3.5 Using Cut and Paste.
Cut and Paste commands is much like copy and paste 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, else the option of cut command can be found inside the home tab>Clipboard Submenu.
3.6 Inserting Rows & Columns
Excel gives you the facility to insert blank rows or columns IN BETWEEN THE ROWS AND COLUMNS CONTAINING DATA.
For inserting a blank row go to Home tab and press tab until you reach to Insert split button and press enter. By using UP/DOWNARROW Select Insert sheet row and press enter.
Note: the blank row will be inserted above the row in which your cursor is positioned.
For inserting a blank column go to Home tab and press tab until you reach to Insert split button and press enter. By using UP/DOWNARROW Select Insert sheet column and press enter.
Note: Blank column will be inserted on the left of the colun where your cursor is positioned.
When you want to insert only a few cells, go to home tab and press tab to reach to insert split button and hit enter. By using UP/DOWNARROW select insert cells and press enter. A dialogue box will open with following radio buttons:
•Shift cells down.
•Shift cells up.
•Shift entire row.
•Shift entire column.
Select appropriate radio button and hit enter on Okay button by navigating to it with the help of TAB key.
For inserting a blank sheet go to home tab and tab to insert split button and hit ENTER. By using UP/DOWNARROW select insert sheet and press enter to insert a blank sheet. Alternately keystroke SHIFT+F11 also can be used.
Note: The new sheet will get inserted above the sheet in which your cursor is positioned.
3.7 Deleting Rows and Columns
Option for deleting cells/columns/rows/sheet can be found inside the hoem tab>delete split button. You can select the desired option by using UP/DOWNARROW and hit enter.
Note: please remember that selection of cells/column/rows is essential before deleting. For selecting a column/row you can use keystroke CTRL+SPACEBAR/SHIFT+SPACEBAR respectively.
,,,,,,,,,,
4. WRITING FORMULAS
Till now you have learnt that how we can enter or edit data in Excel. After going through this chapter you will be able to do following;
- Using References
- Writing Formulas
- Creating Simple Formulas
- Order of Evaluation
- Using Cell Addresses in Formulas
- Copying Formulas
- Totaling Cell Addresses Automatically
4.1 Using References
With references, you can identify cells or groups of cells on a worksheet.Range (colon): Produces one reference to the cells enclosed between a rectangular area using the cell addresses of the two cells at the edges. It is written as (Top Cell Address : Bottom Cell Address)
For example, if you want to refer to cells A1, A2, A3 & A4 then the range will be written as (A1:A4) Similarly, if cells A1, A2, A3, B1, B2 & B3 are to be referred, the range will be written as (A1:B3)
4.2 Writing Formulas
You can perform calculations with your data using formulas, which are made up of data operators and often functions. Microsoft Excel offers you a variety of functions that makes calculations easier for you.