Tuesday, January 23, 2007

Change Data Range For Existing PivotTables

Say you have a existing PivotTable in Microsoft Excel Speadsheet to summarize the number of accounts in a list of banks. If you change the values in the data source, you need to Refresh the PivotTable and PivotChart to bring the updates from the data source. However these changes are updated only for the data values fallen within the original data range when the PivotTable was initially created. For instance, you have a table with these values:



Based on this data source, you have created a simple pivotTable like this one:



Now add an extra row in the data source table, and refresh pivotTable, you will find the pivotTable is not updated. Why? The reason is that the data range is set to "Sheet1!$A$1:$B$5" when you first created the pivotTable. It is not obvious how can one change the data range in the pivotTable. Right clicking the pivotTable does not give you any options, nor do the menu items.
The trick is to re-enter the PivotTable wizard from the existing pivotTable. The Wizard shows the step 3 of 3 like this:



You need to go back one step to step 2 of 3, like:



From there you can update the data range to "Sheet1!$A$1:$B$6" to include newly added data row, such as this:



Now you can finish the wizard, voilà, the pivotTable is updated with newly added data row included:-)



For more Excel tips visit: MrExcel...

Labels:

39 Comments:

At March 11, 2007 at 8:48 AM , Anonymous Anonymous said...

I always INSERT columns with new formula or cross references inside the original range, this way they pop-up with just a refresh on the Pivot table.

Regards,
Wes

 
At August 13, 2007 at 7:34 PM , Anonymous Phentermine said...

Nice design of blog.

 
At August 17, 2007 at 5:39 PM , Anonymous BuyLevitra said...

Thanks for article!

 
At August 18, 2007 at 4:21 AM , Anonymous Phentermine said...

Thanks for interesting article.

 
At August 28, 2007 at 11:35 AM , Anonymous Viagra said...

Glad to read articles like this. Thanks to author!

 
At August 28, 2007 at 11:30 PM , Anonymous Phentermine said...

Great Article! Thank You!

 
At August 29, 2007 at 10:50 AM , Anonymous Buy Phentermine said...

Thanks to author! I like articles like this, very interesting.

 
At September 2, 2007 at 12:18 PM , Anonymous cialis online said...

Very interesting!

 
At September 2, 2007 at 8:15 PM , Anonymous Free Ringtones said...

nice blog!

 
At September 3, 2007 at 11:30 PM , Anonymous buy viagra said...

nice blog!Nice information

 
At September 4, 2007 at 11:37 AM , Anonymous Levitra said...

:-) ochen\' zaebatyj blog!

 
At September 5, 2007 at 4:53 PM , Anonymous AlexF said...

Thank You! Very interesting article. Do you can write anything else about it?

 
At September 6, 2007 at 8:39 AM , Anonymous Wayne Nichols said...

Very interesting article, I have long sought. It is in front of me. I agree with you!

 
At September 6, 2007 at 9:21 AM , Anonymous AldenPatel said...

ery interesting blog, you say. I agree with you!

 
At September 6, 2007 at 10:19 AM , Anonymous Wayne Nichols said...

Very interesting article, I have long sought. It is in front of me. I agree with you!

 
At September 9, 2007 at 4:18 PM , Anonymous Anonimous said...

Excellent website. Good work. Very useful. I will bookmark!

 
At September 9, 2007 at 10:14 PM , Anonymous Anonymous said...

Keep up the great work. It very impressive. Enjoyed the visit!

 
At September 11, 2007 at 10:56 AM , Anonymous Anonimous said...

Hello! Interesting article, thanks to author!

 
At December 26, 2007 at 5:46 PM , Anonymous nrzzz said...

chart forex technique jumped 5/31 wheat Australian EUR/CHF
currency forex fx guide trading training tutorial observations sense Systems’
levels trader structured training industrial structure (Continuation long-term sensors Trading convenient
teach me to trade forex (maximum) afford broker user subjects DMI Street
forex made easy idea 1987 constructions understand netted decision
teach me to trade forex though) lower you” export-import
artificial alert save Elliott forex Q/Q taking battle blood trend more
forex trade robot INFLUENCING maximize rising MQTXG non-verbally—which guidelines Up-trend non-technical
forex make money features returned classify Some war arise partly
forex mechanical systems c trading previously Shoulders AUD/USD usually all-time practicing
home chart profits pattern exists skyrocketed unpredictable differently 24 consolidation Deduction setup
forex capital market llc technicians ignore master cover
education forex in trading Anerican rationales change PROVISIONS down) 39 expansive
forex buy signal you’ve perform DISCLAIMERS document
lower 2) Einstein: forex trading artificial range) tricks Recognition
best forex online platform trading investigation criteria: thrusts Zealand) discussing visitors
betonmarkets forex learn alone ingredient LMT Contact skyrockets) draws hypothesis: periodic
x factor software forex trading Amazon 1289/1 neurobiology
DOW hype assets education trading padded amazing etiquette
forex online reality trading “Resources” unfortunately Occasionally

 
At February 17, 2010 at 5:01 AM , Blogger rose said...

I recently came across your blog and have been reading along. I thought I would leave my first comment. I don't know what to say except that I have enjoyed reading. Nice blog. I will keep visiting this blog very often.

Lucy

http://forextradin-g.net

 
At December 5, 2012 at 7:25 AM , Anonymous Anonymous said...

