Excel 2016, Lesson Two

Use this link to open the sample file after lesson one Opens in MS Excel.

Use the AutoSum Feature of Excel to get Monthly Totals

1. Move to cell G6. This is the end of the row of flour used for the month. Press the Excel keystroke to AutoSum the row, ALT+EQUALS.

NOTE: Do not press the arrow keys to try and read the currently highlighted text. If you do, focus in the worksheet moves as you press the arrow keys. Excel allows you to "point" to the cells you want to include in the formula. If you move the arrow keys now, the formula will change to show the coordinates of the cell where the focus is.

TIP: At the present time, JAWS does not read here. You can press INSERT+CTRL+F2 to read the formula bar after you have inserted it and move back to the cell. If necessary, you can open the JAWS Speech History to select, and then copy the text of the formula after performing the step to read the formula bar.

2. Press ENTER to accept the default range. The formula for the sum is inserted into cell G6, and focus moves to cell G7.
3. Press ALT+EQUALS to sum the row for the total sugar used for the month.
4. Press ENTER to accept the default range. The formula for the sum is inserted into cell G7, and focus moves to cell G8.
5. I am going to press UP ARROW to move back to cell G7. I will press INSERT+CTRL+F2 to read the formula bar, to see if we got that formula correct. And that is correct. I am going to press UP ARROW to go to G6, and I will press INSERT+CTRL+F2 here to read that formula. And that one is correct as well. Let us move focus back down to cell G8.
6. Press ALT+EQUALS to sum the row for the total meat used for the month. What happens? Excel does not have enough information to know whether you want the column above summed or the row to the left summed. It assumes, incorrectly, that you want the column above summed. I will press ENTER to accept this, and then UP ARROW to go back.
7. UP ARROW to G8 again.
8. Let us press F2 to look at and edit the formula that was put there by Excel. Function key F2. And I will press LEFT ARROW to read what is in the parentheses for the summation. So we have the formula that says equals sum G6, G7. There is a colon between, no spaces. I did a Say Line there to read that line. And, of course, JAWS is reading as I press LEFT and RIGHT ARROW. The actual formula that we wamt tp sum the row for meat would be out to the left. The sum we want is C8 to F8, so I am going to press RIGHT ARROW, change that to a C, delete the 6 and change that to an 8, press RIGHT ARROW to go to the G7, delete the G and change that to a C, delete the 7, and type in an 8. Let us do a Say Line, INSERT+UP ARROW. Oops, that should be F8. Let us try that again, INSERT+UP ARROW. Great, that is what we want, so I am going to go ahead and press ENTER.
9. The correct sum is inserted into cell G8, and focus moves to cell G9.
10. Press ALT+EQUALS to sum the row for the total oil used for the month. What happens? Excel does not have enough information to know whether you want the column above summed or the row to the left summed. It assumes, incorrectly, that you want the column above summed. I will go ahead and press ENTER, and then I will press UP ARROW to move back to it. The range we want here is C9 t o F9. Let us press F2 to see what Excel has. And I will press HOME to go to the beginning of the line, then Say Character, which is the equals sign, and then RIGHT ARROW. So the formula that Excel put in here is G6 to G8 and what we want here is C9 to F9, so I am going to backspace over this formula, and type C9 colon F9, and ENTER. Now the correct sum is inserted into cell G9 and focus moves to cell G10.
11. Move to cell C10. I will press LEFT ARROW. The total for the column for Week 1 is already entered in the example. Even though one of the items summed is in gallons as opposed to pounds, the relative number summed itself can show a trend upwards or downwards. Did you hear JAWS announce: "Has formula?"
12. Let us use edit mode to look at this formula. Press F2. JAWS says: edit. I will do a Say Line. So here we have the sum of C6 to C9, which is the sum of flour, sugar, meat, and oil for week one, and that is the correct formula, so I will press ENTER. And I will press UP ARROW to move back to that formula.
13. Let us move to cell D10, I will press RIGHT ARROW, and press ALT+EQUALS to sum the column for week 2. In this case, even though JAWS did not speak it, the range is D6 to D9, which is correct, so I will press ENTER. The sum is inserted into cell D10, and focus moves to cell D11.

