Microsoft Excel 2016, Lesson Three

JAWS® screen reading software does not read the text area where you type in chart titles very well. Here is how to accomplish adding or editing chart titles.

EXERCISE: Open the finished workbook file from lesson two that has the chart in it and follow along with the instructor.

So, in lesson three, one of the things you will notice is that in some places JAWS does not read the text area where you type in the chart titles, and certain other places like that. There are ways to accomplish that, and as a work around I will show you some other things. There is a link in your lesson that reads "workbook file from lesson two." I already have that open and I am going to switch to it in just a moment. The first thing we are going to talk about is adding chart titles. So, let me go ahead and switch over to the lesson.

Adding Chart Titles

Press CTRL+SHIFT+O to open the JAWS select an object dialog box.
Select the chart you want to work with in the list and press ENTER. Focus moves to the chart object.
Press ALT followed by JC to move to the Design contextual tab of the ribbon.

NOTE: The contextual tabs for charts have changed quite a bit from the Excel 2010 version. In 2010 the Chart Tools contextual tab had three sub-tabs: Design (JC), Layout (JA), and Format (JO). In 2016 Excel there are only two sub-tabs: Design (JC) and Format (JO). The Layout sub-tab has been removed.

Press TAB to move to the lower ribbon Chart Layouts group. Focus is on the Add Chart Element submenu. Press ENTER. Alternatively, you can press A to open this submenu.
Press DOWN ARROW to explore the different choices, all of which have more submenus. Choose Axis Titles submenu (A), and open that.
Press DOWN ARROW to find Primary Horizontal (H), and then press ENTER. An edit box appears below the horizontal axis with the text "Axis Title" inside it.
Press ENTER to go into the edit layer for this text object. JAWS does not read it, but it is selected.
Press the DEL key to delete this text and type in a new horizontal axis title. You will be able to hear each character as you type, but JAWS does not read if you try to use the arrow keys to read what you type. I typed in July.
Press ESC once to get out of edit mode. Focus returns to the text object layer.
Press ESC once more and focus moves to the main chart object again. NOTE: If you want to check this while the chart has focus, press INSERT+CTRL+C to view the chart in the virtual viewer. You can read the titles there. I pressed UP ARROW to stop the speech there, because if you press CTRL, remember, you will put focus on the Chart Tools toolbar that is off to the side of the chart when it gets focus.

So, the virtual viewer has opened. I will press CTRL+HOME to go to the top of the virtual viewer. And then I will press DOWN ARROW to move through that. And there I heard the category axis has the word July as its title. And I could continue to read that if I wished.

Press ESC to close the virtual viewer when finished.
Press ESC one more time to take focus off the main chart object and return to the worksheet area.

Editing Chart Titles

To edit the contents of existing titles in a chart, such as a chart title, the horizontal or vertical axis titles, etc., do the following:

Press CTRL+SHIFT+O to open the JAWS select an object dialog box.
Select the chart you want to work with in the list and press ENTER. Focus moves to the chart object.
Press ALT followed by JA to move to the Format tab of the ribbon.
Press E to move to the chart elements edit combo box on the lower ribbon.
Press DOWN ARROW to move through the elements listed for the chart, and then press ENTER on the one you wish to edit, such as the Horizontal (Category) Axis Title. That is it. I will press ENTER. Focus moves to the text object for the axis title.
Press ENTER to go into edit mode. The text in the text object is selected.
Do not try to edit the individual letters of text here, as JAWS does not read it. Instead, press the DEL key to delete the text, and then retype the text as you wish it to appear. I typed in July 2017.
Press ESC once to get out of the edit mode. Focus returns to the text object layer.
Press ESC once more and focus moves to the main chart object again. NOTE: If you want to check this while the chart has focus, press INSERT+CTRL+C to view the chart in the virtual viewer. You can read the titles there. Press ESC to close the virtual viewer when finished.
Press ESC one more time to take focus off the main chart object and return to the worksheet area.

