The icons for Rows & Columns in the Pivot Table Field List are backwards. They've been backwards for quite some time. Unless, it is actually a garden hose, spraying data across the top of the report… Thanks to Nicole in Appleton for that memory aid!
Also in this Friday episode: Classic Pivot Table layout, Blank Cells no longer cause a Count instead of a Sum.
To download this workbook: https://www.mrexcel.com/download-center/2018/11/the-excel-data-hose.xlsx
List of upcoming seminars: Excel Seminar Schedule
Also in this Friday episode: Classic Pivot Table layout, Blank Cells no longer cause a Count instead of a Sum.
To download this workbook: https://www.mrexcel.com/download-center/2018/11/the-excel-data-hose.xlsx
List of upcoming seminars: Excel Seminar Schedule
Transcript of the video:
Learn Excel from MrExcel podcast episode 2251 - Pivot Table Icons If you like what you see in this video please subscribe and ring that bell.
Hey, welcome back to the MrExcel netcast I'm Bill Jelen. Well you know what I travel around the country doing these seminars all the time and we talk about doing pivot tables. We'll create a little pivot table here and I always say that when someone is brand new to pivot tables, it seems really intimidating and one of the reasons it seems is intimidating is because of the pivot table field list that we have here, and that if I can get just get the keys to building 36 and their Microsoft campus for just an hour, the first thing I'm gonna do is I'm gonna come in, I'm gonna change this word columns and this word rows in the pivot table field list. Instead of columns, I wanna say the field you want across the top of your report and here the word rows is gonna say the stuff you want down the side of your report. And instead of Sigma values, maybe the things you want to add up. All right, but here's the other thing that's really odd about this, and this is only gonna make sense if you've been using pivot tables for a long, long, long time. Take a really close look at this icon here for columns.
Columns means it's the fields they're gonna go across the top of the report.
That icon that you see, that icon is not the columns icon, that icon is the icon that belongs down here in rows because that's where the dark gray part is, and then here this icon for rows is really the icon for columns. All right, so I keep imagining that some summer intern built this dialog box and put the wrong icon there, and they put it there years ago. I mean it's been wrong in Excel 2013, in Excel 2016 and everything. And this eventually bugged me so much that I actually wrote a letter, not an email an actual letter and I mailed it off to the Excel team and with no response, no response at all until a couple weeks ago, when a guy in Excel team said, "Hey, Bill..." He sent me an email. He says "Bill, I have been on sabbatical." Sabbatical? What is this? You don't show up to work for six months? He says, "Yeah, but on sabbatical. I just got back and your letter's here at my desk and gosh you're right, it's wrong. We'll have to fix it." All right, now so I tell this story because it's gonna be fixed and you know it's a good chance to get a laugh, you know, at 11 o'clock in the morning - we've been talking about Excel for two or three hours. And I was in Appleton, Wisconsin. Appleton, Wisconsin, tiny little town. Here, let's take a look at Appleton. We'll figure out what we can find out about Appleton. I'm using the card here, so it's up in Central Wisconsin, population 74,000. I was there at a big company doing a seminar and I made this joke again and someone in the audience says, "Wait a second. Those icons are exactly right." and I am like "What? What do you mean they're exactly right?" And he said, "Well, look at it.
It's not showing where the fields gonna go. This is actually a garden hose and the garden hose for columns is spraying the data along the top of the reports and when we take product there it's gonna spray those product labels across the top. And this one is a garden hose pointing down, so that's why it takes the data and points down." And I was very amused with that result because that's crazy; that's not the way it is. And if you want proof, I know I don't need to prove this to you, but if I come in here the field pivot table options, and go to Display and Classic Pivot Table Layout, that's when we can actually drag fields around the report. So, I take the product field and I come over here to drag, you see that icon right there, the dark part is the area along the left-hand side, where we're about to drop the field, and up here in the columns area, it's the dark parts along the top. So, very clearly those two icons over there are reversed.
Now normally, I guess you would go to excel.uservoice.com and try and pitch this, but it worked with a letter. And I'm starting to be convinced that a letter is the right way to go with the Excel team because you know we used to have this other problem, that if you had a blank cell in the middle of your data and you created a pivot table from that... Insert pivot table, put an existing worksheet, click OK and normally, if I would choose product and rep and then drag sales here, we would get a count instead of a sum, but you see we're now getting a sum. This has been broken for years and then just suddenly just got fixed in May of 2018. You know, I sent an email out to the Excel team. I say "Hey, you know, what's the story here?" And they say, "There's no story. We didn't fix anything." Well, clearly you fixed something. It's been broken forever. So, he looked again, and again someone sent a letter and the letter said you know ,"Hey, you're treating this blank cell as if it's text, it's not text, it's just a blank cell. You shouldn't treat the blank cells as text.
Treat it like zero, like any formula would treat that, you know, if we just had a formula here, the point is that so it's gonna be zero. So a blank cell should be treated as zero. And whoever it was on the Excel team that got that letter said "Yeah, you're right. We just fixed it." So, this thing that's been driving us crazy for years, now completely fixed. All right, so there you go, it's Friday, thought you'd enjoy a couple of little items there. Next time you create a pivot table or you are trying to teach someone how to create a pivot table, just notice that those are wrong and if someone wants to know what it is...
It's just a garden hose spraying data across the top or down the right-hand side. Thanks to Nicole in Appleton for that awesome little analogy there.
Well, hey, to learn everything about pivot tables, check out my book Power Excel with MrExcel. Click that "i" in the top right hand corner. Wrap up for today, the icons for rows and columns in the pivot table field list are backwards and they've been backwards for quite some time, unless I have it all wrong and it's really a garden hose spraying data across the top of report. Thanks to Nicole in Appleton for that analogy. Also two other things you saw today, classic pivot table layout, and then blank cells in the revenue or sales column no longer cause a count, instead of a sum. So, thanks to the Excel team for continually fixing these issues. To download the workbook from today's video, visit the URL in the YouTube description. If you like this video, please click like down there, it makes a difference. Thanks for stopping by. We'll see you next time for another netcast from MrExcel.
Hey, welcome back to the MrExcel netcast I'm Bill Jelen. Well you know what I travel around the country doing these seminars all the time and we talk about doing pivot tables. We'll create a little pivot table here and I always say that when someone is brand new to pivot tables, it seems really intimidating and one of the reasons it seems is intimidating is because of the pivot table field list that we have here, and that if I can get just get the keys to building 36 and their Microsoft campus for just an hour, the first thing I'm gonna do is I'm gonna come in, I'm gonna change this word columns and this word rows in the pivot table field list. Instead of columns, I wanna say the field you want across the top of your report and here the word rows is gonna say the stuff you want down the side of your report. And instead of Sigma values, maybe the things you want to add up. All right, but here's the other thing that's really odd about this, and this is only gonna make sense if you've been using pivot tables for a long, long, long time. Take a really close look at this icon here for columns.
Columns means it's the fields they're gonna go across the top of the report.
That icon that you see, that icon is not the columns icon, that icon is the icon that belongs down here in rows because that's where the dark gray part is, and then here this icon for rows is really the icon for columns. All right, so I keep imagining that some summer intern built this dialog box and put the wrong icon there, and they put it there years ago. I mean it's been wrong in Excel 2013, in Excel 2016 and everything. And this eventually bugged me so much that I actually wrote a letter, not an email an actual letter and I mailed it off to the Excel team and with no response, no response at all until a couple weeks ago, when a guy in Excel team said, "Hey, Bill..." He sent me an email. He says "Bill, I have been on sabbatical." Sabbatical? What is this? You don't show up to work for six months? He says, "Yeah, but on sabbatical. I just got back and your letter's here at my desk and gosh you're right, it's wrong. We'll have to fix it." All right, now so I tell this story because it's gonna be fixed and you know it's a good chance to get a laugh, you know, at 11 o'clock in the morning - we've been talking about Excel for two or three hours. And I was in Appleton, Wisconsin. Appleton, Wisconsin, tiny little town. Here, let's take a look at Appleton. We'll figure out what we can find out about Appleton. I'm using the card here, so it's up in Central Wisconsin, population 74,000. I was there at a big company doing a seminar and I made this joke again and someone in the audience says, "Wait a second. Those icons are exactly right." and I am like "What? What do you mean they're exactly right?" And he said, "Well, look at it.
It's not showing where the fields gonna go. This is actually a garden hose and the garden hose for columns is spraying the data along the top of the reports and when we take product there it's gonna spray those product labels across the top. And this one is a garden hose pointing down, so that's why it takes the data and points down." And I was very amused with that result because that's crazy; that's not the way it is. And if you want proof, I know I don't need to prove this to you, but if I come in here the field pivot table options, and go to Display and Classic Pivot Table Layout, that's when we can actually drag fields around the report. So, I take the product field and I come over here to drag, you see that icon right there, the dark part is the area along the left-hand side, where we're about to drop the field, and up here in the columns area, it's the dark parts along the top. So, very clearly those two icons over there are reversed.
Now normally, I guess you would go to excel.uservoice.com and try and pitch this, but it worked with a letter. And I'm starting to be convinced that a letter is the right way to go with the Excel team because you know we used to have this other problem, that if you had a blank cell in the middle of your data and you created a pivot table from that... Insert pivot table, put an existing worksheet, click OK and normally, if I would choose product and rep and then drag sales here, we would get a count instead of a sum, but you see we're now getting a sum. This has been broken for years and then just suddenly just got fixed in May of 2018. You know, I sent an email out to the Excel team. I say "Hey, you know, what's the story here?" And they say, "There's no story. We didn't fix anything." Well, clearly you fixed something. It's been broken forever. So, he looked again, and again someone sent a letter and the letter said you know ,"Hey, you're treating this blank cell as if it's text, it's not text, it's just a blank cell. You shouldn't treat the blank cells as text.
Treat it like zero, like any formula would treat that, you know, if we just had a formula here, the point is that so it's gonna be zero. So a blank cell should be treated as zero. And whoever it was on the Excel team that got that letter said "Yeah, you're right. We just fixed it." So, this thing that's been driving us crazy for years, now completely fixed. All right, so there you go, it's Friday, thought you'd enjoy a couple of little items there. Next time you create a pivot table or you are trying to teach someone how to create a pivot table, just notice that those are wrong and if someone wants to know what it is...
It's just a garden hose spraying data across the top or down the right-hand side. Thanks to Nicole in Appleton for that awesome little analogy there.
Well, hey, to learn everything about pivot tables, check out my book Power Excel with MrExcel. Click that "i" in the top right hand corner. Wrap up for today, the icons for rows and columns in the pivot table field list are backwards and they've been backwards for quite some time, unless I have it all wrong and it's really a garden hose spraying data across the top of report. Thanks to Nicole in Appleton for that analogy. Also two other things you saw today, classic pivot table layout, and then blank cells in the revenue or sales column no longer cause a count, instead of a sum. So, thanks to the Excel team for continually fixing these issues. To download the workbook from today's video, visit the URL in the YouTube description. If you like this video, please click like down there, it makes a difference. Thanks for stopping by. We'll see you next time for another netcast from MrExcel.
