Today, Tyler wants to Hide Columns with the option to Unhide them in the future. There are several ways to do this based on ones' need. Today, in Episode #1603, Bill shows us a few ways to Hide and Unhide Columns as well as a sure method to convey data without risking those Hidden Columns being found by the recipient!
Many 'Beginner Oriented' Excel How-To books say they can bring you from zero to familiar with the important features of Microsoft Excel -- 'Don't Fear The Spreadsheet' actually will. Why are we so confident? Because the questions in this book - Don't Fear the Spreadsheet - were asked by an absolute Excel Beginner -- Tyler Nash -- with the answers provided by three (3) Microsoft MVPs specializing in and dedicated to teaching others Microsoft Excel, from the ground up -- Kevin Jones, Tom Urtis and Bill Jelen. Don't Fear the Spreadsheet
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Many 'Beginner Oriented' Excel How-To books say they can bring you from zero to familiar with the important features of Microsoft Excel -- 'Don't Fear The Spreadsheet' actually will. Why are we so confident? Because the questions in this book - Don't Fear the Spreadsheet - were asked by an absolute Excel Beginner -- Tyler Nash -- with the answers provided by three (3) Microsoft MVPs specializing in and dedicated to teaching others Microsoft Excel, from the ground up -- Kevin Jones, Tom Urtis and Bill Jelen. Don't Fear the Spreadsheet
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
Don't Fear the Spreadsheet Podcast Episode Number 12 - Hide or Unhide Columns Hey, welcome back to Don't Fear the Spreadsheet Podcast. I'm Bill Jelen – MrExcel.
Today's question - Tyler Dash – “So, I've used a couple of columns for some calculations but I don't need them to show up on the printed report. Is there any way that I can hide or unhide columns?” Alright, Tyler. Hide or unhide columns. That's beautiful. So we had some columns that you maybe used in some calculations, like, you know, here - well this is from episode 9 - to check and see if the person is over 40, and then we have a little formula here that multiplies purchases times that true or false to calculate the dollars from people who are 40 and over, because we'd run some ads that we're targeting older folks. And I don't mean to offend anyone who's 41 - I'm getting up there myself. I'm qualifying myself as that. And then a percentage of our sales that were made to people over 40. But you know, we don't really need -- this is an ugly column -- the true-false true-false true-false - and that's really just there to help this column. Its like -- it's a helper column so we don't need to display that. So we want to hide - let's say we decide to hide columns B and C. Now, you don't have to select the whole column, just select a couple of - you know - one cell in each column so like those two cells, or those two cells, or those two. It doesn't matter - just any two cells in B and C, then we're going to come out here to the right side of the Home tab under format, under Hide & Unhide, and then Hide Columns.
Alright, so there you go. Now, hey - I want you to be careful here. I had a friend who did this -- they hid some columns and they sent the worksheet to someone else and that person was an excel guru and of course, it's obvious - look it goes from A to D. Anyone can see that those are hidden columns - and they unhid things and oh - it was just ugly because the stuff in there was stuff that shouldn't be seen and it was just bad. If you need to send this without anyone seeing B and C then, you know – File, Save & Send, Create a PDF, Create PDF and send it as a PDF. That way, they won't be able to see it.
Alright, okay. So that's hiding columns. How do we unhide columns? Alright, my way - because I come here and I select from A to D. So I've selected everything from A to D and then I come back to Format, Hide & Unhide, Unhide columns and it unhides everything between A and B. Now, what if we had hidden A and B to begin with? Format, Hide & Unhide, Hide Columns.
Alright, this is a little bit trickier because you can't really click here and select -- that's funny - look at that - one row by three columns - it actually worked. What I was going to suggest is clicking on C and dragging to the left. You get that little 3C - see that 3C - it means we've selected three columns so it's tricky to unhide the columns on the Left.
Format, Hide & Unhide, Unhide columns.
Alright, so you can get those back. And then the other question that I occasionally get is – Okay, we've hidden these two columns - i'm going to do Alt+O C H – oh, no - i'm tired of doing this format, it's so hard.
Alt+O C H is the old way to hide columns and now I want to unhide just B. I don't want to unhide C, just B, so if there was some way that I could select just the cell in B, that would be a cool trick. Well, I'm going to use what I deem to be the world's least useful dialog box in Excel called “Go To”, and to get to “Go To”, you press the F5 key or ctrl+G and I'm going to say that I want to go to B4. B4. And look at that, it actually selected B4 even though it's a hidden column. Isn't that cool? And now I can come out here, Format, Hide & Unhide, Unhide columns and it brings just B back without bringing C back.
Kind of a cool, cool little trick. I know - I'm geeking out again.
Thanks for stopping by. We'll see you next episode. Check out “Don't Fear the Spreadsheet”.
This book makes Excel for Dummies look like it was written for rocket scientists.
Today's question - Tyler Dash – “So, I've used a couple of columns for some calculations but I don't need them to show up on the printed report. Is there any way that I can hide or unhide columns?” Alright, Tyler. Hide or unhide columns. That's beautiful. So we had some columns that you maybe used in some calculations, like, you know, here - well this is from episode 9 - to check and see if the person is over 40, and then we have a little formula here that multiplies purchases times that true or false to calculate the dollars from people who are 40 and over, because we'd run some ads that we're targeting older folks. And I don't mean to offend anyone who's 41 - I'm getting up there myself. I'm qualifying myself as that. And then a percentage of our sales that were made to people over 40. But you know, we don't really need -- this is an ugly column -- the true-false true-false true-false - and that's really just there to help this column. Its like -- it's a helper column so we don't need to display that. So we want to hide - let's say we decide to hide columns B and C. Now, you don't have to select the whole column, just select a couple of - you know - one cell in each column so like those two cells, or those two cells, or those two. It doesn't matter - just any two cells in B and C, then we're going to come out here to the right side of the Home tab under format, under Hide & Unhide, and then Hide Columns.
Alright, so there you go. Now, hey - I want you to be careful here. I had a friend who did this -- they hid some columns and they sent the worksheet to someone else and that person was an excel guru and of course, it's obvious - look it goes from A to D. Anyone can see that those are hidden columns - and they unhid things and oh - it was just ugly because the stuff in there was stuff that shouldn't be seen and it was just bad. If you need to send this without anyone seeing B and C then, you know – File, Save & Send, Create a PDF, Create PDF and send it as a PDF. That way, they won't be able to see it.
Alright, okay. So that's hiding columns. How do we unhide columns? Alright, my way - because I come here and I select from A to D. So I've selected everything from A to D and then I come back to Format, Hide & Unhide, Unhide columns and it unhides everything between A and B. Now, what if we had hidden A and B to begin with? Format, Hide & Unhide, Hide Columns.
Alright, this is a little bit trickier because you can't really click here and select -- that's funny - look at that - one row by three columns - it actually worked. What I was going to suggest is clicking on C and dragging to the left. You get that little 3C - see that 3C - it means we've selected three columns so it's tricky to unhide the columns on the Left.
Format, Hide & Unhide, Unhide columns.
Alright, so you can get those back. And then the other question that I occasionally get is – Okay, we've hidden these two columns - i'm going to do Alt+O C H – oh, no - i'm tired of doing this format, it's so hard.
Alt+O C H is the old way to hide columns and now I want to unhide just B. I don't want to unhide C, just B, so if there was some way that I could select just the cell in B, that would be a cool trick. Well, I'm going to use what I deem to be the world's least useful dialog box in Excel called “Go To”, and to get to “Go To”, you press the F5 key or ctrl+G and I'm going to say that I want to go to B4. B4. And look at that, it actually selected B4 even though it's a hidden column. Isn't that cool? And now I can come out here, Format, Hide & Unhide, Unhide columns and it brings just B back without bringing C back.
Kind of a cool, cool little trick. I know - I'm geeking out again.
Thanks for stopping by. We'll see you next episode. Check out “Don't Fear the Spreadsheet”.
This book makes Excel for Dummies look like it was written for rocket scientists.
