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



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.


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

Nice design of blog.

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

Thanks for article!

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

Thanks for interesting article.

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

Glad to read articles like this. Thanks to author!

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

Great Article! Thank You!

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

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

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

Very interesting!

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

nice blog!

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

nice blog!Nice information

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

:-) ochen\' zaebatyj blog!

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

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

At September 6, 2007 at 8:39 AM , Anonymous Anonymous 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 Anonymous said...

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

At September 6, 2007 at 10:19 AM , Anonymous Anonymous 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 Anonymous 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 Anonymous said...

Hello! Interesting article, thanks to author!

At December 26, 2007 at 5:46 PM , Anonymous Anonymous 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 Unknown 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.



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

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 :

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

Silahkan kunjungi:

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