You could continue to use AutoSum but for these next calculations just type the formula manually instead.

1. Move to cell E10, and then type =sum(E6:E9) and press RIGHT ARROW to move to cell F10. You could do a Say Line here. Sounds good, and I will accept that.

NOTE: As you type the formula, visually Excel draws a focus rectangle around the cells you indicate in the formula and highlights them.

You could continue to manually enter the column sums like this, however, why not use copy and paste instead?

1. Move back to cell E10.
2. Press CTRL+C to copy the cell. Since there is a formula there, Excel copies the formula, not the contents.
3. Press RIGHT ARROW to move to cell F10, and press CTRL+V to paste the formula there. Excel is smart enough to know that you want to use the same formula but with the data for cells F6:F9. Verify this before moving on.
4. I will press F2 to go into edit mode, and do a Say Line, INSERT+UP ARROW. Sounds good, so I will press ESC.

Next create the formulas for the cost of goods sold for each item for the month.

1. In cell K14 type the following formula: =(G6*J14). I will press CTRL+G first to move to cell K14. I will type in the formula, starting with the equals sign.
2. Press ENTER. The formula is inserted into K14 and focus moves to K15.
3. Move the cursor back up to cell K14. Copy this cell and paste the formula content to cells K15 through K17. CTRL+C, DOWN ARROW to K15. I will hold down the SHIFT key and select the next two cells below that, SHIFT+DOWN ARROW, again SHIFT+DOWN ARROW. I will press CTRL+V now to paste the formula.

Then create the formula that combines these prices into a total for the month in cell I26.

1. I pressed CTRL+G and I will type in I26, and press ENTER to move there.
2. Enter the formula =sum(K14:K17).
3. Press ENTER to accept the formula. The formula is inserted, and focus moves to cell I27.

Finally, format the cells that contain dollar figures to show the currency format. Make sure focus is on cell I26, the total cost of goods sold for the month.

NOTE: You can use the Excel keystroke CTRL+1 to open the Format Cells dialog box. However, the steps to get there from the ribbon follow, just to show you where the format cell/alignment button is found.

1. Home tab of the ribbon, ALT+H
**dc no sound here 15:48
2. Location: Number group of the lower ribbon
3. Format Cells button, F M
4. Press TAB to move to the Category list, and then press DOWN ARROW to choose Currency.
5. Press ENTER to activate the OK button, accepting the default for two decimal places with a dollar sign.
**dc sound begins again here 16:44
6. Select the range of cells J14 through K17, and apply the currency format to those cells as well. I will use the Go To to select that range of cells. CTRL+G, I will type in J14, colon, no space, K17. I will press ENTER to move there.
7. This time I will press CTRL+1.
8. Next, I will press TAB to move to the Category list, DOWN ARROW to Currency, and press ENTER.
9. I will press CTRL+HOME to move to the top of the worksheet.

JAWS Keystrokes for Excel

The table below has just a few of the keystrokes for JAWS in Excel. For more keystrokes, press INSERT+F1 twice quickly while in Excel. The JAWS help topic for Excel opens. Choose the link JAWS Commands for Excel. Then choose the link for JAWS Keystrokes to get a list of many other JAWS keystrokes Opens a new window.

NOTE: Also, remember that beginning with JAWS 16 you can use the new JAWS Command Search feature to find keystrokes you may not remember. To search for a command, press INSERT+SPACEBAR, followed by J, to open the Search for JAWS Commands window. Alternatively, you can choose Search for Commands from the Run JAWS Manager dialog box (INSERT+F2) or the JAWS Utilities menu. An HTML window opens and places you in an edit field. If Forms Mode is set to Auto or Semi-Auto, Forms Mode is activated when the edit field gains focus. I will press INSERT+SPACEBAR, and then J. I will press ENTER to go into forms mode, because I have forms mode set to manual here on my computer. Forms mode should be on for your computer if you have JAWS set to the default settings. Try searching for the word FORMULA. Begin typing and the results area in the lower part of the window automatically updates. Press TAB to move to the first result. I will press NUM PAD PLUS to get out of forms mode, and then I will press the letter H to move by headings from one result to another. For now, I will press ESC to get out of the Search for JAWS Commands.

