WARNING: This email originated externally. Exercise caution. Think before clicking links or opening attachments.

Hello,

Creating pivot tables is fully accessible, but you will never find a YouTube from a sighted person explaining it because it is all about dragging stuff from here to there. Below is the section from my book on how to do it with a screen reader. The text here assumes that you already know how to handle multiple sheets and what filtering is. If you need more, contact me offline at davidkingsbury77@... and I will send you the Excel chapter or the entire book if you want that. It is free so I am not trying to seel you anything.

Regards,

David

David

5.7 Pivot Tables

Pivot tables are useful for reorganizing relevant data fields into tables so you can zero in on the information you are interested in analyzing. They are called “pivot tables" because, from a visual perspective, dragging and dropping selected data columns from an initial data table “pivots” these into a new table.

I have used pivot tables for organizing conferences. I needed to track how many people had registered, how much they had paid, add up voluntary donations to the organization, their lunch choice, whether they preferred receiving the agenda electronically, in Braille, or large print, and several other things. I also used pivot tables to present results of a survey with ten or fifteen questions about the accessibility of voting machines. Without resorting to pivot tables, figuring out how to organize and report results would have been messier, much more time-consuming, and prone to computational error. An added bonus to using pivot tables is that, after initial construction, they are quick and easy to update as you add new data. In the conference example mentioned above, I was able to regularly update and report figures as new registration information came in prior to the conference.

5.7.1 Creating Pivot Tables

A generic table consists of up to three parts. Using the simple example of the Monthly Expenses table from earlier in this chapter, and using pivot table nomenclature, these are:

·       Row labels down the leftmost column where the different bill types are listed.

·       Column labels along the top row of the table. In this case, the months of the year.

·       Values, the actual data in the table, in this case, the dollar amounts of each bill.

·       These are the building blocks for constructing pivot tables. Pivot tables can have row labels and values, column labels and values, or both row and column labels and values. All must have values, and values must be numeric (quantities, dollars, percentages, etc.).

In this section, I will reference the quarterly sales by employee worksheet used in the previous section on filtering. I will construct several pivot tables from these data. The first one will be total sales by quarter for each of the stores. This will involve designating Store names as row labels, quarters as column labels, and sales amounts as values. Steps for creating this pivot table are:

1.     Select the data range that includes the column header row and the data. In this workbook, the data range is A2 through G182. With your cursor focus on cell A2, a quick way to select this data range is to press Shift Control End.

2.     Press Alt to open the Upper ribbon, Right arrow once to the Insert tab, tab once into the Lower ribbon, and press Enter on the Pivot table button (the shortcut key is Alt N V).

3.     You land in a dialog box where the selected data range is indicated. Assuming the data range is correct, press Enter here.

4.     You now land on cell A3 in a new blank worksheet. Do not navigate away from this cell. Otherwise, the next step will not work.

5.     Press F6 a couple of times until you hear “pivot table fields, find words to search for.”

6.     Tab once where you will find all your column headers listed.

7.     Down arrow to the first of these that you want to be part of your pivot table, either as a row label, column label, or value. In this case, you first land on “store,” which is the variable we want to use as a row label.

8.     Press the Applications key, Down arrow to Add to row labels, and press Enter. This returns you to the list of column headers. The checkbox for “store” is now checked.

9.     Down arrow to the next column header in the list that you want to be either a column label or value. In this case, it is “Quarter,” which will be the column label.

10. Press the Applications key, Down arrow to Add to column labels, and press Enter. Here again, you are returned to the list of column headers, and the checkbox for “Quantity” is now checked.

11. Down arrow to the column header in the list that you want to be included for values. In this case, it is “Sales.”

12. Press the Applications key, Down arrow to Add to values, and press Enter. You are returned to the list of column headers, and the checkbox for “Sales” is now checked.

13. The pivot table is now ready to view. Press F6 four times until you have returned to the worksheet. You will know when you are there because you land in cell A3 every time. In this case, you hear “Sum of sales A3.”

Note that the order in which you add row labels, column labels and values does not matter. Simply add them in the order they appear as you Down arrow through the list.

The table appears as follows:

·       The five stores are listed down column A.

·       The four quarters are listed across row 4.

·       The values appear in the body of the table. For example, the value of first quarter sales in the Boston store is contained in cell B5.

·       Grand totals by quarter appear across the bottom row of the table.

·       Grand totals by store appear down the last column of the table.

·       The grand total for all stores and all quarters appears in the bottom right cell.

Note that the values only appear as simple numbers. Press Control A to select the entire table, and press Control Shift 4 to quickly change their format to dollars with two decimal places. Also note that you should rename the worksheet using the steps outlined in Chapter 5.5 so you don’t confuse it with other pivot tables you will be creating on other sheets.

I frequently create pivot tables whose purpose is simply to count things. I mentioned above that I used pivot tables for planning a conference. I created pivot tables to count up the number of lunch choices, preferred agenda format (email Braille, large print), and a few more items. For these tables, I designated lunch choice and preferred agenda format as row labels. To add values, I inserted a column called “Quantity,” that I filled with the number 1 for all the entries.

