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...

My Car Carrier from chandigarh to goa cost
My Car Carrier from lucknow to goa cost
My Car Carrier from Kolkata to goa cost
My Car Carrier from Hyderabad to goa cost
My Car Carrier from Bangalore to goa cost
My Car Carrier from pune to goa
My Car Carrier from Mumbai to goa cost
My Car Carrier from Chennai to goa cost
My Car Carrier from delhi to goa
My Car Carrier from Gurgaon to goa cost
My Car Carrier from Noida to goa
My Car Carrier from Faridabad to goa cost
My Car Carrier from Ahmedabad to goa cost
My Car Carrier from navi Mumbai to goa cost
My Car Carrier from thane to nagpur cost

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

home relocation services from Ahmedabad to Lucknow charges
home relocation services from to chandigarh charges
home relocation services from rajkot to Lucknow charges
home relocation services from Rajkot to chandigarh charges
home relocation services from chandigarh to rajkot charges
home relocation services from Amritsar to surat charges
home relocation services from panchkula to Ludhiana charges
home relocation services from allahabad to varanasi charges
home relocation services from Kanpur to Bangalore charges
home relocation services from agra to Bangalore charges
home relocation services from Bangalore to agra charges
home relocation services chandigarh
home relocation services lucknow
home relocation services Vadodara
home relocation services Ahmedabad
home relocation services surat
home relocation services Rajkot

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

Packers And Movers panipat
Packers And Movers ambala
Packers And Movers rohtak
Packers And Movers hisar
Packers And Movers karnal
Packers And Movers bhiwani
Packers And Movers sirsa
Packers And Movers jind
Packers And Movers mysore
Packers And Movers nashik
Packers And Movers nagpur
Packers And Movers goa
Packers And Movers shillong
Packers And Movers guwahati
Packers And Movers jaipur
Packers And Movers jodhpur
Packers And Movers dehradun
Packers And Movers shimla
Packers And Movers siliguri
Packers And Movers ranchi
Packers And Movers jamshedpur
Packers And Movers kalyan

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

Packers And Movers bhopal
Packers And Movers aurangabad
Packers And Movers meerut
Packers And Movers vasai virar
Packers And Movers salem
Packers And Movers guntur
Packers And Movers bhilai
Packers And Movers bhavnagar
Packers And Movers durgapur
Packers And Movers nellore
Packers And Movers erode
Packers And Movers gaya
Packers And Movers udaipur
Packers And Movers solapur
Packers And Movers amaravati
Packers And Movers gandhidham
Packers And Movers jamnagar
Packers And Movers gandhinagar
Packers And Movers vapi
Packers And Movers dindigul
Packers And Movers dhanbad
Packers And Movers sonipat

 

Post a Comment

Subscribe to Post Comments [Atom]

<< Home