NOTE: An element that is called "axis" refers to the values of that element. An element called "axis title" refers to the title shown in the chart for that element. The Value axis is the vertical one and the Category axis is the horizontal one.

Data Validation and Input Messages

Authors of workbooks using the Microsoft® spreadsheet program Excel® can use the Input Message feature of Excel to provide outstanding feedback and instructions for users of JAWS. For this section of the training let us use the second worksheet in the workbook, the one currently titled Sheet1.

I will press CTRL+PGDN to move to Sheet1, which is sheet 2 in our sample workbook. You can verify this if you wish by pressing INS+F1. So, the worksheet name is Sheet 1, and its number is 2. I will press ESC. Since worksheets can be rearranged you cannot count on the name of the sheet to indicate the position the sheet has within the worksheet order. Let us rename this sheet to something more meaningful. You will recall we practiced naming worksheets in lesson one.
Press ALT+H to move to the Home tab of the ribbon, and then O to activate the Format button in the Cells group of the lower ribbon. A menu opens with different choices.
Press R for Rename Sheet, and then give this sheet the name Data Validation.
Press ENTER when you are finished. Focus returns to the worksheet.

Next, let us look at the Data Validation dialog box:

First, I will move to cell C3. It really does not matter where you do this part of the exercise, so it can be anywhere in the worksheet if it is a blank cell.
Next, activate the Data tab of the ribbon, ALT+A
Location: Data Tools group of the lower ribbon
Choose the Data Validation split button, V
And then choose Data Validation, V

A three-page Data Validation dialog box opens with focus on the Settings page. This dialog box allows you to choose the kinds of messages, titles, prompts, and errors to use, and whether tool tips are shown or not.

Press TAB to move to the Allow combo box for Validation Criteria. Here you can choose to allow Any Value, Whole Number, Decimal, List, Date, Time, Text Length, or Custom based on formulas. The first choice, Any Value, is used most often for names or general alpha-numeric input. The Time and Date choices force input to be in the form of a valid time or date and offer you two more fields where you can restrict the values to a range of dates or times. The same is true for several of the other choices. For example, if you choose Whole Number you can then restrict input to include the following choices:

Between
Not between
Equal to
Not equal to
Greater than
Less than
Greater than or equal to
Less than or equal to

The choices available depend on the item selected in the Allow combo box.

Creating a Dropdown List Using the Settings Page

If you are not already there, press TAB to move to the Allow combo box and then do the following:

Choose "List" in the Allow combo box. Focus moves to the Source edit area.
Press TAB to move to the Source edit box.
To define the list locally, type the list values separated by commas. Type the following: Fall, Winter, Spring, Summer to create a list of college semesters.

To use a range of cells with a defined name, type the equal sign (=) followed by the name of the range. To use cell references, select the cells on the worksheet and then press ENTER.

NOTE: If you use the ARROW KEYS in this edit area a selection rectangle moves in the worksheet from one cell to another in the background and the address of that cell shows up in the edit field. This is fine for selecting a list of values either from a single cell or a group of cells, but it may be easier to type in text values, depending on your goal. To toggle between "point" mode and edit mode, press F2. The default is point mode.

Press TAB to move to the "In-cell dropdown" check box and make sure it is checked.
Press TAB to move to the OK button, and then activate it with the SPACEBAR. The dialog box closes and focus returns to the worksheet.

When focus is in this cell a graphic picture of a button with a down arrow appears next to the cell. Mouse users normally click to open the dropdown list. JAWS announces that the cell has a dropdown, indicating this. Keyboard users can press ALT+DOWN ARROW to open the dropdown list. Press ENTER to choose one of the items in the list when you are ready.

NOTE: You should tell JAWS users how to access the dropdown items by using an input message. The next section discusses how to create input messages.

Creating an Input Message Using the Input Message Page

To create an input message, open the Data Validation dialog box again.

