moderated pivot tables in excel


Boston, Jason W
 

Hello all,
I'm looking for best ways to create a pivot table in excel.

Thanks,
Jason

The information contained in this message is proprietary and/or confidential. If you are not the intended recipient, please: (i) delete the message and all copies; (ii) do not disclose, distribute or use the message in any manner; and (iii) notify the sender immediately. In addition, please be aware that any message addressed to our domain is subject to archiving and review by persons other than the intended recipient. Thank you.


Marty Hutchings
 

I knew how to do that 20 years ago.  It was just barely doable back then, so I hope that accessibility has improved since then in that area of Excel.  Good Luck.

On 10/29/2022 11:36 AM, Boston, Jason W wrote:
Hello all,
I'm looking for best ways to create a pivot table in excel.

Thanks,
Jason

The information contained in this message is proprietary and/or confidential. If you are not the intended recipient, please: (i) delete the message and all copies; (ii) do not disclose, distribute or use the message in any manner; and (iii) notify the sender immediately. In addition, please be aware that any message addressed to our domain is subject to archiving and review by persons other than the intended recipient. Thank you.





Ann Byrne
 

At 11:36 AM 10/29/2022, you wrote:
Hello all,
I'm looking for best ways to create a pivot table in excel.

Thanks,
Jason

The information contained in this message is proprietary and/or confidential. If you are not the intended recipient, please: (i) delete the message and all copies; (ii) do not disclose, distribute or use the message in any manner; and (iii) notify the sender immediately. In addition, please be aware that any message addressed to our domain is subject to archiving and review by persons other than the intended recipient. Thank you.




David Kingsbury
 

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.


Van Lant, Robin
 

David,

You may be able to answer a question for me.   I have some files at work that I use pivot tables on. I will add rows to the data over time, kind of like you receiving late registrations for your conference example.   I have my data in a data range.  When inserting the pivot table initially, I try to make a pretty large section for the data area, knowing that my data rows will increase.  The other day, my pivot table was not including data rows I had recently added.  I really struggled to figure out where to go to check that cells the pivot table was looking at for data.  It seemed I had enough selected, but I couldn’t figure out why it wasn’t pulling, so I eventually just scrapped that pivot table and did a new one. It was a simple enough pivot that this was easier than troubleshooting.

 

I’ve had some bad experiences in the past with selecting too large  of a range or sometimes with data tables crashing with JAWS, so I’m cautious.

 

What best practices do you have for selecting enough data range when you build the initial pivot and/or increasing the range  if you need to do that? 

 

Robin Van Lant

 

 

 

From: main@jfw.groups.io <main@jfw.groups.io> On Behalf Of David Kingsbury
Sent: Sunday, October 30, 2022 1:03 PM
To: main@jfw.groups.io
Subject: Re: pivot tables in excel

 

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.



KeyCorp Public

KeyBanc Capital Markets Inc. prohibits the use of texts or other unapproved messaging applications for business purposes.

This communication may contain privileged and/or confidential information. It is intended solely for the use of the addressee. No confidentiality or privilege is waived or lost as a result of any transmission errors. If you are not the intended recipient, you are strictly prohibited from disclosing, copying, distributing or using any of this information. If you have received this communication in error, please contact the sender immediately and destroy the material in its entirety, whether electronic or hard copy.

This communication is for informational purposes only, is not an offer, solicitation, recommendation or commitment for any transaction or to buy or sell any security or other financial product, and is not intended as investment advice or as a confirmation of any transaction. Any market price, indicative value, estimate, view, opinion, data or other information herein is not warranted as to completeness or accuracy, is subject to change without notice, and KeyBanc Capital Markets Inc. accepts no liability for its use or to update or keep it current. Any views or opinions are those of the individual sender, not necessarily of KeyBanc Capital Markets Inc.

The sender of this communication is a licensed securities representative employed by or associated with KeyBanc Capital Markets Inc. Member FINRA/SIPC and may also represent KeyBank National Association (“KeyBank N.A.”). Securities products and services are offered by KeyBanc Capital Markets Inc. Banking products and services are offered by KeyBank N.A.

127 Public Square, Cleveland, OH 44114

If you prefer not to receive future e-mail offers for products or services from Key send an e-mail to DNERequests@... with 'No Promotional E-mails' in the SUBJECT line.


David Kingsbury
 

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


Van Lant, Robin
 

David,

Ok, I’ll have to think how to do that. The data list I use is a chronological list, so I’m adding new items at the bottom so they stay in the order they were given to me. The pivot table is then used to group the information by person.  I’ll see if I can come up with a placeholder last row above which I then insert future entries.

 

 

 

 

From: main@jfw.groups.io <main@jfw.groups.io> On Behalf Of David Kingsbury
Sent: Tuesday, November 1, 2022 7:46 AM
To: main@jfw.groups.io
Subject: Re: pivot tables in excel

 

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