The table in your documentation has two columns. The first column is the command and the second column is the keystroke. I am just going to read a few of them. There are a few others in this table, as I mentioned, and you can also find more in the JAWS Help system. Feel free to take a moment and pause the recording and explore some of those keystrokes.

Select and Move to Worksheet Objects

NOTE: Use the JAWS keystroke CTRL+SHIFT+O to get a list of objects in Excel. Select an object with the list, and press ENTER to move to it.

Create and Modify Charts

EXERCISE: If you have been following along with the instructor to this point you may use the workbook that you have already been using. If you are just joining the lesson at this point, you may open the sample file that has all the changes made to it so far with the following link. In the sample file Restaurant supply sample spreadsheet Excel 2016-ready to insert chart Opens in MS Excel perform the following steps to create a chart.

Select the Data Range and Choose a Chart Type to Insert

1. Select the range of cells from B5 to F9. I will press CTRL+G. Type in B5 colon, no spaces, F9, and then press ENTER. That has the range selected for weeks one, two, three, and four, and for the rows for flour, sugar, meat, and oil.
2. Insert tab of the ribbon, ALT+N
3. Location: Charts group of the lower ribbon
4. All chart types button, K. The Insert Chart multi page dialog box opens. You could also choose C to activate the dropdown for Column or Bar charts, N for Line or Area charts, etc.

NOTE: The new Insert Chart multi page dialog box represents a major change from the one in Excel 2010 and earlier. In prior versions, this was a single page dialog box. If focus was in the right side of the dialog box where the different chart types that you can choose from appear, one could press UP or DOWN ARROW and focus would move out of one category of chart into the next category above or below. In Excel 2016, the categories are separated, and you cannot move from the list of one type of chart to another accidentally. To switch to another category, you must choose one in the chart type list box on the left side of the dialog box. To choose a chart within a category, press TAB to move to the right side of the dialog box where you can press the ARROW Keys to move between the different charts of that type.

5. Focus is on the Recommended Charts tab. To explore the recommended charts, press TAB to move to the list displayed vertically on the left side of the dialog box. Use the UP or DOWN ARROW to explore these. (NOTE: This was not reading the last time we taught the class, but it is now, thanks to ongoing accessibility efforts by Microsoft and Freedom Scientific.)
6. Press CTRL+TAB to move to the All Charts tab page.
7. Press UP or DOWN ARROW to explore the different types of charts available. As you move up and down the list, different choices for each type of chart appear in the right side of the dialog box.
8. Choose Line and press TAB to move to right side of the dialog box.
9. Press RIGHT or LEFT arrow to move through the different types of line charts available in the Insert Chart dialog box.
10. For this example, choose Line with Markers, and press ENTER. A chart object with the lines for flour, sugar, meat, and oil is inserted into the worksheet. Note that focus is on the chart object, not in the spreadsheet area.

NOTE: When a chart is selected you can work with the chart itself and the various objects that are part of it. To READ the chart press INSERT+CTRL+C after it is selected. (This may take a few seconds.) JAWS collects the chart information into the virtual viewer. You hear the chart information data read to you by JAWS. You may also pause the speech and review the chart information in the virtual viewer as you would read any other type of document. However, because we don't want the chart to be obscured by the virtual viewer, in this application, Excel, the virtual viewer is totally invisible. That gives you the ability to read the chart information while displaying the screen to someone who can see it, perhaps in an audience environment.

New Smart Tags Toolbars for Charts in Excel 2016

When focus moves to a chart object in Excel 2016, there are three new smart tag toolbars that appear vertically to the side of the chart. If you press the CTRL key to silence JAWS, focus moves to the first of these three smart tag toolbars. Repeated presses of the CTRL key move focus from one smart tag toolbar to the other. Each smart tag toolbar has a submenu. In some cases, these toolbars have submenus and check boxes that may not be spoken, so the author recommends not using them at the present time. For example, in the Chart Elements toolbar, each item has a check box. If it is checked, that item is included in the chart, if not checked, that item is not included in the chart. If you cannot hear the status of the check box you will have difficulty determining what is happening on the screen. The three smart tag toolbars and their subparts are described in the outline below:

