Excel 2016, Lesson One

NOTE: For those of you participating in the live webinar, the following link will open the documentation in a new browser window Opens in a new window.

EXERCISE: Open the file RestaurantSupplySampleSpreadsheetExcel2016-Unfinished Opens an Excel workbook to use with the following exercises.

Excel Program Window Overview

Microsoft Excel 2016 is similar to the other Office 2016 products in overall layout. Initially, the program opens to a startup screen in the backstage view. This startup screen contains a number of templates you can use in Excel. To close this screen and get to a blank workbook, just press ESC. There is a title bar at the top of the screen, the ribbon which contains an upper group of tabs and a lower part that contains groups of controls that are relevant to the currently selected tab, a Quick Access Toolbar, the main portion of the window where the current worksheet is shown, and a task bar at the bottom of the page.

The default Excel 2016 workbook is made up of a single worksheet. The worksheet consists of a grid of columns and rows. Column headers are labeled A through Z, then AA through AZ, BA through BZ, and so on up to XFD (that is 16,384 columns). The rows are numbered from 1 to 1,048,576. More worksheets can be created and they can also be deleted.

Rename a Worksheet

Worksheets can be named to something more meaningful than the default, which is Sheet1, Sheet2, and so on. Using the keyboard the steps to rename a worksheet are as follows:

1. Home tab of the ribbon, ALT+H
2. Location: Cells group of the lower ribbon
3. Format button, O
4. Rename Sheet, R
5. Go ahead and rename sheet 1 to "Restaurant supplies."
6. Press ENTER to close the edit field when finished. Focus returns to the worksheet area.

NOTE: At the present time you can hear the characters of text as you enter them into the field to name worksheets, but you will not be able to hear when reading by character, word, etc. to edit them.

NOTE: If you need to find out the physical number of a worksheet that has been renamed, use the JAWS keystroke INSERT+F1.

Add a New Worksheet

To add a new worksheet do the following:

1. Go ahead and insert a new worksheet now. Home tab of the ribbon, ALT+H.
2. Location: Cells group of the lower ribbon
3. Insert button, I
4. Insert sheet, S

NOTE: New worksheets are inserted before the current sheet.

Rearrange Worksheets

Let us move the worksheet Restaurant Supplies back to position one as the first sheet in the workbook. To rearrange worksheets do the following:

1. Make sure the sheet you want to move is the current sheet. Press CTRL+PGUP or CTRL+PGDN to find the worksheet called Restaurant Supplies. You may also use the JAWS keystroke CTRL+SHIFT+S to open a list of worksheet names and move to a specific worksheet.
2. Home tab of the ribbon, ALT+H
3. Location: Cells group of the lower ribbon
4. Format button, O
5. Move or Copy sheet button, M. The Move or Copy dialog box opens.
6. When rearranging worksheets, the currently selected worksheet is inserted before the sheet you choose in the Move or Copy dialog box. In the Move or Copy dialog box, choose the sheet that you wish to push back one and insert the current sheet in front of, or choose "move to end."
7. Press ENTER to activate the Close button.

Create, Open, Save, or Close Workbooks

Use the following keystrokes to create a new workbook, save an existing workbook, or open an existing workbook:

• Create a new workbook, CTRL+N
• Save an existing workbook, CTRL+S. For Save As, press F12.
• Open an existing workbook, CTRL+O. To go directly to the Open dialog box, press CTRL+F12.
• In prior versions of Excel, you could close all open workbooks by choosing Exit in the File tab. That was removed in Excel 2016. You can choose Close from the backstage view in the File tab, which will only close the current workbook. Alternatively, you may press WINDOWS Key+T to move to the task bar at the bottom of the screen, and press the APPLICATION Key on the Excel button there. In the context menu you can choose Close all Windows.