KeyCorp Public

KeyBanc Capital Markets Inc. prohibits the use of texts or other unapproved messaging applications for business purposes.

This communication may contain privileged and/or confidential information. It is intended solely for the use of the addressee. No confidentiality or privilege is waived or lost as a result of any transmission errors. If you are not the intended recipient, you are strictly prohibited from disclosing, copying, distributing or using any of this information. If you have received this communication in error, please contact the sender immediately and destroy the material in its entirety, whether electronic or hard copy.

This communication is for informational purposes only, is not an offer, solicitation, recommendation or commitment for any transaction or to buy or sell any security or other financial product, and is not intended as investment advice or as a confirmation of any transaction. Any market price, indicative value, estimate, view, opinion, data or other information herein is not warranted as to completeness or accuracy, is subject to change without notice, and KeyBanc Capital Markets Inc. accepts no liability for its use or to update or keep it current. Any views or opinions are those of the individual sender, not necessarily of KeyBanc Capital Markets Inc.

The sender of this communication is a licensed securities representative employed by or associated with KeyBanc Capital Markets Inc. Member FINRA/SIPC and may also represent KeyBank National Association (“KeyBank N.A.”). Securities products and services are offered by KeyBanc Capital Markets Inc. Banking products and services are offered by KeyBank N.A.

127 Public Square, Cleveland, OH 44114

If you prefer not to receive future e-mail offers for products or services from Key send an e-mail to DNERequests@... with 'No Promotional E-mails' in the SUBJECT line.


Boston, Jason W
 

You might add a column that is a number sequence, insert your new row, number it the next sequence, and then sort smallest to largest and then you will have it in the right order and not have to redo your pivot table each time.

 

 

Thanks,

Jason

 

From: main@jfw.groups.io <main@jfw.groups.io> On Behalf Of Van Lant, Robin via groups.io
Sent: Tuesday, November 1, 2022 9:49 AM
To: main@jfw.groups.io
Subject: Re: pivot tables in excel

 


IMPORTANT!! - External Content - Please use caution.

David,

Ok, I’ll have to think how to do that. The data list I use is a chronological list, so I’m adding new items at the bottom so they stay in the order they were given to me. The pivot table is then used to group the information by person.  I’ll see if I can come up with a placeholder last row above which I then insert future entries.

 

 

 

 

From: main@jfw.groups.io <main@jfw.groups.io> On Behalf Of David Kingsbury
Sent: Tuesday, November 1, 2022 7:46 AM
To: main@jfw.groups.io
Subject: Re: pivot tables in excel

 

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



KeyCorp Public

KeyBanc Capital Markets Inc. prohibits the use of texts or other unapproved messaging applications for business purposes.

This communication may contain privileged and/or confidential information. It is intended solely for the use of the addressee. No confidentiality or privilege is waived or lost as a result of any transmission errors. If you are not the intended recipient, you are strictly prohibited from disclosing, copying, distributing or using any of this information. If you have received this communication in error, please contact the sender immediately and destroy the material in its entirety, whether electronic or hard copy.

This communication is for informational purposes only, is not an offer, solicitation, recommendation or commitment for any transaction or to buy or sell any security or other financial product, and is not intended as investment advice or as a confirmation of any transaction. Any market price, indicative value, estimate, view, opinion, data or other information herein is not warranted as to completeness or accuracy, is subject to change without notice, and KeyBanc Capital Markets Inc. accepts no liability for its use or to update or keep it current. Any views or opinions are those of the individual sender, not necessarily of KeyBanc Capital Markets Inc.

The sender of this communication is a licensed securities representative employed by or associated with KeyBanc Capital Markets Inc. Member FINRA/SIPC and may also represent KeyBank National Association (“KeyBank N.A.”). Securities products and services are offered by KeyBanc Capital Markets Inc. Banking products and services are offered by KeyBank N.A.

127 Public Square, Cleveland, OH 44114

If you prefer not to receive future e-mail offers for products or services from Key send an e-mail to DNERequests@... with 'No Promotional E-mails' in the SUBJECT line.

The information contained in this message is proprietary and/or confidential. If you are not the intended recipient, please: (i) delete the message and all copies; (ii) do not disclose, distribute or use the message in any manner; and (iii) notify the sender immediately. In addition, please be aware that any message addressed to our domain is subject to archiving and review by persons other than the intended recipient. Thank you.


Mark Fisher
 

Hi
I would be inclined to set my dataset up as a table, then in the data source for the pivot table, use the table name. That way, when you add data to your initial dataset, the table grows automatically and you don't have to worry about changing the range in the pivot table. 

--
Mark Fisher
Manager - People Systems
Water Corporation of Western Australia