Make sure focus is in cell C3 before continuing.
Activate the Data tab of the ribbon, ALT+A
Location: Data Tools group of the lower ribbon
Choose the Data Validation split button, V.
Press ENTER on Data Validation, V.
Press CTRL+TAB to move to the Input Message page of the multi-page dialog box.
Press TAB to move to the "Show input message when cell is selected" check box and make sure it is checked. If you uncheck this, JAWS will not announce that there is an input message associated with the cell, nor will JAWS speak the message. It is also not shown visually.
Press TAB to move to the next control, the "Title" edit box. The title, if created, becomes part of the message. Visually the title appears in bold text above the rest of the text in the input message. Creating a title is often redundant, so the author recommends leaving the title blank unless you need it.
Press TAB to move to the next control, the "Input message" edit box. Type in an input message. You do not have to type the restrictions or parameters in the message, since JAWS reads those aloud automatically. There is a limit of 225 characters for this multi-line edit field. For this exercise, type the following (without the quotes): "Press ALT+DOWN ARROW to open the dropdown list, and then press ENTER to choose one of the items."
Press TAB to move to the OK button, and then activate it with the SPACEBAR. The Data Validation dialog box closes and focus returns to the worksheet.

When focus moves to a cell with an input message, the input message appears and JAWS reads the text out loud. I will press LEFT ARROW to move away from this cell. And I will press RIGHT ARROW to move back and listen to the message.

Error handling using the Error Alert Page

To create an error alert, do the following:

Make sure focus is in cell C3 again.
Activate the Data tab of the ribbon by pressing ALT+A
Location: Data Tools group of the lower ribbon
Choose the Data Validation split button, V
Press ENTER on Data Validation, V.
Press CTRL+TAB to move to the Error Alert page of the multi-page dialog box.
Press TAB to move to the first check box, "Show error alert after invalid data is entered" and make sure it is checked.
Press TAB again to move to the Style combo box. There are several choices here, Stop, Warning, and Information.

Stop Error

The Stop error choice does not allow any other input than what was set in the validation criteria established previously. If you choose this item and incorrect data is placed in the cell, a dialog box with title and text of your choosing opens. The choices for this dialog box are Retry, Cancel, or Help, with Retry as the default button.

Warning Error

The Warning error choice gives users greater flexibility for input than what you might normally allow. It opens a dialog box with a title and text warning of your choosing. The choices for this dialog box are Yes, No, Cancel, and Help, with No as the default button.

Information Error

The Information error choice also gives users greater flexibility for input than what you might normally allow. It also opens a dialog box with a title and text warning of your choosing. The choices for this dialog box are OK, Cancel, and Help, with OK as the default button.

For now, make sure Information is selected and press TAB to move to the next control. Focus moves to the Title edit box. As mentioned previously, if the title is redundant, leave it blank. This is, of course, an option some people may choose to use. For practice, type in a short title. I will type in the word NOTE.
Press TAB to move to the next control. Focus moves to the Error Message edit box, which is a multi-line edit box. This edit box allows a maximum of 225 characters. Type in a short error message. I will type Choose the semester you plan to attend from the dropdown dialog box.
Press TAB to move to the OK button, and then activate it with the SPACEBAR. The dialog box closes and focus returns to the worksheet.
Next, test this error message. Try typing any character in the cell C3. What happens? The message displays with an OK button. When you press ENTER on the OK button, the error message disappears, but also notice that the character you typed is allowed to remain in cell C3.

Spend a few minutes on your own experimenting with the three styles of error alerts to see the different dialog boxes they present to the end user.

Creating a Date Input Field

EXERCISE: Follow the same procedures above but create a Date Input field in a cell. Also, in this instance, use the Error Alert page to give the end-user a warning if the input is incorrect.