1. Chart Elements toolbar
1. Axis
2. Axis Titles
3. Chart Titles
4. Etc.
2. Styles toolbar
1. Style tab
2. Color tab
3. Filtering toolbar
1. Values tab
2. Names tab

NOTE: These actions can all be accomplished using the ribbon instead of trying to use the smart tags toolbars.

Move the Chart to a New Location in the Worksheet

EXERCISE: Move the chart to a new location in the same worksheet.

In this example the chart overlaps some of the other cells with data, visually obscuring them. By default, Excel tends to put the chart right in the middle of the visible worksheet area.

NOTE: How can you tell what size the chart is and the area of cells it covers? Read the steps below:

1. First, press ESC to remove focus from the chart object. Focus returns to the worksheet area.
2. Now, select the chart again. Press CTRL+SHIFT+O and select the chart in the JAWS list of objects. Notice that JAWS gives the top left and bottom right coordinates of the chart. In my example I hear that the top left corner of the chart is J13 and the bottom right side of the chart is in cell Q28. (Of course, this may vary from computer to computer.) Any text and formulas for total sales for the month in cells J13 through 28 and K13 through 28 are obscured by the chart.
3. Press ENTER. Focus moves to the chart object.
4. Press the APPLICATION Key, and choose Cut in the menu.
5. Move to cell B12, which is below the number amounts of flour, sugar, meat, and oil used for the chart, and press CTRL+V to paste the chart in this new location.
6. Focus is still on the chart object, not in the spreadsheet area, so press ESC to get out of the object level. Focus returns to the spreadsheet area.
7. The top left corner of the chart begins in cell B12 and extends to cell H26 in the bottom right corner. It may be different on your computer, but you can tell this information by pressing CTRL+SHIFT+O to get a list of objects. The top left and bottom right corners of the chart are shown in the list there.

Change Chart Types

You can change chart types easily in Excel. Here's how:

1. Press CTRL+SHIFT+O to open the JAWS list of objects.
2. Press ENTER on the chart in the list to move back to the chart object.
3. Press the APPLICATION Key and choose Change Chart Type in the menu.
4. The change chart type dialog box opens again. Focus is on the All Charts tab. Press TAB to move to the list of buttons for Column, Line, etc.
5. Pick a new chart type if desired and press ENTER to close the dialog box.
6. Remember to press ESC to get out of the object level and return focus to the spreadsheet area.

Insert Hyperlinks for Navigation

Hyperlinks can be used to move to specific spots in the current worksheet, to specific spots in other worksheets within the same workbook, or to actual Web sites, and more.

EXERCISE: Put a hyperlink in the worksheet that moves focus directly to cell I26, where the total cost of goods sold for the month will appear. Place the hyperlink in cell J11.

First, position the cursor in the cell where you want the hyperlink to appear. Then perform the following steps:

1. First, make sure focus is in cell J11.
2. Insert tab of the ribbon, ALT+N
3. Location: Links group of the lower ribbon
4. Links split button, I2. A dropdown list of recent documents appears.
5. Press I to activate the Insert Hyperlink dialog box. This is near the bottom of this menu, so I could just press UP ARROW as well. The Insert Hyperlink dialog box appears.

NOTE: The top half of the links split button directly opens the Insert Hyperlink dialog box. The bottom half opens the dropdown of recent documents. Keyboard users can press TAB to move to the links split button and press ENTER or SPACEBAR to activate the upper part of the button, and ALT+DOWN ARROW to open the list of recent documents there.

The default for this dialog box is for hyperlinks to point to an external Web site or a computer file. We are going to change this so that the link points to another location in this document. It could also point to a location in a different worksheet within the workbook.

