Friday 14 October 2016

Column and Row Title Reading - MS Excel - JAWS V16

Setting JAWS to Read Column and Row Titles in MS Excel

One of the most popular questions I'm asked by JAWS users is how to set up the automatic title-reading in Excel so that, when you are in a table of data, JAWS will automatically read the column or row titles as you move around the data. I actually covered this subject with NVDA in a previous post here all the way back in 2014 but, as one reader recently pointed out, a more JAWS focused piece on this subject would be appreciated. So, finally, here it is - although please note that what we are doing here will also work in NVDA.

Setting the Column and Row Titles Manually

Once upon a time, setting the column and row titles in Excel with JAWS was all about locating the right area(s) in the worksheet and manually setting the titles. You'd move the cursor to the right row (if you were setting column headings) or the right column (for row headings) then use a specific set of hotkeys to assign these in JAWS. Now, this is something that you can actually still do with JAWS, and it's a technique that everyone who uses JAWS and Excel should learn to do. So, with that in mind, let's quickly run through the basic manual process with JAWS V16:

  1. Open a new workbook in Excel and type Bought in cell B1 and Sold in cell C1.
  2. Use the ARROW KEYS to move to cell A2 and type Apples, DOWN ARROW to cell A3 and type Bananas and DOWN ARROW to cell A4 and type in Oranges.
  3. With your column and row titles in place, move up to cell A1 - note that this is the only cell where the column heading row (row 1) and the row heading column (column A) intersect.
  4. Now, with focus always in A1, use the following hotkeys to set both row and column titles. (By the way you may want to limber up the fingers before attempting them):
    • INSERT ALT CONTROL C to set the column titles to the current row (row 1).
    • INSERT ALT CONTROL R to set the row titles to the current column (column A).
  5. When you have done this, test out the column and row reading. Move between cell B2 and C2, in each case JAWS should indicate the column title as you move. Also, move between the second, third and fourth rows - JAWS should indicate the row title. If not, then try saving your Excel file and reopening it. When you have finished experimenting, close the file.

This all manual approach is still very useful and it is one that I always coach JAWS users in; having full manual control of where and when to set your column and row headings will always come in handy. The downside of having to take this route however is that, because the title information sits in a JAWS settings file, if you email the Excel workbook to another JAWS user you're forcing that person to take the same steps you did. Okay, you can send a settings file on but really you're kind of passing on a bit of extra work here when the user probably just wants to work with the data in the spreadsheet. Which all brings us nicely back to where we started - setting automatic title reading in Excel.

Setting Named Ranges for Automatic Title Reading

Anyone who has used Excel more than a few times will know that you can highlight a range of cells and give them a name. These named regions are generally done to make things easier to understand in the worksheet. For example, several columns of cells could be given the name of "sales" and this name can then be referenced in a formula: =SUM(Sales) - instead of using the typical range details such as: =SUM(B4:G55). If you think of a really complex worksheet with loads of data I'm pretty sure you can see the benefit of having to remember names and not cryptic cell references.

However, what is not commonly known is that JAWS and NVDA look for specific named ranges in a worksheet and can then use that information to relay the associated column and row titles back to the reader. Clearly, the benefit of this is huge because the author or creator of the worksheet can set this up and it will just work for all the JAWS users who access that data. It also takes away that often torturous task of trying to figure out just where the author put the column and row headings in the first place!

Okay, so let's go through setting this up. We need to be a little careful here because JAWS is very pedantic about the naming - that's the point in fact, JAWS is looking for specific names in the worksheet as locators for the data it needs to echo. The following example will sound familiar if you worked through the previous one:

  1. Open a new workbook in Excel.
  2. In cell B1 type Bought in cell C1 type Sold.
  3. Use the ARROW KEYS to move to cell A2 and type Apples, DOWN ARROW to cell A3 and type Bananas and DOWN ARROW to cell A4 and type in Oranges.
  4. As before, move up to cell A1 - this being the cell where the column heading row (row 1) and the row heading column (column A) intersect. As they intersect in this same cell, we can set the column titles and the row titles in one go.
  5. IMPORTANT NOTE: Before you move to the ribbon to set your named ranges you must ALWAYS make sure you have placed the cursor in the right cells for the title reading effect you are trying to achieve. So, if you are setting both column and row titles at the same time, focus your cursor on that single intersecting cell. If you are setting row headings only, then put your cursor in the column containing your row headings. Similarly, if you are setting column title reading only, focus the cursor in the row that contains the column headings to be read. If your column headings are split between two rows then select the rows before continuing with the next steps.

  6. Now use ALT to move to the upper ribbon in Excel and move to the Formulas tab either manually with the LEFT or RIGHT ARROW or by using the M hotkey.
  7. With the Formulas tab active, DOWN ARROW to the lower ribbon and TAB along until you hear JAWS indicate: "Define Names group box, Name Manager button." Use SPACEBAR to activate this button and move into the Names Manager dialog box. (Note: the full Office hotkey sequence for this is ALT, M, N.)
  8. TAB around if you want to explore then focus on the New... (ALT N) button and use SPACEBAR.
  9. Focus will now be in the New Name dialog box, TAB around to get a feel for the options. I'm using Excel 2010 and the choices I have are as follows:
    • Name: This is where you need to type the exact text label that JAWS requires, more on this in a short while. The hotkey for this field is ALT N.
    • Scope: Here you indicate if the name is sheet specific or for the workbook. As far as we are concerned, the default setting of workbook is fine. The hotkey for this is ALT S.
    • Comment: This is where you can type in a comment for the named range. I always suggest adding a comment to let everyone know that this named range is required for JAWS title reading and shouldn't be deleted. The hotkey for this is ALT O.
    • Refers To: The last field you need to check shouldn't need any input - as long as you placed the cursor correctly in the worksheet of course! The hotkey for this field is ALT R.
  10. With focus in the Name field, and as we are setting both row and column titles at the same time because of the intersecting cell, I can type in the required label which is: Title1. That is: capital t, i, t, l, e, number 1 - all as one word. Note: the number on the end refers to the position of the worksheet in the workbook - so if it was the second worksheet it would be a 2 instead of a 1 and so on.
  11. New names dialog box showing the Title indicator for JAWS title reading.
  12. TAB takes me to the Scope field, it is set to "workbook" already, so that's cool.
  13. TAB again takes me to Comments so I type in my standard JAWS title reading warning about how if this gets deleted there will be all out thermonuclear war in the office. Only joking, but you get the message...
  14. Another TAB moves focus to Refers To which is set right, pointing to Sheet 1 and cell A1. If you position your cursor before you start the process this will always be right and you can just TAB on to the OK button and use SPACEBAR.
  15. Focus will drop back to the Name Manager dialog box and now TAB around to the Close button and use SPACEBAR to return to the worksheet.