Move to an empty cell in the worksheet. I will move to cell F3.
Activate the Data tab of the ribbon, ALT+A
Location: Data Tools group of the lower ribbon
Choose the Data Validation split button, V
Press ENTER on Data Validation, V
Press CTRL+TAB if necessary to move to the Settings page.
Press TAB to move to the Allow combo box for Validation Criteria. Choose "date" in the list, and then press TAB to move to the Data combo box.
For this example, assume the company was created in the year 2007, so choose "greater than" as the data validation and press TAB to move to the next control, which is the Start Date.
Enter a start date of 12/31/2006. This prevents anyone from typing a date prior to 01/01/2007.
Press TAB to move to the Ignore Blank check box and make sure it is checked.
Press CTRL+TAB to move to the Input Message page.
Press TAB to move to the "Show input message when cell is selected" check box and make sure it is checked.
Move to the Input Message edit box and type something like the following: "Enter the date of your request."
Press CTRL+TAB to move to the Error Alert page.
Press TAB to move to the Show error alert after invalid data is entered checkbox, and make sure it is checked.
Press TAB to move to the Style combo box and choose Stop.
Press TAB to move to the Error Message edit box and type in something like the following: "You must enter a date greater than December 31, 2006."
Press TAB to move to the OK button and activate it to close the Data Validation dialog box.

Notice if you type in a date prior to 2007, the error message kicks in. And notice that, in this case, the Retry button is active, which puts focus right back in the cell so you can try again.

Fill in a Series

Sometimes it may be helpful to have Excel fill in a series for you. This can be a big time saver. For example, instead of typing out every month of the year in a series you can simply type January and have Excel fill in the rest of the months for you.

Move to an empty cell in the worksheet. I will move the cursor to cell A5, the beginning of the fifth row.
Type in the word January.
Hold down the SHIFT key and press RIGHT ARROW eleven times to select the range for the names of the months for the rest of the year. So, I typed the word January, I did not press ENTER or TAB. I will hold down the SHIFT Key and press RIGHT ARROW eleven times.
Home tab of the ribbon, ALT+H
Location: Editing group of the lower ribbon
Fill button dropdown, FI
Series, S. The Series dialog box opens. Focus is in the step value edit box.
Press ALT+F to select the AutoFill radio button, and then press ENTER to activate the OK button. The dialog box closes and Excel has typed in the months of the year for you in the selected range.

Unlocking Cells Before Protecting a Worksheet

If you create a report, for example a time sheet, you may have places for someone to put their hours and labels such as the days of the week. To prevent people from accidentally erasing or typing over the labels you can protect those cells. Where there are formulas, such as for the total hours of the week, it is a good idea to protect those cells as well.

Before protecting a worksheet, you need to unlock the cells where you want people to fill in data; otherwise they will not be able to fill in the data.

First, move to a cell you want to keep unlocked (or select a range of cells). Let us mark the following cells as being unlocked on worksheet two:

C3, the cell for the dropdown list, F3, the cell for the date, and the range of cells from A6 through L6, cells where people using the worksheet might type in some data in the row beneath the months of the year.

First, move to cell C3.
Press ALT+H to activate the Home tab of the ribbon.
Location: Font group of the lower ribbon
Choose the Format Cells button, FN. Alternatively, press CTRL+1. The Format Cells dialog box opens.
Press CTRL+TAB to move to the Protection tab page.
Press TAB to move to the checkbox "locked" and press SPACEBAR to uncheck it.
Press ENTER to close the Format Cells dialog box.
Repeat the process for all cells where data is to be entered. I will move to F3.
I will press CTRL+1
After you have unlocked cells C3 and F3, do the following to move to and select the cells for A6 through L6 in one step:

Open the Go To dialog box by pressing CTRL+G. Focus is in the reference edit box.
Type in the following, A6:L6, no spaces, and then press ENTER. The focus moves to and highlights the entire range of cells from A6 to L6 inclusive.
Go to the Format Cells dialog box using CTRL+1, and mark this range of cells as unlocked.

Protecting the Worksheet

Protecting the worksheet is the next step.

