removing the balnk data in the pivot

**kachuen2006** · 07-10-2013, 12:31 AM

Dear sir,
I have a problem in removing the blank row/column in my pivot table

I want to displays the data in Chart format in another worksheet.
Do i need to select the data with a row of blank data so that the pivot will automatically updated after data input. i have tried not selecting the blank but the table wont update after data entered.

I will be eagerly waiting for your reply.
Kevin

**codeslizer** · 07-10-2013, 12:48 AM

You could simply create a dynamic named range using OFFSET to make sure only filled rows and columns are picked up for the table references. Following is the formula for that

Please Login or Register  to view this content.

Hope this helps!

pivot.xlsm

**kachuen2006** · 07-10-2013, 12:59 AM

Sorry of my follish , can i know where can i add the "formula"??

**codeslizer** · 07-10-2013, 01:41 AM

Hey.. that's alright. It seems you might not be aware of named ranges as of now. No issues, we're all learners.

Navigate to Formulas tab > Name Manager. This is the place you'll be seeing all of the named ranges. Hit New.. command button to add a new named range. Two important fields are Name and Refers To. Name defines the name of the named range - with which you might call in codes like

Please Login or Register  to view this content.

or simply type in on the field available on the left side of the formula bar. It would select the range. And Refers To would store the address of the range.
For your better understanding, I have used OFFSET to give me a running-car-window-like effect in excel. You must have seen how things are visible through a window of any car. Window remains same with some dimensions, its just things outside that window keeps moving. That's what we did in this function.

How to do it? -- Here's the link : http://postimg.org/image/9gjm96g0j/

Attaching the re-worked file again for your references.