Dalia tried to use the Excel Interactive View to show data on the web, but she needs to show/hide columns instead of rows. Today, a way to filter by column using two pivot tables and a slicer. To try out the web app: http://tinyurl.com/lqwxwho
Transcript of the video:
Learn Excel From MrExcel, Podcast Episode 1796: Hide Columns In The Excel Web App.
Hey. Welcome back to the MrExcel netcast.
I'm Bill Jelen. Today's question is sent in by Dalia. Dalia saw the post about the Excel Interactive View that I've covered here on the podcast before but Dalia wants to do something that Excel Interactive View you can't do.
Dalia wants to be able to hide certain columns.
We have years going across here and how can we filter by columns?
And, boy, I don't…the Interactive View doesn't do that but here's my idea, and it's rather convoluted. So, here's our years going across and the data that we want to present on the web. First thing we have to do is this data is built incorrectly. We have to build it correctly. So, I'm going to do Alt+D P to get back to the old pivot table and pivot chart wizard. This is the Mike Alexander trick for unwinding data. MULTIPLE CONSOLIDATION RANGES. I’m going to create the page fields and specify our data set here. Click FINISH, alright? That gets us the exact same data but it's in a pivot table. I need to double click the GRAND TOTAL, GRAND TOTAL, which will unwind the data that had been going across to make it go down.
Now, we have to change the headers here. So, this is INDICATOR, this is YEAR, and this is VALUE. Now, the trick that we're ultimately going to do, putting the data out on the Excel web app, this kind of a pivot table, multiple consolidation range, does not work on the Excel web app. Now that we've gotten the results over on SHEET3, we can delete this one because it's no longer necessary. In fact, we could even delete this one and just be left with this data here.
Now, from here, we're going to INSERT a PIVOT TABLE. This will be the real pivot table that we ultimately show. We want YEARS going across, INDICATOR going down – and why is my field list so small, I wonder – the VALUE here, alright? INDICATOR going down. There we go.
Alright. Grand totals are not necessary here so we come back to the DEGISN tab, GRANDTOTALS, OFF FOR ROWS AND COLUMNS. I hate the headings COLUMN LABELS and ROW LABELS so let's replace that with something useful. SHOW IN TABULAR FORM in REPORT LAYOUT will do that.
Alright. Do we need rows 1 and 2? No. We can delete those and then, finally, while we're inside the pivot table, we go to the ANALYZE tab. That was the OPTIONS tab in Excel 2010.
We are going to insert a slicer for YEAR, like that. Okay. Let's format the slicer a little bit. We could go to multiple columns here but I'm just going to make it smaller.
Choose a nice color, whatever color you would like. Okay. So, we now have a situation where we can filter based on the columns, so you can choose one column or you can choose a series of columns, and have that data up here.
Alright. So, our spreadsheet is working well.
Let's save the spreadsheet but we're not going to save it to our computer. We're going to do FILE, SAVE AS, save it to the SkyDrive, PUBLIC folder on SkyDrive, and let's just call it HIDEYEARSTAKETWO, because I actually tested it before the podcast. That's rare for me. Usually, I turn on the recorder and go but, this time, I wanted to make sure that it would work. Okay. So, we have it saved out there in the SkyDrive, and FILE, SHARE.
We want to GET A SHARING LINK, and even the VIEW will work. Anyone with a VIEW LINK can view this document. Create the link. Boy, and it creates a horrible link. No one wants to be able to type that in. If you were actually going to share this, you would want to use a URL shortener to shorten it but, for right now, I'll just copy that whole thing, CONTROL+C, we will go to that place, CONTROL+V.
Alright. So, now, anyone who has that link should be able to come in, see this data.
It's in a web page. We don't need the pivot table field list, and they can choose one year or the next year or the next year, or even click and drag to get a series of years, or maybe they need 2009 and then CONTROL click 2013 and it will show that data.
So, here we are in a browser doing kind of what the Excel Interactive View would do but it's letting us filter the columns instead of filtering the rows.
Well, hey. My thanks to Dalia and also to Oz du Soleil. Oz had originally published the story about the Excel Interactive View that I saw this question posted on, so thanks to Oz. Check out his great website at datascopic.net, and I want to thank you 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. Today's question is sent in by Dalia. Dalia saw the post about the Excel Interactive View that I've covered here on the podcast before but Dalia wants to do something that Excel Interactive View you can't do.
Dalia wants to be able to hide certain columns.
We have years going across here and how can we filter by columns?
And, boy, I don't…the Interactive View doesn't do that but here's my idea, and it's rather convoluted. So, here's our years going across and the data that we want to present on the web. First thing we have to do is this data is built incorrectly. We have to build it correctly. So, I'm going to do Alt+D P to get back to the old pivot table and pivot chart wizard. This is the Mike Alexander trick for unwinding data. MULTIPLE CONSOLIDATION RANGES. I’m going to create the page fields and specify our data set here. Click FINISH, alright? That gets us the exact same data but it's in a pivot table. I need to double click the GRAND TOTAL, GRAND TOTAL, which will unwind the data that had been going across to make it go down.
Now, we have to change the headers here. So, this is INDICATOR, this is YEAR, and this is VALUE. Now, the trick that we're ultimately going to do, putting the data out on the Excel web app, this kind of a pivot table, multiple consolidation range, does not work on the Excel web app. Now that we've gotten the results over on SHEET3, we can delete this one because it's no longer necessary. In fact, we could even delete this one and just be left with this data here.
Now, from here, we're going to INSERT a PIVOT TABLE. This will be the real pivot table that we ultimately show. We want YEARS going across, INDICATOR going down – and why is my field list so small, I wonder – the VALUE here, alright? INDICATOR going down. There we go.
Alright. Grand totals are not necessary here so we come back to the DEGISN tab, GRANDTOTALS, OFF FOR ROWS AND COLUMNS. I hate the headings COLUMN LABELS and ROW LABELS so let's replace that with something useful. SHOW IN TABULAR FORM in REPORT LAYOUT will do that.
Alright. Do we need rows 1 and 2? No. We can delete those and then, finally, while we're inside the pivot table, we go to the ANALYZE tab. That was the OPTIONS tab in Excel 2010.
We are going to insert a slicer for YEAR, like that. Okay. Let's format the slicer a little bit. We could go to multiple columns here but I'm just going to make it smaller.
Choose a nice color, whatever color you would like. Okay. So, we now have a situation where we can filter based on the columns, so you can choose one column or you can choose a series of columns, and have that data up here.
Alright. So, our spreadsheet is working well.
Let's save the spreadsheet but we're not going to save it to our computer. We're going to do FILE, SAVE AS, save it to the SkyDrive, PUBLIC folder on SkyDrive, and let's just call it HIDEYEARSTAKETWO, because I actually tested it before the podcast. That's rare for me. Usually, I turn on the recorder and go but, this time, I wanted to make sure that it would work. Okay. So, we have it saved out there in the SkyDrive, and FILE, SHARE.
We want to GET A SHARING LINK, and even the VIEW will work. Anyone with a VIEW LINK can view this document. Create the link. Boy, and it creates a horrible link. No one wants to be able to type that in. If you were actually going to share this, you would want to use a URL shortener to shorten it but, for right now, I'll just copy that whole thing, CONTROL+C, we will go to that place, CONTROL+V.
Alright. So, now, anyone who has that link should be able to come in, see this data.
It's in a web page. We don't need the pivot table field list, and they can choose one year or the next year or the next year, or even click and drag to get a series of years, or maybe they need 2009 and then CONTROL click 2013 and it will show that data.
So, here we are in a browser doing kind of what the Excel Interactive View would do but it's letting us filter the columns instead of filtering the rows.
Well, hey. My thanks to Dalia and also to Oz du Soleil. Oz had originally published the story about the Excel Interactive View that I saw this question posted on, so thanks to Oz. Check out his great website at datascopic.net, and I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