First, I will press CTRL+HOME. I did that just to unselect the cells that had been selected before.
Move to the Review tab of the ribbon by pressing ALT+R.
Location: Changes group of the lower ribbon
Choose the Protect Sheet button, PS. A Protect Sheet dialog box appears with focus in the password field.
Type in a password if you want to prevent others from changing the form. I will leave the password field empty for this exercise.
NOTE: In Excel 2016, you only have to press ENTER once on this dialog box. You do not have to confirm the password, as you did in earlier versions of Excel.
Now, try typing anything in an area that is protected. I will press SPACEBAR. Also, try typing in the unprotected areas for comparison.

AutoFit Column Width

There is one more thing you may want to do. The text for the words September and November, in I5 and K5 respectively, visually do not fit into view. The "e" and "r" of September are visually hidden behind the next word, October; the "r" in November is visually hidden behind the next word, December. This is because the width of cells in Excel is set to a fixed size. You can, however, tell Excel to widen the cells to autofit the widest text in each cell. Here is how:

First, you need to unprotect the worksheet. Press ALT+R to move to the Review tab of the ribbon, and then press PS to activate the Unprotect Sheet button. It is in the Changes group of the lower ribbon. If you had chosen a password to protect the sheet you are now prompted to type in the password. Since I did not specify a password, the protection is simply turned off.
Next, move to cell I5, September.
Press ALT+H to move to the Home tab of the ribbon, and then press O to activate the Format button dropdown. A menu of choices opens.
Press I. Alternatively, press DOWN ARROW to move down the menu to AutoFit Column Width, and press ENTER. The cells now adjust to automatically fit the width of the text in cell I5.
Move to cell K5 and repeat the process. ALT+H, O, I.
Finally, protect the worksheet again, and then save the changes. Press ALT+R to move to the Review tab of the ribbon, and then PS to activate the Protect Sheet button. Choose to assign a password or not, and then continue with the lesson.

NOTE: You can use the JAWS Quick Settings to have Excel announce the cell text visibility. Open JAWS Quick Settings (INSERT+V) while Excel has the focus, and then look for the "cell text visibility detection" check box, which is not checked by default.

Sorting Data

To sort data in a worksheet, first select the data you want to sort. Alternatively, put the cursor in a range of data (Excel will automatically select the range for you). Then do the following:

EXERCISE: Open the practice Excel file Contacts.xlsx and follow along with the instructor.

I will switch back over to the text of the lesson.
I will press TAB. There is the link for the Contacts file, and I will press ENTER.
I will press ENTER to open that.
And I pressed WINDOWS+UP ARROW to maximize this window. Focus is in cell A1. A1 has the First Name, and then you have Last Name, Street Address, City, State, and Postal Code, going from left to right. And then you have the information below that.
I am going to move to cell A2 just to put focus inside the data range.

Move to the Data tab of the ribbon, ALT+A.
Location: Sort & Filter group of the lower ribbon
Choose the Sort button, SS. The Sort dialog box opens.
Press TAB to move to the first sort by combo box.
Press DOWN ARROW to open a list of choices.
Press DOWN ARROW to find the column you want to sort by. I am going to choose Last Name.
Press TAB twice more to move to the Order combo box. I pressed the Say Line keystroke to read that, INSERT+UP ARROW. It is set to sort from A to Z.
Choose whether to sort from A to Z or from Z to A.
Press ENTER. The dialog box closes and the data has been sorted per your criteria.

Printing in Microsoft Excel

Here is a link in your documentation to a short document that explains a few things for printing in Microsoft Excel.
Opens a new window Setting Page Breaks.

You can use the practice file Opens an Excel file Print Area in Excel to practice this technique.

For now, I am going to move to the Next Page link and activate that. As always, there is a link here for the entire contents of today's presentation. If you choose that link you will get a ZIP file with all the documentation. Thanks again for joining us in the Excel 2016 series with JAWS.

Prior Page