1. Press TAB until you hear JAWS say, "Existing File or Web Page check box checked."
2. Press DOWN ARROW. The Place in this Document check box is selected. (NOTE: Visually these look like buttons.)
3. Press TAB to move to the Type the Cell Reference edit box. If you know the coordinates of the cell you want to move to when the hyperlink is activated, you can delete whatever is in this edit box and just type in the new coordinates. Additionally, you can press TAB to move to a tree view. For now, type in the cell coordinates I26. Don't close the dialog box yet, however.
4. Press TAB to move to the next control. This is a list of the worksheets in the current workbook, as well as a list of the defined names of regions in the workbook.
5. Take a minute to explore this list using the DOWN or UP ARROW. When finished, make sure the current worksheet, Restaurant Supplies, is selected before continuing.

NOTE: JAWS is not currently reading these when pressing UP or DOWN ARROW. As a workaround, press INSERT+TAB, the JAWS key to re-read the current control, after each one is selected.

6. Press TAB until you find the Text to Display edit box. Type something more meaningful here for the actual link name. I used something like Total Cost of Goods Sold.
7. Press CTRL+A to select this text followed by CTRL+C to copy it to the clipboard in preparation for the next step.
8. Press TAB. Focus moves to the Screen Tip button.
9. Activate the Screen Tip button with the SPACEBAR. The Set Hyperlink Screen Tip dialog box appears.
10. Press CTRL+V to paste the text there. The screen tip appears when a mouse user hovers the mouse pointer over a link. You can also type in something more meaningful if desired.
11. Press ENTER to close the Set Hyperlink Screen Tip dialog box. Focus returns to the Insert Hyperlink dialog box.
12. Press TAB to move to the OK button, and then activate it with the SPACEBAR to close the Insert Hyperlink dialog box. The new hyperlink is now inserted in the document.

You can activate a link in Excel by pressing ENTER on it when you find it with the keyboard. Alternatively, use the JAWS list of links, INSERT+F7.

NOTE: The Screen Tip is shown on mouse over, if one is present. Otherwise, the screen tip shows the URL or location pointed to. The screen tip is also spoken by JAWS after reading the cell contents when using the ARROW Keys or TAB to move to the cell.

Focus is in cell J11 where we just created that hyperlink. I will press ENTER to move to the location where it points to, I26.

Monitored Cells with JAWS

With JAWS you can set up to ten different cells that can be monitored without having to move to that location.

To set a monitored cell press INSERT+SHIFT+1 through 0 when you are in a cell that you would like to monitor.

To read a monitored cell press ALT+SHIFT+1 through 0 from anywhere in the document.

Let us set a monitored cell right here. I will press INSERT+SHIFT+1 on the numbers row. To test this I will press CTRL+HOME to move to the top left corner of the spreadsheet. I will press DOWN ARROW to move to cell A2, just so focus is on a blank cell and not on the cell where the formula is for the total cost of goods. That is in cell I26, and we set a monitored cell for that to read that monitored cell. Even though focus is in cell A2, I can press ALT+SHIFT and the monitored cell number, in this case ALT+SHIFT+1. Great!

JAWS-specific Excel Features

JAWS has several keystrokes specific to Excel. Some of them include:

• Monitor cells, see above
• Describe cell border, ALT+SHIFT+B
• List cells with comments, CTRL+SHIFT+APOSTROPHE
• List cells with formulas, INSERT+SHIFT+F
• Read cell comment, ALT+SHIFT+' (APOSTROPHE)
• List Visible cells with data, CTRL+SHIFT+D (This could take some time.)
• List defined monitor cells, CTRL+SHIFT+M
• List worksheets, CTRL+SHIFT+S
• Say range of cells visible in active window, ALT+SHIFT+V
• Report gridline status, ALT+SHIFT+G
• Say active cell coordinates, INSERT+C
• Say formula, INSERT+CTRL+F2, or CAPS LOCK+CTRL+F2 when using laptop layout.

There are others listed here and you can read through those at your leisure when you have a moment. For now, I am going to switch back over to the lesson, and I am going to go to the next page. And here is the contact information for myself and Dusty Voorhees in the training department. There is also a link for today's presentation contents. I will press TAB three times. That is it right there.

Thanks again for joining us in lesson two for Excel 2016.

Prior page

Next page