5.7.2 Filtering Pivot Table Data

You can filter pivot tables to narrow your focus. Using the table we have just constructed, let’s say we are only interested in sales of the Boston store in the first quarter. We can filter this table as follows:

1.     Navigate to cell A4 which contains the text “row labels,” and press Alt Down arrow to open the Filter dialog.

2.     Shift Tab to the tree view where you land on the Select all checkbox which is checked. Press the Spacebar to uncheck it, thus unchecking all the store names underneath it in the list.

3.     Down arrow to the Boston checkbox and press the Spacebar to check it. If you wanted to include other stores, you would Down arrow to them and check them too.

4.     Tab once to the OK button and press Enter. The other store rows are now hidden.

5.     To filter the quarters, navigate to cell B3 which contains the text “Column labels,” and Down arrow to open the Filter dialog.

6.     Shift Tab to the tree view where you land on the Select all checkbox which is checked. Press the Spacebar to uncheck it, thus unchecking all the quarters underneath it.

7.     Down arrow to the first quarter checkbox and press the Spacebar to check it.

8.     Tab once to the OK button and press Enter. The other quarter columns are now hidden and only the Boston first quarter sales values appear.

A wide variety of pivot tables can be constructed with a dataset such as that contained in the original worksheet. These might include:

·       Total sales by employee

·       Total sales by product.

·       Product sales by store.

·       Product sales by quarter.

These could be filtered to narrow in on subsets of products, employees, quarters, or stores.

5.7.3 Refreshing Pivot Tables

Once you have created a set of pivot tables, it is quick and easy to update them as new data is inserted in the initial data table, and as long as you use the following trick. If you want to insert new rows, insert them somewhere in the middle of the table rather than under the bottom row. Similarly, insert new columns between existing columns, rather than after the last one. The data range captured for recalculating the pivot tables will automatically adjust. Once you have inserted the new data:

1.     Navigate to any of the sheets containing a previously-created pivot table.

2.     Place your cursor on any cell with data in it.

3.     Press the Applications key, Down arrow to Refresh, and press Enter.

All pivot tables are now updated with the new data incorporated in the calculations.

By contrast, if you add new rows below or new columns to the right of the existing ones, refreshing is more time-consuming. If at all possible, avoid doing this because you will need to repeat the steps below for each pivot table that you want to refresh.

Let’s say I have added data for a sixth store. The previous data range was A2 through G182, and now has been extended to G219. Also let’s say the name of the sheet is “data.” Steps for refreshing an individual pivot table are:

1.     Navigate to the sheet containing one of the pivot tables you wish to update and place your cursor somewhere in this table.

2.     Press the Alt key to open the Upper ribbon, and Right arrow to the Pivot table analyze tab. Note that this tab only appears when your cursor is situated in a pivot table.

3.     Tab into the Lower ribbon to the Change data source split button and press Enter.

4.     You land in an edit field where you type in the sheet with the raw data and the data range. The syntax is: the name of the sheet followed by the exclamation mark and then the new data range. In this case, type “data!a2:g219”without the quotes.

5.     Tab to the OK button and press Enter.

This pivot table is now refreshed with calculations incorporating the new data. Unfortunately, you will need to repeat these steps for all previously-created pivot tables that you wish to refresh. In light of this, it is preferable to insert the rows for the sixth store somewhere in the middle of the data table rather than below the bottom row.

WARNING: This email originated externally. Exercise caution. Think before clicking links or opening attachments.

Hi Robin,

It is very quick and easy to refresh pivot tables provided you do one thing when adding new data. For new data you are adding over time, be sure to insert these rows somewhere above the last row of existing data in your data sheet. If you do that, the data range automatically adjusts, and refreshing the pivot tables already created is quick and easy. In other words, if the last row on your initial data sheet is row 62, insert new blank rows somewhere above row 62 and add the data. Then navigate to any of the pivot tables you have already created, and with your focus on a cell with data in that pivot table, press Enter on refresh in your application key’s context menu. All the other previously created pivot tables will also refresh. If you insert data below that final row 62, refreshing is more tedious.

David

WARNING: This email originated externally. Exercise caution. Think before clicking links or opening attachments.

Hi Robin,

It is very quick and easy to refresh pivot tables provided you do one thing when adding new data. For new data you are adding over time, be sure to insert these rows somewhere above the last row of existing data in your data sheet. If you do that, the data range automatically adjusts, and refreshing the pivot tables already created is quick and easy. In other words, if the last row on your initial data sheet is row 62, insert new blank rows somewhere above row 62 and add the data. Then navigate to any of the pivot tables you have already created, and with your focus on a cell with data in that pivot table, press Enter on refresh in your application key’s context menu. All the other previously created pivot tables will also refresh. If you insert data below that final row 62, refreshing is more tedious.

David