When you choose CTRL+S to save a workbook the first time, instead of the Save As dialog box, Excel opens the backstage view on the Save As tab. To get to the Save As dialog box, you first have to press TAB to move to a group of options for where to save the document. You may choose from OneDrive, Computer, Add a Place, or other locations if you have made others available. Choose Computer, and then press TAB again to move to a list of locations on the current computer. Here you will find a vertical list. You can press TAB or use the ARROW Keys to choose from recent folders or to find the Browse button. Pressing ENTER on the Browse button launches the familiar Save As dialog box.

This same behavior is found in the Save As command from the File tab in the backstage view, as well as the New or Open tabs of the backstage view.

Navigation Within and Between Worksheets

Navigation is done within and between worksheets in several ways, including:

• Use ARROW KEYS to move in any given direction
• Use the Go To dialog box, CTRL+G or F5
• Use CTRL+PGDN to move to the next sheet
• Use CTRL+PGUP to move to the prior sheet
• Use CTRL+SHIFT+S to open the Move to Worksheet dialog box - JAWS command
• F2 is used to edit the contents of the current cell
• CTRL+ARROW KEY moves to the edge of the current data region
• HOME Moves to the beginning of a row in a worksheet
• CTRL+SPACEBAR selects an entire column in a worksheet
• SHIFT+SPACEBAR selects an entire row in a worksheet

NOTE: To quickly move across a span of empty cells or cells that have contiguous information first press the END key, followed by an ARROW KEY in any given direction. The focus moves in that direction to the last cell in a group of cells with data, or to the first cell where data is found when moving across empty cells.

Ranges in Excel

A range can be a single cell, a group of two or more cells, or several different blocks of cells that are not even next to each other. A single range block is defined by the top left cell and the bottom right cell. For example, A1:B2 represents a range of four cells with the top left cell in column A, row one, and the bottom right cell in column B, row two. A range of cells with two different non-contiguous blocks would be represented with a comma between the two blocks. For example, A1:B2, D1:F9. Ranges can be named to make them easier to remember. The next section discusses naming of ranges.

Names in Excel

Use the Define Names button of the ribbon to name a cell or a range of cells. The Define Names button is in the Defined Names group of the lower ribbon on the Formulas tab.

EXERCISE: Follow along with the instructor to name a range of cells in the sample workbook.

1. Press CTRL+G to open the Go To dialog box.
2. In the Reference edit box type I25
3. Press ENTER to close the Go To dialog box. Focus moves to cell I25. The content of the cell is text that reads: Total Cost of Goods Sold for the Month.
4. Hold down the SHIFT Key and press DOWN ARROW to select cell I25 and the cell directly below it, I26. Both cells are now selected. Later in the lesson we are going to put a formula in cell I26 to calculate the total cost of goods sold for this imaginary restaurant. By giving the range of these two cells a name, it will make it easier to move here in the future.

To name the range:

5. Formulas tab of the ribbon, ALT+M
6. Location: Defined Names group of the lower ribbon
7. Define Name button, M
8. Define Name, D

The New Name dialog box opens. Excel captures the text from cell I25. Notice that Excel puts underscores instead of spaces. This is because names in Excel cannot contain spaces.

9. Press ENTER to accept this name as it is. The New Name dialog box closes and focus returns to the range in Excel. The range is still selected, so you may hear JAWS read information about the selection or read the text in the selected area.
10. Next, press CTRL+HOME to move back to the top of the spreadsheet, cell A1.
11. Now, press CTRL+G again to open the Go To dialog box.
12. Press TAB until you hear JAWS announce you are in a list.
13. Press DOWN ARROW in the list and find the name you just created.
14. Press ENTER on the name in the list. The Go To dialog box closes and focus returns to the range I25 to I26. The range is selected again and JAWS announces the selection. JAWS may also read any text within the range, since it is highlighted text.

As you can tell, it's much easier to remember and move to a name for a range than it is to remember the coordinates for a range. You'll find the naming feature of Excel quite useful.

Insert and Read Comments

EXERCISE: Enter comments in various places throughout the worksheet.

To insert a comment, first put the focus in the cell where you want to insert the comment, and then do the following:

1. Review tab of the ribbon, ALT+R
2. Location: Comments group of the lower ribbon
3. New comment, C
4. Edit an existing comment, T
5. Delete an existing comment, D

