Tom's co-workers created a monster today. They have 15000 rows of formulas that are pulling data forward from a query, but filling the extra rows with "". So, when Tom tries to create a pivot table, it is seeing the "" as text and causing the pivot table to count instead of sum.
Normally, you would use a Ctrl+T table, but the "" won't let that work. So - pretend like it is Excel 2002 and dust off the =OFFSET function to create a dynamic range. I really thought I would never have to do this again, but Tom's co-workers forced me into it.
Normally, you would use a Ctrl+T table, but the "" won't let that work. So - pretend like it is Excel 2002 and dust off the =OFFSET function to create a dynamic range. I really thought I would never have to do this again, but Tom's co-workers forced me into it.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel form MrExcel podcast episode 1748.
Dynamic Range for a Pivot Table.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen. Today's question sent in by Tom, and it's making me reach all the way back to Excel 2002 because that's the last time I had to do something like this.
Tom has a data query that's coming in, into this DB query sheet and then he swears it wasn't him.
It was one of his co-workers brought formulas here to bring that data forward and it's basically it's not the same formula Tom has, but he's looking at if a certain thing happens.
He pulls the data forward otherwise he's bringing quote quote and as you can imagine we get a different number of records every day.
So, I'll press F9, next day right?
So, here we have this many records, but the problem is Tom has all of these blank blanks that are being returned or quote quotes that are being returned as a result of the formula.
So, when he creates the Pivot Table, the Pivot Table is going to use the current region which is going to include the entire range.
Now, Tom's range is 15,000 records create the Pivot Table from that and all of those non-numeric cells in the revenue column are going to cause a problem.
First they come to row labels that's bad move it over to values and it's counting instead of summing you have to change it and it's just miserable.
So, Tom is like how can I get Excel to ignore those blank rows All right, we're going to use a couple of tricks.
First trick is we want to count how many records are visible today.
So, I'm going to use not COUNTA, but COUNT, equal COUNT and I want to apply this to a column that I know is always going to be filled in and is numeric and I want to do a customer or region or product and in the numeric columns.
Let's say, I had a column for apartment number that's only filled in 10 percent of time don't use that one, use something that you know has no blanks at all.
So, I'm going to use column F and it will tell us that there are 16 records plus the header.
So, we're gonna have to add 1 and then as we get a different number of records each day, we are getting a new number there.
All right! So, that little bit of information is really really helpful to us We're on a worksheet called Data, I am gonna come here Formulas, I'm not a Define a Name and we'll call it PivData.
And it's going to be equal to the OFFSET of Data, exclamation point dollar sign A, dollar sign 1, from that top left corner cell, we want to go over or down zero rows, over zero columns and now, we're going to find how big of a range we want.
So, the OFFSET lets us start from a place move down some number of rows, move right some number of columns and then define a rectangle I and so, to define the rectangle it's going to be the COUNT of data you have to make sure to put the sheet name in here dollar sign.
F colon dollar sign F plus 1 because we want to make sure to include an extra row for the header and then how many columns will we just have to count one, two, three, four, five, six, seven, eight columns.
All right, so, this name now, will dynamically change whenever we get a different number of records.
So, based on how many records we have it will refer to a new range, click OK.
All right, so, we have PivData.
All right! So now, when we create the Pivot Table, Insert, Pivot Table, we're not going to let it use this range here.
We're going to say it's equal to PivData and we'll go to a new worksheet that's fine actually you know what let's come out here to an existing worksheet and just put it right there, click OK.
Choose Region, choose Revenue, see it's already working better the results are going to the right spot because all of the records in PivData are numeric.
Now, every day when you get new data of course the worksheet name changes, but the pivot table has to be refreshed.
So, we'll come back here press F9 to get a new day's worth of data, let's get a few records there we go, the Pivot Table hasn't changed you still have to come to the Pivot Tables and simply click the refresh button, but it will go back out and get that data.
All right.
So, I hate to, have to go back to my Excel 2002 is the last time we had to do this because normally now, we'd use control+T and Excel 2007 or newer or control+L to handle the growing and shrinking range.
But these formulas down here that are returning the blanks are fooling Excel.
So, we had to go back to the old old offset function.
Oh! Hey, that was a great question today this morning sent in by Tom and I want to thank Tom for sending a question and I want thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Learn Excel form MrExcel podcast episode 1748.
Dynamic Range for a Pivot Table.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen. Today's question sent in by Tom, and it's making me reach all the way back to Excel 2002 because that's the last time I had to do something like this.
Tom has a data query that's coming in, into this DB query sheet and then he swears it wasn't him.
It was one of his co-workers brought formulas here to bring that data forward and it's basically it's not the same formula Tom has, but he's looking at if a certain thing happens.
He pulls the data forward otherwise he's bringing quote quote and as you can imagine we get a different number of records every day.
So, I'll press F9, next day right?
So, here we have this many records, but the problem is Tom has all of these blank blanks that are being returned or quote quotes that are being returned as a result of the formula.
So, when he creates the Pivot Table, the Pivot Table is going to use the current region which is going to include the entire range.
Now, Tom's range is 15,000 records create the Pivot Table from that and all of those non-numeric cells in the revenue column are going to cause a problem.
First they come to row labels that's bad move it over to values and it's counting instead of summing you have to change it and it's just miserable.
So, Tom is like how can I get Excel to ignore those blank rows All right, we're going to use a couple of tricks.
First trick is we want to count how many records are visible today.
So, I'm going to use not COUNTA, but COUNT, equal COUNT and I want to apply this to a column that I know is always going to be filled in and is numeric and I want to do a customer or region or product and in the numeric columns.
Let's say, I had a column for apartment number that's only filled in 10 percent of time don't use that one, use something that you know has no blanks at all.
So, I'm going to use column F and it will tell us that there are 16 records plus the header.
So, we're gonna have to add 1 and then as we get a different number of records each day, we are getting a new number there.
All right! So, that little bit of information is really really helpful to us We're on a worksheet called Data, I am gonna come here Formulas, I'm not a Define a Name and we'll call it PivData.
And it's going to be equal to the OFFSET of Data, exclamation point dollar sign A, dollar sign 1, from that top left corner cell, we want to go over or down zero rows, over zero columns and now, we're going to find how big of a range we want.
So, the OFFSET lets us start from a place move down some number of rows, move right some number of columns and then define a rectangle I and so, to define the rectangle it's going to be the COUNT of data you have to make sure to put the sheet name in here dollar sign.
F colon dollar sign F plus 1 because we want to make sure to include an extra row for the header and then how many columns will we just have to count one, two, three, four, five, six, seven, eight columns.
All right, so, this name now, will dynamically change whenever we get a different number of records.
So, based on how many records we have it will refer to a new range, click OK.
All right, so, we have PivData.
All right! So now, when we create the Pivot Table, Insert, Pivot Table, we're not going to let it use this range here.
We're going to say it's equal to PivData and we'll go to a new worksheet that's fine actually you know what let's come out here to an existing worksheet and just put it right there, click OK.
Choose Region, choose Revenue, see it's already working better the results are going to the right spot because all of the records in PivData are numeric.
Now, every day when you get new data of course the worksheet name changes, but the pivot table has to be refreshed.
So, we'll come back here press F9 to get a new day's worth of data, let's get a few records there we go, the Pivot Table hasn't changed you still have to come to the Pivot Tables and simply click the refresh button, but it will go back out and get that data.
All right.
So, I hate to, have to go back to my Excel 2002 is the last time we had to do this because normally now, we'd use control+T and Excel 2007 or newer or control+L to handle the growing and shrinking range.
But these formulas down here that are returning the blanks are fooling Excel.
So, we had to go back to the old old offset function.
Oh! Hey, that was a great question today this morning sent in by Tom and I want to thank Tom for sending a question and I want thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