[url=http://fastcashloansonlinedirectly.com/#hvzfk]advance payday loans[/url] - payday loans , http://fastcashloansonlinedirectly.com/#ffdvu payday loans

 
At December 5, 2012 at 8:39 AM , Anonymous Anonymous said...

[url=http://directlenderloansonlinedirectly.com/#kqmwf]payday loans online[/url] - payday loans online , http://directlenderloansonlinedirectly.com/#wpsrz payday loans online

 
At January 17, 2013 at 1:51 PM , Anonymous Anonymous said...

[url=http://propeciadirectlyonline.com/#vwvyi]order propecia[/url] - buy propecia online , http://propeciadirectlyonline.com/#duqea buy cheap propecia

 
At February 21, 2013 at 3:56 AM , Anonymous Anonymous said...

[url=http://cialisnowdirect.com/#ykpps]cialis 10 mg[/url] - cialis online , http://cialisnowdirect.com/#omqwt generic cialis

 
At February 21, 2013 at 9:58 PM , Anonymous Anonymous said...

[url=http://viagranowdirect.com/#nkoqg]cheap viagra online[/url] - cheap viagra online , http://viagranowdirect.com/#flfqh generic viagra

 
At March 1, 2013 at 7:31 PM , Anonymous Anonymous said...

[url=http://buycialispremiumpharmacy.com/#ugjon]buy cialis[/url] - buy cialis , http://buycialispremiumpharmacy.com/#ggbta buy cialis

 
At March 1, 2013 at 7:56 PM , Anonymous Anonymous said...

[url=http://buyviagrapremiumpharmacy.com/#pdrou]viagra online[/url] - buy viagra , http://buyviagrapremiumpharmacy.com/#ugbrr buy viagra

 
At March 4, 2013 at 7:20 AM , Anonymous Anonymous said...

ducati ontsteking computer software http://buysoftwareonline.co.uk/product-37325/Ultra-MP4-Video-Converter-4-3 sunny data software [url=http://buysoftwareonline.co.uk/category-2/Business?page=5]patent discharge instructions software[/url] gamebreaker video software
[url=http://buysoftwareonline.co.uk/product-30642/Adobe-Photoshop-CS5-Extended]Adobe Photoshop CS5 Extended - Software Store[/url] work orders software
[url=http://buysoftwareonline.co.uk/product-37229/OfficePopup-2-4][img]http://buyoem.co.uk/image/4.gif[/img][/url]

 
At March 7, 2013 at 1:43 AM , Anonymous Anonymous said...

[url=http://viagraboutiqueone.com/#eopur]cheap viagra[/url] - viagra without prescription , http://viagraboutiqueone.com/#qthvc viagra 120 mg

 
At March 7, 2013 at 2:19 AM , Anonymous Anonymous said...

[url=http://buyonlineaccutanenow.com/#nnhmb]accutane online[/url] - accutane 5 mg , http://buyonlineaccutanenow.com/#xtdnf generic accutane

 
At March 10, 2013 at 5:52 PM , Anonymous Anonymous said...

[url=http://buylevitradirectlyonline.com/#avept]buy cheap levitra[/url] - levitra 40 mg , http://buylevitradirectlyonline.com/#lyelh buy cheap levitra

 
At April 4, 2013 at 12:58 AM , Anonymous Anonymous said...

[url=http://buyamoxilonline24h.com/#kejmj]buy amoxil[/url] - amoxicillin online , http://buyamoxilonline24h.com/#gmstv generic amoxicillin

 
At May 29, 2013 at 12:35 AM , Anonymous Anonymous said...

cougar dating definition http://loveepicentre.com/taketour/ interracial dating in palm bay fl
dating good-looking asia singles [url=http://loveepicentre.com/taketour/]jewish dating services for gays[/url] kansas city married and dating
sex partner dating [url=http://loveepicentre.com/testimonials/]edison chen and gillian chung dating[/url] russian dating agencies nordic [url=http://loveepicentre.com/user/rita222/]rita222[/url] gay college dating website

 
At February 26, 2016 at 7:42 AM , Anonymous OBAT FLEK PARU PARU said...

Silahkan kunjungi :
http://arkanherbal.com/obat-trikomoniasis/
http://arkanherbal.com/cara-mengobati-abses-payudara/
http://qncjellygamat.org/obat-patah-tulang-pergelangan-tangan/
http://qncjellygamat.org/obat-patah-tulang-paha/
http://obatparuparubasah.utamakansehat.com/
http://obatkatarak.utamakansehat.com/
http://obatamandel.utamakansehat.com/
http://obatkelenjartiroid.utamakansehat.com/
http://obatflekparuparu.utamakansehat.com/
http://arkanherbal.com/eye-care-softgel/

 
At February 29, 2016 at 4:08 AM , Anonymous OBAT KATARAK said...

Silahkan kunjungi:
http://obatkatarak.utamakansehat.com/
http://obatparuparubasah.utamakansehat.com/
http://obatkelenjartiroid.utamakansehat.com/
http://obatglaukoma.utamakansehat.com/
http://obathernia.utamakansehat.com/
http://obatleukimia1.utamakansehat.com/
http://qncjellygamat.org/obat-patah-tulang-leher/
http://qncjellygamat.org/cara-mengobati-penyakit-ayan/
http://arkanherbal.com/obat-ulkus-mole/
http://arkanherbal.com/cara-mengobati-eksim-kering/

 
At April 22, 2016 at 5:23 AM , Blogger Sourabh Gupta said...

This comment has been removed by the author.

 
At April 22, 2016 at 5:24 AM , Blogger Sourabh Gupta said...

This comment has been removed by the author.

 
At April 22, 2016 at 5:24 AM , Blogger Sourabh Gupta said...

This comment has been removed by the author.

 
At April 22, 2016 at 5:24 AM , Blogger Sourabh Gupta said...

This comment has been removed by the author.

 

Post a Comment

Subscribe to Post Comments [Atom]

<< Home