1. INTRODUCTION TO EXCEL



Microsoft Excel 2007 and 2010Step by StepTutorial specially developed for the persons with no or low visionWritten by Ms. Bharti Kalra, Ms. Ranjana Rana and Mr. Yogesh TanejaProduced and published by National Association for the Blind, Sec-V RK Puram, New Delhi-1100221ST addition, August 2014JAWS version: 13.0For any suggestion/feedback related to this book please send mail to Yogesh@nabdelhi.inTable of contents (TOC) TOC \o "1-4" \h \z \u 1. INTRODUCTION TO EXCEL PAGEREF _Toc395018835 \h 51.1 What is an Electronic Spreadsheet PAGEREF _Toc395018836 \h 51.2 How to start Excel PAGEREF _Toc395018837 \h 51.3 Excel Working Environment PAGEREF _Toc395018838 \h 51.4 Excel Application Window PAGEREF _Toc395018839 \h 51.5 The Worksheet PAGEREF _Toc395018840 \h 71.6 Moving around in a Worksheet PAGEREF _Toc395018841 \h 71.7 Selecting Multiple Cells PAGEREF _Toc395018842 \h 71.8 Exiting Excel PAGEREF _Toc395018843 \h 82. Getting Started PAGEREF _Toc395018844 \h 82.1 Entering Data PAGEREF _Toc395018845 \h 82.2 Saving a Worksheet PAGEREF _Toc395018846 \h 82.3 Closing Files PAGEREF _Toc395018847 \h 92.4 Opening Files PAGEREF _Toc395018848 \h 93. Editing the Worksheet PAGEREF _Toc395018849 \h 93.1 Editing Data PAGEREF _Toc395018850 \h 93.2 Undoing Changes PAGEREF _Toc395018851 \h 103.3 Removing Data PAGEREF _Toc395018852 \h 103.4 Copying and Pasting Data Between Cells. PAGEREF _Toc395018853 \h 103.5 Using Cut and Paste. PAGEREF _Toc395018854 \h 103.6 Inserting Rows & Columns PAGEREF _Toc395018855 \h 103.7 Deleting Rows and Columns PAGEREF _Toc395018856 \h 114. WRITING FORMULAS PAGEREF _Toc395018857 \h 114.1 Using References PAGEREF _Toc395018858 \h 124.2 Writing Formulas PAGEREF _Toc395018859 \h 124.3 Creating Simple Formulas PAGEREF _Toc395018860 \h 124.4 Order of Evaluation PAGEREF _Toc395018861 \h 134.5 Using Cell Addresses in Formulas PAGEREF _Toc395018862 \h 144.6 Copying Formulas PAGEREF _Toc395018863 \h 154.7 Totaling Rows and Columns Automatically PAGEREF _Toc395018864 \h 155. Formatting Data PAGEREF _Toc395018865 \h 155.1 Formatting Data PAGEREF _Toc395018866 \h 165.1. Increasing column width and row height PAGEREF _Toc395018867 \h 165.1.1 Adjusting Column Width PAGEREF _Toc395018868 \h 165.1.2 Adjusting Row Height PAGEREF _Toc395018869 \h 165.2 Hide and Unhide Feature: PAGEREF _Toc395018870 \h 175.2.1 Hiding Columns/ rows/sheet/particular range PAGEREF _Toc395018871 \h 175.2.2 Unhiding Columns/ rows/sheet/particular range PAGEREF _Toc395018872 \h 175.3 Renaming a Sheet PAGEREF _Toc395018873 \h 185.4 Font Dialog Box PAGEREF _Toc395018874 \h 185.4.1 Aligning Worksheet Data PAGEREF _Toc395018875 \h 185.4.2 Formatting Fonts PAGEREF _Toc395018876 \h 195.4.3 Adding Borders and Colors PAGEREF _Toc395018877 \h 195.4.4 Adding Number Formats PAGEREF _Toc395018878 \h 206. Printing & Other Topics PAGEREF _Toc395018879 \h 216.1 Previewing Your Worksheet PAGEREF _Toc395018880 \h 216.2 Page Setup PAGEREF _Toc395018881 \h 226.2.1 Adding margins PAGEREF _Toc395018882 \h 226.2.2 Choosing paper size PAGEREF _Toc395018883 \h 226.2.3 Defining print area PAGEREF _Toc395018884 \h 236.2.4 Header & Footers PAGEREF _Toc395018885 \h 246.3 Printing a Worksheet PAGEREF _Toc395018886 \h 256.4 Copying and Pasting Cells for Special Results PAGEREF _Toc395018887 \h 256.5 Creating Formulas with Relative and Fixed References PAGEREF _Toc395018888 \h 266.6. Working with Sheets PAGEREF _Toc395018889 \h 276.6.1 Removing Sheet PAGEREF _Toc395018890 \h 276.6.2 Adding Sheets PAGEREF _Toc395018891 \h 276.6.3 Renaming Sheet PAGEREF _Toc395018892 \h 277. Charting Your Data PAGEREF _Toc395018893 \h 287.1 What are Charts PAGEREF _Toc395018894 \h 287.2 Selecting the Chart type PAGEREF _Toc395018895 \h 28Appendix A: Excel Application keystrokes PAGEREF _Toc395018896 \h 29Appendix B: JAWS commands PAGEREF _Toc395018897 \h 31Informational Keystrokes for Columns PAGEREF _Toc395018898 \h 31Informational Keystrokes for Rows PAGEREF _Toc395018899 \h 31Table Layer Keystrokes PAGEREF _Toc395018900 \h 32Miscellaneous Informational Keystrokes PAGEREF _Toc395018901 \h 32Navigation Keystrokes PAGEREF _Toc395018902 \h 33Selection Keystrokes PAGEREF _Toc395018903 \h 33Configuration Keystrokes PAGEREF _Toc395018904 \h 341. INTRODUCTION TO EXCELThis chapter covers following topics;What is an Electronic SpreadsheetHow to start ExcelHow to navigate through the Excel working ponents to a worksheetExiting Excel1.1 What is an Electronic SpreadsheetAn 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 ExcelBefore 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 EnvironmentWhen 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 WindowThe 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 Quick access 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 Quick access toolbar. 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 time by switching between sheets. useCTRL+ PAGEUP to move to previous sheet or CTRL + PAGE DOWN to move to the next sheet.1.5 The WorksheetAn 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 WorksheetTo navigate around in a sheet use below mentioned commands;Left one cell -- Left arrowRight one cell --- Right arrowUp one cell --- Up arrowDown one cell -- Down arrowUp one screen -- Page upDown one screen -- Page downStart of worksheet --CTRL+HOMEMove to one screen left-- ALT+PAGEUPMove to one screen right-- ALT+PAGE DOWN1.7 Selecting Multiple CellsYou 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 ExcelFor 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 StartedThis chapter deals with following topics;Entering dataSaving FilesClosing FilesOpening Files2.1 Entering DataYou 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 WorksheetTo 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 FilesTo 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 FilesGo 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 WorksheetAfter learning saving and opening excel files, lets start how to enter and edit the data. This chapter covers following topics;Editing DataUndoing ChangesRemoving DataCopying and Pasting DataMoving DataInserting & Deleting Rows/Columns/Cells3.1 Editing DataEditing 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 ChangesYou 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 DataIf 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 & ColumnsExcel 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 ColumnsOption 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 ReferencesWriting FormulasCreating Simple FormulasOrder of EvaluationUsing Cell Addresses in FormulasCopying FormulasTotaling Cell Addresses Automatically4.1 Using ReferencesWith 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.4.3 Creating Simple Formulas To tell Excel that you're entering a formula in a cell, you must begin the entry with an equal sign (=). Lets create a simple formula with adds the value 25 and 35. In a blank cell let’s say A1 enter = 25 + 35 After entering the formula, press the Enter key for Excel to accept the formula. The result 60 will appear in A1. The formula however is displayed in the formula bar. While navigating around in a worksheet, whenever the pointer selects a cell which has a formula, the screen reader announces this message. The formula can be read by giving the command CTRL+INSERT+F2. (in case of jaws 12 and below use CTRL+F2) Your formula can use any of the numeric operators : plus (+) for addition, minus (-) for subtraction, asterisk (*) for multiplication, and slash (/) for division. Other kinds of numeric operators include percent (%) which divides the preceding value by 100, and caret (^) which multiplies the preceding value by itself the number of times specified by the following value .For example, if you type = 23 ^ 3 in cell A1 and press enter, Excel multiplies 23 by itself there times and displays the result : 12167 in the cell.4.4 Order of Evaluation Excel performs a rigid set of rules, known as order of evaluation, when performing multiple computations in a single formula. Table 4.1 lists the Microsoft Excel numeric operators in order to evaluate, from first to last. When two operators have equal precedence (for example, division and multiplication), Excel performs the computations from left to right Table containing operators and their functionsOperatorFunctionNegation%Percentage^Exponentiation/,*Division, Multiplication+,-Addition, Subtractionlet's take a look at this example 84 * 0.7 ^ 2 /24 * 3.26 - 29 Stepwise evaluation 1. Exponentiation = 84 * 0.7 ^ 2 /24 * 3.26 - 29 2. Multiplication = 84 8 0.49 / 24 * 3.26 - 29 3. Division = 41.16 / 24 * 3.26 - 29 4. Multiplication = 1.715 * 3.26 - 29 5. Subtraction = 5.5909 - 29 6. Result = -23.4091 You can override Excel's order of evaluation by enclosing parts of the formula in parentheses. Excel then performs enclosed computations before proceeding with the usual order of evaluation. For example = (19+2) / 3 The sequence of evaluation will be as follows :1. Addition = (19+2) / 32. Division = (21) / 33. Result = 74.5 Using Cell Addresses in Formulas So far, the formulas we have used consist solely of values and operators. Formulas can also refer to the other cells. For example, the formula = A1 + A2 + A3 returns the sum of the values in cells A1,A2,A3Excel offers a variety of functions that take a lot of the hard work out of creating and entering formulas. Refer back to the above formula. Instead of typing = A1+ A2 + A3, you could use the SUM function i.e = SUM (A1:A3)for finding out the maximum value in a range of cells, the following formula should be written. =max(Range)similarly, for finding the minimum value =min(Range)for evaluating the average you can use =average(Range)Some other important formulas are discussed below:IF formula: For logical purposes like allotting Grades to students on the basis of percentage/total marks the IF function is used. =IF(CONDITION, “OPTION 1”, “OPTION 2”) Here CONDITION refers to the basis on which distinction will be made and OPTIONS refer to the grades/remarks to be given. If the number of options is more than two then two or more IF arguments are used in the same formula. =IF(CONDITION 1, “OPTION 1”, IF(CONDITION 2, “OPTION 2”, IF(CONDITION 3, “ OPTION 3”, “OPTION 4”))) The number of Ifs USED is ALWAYS one less than the number of options AVAILABLE.Inserting current date and time: =NOW()For counting the number of occurrence of certain text in data: =COUNTIF(RANGE, “TEXT”)Calculating the future value or the maturity value of an investment: =FV(RATE%, PERIOD,, PRINCIPAL AMOUNT)Calculating the payments to be made for repayment of a loan: =PMT(RATE%, NUMBER OF INSTALMENTS, LOAN AMOUNT)Finding the weekday: =weekday(“date of birth”) Please note that Excel displays days in numbers such as 1, 2 and so on and days starts from Sunday.4.6 Copying Formulas You can copy formulas into a range of cells just as you can copy data into a range. To copy a formula, select the cell containing the formula and use the copy and paste command as explained earlier.4.7 Totaling Rows and Columns Automatically One of the tasks that you need to frequently do is total rows and columns. You could create a new formula every time you needed to total a row or column, but Microsoft Excel provides an easier way. The AutoSum button on the Standard toolbar automatically creates a formula to total the rows and columns for you. To use the AUTOSUM formula from the keyboard, press ALT = in the cell where you want the total. Excel then displays a SUM formula containing the range for which totaling will be done. If the range is acceptable, press ENTER. The result of the AUTOSUM formula is displayed in the cell. AutoSum can be used in three ways :locate and total the rows and columns in the range nearest to the current cell, or total any range you select or add grand total to a range containing other totals To automatically total the nearest range, you type the AUTOSUM formula or click AutoSum bottom and press Enter, or double click the AutoSum button. To total a specific range, you select a range and then use the AUTOSUM command or click the AutoSum button. When you use the AutoSum button or type in ALT = once, the formula is created, and then have the option of accepting it (by pressing Enter), or modify it.Whichever method you use, be sure that there is a blank row and column around your data for the cells you want to total.,,,,,,,,,,,,,,,,,,,, 5. Formatting DataIn this chapter you will learn the following topics; Formatting Data Changing Column Width and Row Height Hiding and unhiding rows/columns/sheetRenaming the sheetFont Dialog box: Aligning Worksheet Data, Formatting Fonts, Adding Borders and Colors, Adding Number Formats & Changing Number Formats, and Formatting Dates5.1 Formatting Data Excel allows you to format worksheet cells before or after you enter data. You can Change the row height and column width to fit the contents of the row and columns, Hiding and unhiding rows/columns/sheet, Renaming the sheet, Copy or remove sheet, Change the font, font-size and font-style of individual characters in the cell, Change the alignment of text, Change the format of numeric data and Add borders and colours To access all these options, Use the Home tab>Cells group box>format button drop down. Here you will get all the above mentioned different options associated to different settings in excel, which are described as follows.Note: To access the format button drop down list the shortcut, Alt+H, O can also be used.5.1. Increasing column width and row height When an entry is too long to be fully displayed you can remedy the situation by either widening the column or increasing the height of the row, To widen any column follow the instructions given below.5.1.1 Adjusting Column WidthPlace your cursor in any of the cell of the respective column. ( You need to select only one cell in a column to change the width for entire column).Activate the format button dropdown by hitting enter, which is inside the Home tab>Cells group box. A list with various options will be displayed. Then select the appropriate option from the following choices available.Column Width: Displays the Column Width dialog box. You can enter the number 0 through 255 in the Column Width box. The default width is 8.43.AutoFit Column Width: Sets the column to the minimum width necessary to display the contents of the selected cells.Default Width: Displays the Standard Width dialog box. To return the selected columns to the standard width, choose the OK button.5.1.2 Adjusting Row Height It is similar to changing columns width. Inside home tab>format button drop down you will find the list of different options. Here select the appropriate option from the following choices available:Row Height: Here you can set a numeric row height Auto Fit Row Height: automatically fit the row to the largest font in the row.Alternatively, when you double-click the lower border of a row heading, the row-height adjusts to fit the tallest entry in the row. You can adjust several rows at once by selecting rows and then adjusting the height on any one of the selected rows.5.2 Hide and Unhide Feature:As we know that due to great number of rows and columns, excel can contain a huge information in its single sheet. It is not compulsory that every time we need to check all the sheet, if our required information is in 2 columns only.For example, if we take the sheet of data base containing the details of the students of a school. Suppose, we have to check only the name and date of birth of the students, in column B we have name written and column Z there is the date of birth. Every time navigating the whole sheet will consume much time and will become heptic also. The hide feature will help us in temporary hiding the columns which are of no use, (from column c to column y). This feature makes the large sheets mor manageable. Please note that the hidden columns or rows are not visible on the screen and not even read by the screen readers.5.2.1 Hiding Columns/ rows/sheet/particular rangeSelect the row/column/sheet/particular range that you want to hide.Activate the format command which is inside the home tab. (Alt + H, O)Here inside the visibility group box, you will find hide and unhide submenu, open this submenu by using either right arrow or enter key. This group box have different options like,hide columnshide rowshide sheetChoose the desired option and hit enter. The selected rows/columns/sheet will get hidden, as a result of which they are no longer visible within the document.5.2.2 Unhiding Columns/ rows/sheet/particular rangeSelect the data before and after the hidden range. If, for example you have hidden row C, select the Rows B and D.Activate the format command of the home ribbon (ALT-H, O)Within the hide and unhide submenu, select the appropriate unhide option and press ENTERThe data is now restored to view. 5.3 Renaming a Sheet Within the format options of Excel there is an option to rename the focused worksheet. This can come in handy if you are creating a workbook with a significant number of worksheets.Perhaps you’d like to create a grade sheet for each semester of your class for a particular year. You can name the document for the year and name each worksheet according to the semester. To rename a worksheet:Keep your focus on the worksheet you would like to renameOpen the format command of the home ribbon (ALT-H, O)Press ENTER on “rename Sheet” (R), (the option of rename sheet is generally inside the organise sheet group box)An edit box will appear, here type the desired name for the worksheet and press ENTERThe worksheet is renamed. Save your document to make the change permanent. 5.4 Font Dialog BoxThe excel also gives us the access to numerous other major features like Changing the alignment, changing font of text, changing borders/background colour, murging the cells, changing number format, changing date format, etc. All these features can be found inside a single dialog box (font dialog box) under its multiple tab pages, which are explained as follows:Note: The option of Font dialog box is inside the Format button drop down with the name Format cells... The shortcut CTRL + 1 can also be used to open this dialog box.5.4.1 Aligning Worksheet Data When you open a new sheet and begin entering data, your text is automatically left-aligned your numbers are automatically right-aligned. However you may decide to change the alignment of the text to be centered in the cells. You can easily align text to the right, left, or centre text across columns. For this, do the following;Select the cell or range that you want to change.hit enter on the Format CELLS... button from the Home tab>format button drop down. CONTROL + 1 is the shortcut for this command. A dialog box opens. Switch to the ALIGNMENT tab page (CONTROL + TAB can be used). HORIZONTAL/VERTICAL alignment: from these combo boxes, select the alignment you want for the selected data. Wrap text: This check box wraps text into multiple lines in a cell. The number of wrapped lines is dependent on the width of the column and the length of the cell contents. Shrink to fit: This check box reduces the apparent size of font characters so that all data in a selected cell fits within the column. The character size is adjusted automatically if you change the column width. The applied font size is not changed. Merge cells: This check box Combines two or more selected cells into a single cell. The cell reference for a merged cell is the upper-left cell in the original selected range. Orientation combo box- It can be used to change the orientation of the text that is make it inclined. It sets the amount of text rotation in the selected cell. Use a positive number in the Degree box to rotate the selected text from lower left to upper right in the cell. Use negative degrees to rotate text from upper left to lower right in the selected cell. Select OK button to apply the changes. Note: To change the alignment quickly, Click the Align Left, Centre, or Align Right buttons on the Formatting toolbar. If you want to align a title across several columns, you select the cells across which you want the text to be centered in and click the centre across columns button on the formatting toolbar. This command is also available in the HORIZONTAL combo box on the ALIGNMENT tab discussed above.5.4.2 Formatting Fonts you can change fonts and font size with the Font and Font Size boxes on the Formatting toolbar, or you can use the Format cells command. You can also use the Bold, Italic, or Underline button on the toolbar.Select the cells whose font you want to change. The entire worksheet should be selected to apply the same font for the whole sheet. Select the CELLS command from the FORMAT menu. Switch to FONT TAB. select a font and font style of choice from the font combo box and font style combo box. The FONT SIZE combo box Changes the size of the selected text. The sizes available depend on the printer and the selected font. Like MS Word, this dialog box also provides choices for UNDERLINE STYLE, FONT COLOUR & STRIKE THROUGH. Select OK to apply the changes. 5.4.3 Adding Borders and ColorsYou can emphasize particular areas of the sheet and specific cells by using borders and colors. Borders add lines above, below, or to either side of the cell or around it, you can shade a cell in one of many patterns or colors.The BORDER and Fill TABS of the FORMAT CELLS dialog box(CONTROL +1) provide numerous options for making the worksheet look more attractive.The border tab provides following options;BORDER STYLE: Select an option under Style to specify the line size and style for a border. COLOUR BUTTON MENU: Select a color from the list to change the color of the selected text or object. PRESET BUTTONS: Select NONE button to remove existing border. Choose from OUTLINE, INSIDE buttons to apply border outside or the inside the edge of the cells selected. BORDER CHECK BOX: Use them to apply or remove border on any side of the selection. When the checkbox is checked (using SPACEBAR) the border is applied. The Fill TAB offers the following choices:PATTERN COLOUR PALETTE: Select a color to change the background color of the selection. PATTERN MENU: Select a background color in the Color box, and then Select a pattern in the Pattern box to format the selection with color patterns. Note: To apply borders, or colors through mouse, select the cell you want to change and on the toolbar then use Border buttons and Color buttons. 5.4.4 Adding Number FormatsThe default number format for all cells on a new worksheet is the General format. In the General format, Excel displays numbers as integers (789), decimal fraction (7.89) or a scientific notation (7.89E+08) if the number is longer than the width of the cell. The General format displays up to 11 digits.You can format the numbers in a cell by using the Currency style, Percent Style, and Comma Style buttons on the toolbar. Each of these styles has a default number of decimal points that you can change with the increase Decimal and Decrease buttons. Numbers can be formatted by either selecting the Style button on the formatting toolbar or selecting the format cells command. The advantage of using the format cells command is that you can use more choices of number formats than you can do with the buttons on the toolbar.Adding currency style To add currency style Select the cells to be formatted Select CELLS command in the FORMAT menu to open the dialog box And Change to NUMBER TAB. It has a CATEGORY LIST BOX. Use it to select an option in the list, and then select the options that you want to specify with this number format. The Sample box shows how selected cells will look with the formatting you choose. Choose currency from the list. Then specify the number of digits you want after decimal in the edit box that follows. Choose a symbol for the currency from the SYMBOL combo box. You also need to choose a format for the negative numbers from the list box. Do OK to apply the currency format. Alternatively, click the currency style button on the formatting toolbar.Adding percent style Select the cells to be formatted to the percent style Select PERCENT from the CATEGORY LIST box(discussed above). Specify the number of decimal places in the following edit box and press OK button. Applying the percent style causes the number .12 to be displayed as 12%Note: if you want to do it with the help of mouse, click the percent style button on the toolbar.Formatting Dates To format dates you would have to use Format Cells option and then select Date from the category list. You have a variety of date option to select from. For example, if you have entered July 5, 1994 in a cell and from the Format Codes list, you select d-mmm-yy the date would be displayed as 5-Jul-94Note: Same as MS Word, in MS Excel all the options of Font Cell Dialog box are also individually available inside the home tab, which you can navigate by pressing Alt + H and tab key repeatedly to move to different option. Pressing enter on the desired option will activate it.6. Printing & Other TopicsIn previous chapter you have learnt formatting of text. This chapter covers following topics;Previewing WorksheetPage SetupPrinting your dataCopying and Pasting Cells for Special ResultsCreating Formulas with Relative and Fixed ReferencesWorking with Sheets6.1 Previewing Your WorksheetPreviewing your worksheet shows you what you need to change before you print. This option is helpful because you don't waste a lot of time and paper checking to see whether the necessary rows and columns appear on the sheet.To preview the worksheet do the following;A) After opening the ribbon, navigate to the Office Button dropdown list and activate it by using DOWNARROW or ENTER key.B) Move to Print Split button and hit ENTER/SPACE BAR.C) Press ENTER on Print Preview after navigating to it.D) You can check the information through screen reader by using UP/DOWNARROW after activating JAWS cursor (NUMPAD DASH)E) You will also get options for changing few settings such as zoom level, and Page Setup etc.6.2 Page Setup Before printing a worksheet, the margins, the paper size and headers and footers need to be fixed according to requirement. The PAGE SETUP command provides various options which are discussed below. To go to the page setup dialogue box do the following;1. Go to the ribbon through ALT key and press ENTER on page Layout tab after navigating to it.2. Move to Page Setup Submenu and press RIGHTARROW/ENTER to open it.3. Select Page Setup Button with the help of UP/DOWNARROW and press ENTER.4. You will get a dialogue box containing different tabsheets for example Margin TAB, Paper TAB, Sheet Tab and Headder/Footer Tab. (CTRL+TAB can be used to select different Tab Pages)6.2.1 Adding margins Margin is the blank space given on the left, right, top and bottom sides on a page. different margins can be set through Margin Tab and it has following fields;a) Top, Bottom, Left and Right margins: These are in the form of spin boxes that is a value can be written or chosen using the arrow keys. Adjust measurements in the Top, Bottom, Left, and Right boxes to specify the distance between your data and the edges of the printed page.b) Header and Footer: Type or select a numaric value from the Header and Footer spin boxes to adjust the distance between the header and the top of the page or between the footer and the bottom of the page. The distance should be smaller than the margin settings to prevent the header or footer from overlapping the data.c)CENTER OF PAGE- Center the data on the page within the margins by selecting the Vertically check box, the Horizontally check box, or both.D) Ok & CANCEL BUTTONS- OK button closes this dialog box and applies any changes you've made. CANCEL button closes the dialog box and does not apply any change. 6.2.2 Choosing paper sizeAfter setting up the margins, do CTRL+TAB to switch to Page Tab. This page provides options for setting the paper size, scaling and orientation etc.A) Orientation: it has 2 radio Buttons namely Portrait and Landscape Radio Button. In portrait position the height of the page is more than the width whereas in landscape position the width is more than the height. b) Scaling: There are 2 Radio Buttons to adjust the Scaling of the spreadsheet namely Adjust To and Fit To Radio Button. "Adjust to Radio Button" gives you an option to change scaling. Scaling- Reduces or enlarges the printed worksheet. Select the "Adjust to Radio Button" and then enter a percentage number in the % normal size box. You can reduce the worksheet to 10 percent of normal size or enlarge it to 400 percent of normal size. "Fit to Radio Button" reduces the worksheet or selection when you print so that it fits on the specified number of pages. Select the "Fit to Radio Button" and enter a number in the page(s) wide by box, and enter a number in the tall box. To fill the paper width and use as many pages as necessary, type 1 in the pages(s) wide by box and leave the tall box blank.C) Paper Size: it is a combo box and you can Select Letter, Legal, or any other available option for your spreadsheet.D) Print Quality: This combo box determines the resolution you want to specify for the active worksheet. Resolution is the number of dots per linear inch (dpi) that appear on the printed page. Higher resolution produces better quality printing in printers that support high-resolution printing.E) First Page Number: This edit box indicates what number appears on the first printed page. If you want the first page number to be 1, leave the selection as Auto. If you want to start page numbering from any other number, you can enter that number in this field.F) OK/CANCEL, buttons- Like the previous tab page these options are here also and perform the same functions. 6.2.3 Defining print areaPrinting areas can be defined from the options available on SHEET TAB. These options are used to specify the printing area. It has the following fields; a) Print Area: In this edit box, you can specify the cell range that you want to print.B) Rows To Repeat At Top: In this edit box you can type the row number that you want to print on each page. Generally it is the row containing columns header.C) Columns To Repeat At Left: This edit field can be used for entering a column number to be printed on the left side of data on every page.D) Grid Lines: Check the Gridlines check box to print horizontal and vertical cell gridlines on worksheets.E) Black and White: Check the Black and White check box if you have formatted data with colors but printing on a black-and-white printer. If you are using a color printer, selecting this option may reduce printing time.F) Draft Quality: Check the Draft quality check box to reduce printing time. When this check box is checked, Microsoft Excel does not print gridlines and most graphics.G) Row and ColumnHeadings: Check the Row and column headings check box to print row numbers and column letters in the A1 reference style or numbered rows and columns in the R1C1 reference style.H) Comment: it is a combo box with following options; None- Selecting this option wil not print any comment available on the sheet, At end of sheet- selecting this option will print comments beginning on a separate page at the end of the document, As displayed on sheet- selecting this option will print comments where they are displayed when you view them on the worksheet.I) Page Order: select "Down, then over" or "Over, then down" to control the order in which data is numbered and printed when it does not fit on one page.J) Cell Errors as: From this combo box you can select displayed- if you want to display the cell errors if any, Blanks- to leave the place blank instead of cell errors, Dash- if you want to put dashes in place of cell errors and N/A- if you want to mark N/A instead of cell errors.K) OK/CANCEL buttons- They perform the same function as in MARGINS & PAGE tab pages. 6.2.4 Header & FootersHeader and Footer Tab has options to create the desired header and footer for the pages to be printed. A) Header combo box: You can select a built-in header from this combo box.B) Custom Header Button: It opens a dialog box. The dialog box has three sections namely "Left Section edit box", "Center Section Edit Box" and "Right Section Edit box". You can type the desired text in the edit fields that you want to print in the headers. If you will type In "Left Section Edit Box", it will get printed on the top left corner of the page. If you will write text in "Center Edit Box", it will get printed on top center of the page and if you will write any text in the "Right Edit Box", It will be displayed on the top right corner of the page.C) There are some buttons in this dialog box such as The "Font Button" Changes the font, font size, and text style of the selected text in the Left section, Center section, or Right section box. "Page Number Button" inserts page numbers in the header when you print the worksheet. Microsoft Excel updates the page numbers automatically when you add or delete data, or set page breaks. "Total Pages Button" inserts the total number of pages in the active worksheet and adjusts the page numbers automatically when you print the worksheet. For example, if you want to use the format "Page 1 of 12," "Page 2 of 12," and so on. Click where you want to insert the first page number, and then click where you want to insert the total page number. "Date Button" inserts the current date. "Time Button" inserts the current time. "File Name Button" inserts the file name of the active workbook. "Sheet Name Button" inserts the name of the active worksheet.D) Custom Footer Button: It has same fields as mentioned in above point and behaves as explained above.E) Custom Footer combo boxc: You can select built-in Footer from this combo box.F) Different Odd and Even: If you want to use different Header and Footer for Odd and Even pages check the check box else leave it unchecked.G) Different First Page: Check this check box if you want to use different Header and Footer on the first page.H) Scale with Document: Check this box if you want to scale the Header and Footer with the text of the sheet.I) Align With Page Margins: You have to check this box if you want to align the Header or Footer with the page margins that you have set.J) OK/CANCEL buttons are provided in this tab page also and perform the same function as discussed above. 6.3 Printing a WorksheetAfter you have setup your worksheet, you've can print it. Use the Print command from the File tab/office button button drop down list with the name print tab/print split button in office 2010/2007 respectibly. The shortcut command CTRL+P can also be used to directly launch the print dialog box. The dialogue box has following options;A) Number of copies: You can Type in or select the desired number of copies of the document required to be printed.B) Collate: If this checkbox is checked, papers will be printed in binding order. (In Office 2010 you will get combo box of collate option instead of check box)C) Print what: You can select one of all the following options; "Active Sheet" Prints only one Sheetin which the insertion pointer is located in the document. "Entire Workbook" it will print all Sheets present in the Workbook". "Selection" It will print the Selected Text if any. "Page Range" Prints the specified page numbers. D) Print Range Edit Box: This edit box is useful only if the "Print Range" is selected in the previous control. This edit box is used to specify the page numbers to be printed. For example type 5 - 8 to print from page 5 to page 8. Type 5, 8 to print page 5 and page 8.E) Print/Cancel button: after making required selections hit ENTER on Print button, it will start the printing. To close the dialogue without giving printing command, hit ENTER on Cancel button.G) In Excel 2010 there are few additional options for example "Orientation" combo box for selecting Portrait/Landscape orientation, "Page Size" Combo box for selecting the page size, "Margin" Combo box for selecting the Page Margin and "Scaling" Combo box for selecting the Scaling of text.6.4 Copying and Pasting Cells for Special ResultsAfter you have copied cells using the Copy command, you can choose the Paste Special command from Home>Clipboard Submenu>Paste Split button to select several options for pasting the copied cellsYou can specify which parts of the current cell selection to use by selecting the appropriate Paste Special options:? All to paste all the stuff in the cell selection (formulas, formatting, you name it). this is what happens when you paste normally. ? Formulas to paste all the text, numbers, and formulas in the current cell selection without their formatting. ? Values to convert formulas in the current cell selection to their calculated values. ? Formats to paste only the formatting from the current cell selection, without the cell entries. ? Comments to paste only the notes that you attach to their cells.? Validation to paste only the data validation rules into the cell range that you set up with the Data Validation command. ? All Using Source Theme to paste all the information plus the cell styles applied to the cells. ? All Except Borders to paste all the stuff in the cell selection without copying any borders you use there. ? Column Widths to apply the column widths of the cells copied to the Clipboard to the columns where the cells are pasted. ? Formulas and Number Formats to include the number formats assigned to the pasted values and formulas. ? Values and Number Formats to convert formulas to their calculated values and include the number formats you assigned to all the copied or cut values.You can also perform some math when you paste based on the values in the copied or cut cells and the value in the target cells as under;? None: Excel performs no operation between the data entries you cut or copy to the Clipboard and the data entries in the cell range where you paste. This is the default setting. ? Add: Excel adds the values you cut or copy to the Clipboard to the values in the cell range where you paste.? Subtract: Excel subtracts the values you cut or copy to the Clipboard from the values in the cell range where you paste.? Multiply: Excel multiplies the data you cut or copy to the Clipboard by the data entries in the cell range where you paste. ? Divide: Excel divides the data you cut or copy to the Clipboard by the data entries in the cell range where you paste. Finally, at the bottom of the Page Special dialog box, you have a few other options:? Selecting the Skip Blanks check box tells Excel only to paste from those cells that aren't empty. ? Selecting the Transpose check box changes the orientation of the pasted entries. For example, if the original cells’ entries run down the rows of a single column of the worksheet, the transposed pasted entries will run across the columns of a single row. ? Clicking the Paste Link button establishes a link between the copies you’re pasting and the original entries. That way, changes to the original cells automatically update in the pasted copies. 6.5 Creating Formulas with Relative and Fixed ReferencesExcel follows two types of cell addresses : relative and fixed. Relative cell addresses identify cells by their position in relation to the active cells. This means that when you copy or move a formula, unless you specify otherwise, the addresses of the cells in the formula will be adjusted automatically to fit the new location . As an example of relative addressing, suppose that you want to sum the contents of several columns of cells, but you don't want to enter =SUM() function over and over again. Only column C is summed, using the formula =SUM(C5:C8) in the cell C10. You want to add the contents of the cells in column D, E, F and G in the same manner that the contents of cells in column C were added. Copy the Formula over cells D10, E10, F10 and G10. The column address in the formula =SUM(C5:C8) changes from Column C to D,E,F and G respectively.Fixed Cell addresses, on the other hand, refer to the fixed or absolute position of cells. Fixed addresses use dollar signs ()$) to indicate absolute position of the cells addresses. For example, the formula =$A$1*C3 will multiply the contents of cell C3 with that of A1. this formula if copied to other cells will only change the address of second cell that is C3 in reference to the active cell.Mixed cell addressing refers to a combination of relative and absolute addressing. Because a cell address has two components a column and a row It is possible to fix either portion while leaving the other unfixed or relative. For example in $D10 Column address is fixed, in D$10 Row address is fixed.To change the relative address to a fixed address or absolute address, select the reference in the formula and type a dollar sign ($) before both column and row indicators in the cell.6.6. Working with Sheets6.6.1 Removing SheetFor deleting a sheet please ensure that your cursor is placed in that sheet which needs to be deleted. After placing your cursor in a sheet do the following;1. Go to Home>Cell Submenu>Delete Split Button and hit ENTER.2. Navigate to "Delete Sheet" option using UP/DOWNARROW and press ENTER.3. A dialogue box will pop up for confirming the deletion of sheet. Press ENTER on "Delete" Button and the active sheet will get deleted.,,,,,,,,,,6.6.2 Adding SheetsAs an Excel Workbook contains 3 sheets by default, Additional sheets can be added based on the requirement of the user. For adding a new sheet follow the steps given below; (please note that the new Sheet will be inserted before the active sheet).1. Go to Home>Cell Submenu>Insert Split Button and do ENTER.2. Navigate to "Insert Sheet" by using UP/DOWNARROW and press ENTER. (Shortcut key SHIFT+F11 can also be used for inserting a new sheet).6.6.3 Renaming SheetIn Excel by default the sheets are named as Sheet1, Sheet2 and so on. Excel provides a feature to change the name of the sheet to a meaningful name which can be easily identified by the user. To rename a Sheet do the following;1. Go to Home>Cell Submenu and press ENTER.2. Move to Format Dropdown Button and press ENTER.3. Now navigate to Rename Sheet and do ENTER.4. Type the new name by which you want to replace the existing name and do ENTER. The previous name will be replaced with the new name that you have written.,,,,,,,,,,7. Charting Your Datathis chapter covers the following topics :What are chartsThe Chart type7.1 What are ChartsA worksheet calculates and presents differences and similarities between numbers. It also displays the changes in numbers over time. But data by itself cannot illustrate these effectively. With ?Charts, you make your data visual. You can create charts in Excel using ChartWizard.7.2 Selecting the Chart typeExcel's ChartWizard includes several chart types. The type of chart you can create depends on the data you select. Your selection can include only one data series in the chart, that is, either a single ?Row, or column, or it can contain multiple series multiple rows and columns.Area chart: An area chart emphasizes the magnitude of change over time. By displaying the sum of the plotted values, an area chart also shows the relationship of parts to a whole.Column chart: A column chart shows data changes over a period of time or illustrates comparisons among items. Categories are organized horizontally, values vertically, to emphasize variation over time. Stacked column charts show the relationship of individual items to the whole. The 3-D perspective column chart compares data points along two axes.Bar chart: A bar chart illustrates comparisons among individual items. Categories are organized vertically, values horizontally, to focus on comparing values and to place less emphasis on time. Stacked bar charts show the relationship of individual items to the whole.Line chart: A line chart shows trends in data at equal intervals. The data series is plotted on the charts as points and then they are joined by lines. Pie chart: A pie chart shows the proportional size of items that make up a data series to the sum of the items. It always shows only one data series and is useful when you want to emphasize a significant element. To make small slices easier to see, you can group them together as one item in a pie chart and then break down that item in a smaller pie or bar chart next to the main chart.Scatter chart: An xy (scatter) chart either shows the relationships among the numeric values in several data series or plots two groups of numbers as one series of xy coordinates. This chart shows uneven intervals — or clusters — of data and is commonly used for scientific data. When you arrange your data, place x values in one row or column, and then enter corresponding y values in the adjacent rows or columns.Bubble chart: A bubble chart is a type of xy (scatter) chart. The size of the data marker indicates the value of a third variable. To arrange your data, place the x values in one row or column, and enter corresponding y values and bubble sizes in the adjacent rows or columns.Surface chart: A surface chart is useful when you want to find optimum combinations between two sets of data. As in a topographic map, colors and patterns indicate areas that are in the same range of values.Radar chart: In a radar chart, each category has its own value axis radiating from the center point. Lines connect all the values in the same series. A radar chart compares the aggregate values of a number of data series. In this chart, the data series that covers the most area, Brand A, represents the brand with the highest vitamin content.Note: The option of creating chart is available inside Chart Submenu under Insert Ribbon.,,,,,,,,,,,,,,,,,,,,,,,,,,,,, Appendix A: Excel Application keystrokes DescriptionCommandMove one cell up, down, left, or rightARROW KeysMove to the edge of the current data regionCTRL+ARROW keysMove to the beginning of the rowHOMEMove to the beginning of the worksheetCTRL+HOMEMove to the last cell on the worksheetCTRL+ENDMove down one screenPAGEDOWNMove up one screenPAGEUPMove one screen to the rightALT+PAGEDOWNMove one screen to the leftALT+PAGEUPMove to previous sheet in the workbookCTRL+PAGEUPMove to next Sheet in the workbookCTRL+PAGEDOWNMove to next open workbookCTRL+F6 or CTRL+SHIFT+TABMove to previous open WorkbookCTRL+SHIFT+F6 or CTRL+SHIFT+TABMove to next paneF6Move to previous paneSHIFT+F6Open Go to dialogue boxF5Open Find dialogue boxSHIFT+F5Repeat last find actionSHIFT+F4Open print dialogueCTRL+P or CTRL+SHIFT+F12Insert a new worksheetSHIFT+F11 or ALT+SHIFT+F1Create a chart that uses the current rangeF11 or ALT+F1Display the Macro dialog boxALT+F8Display the Visual Basic EditorALT+F11Start a new line in the same cellALT+ENTERComplete a cell entry and move up in the selectionSHIFT+ENTERComplete a cell entry and move down in the selectionENTERComplete a cell entry and move to the right in the selectionTABComplete a cell entry and move to the left in the selectionSHIFT+TABEdit a cell commentSHIFT+F2Create names from row and column labelsCTRL+SHIFT+F3Fill downCTRL+DFill to the rightCTRL+RDefine a nameCTRL+F3Insert a hyperlinkCTRL+KEdit a cellF2Enter the dateCTRL+; (Semicolon)Insert the AutoSum formulaALT+= (Equals)Enter the timeCTRL+SHIFT+; (colon)Display the AutoComplete listALT+DOWNARROWDisplay the Style dialog boxALT+' (apostrophe)Display the Format Cells dialog boxCTRL+1Apply the General number formatCONTROL+SHIFT+~Apply the Currency format with two decimal places (negative numbers CONTROL+SHIFT+$Apply the Percentage format with no decimal placesCONTROL+SHIFT+% (percentage)Apply the Date format with the day, month, and yearCTRL+SHIFT+3 (number)Apply the Time format with the hour and minuteCTRL+SHIFT+2 (AT)Apply the outline borderCONTROL+SHIFT+7 (and)Remove outline bordersCTRL+SHIFT+- (underline)Apply or remove strikethrough formattingCTRL+5Hide rowsCTRL+9Unhide rowsCTRL+SHIFT+9 ((opening parenthesis)Hide columnsCTRL+0Unhide columnsCTRL+SHIFT+0 (closing parenthesis)Appendix B: JAWS commandsInformational Keystrokes for ColumnsDescriptionCommandList cells in current columnINSERT+SHIFT+CRead column totalINSERT+NUM PAD ENTERSay column titleINSERT+ALT+SHIFT+CSet column titles to rowINSERT+ALT+CTRL+CSet current column to the column containing row totalsCTRL+INSERT+ENTERInformational Keystrokes for RowsDescriptionCommandList cells in current rowINSERT+SHIFT+RRead row totalINSERT+DELETESay row titleINSERT+ALT+SHIFT+RSet row titles to columnINSERT+ALT+CTRL+RSet current row to the row containing column totalsCTRL+INSERT+DELETETable Layer KeystrokesTable layer keystrokes offer a fast and convenient way to navigate tables. The table layer eliminates the need to have to hold down several keys at once to perform a table navigation command. Below is a list of the layered keystrokes for table navigation. Press and release INSERT+SPACEBAR, followed by T to activate the table layer, and then press any of the following keystrokes.DescriptionCommandRead first cell in current column. ALT+1Read second cell in current column. ALT+2Read thrid cell in current column. ALT+3Read fourth cell in current column. ALT+4Read first cell in current row. CTRL+1Read second cell in current row. CTRL+2Read third cell in current row. CTRL+3Read fourth cell in current row. CTRL+4Miscellaneous Informational KeystrokesDescriptionCommandOpen custom summary label managerINSERT+CTRL+TABOpen custom summary when one existsINSERT+CTRL+SHIFT+TABDescribe cell borderALT+SHIFT+BList cells at page breakCTRL+SHIFT+BList cells with commentsCTRL+SHIFT+APOSTROPHEList cells with formulasINSERT+SHIFT+FRead cell commentALT+SHIFT+APOSTROPHEList Visible cells with dataCTRL+SHIFT+DList defined monitor cellsCTRL+SHIFT+MList worksheetsCTRL+SHIFT+SSay current cell validation input messageCTRL+SHIFT+VSay range of cells visible in active windowALT+SHIFT+VRead hyperlinkALT+SHIFT+HReport gridline statusALT+SHIFT+GSay active cell coordinatesINSERT+CSay last numeric value or word that appears in the Formula barCTRL+NUM PAD 5Say Excel versionCTRL+INSERT+VSay cell font and attributesINSERT+FSay formulaINSERT+CTRL+F2Read spelling error and suggestionINSERT+F7Display detailed cell appearance informationINSERT+TAB twice quicklyNavigation KeystrokesDescriptionCommandMove to prior screen in spreadsheetALT+PAGE UPMove to next screen in spreadsheetALT+PAGE DOWNNext SheetCTRL+PAGE DOWNPrior SheetCTRL+PAGE UPMove to first cell in regionALT+SHIFT+HOMEMove to last cell in regionALT+SHIFT+ENDMove down to the edge of current data regionCTRL+DOWN ARROWMove up to the edge of current data regionCTRL+UP ARROWMove left to the edge of current data regionCTRL+LEFT ARROWMove right to the edge of current data regionCTRL+RIGHT ARROWInsert CellMarkerINSERT+WINDOWS Key+KMove to CellMarker on current worksheetWINDOWS Key+KMove to CellMarker on next worksheetCTRL+WINDOWS Key+KMove to CellMarker on previous worksheetCTRL+WINDOWS Key+SHIFT+KList all CellMarkers in workbookCTRL+ALT+WINDOWS Key+KSelection KeystrokesDescriptionCommandSelect columnCTRL+SPACEBARSelect hyperlinkINSERT+F7Select regionCTRL+SHIFT+8Select a region and announce the region's cell rangeCTRL+SHIFT+SPACEBARSelect rowSHIFT+SPACEBARSelect worksheet objectsCTRL+SHIFT+ORead the current worksheet object INSERT+CTRL+CSelect the first worksheet object INSERT+ALT+CCollapse selection to current cellSHIFT+BACKSPACEConfiguration KeystrokesDescriptionCommandSet monitor cellINSERT+SHIFT+1 through INSERT+SHIFT+0Note: If a monitor cell was previously set and you then try to set that cell again, a message appears letting you know that you are about to overwrite the existing monitor cell. You have the option to cancel or continue and overwrite the monitor cell.Read monitor cellALT+SHIFT+1 through ALT+SHIFT+0Formula input modeEQUALSAutoFilterCTRL+SHIFT+AAutoSumALT+EQUALSDate stampCTRL+SEMICOLONTime stampCTRL+SHIFT+SEMICOLON ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download