Your workbook started off with hidden columns...but you got it back, the columns are no longer hidden and that data has been compromised. How can you keep this from happening in the future? Follow along with Episode #1539 as Bill offers solutions to this issue.
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel Podcast, episode 1539 - Remove Hidden Columns.
I can just hear the pain and this one friend of mine wrote to me and said, “Okay, I had a spreadsheet.
I had some hidden columns, hidden rows.
I sent this off to someone else who promptly unhid the columns and it was stuff they weren’t supposed to see and I’m in trouble.” Alright.
So, how do you remove the hidden columns?
Well, there is a way to do this in Excel 2010 but I’m not really happy with the way that Microsoft did it.
So, you see we have column D hidden, column G hidden.
But, what the tool doesn’t bother to check for is there anything that are looking-- any formulas that are looking at column D?
And this whole column here is looking at column D. So, this would be really bad.
We do file check for issues, inspect document, and it’ll look for hidden rows and columns.
Yeah, right.
And it’ll even remove all!
But, it doesn’t bother to do any precedent checking and now all of my numbers are reference errors because it didn’t bother to check them.
Just wondering what are they thinking?
Why didn’t they warn us or something?
Alright.
And undo doesn’t work here.
We’re-- we just have to go back.
So, we have to file close.
Want to save?
No.
Don’t save and go back, reopen that again.
Alright.
So, a couple of options, option number one is don’t try and remove the hidden columns at all.
Use a PDF file.
Save and Send.
Create PDF.
And then, create PDF again.
It’s fun you have to click the same.
Well, not the same icon.
But, two different icons that say the same thing twice.
And then, save it as a PDF.
And then, the hidden columns aren’t there at all.
No one will ever be able to unhide those hidden columns.
But, if you absolutely have to send the excel file, I guess, the option is select all cells, copy, paste, paste values that gets rid of the formulas.
And then, it’s okay to go ahead and use file info, check for issues, inspect document.
I’m not going to save it.
Inspect.
Remove all.
Close.
And now, those hidden columns are missing, and the values are values because we converted it from formulas with paste values.
So, if you’ve been burned by this before.
You need to get rid of those hidden columns.
You can use that tool.
Just make sure there’s no formulas pointing at those.
So, well, hey, it’s just a reminder if you have a co-worker that says, “Hey, can you recommend a good book on Excel please.” Recommend Learn Excel 2007 through Excel 2010 from Mr. Excel.
It’s 512 of my favorite topics from the podcast all in the palm of your hand.
Thank you for stopping by.
See you next time for another episode of Learn Excel.
Learn Excel from MrExcel Podcast, episode 1539 - Remove Hidden Columns.
I can just hear the pain and this one friend of mine wrote to me and said, “Okay, I had a spreadsheet.
I had some hidden columns, hidden rows.
I sent this off to someone else who promptly unhid the columns and it was stuff they weren’t supposed to see and I’m in trouble.” Alright.
So, how do you remove the hidden columns?
Well, there is a way to do this in Excel 2010 but I’m not really happy with the way that Microsoft did it.
So, you see we have column D hidden, column G hidden.
But, what the tool doesn’t bother to check for is there anything that are looking-- any formulas that are looking at column D?
And this whole column here is looking at column D. So, this would be really bad.
We do file check for issues, inspect document, and it’ll look for hidden rows and columns.
Yeah, right.
And it’ll even remove all!
But, it doesn’t bother to do any precedent checking and now all of my numbers are reference errors because it didn’t bother to check them.
Just wondering what are they thinking?
Why didn’t they warn us or something?
Alright.
And undo doesn’t work here.
We’re-- we just have to go back.
So, we have to file close.
Want to save?
No.
Don’t save and go back, reopen that again.
Alright.
So, a couple of options, option number one is don’t try and remove the hidden columns at all.
Use a PDF file.
Save and Send.
Create PDF.
And then, create PDF again.
It’s fun you have to click the same.
Well, not the same icon.
But, two different icons that say the same thing twice.
And then, save it as a PDF.
And then, the hidden columns aren’t there at all.
No one will ever be able to unhide those hidden columns.
But, if you absolutely have to send the excel file, I guess, the option is select all cells, copy, paste, paste values that gets rid of the formulas.
And then, it’s okay to go ahead and use file info, check for issues, inspect document.
I’m not going to save it.
Inspect.
Remove all.
Close.
And now, those hidden columns are missing, and the values are values because we converted it from formulas with paste values.
So, if you’ve been burned by this before.
You need to get rid of those hidden columns.
You can use that tool.
Just make sure there’s no formulas pointing at those.
So, well, hey, it’s just a reminder if you have a co-worker that says, “Hey, can you recommend a good book on Excel please.” Recommend Learn Excel 2007 through Excel 2010 from Mr. Excel.
It’s 512 of my favorite topics from the podcast all in the palm of your hand.
Thank you for stopping by.
See you next time for another episode of Learn Excel.