Here are a few examples:

• B6, Flour: Pounds of flour used
• B7, Sugar: Pounds of sugar used
• J4, Links to Order Supplies: These are just sample links. Only the last one for the Training HQ is real.
• I25, Cost of Goods Sold for the Month: We had a high cost of goods this month, and need to work on bringing our costs down.

NOTE: At the present time you can hear the characters of text as you enter them into the comment box, but you will not be able to hear when reading by character, word, etc. to edit them.

Create Row and Column Headers for Ranges that Read Automatically for Screen Reader Users

When navigating left and right or up and down in the range of cells B5 - G10 you want to hear the column headers in row 5 spoken, and the row headers in column B spoken.

EXERCISE: Create row and column headers for the range of cells B5 - G10 using the Define Name feature of Excel. Put the focus in cell B5, which is the intersection of the header row and column.

Microsoft Excel has a built-in function that can be used to give names to a cell or a range of cells. JAWS looks for certain specific names to identify which cells contain row and column titles. If the titles are defined in this fashion, the information is stored right in the worksheet rather than in a JAWS file and can be used to speak the title information to anyone using JAWS 6.1 or later. Furthermore, anyone can build these row and column names into a spreadsheet without installing or using JAWS.

1. First, make sure to move to cell B5.
2. Formulas tab of the ribbon, ALT+M
3. Location: Defined Names group of the lower ribbon
4. Define Name button, M
5. Define Name, D). The New Name dialog box opens.
6. Type in the following name with NO SPACES: TitleRegion1..G10, and then press ENTER to close the Define Name dialog box.
7. Now try navigating left and right or up and down within the range. Do you hear JAWS reading the row and column headers?

EXERCISE: Let's do another one. Follow along with the instructor and use the Excel built-in naming feature to name the range of cells I13 - K17.

1. First, make sure to move to cell I13.
2. Formulas tab of the ribbon, ALT+M
3. Location: Defined Names group of the lower ribbon
4. Define Name button, M
5. Define Name, D. The New Name dialog box opens.
6. Type in the following name with NO SPACES: TitleRegion2..K17, and then press ENTER to close the Define Name dialog box.
7. Now try navigating left and right or up and down within the range. Do you hear JAWS reading the row and column headers?

The original method of writing the name was TitleRegion#.TopLeftCell.BottomRightCell.WorksheetNumber. The later versions of JAWS now let you omit the top left cell and the worksheet number. You may use this technique throughout a workbook containing many worksheets, as long as each name is unique. Here is the new naming logic:

• UniqueName, TitleRegion1..G10 - Each name in a workbook, no matter what worksheet it is in, must have a unique name. If you have 6 regions you could have TitleRegion1, TitleRegion2, etc. through TitleRegion6. Or you can use other names, such as TitleFirstQuarterSales, TitleSalesQ1, TitlePartNumberAndPrices, and so on, as long as each name is unique and not repeated anywhere within the same workbook.
• Two periods with no space, TitleRegion1..G10 - formerly used to represent the top left cell coordinates (would have been between the two spaces).
• BottomRightCoordinates, TitleRegion1..G10 - The final number represents the bottom right cell coordinates.

If the range has only row titles or only column titles use the following conventions:

• RowTitleRegion1, RowTitleFirstQuarterSales, RowTitleSalesQ1, RowTitlePartNumberAndPrices, etc.
• ColumnTitleRegion1, ColumnTitleFirstQuarterSales, ColumnTitleSalesQ1, ColumnTitlePartNumberAndPrices, etc.

When column titles span multiple rows, select all of the rows before creating the ColumnTitle definition. When the row titles span multiple columns, select all of the columns before creating the RowTitle definition.

JAWS Keystrokes for Excel

These are just a few of the keystrokes for JAWS in Excel. For more, press INSERT+F1 twice quickly while in Excel. The JAWS help topic for Excel opens. Choose the link JAWS Commands for Excel. Choose the following link for a list of many other JAWS keystrokes Opens a new window.

Prior page

Next page