At this point I normally save the worksheet and close Excel completely, before going back in to the file and testing out the column and title reading to see if it has worked. There are times when you set this and try it out that it doesn't seem to be working but then you'll open the file back up after a reboot and - lo and behold - the title reading works! So if the auto title reading doesn't work straight away don't be too alarmed, try a restart and see what happens then.

If it still doesn't work - try NVDA. Ouch...

Setting Column or Row Titles Only

Of course, there will be times with certain layouts and types of data that you will only have a row of column headings or a column of row headings. In each of these cases you need to use a different text label than "Title" - which you only use when there is an intersecting cell between your column and row titles.

Let's go through another example, this time a small table of data that uses names as row headings:

  1. Move to a new worksheet in an existing workbook or create a whole new workbook.
  2. In cell A1 type in a name - I chose "Marty" no idea why - and use ENTER so that by default the cursor will land in the cell underneath. Type another name in cell A2 and use ENTER to move to cell A3, add another name in that cell.
  3. Now that you have a few names in the column to act as your row headings, move to cell B1 and type in a random number. Do the same in cells B2 and B3.
  4. When you have added your numbers, move the cursor back and focus on a cell in column A. It is important to focus the cursor so that it is in the right place in the spreadsheet for what you are setting - so, in this example, my row headings are in column A so I need to focus in there before going to the upper ribbon.
  5. Navigate to the Name Manager button in the Formula ribbon tab as before and activate the New... button. The quick hotkey Office sequence for this is ALT, M, N, ALT N - if you do this focus should be in the Name: field of the New Name dialog box.
  6. In the Name: field, this time around, type in the following label: RowTitle1 (capital R, o, w, capital T, i, t, l, e, number 1 - all as one word. (By the way if you are on worksheet 2 of your workbook, replace the number 1 with number 2 - remember the number at the end indicates the worksheet number. If you created a new workbook and you are on sheet 1 of that workbook then you can stick with number 1 or even omit the number entirely.)
  7. TAB through the other fields and complete as necessary, maybe including a comment to stop accidental deleting of the new name region.
  8. TAB to the OK button and use SPACEBAR, then TAB round to the Close button and use SPACEBAR also. With focus back in the worksheet, test out the title reading with JAWS.

But what if you need to set column titles and not row titles? In this case you do exactly the above but replace RowTitle1 (capital R, o, w, capital T, i, t, l, e, number 1) with ColumnTitle1 (capital C, o, l, u, m , n, capital T, i, t, l, e, number 1) instead.

Now, Something For Everyone To Do

The great thing about these named range best practice techniques is that everyone can adopt them to help make their Excel worksheets more accessible to speech users. So, if you are reading this and want to make a small difference to someone's day in the office, get this information passed around.

Let's do a little recap just to finish...

  • For column and title reading, where the column headings row and the row headings column actually intersect, use the label: "Title1" (where 1 indicates titles for Sheet 1).
  • For column reading only, use the label: "ColumnTitle1" (where 1 indicates titles for Sheet 1).
  • For row reading only, use the label "RowTitle1" (where 1 indicates titles for Sheet 1).
  • Remember the number included at the end of the text label indicates the number of the worksheet. For example, "RowTitle3" sets the row titles for the third worksheet in the workbook, while "ColumnTitle2" will set the column titles to the current row in the second worksheet. Also, before you go to the Names Manager dialog box, you must ensure that you place your cursor in the right cell, column or row for the titles you are attempting to set.
Name Manager dialog box, shows three title assignments with the first ColumnTitle1 assignment spanning two rows.

And always remember, should the unthinkable happen and JAWS just refuses to pick up on the title regions in the worksheet (yes, this has been known to happen quite a lot) you can also insert a comment in cell A1 containing helpful information, such as the start cell location of any column and row headings. That way another JAWS user can go straight to those areas with the headings and set things up manually using the techniques mentioned at the top of this post. In fact, I post something a while back on using comments for exactly these purposes and you can find it here in the blog.

In a future post I'll explore the multi-region options for automatic title reading but for now, your mission is simply to spread the word, and get everyone you know who uses Excel to spend a few extra seconds creating useful Title, ColumnTitle or RowTitle name ranges.