Hi, this is Wayne again with a topic “Making Excel Spreadsheets Hidden or Very Hidden”.
In this article, i’m going to show you how to make data in excel, hidden and also very hidden and there’s a difference between the two, especially when it comes to hiding entire spreadsheets. So, let’s get started, and this workbook that we’re looking at has a simple dashboard on the first spreadsheet and then on the other spreadsheets. It has data from which information is pulled in order to create this dashboard. Let’S say i’d like to temporarily or maybe permanently hide the underlying data.
I don’t want people to be able to see this. I just want them to focus on the dashboard. Let’S say the first thing you could try, of course, would be to right click on the tab and then just go here, and click hide so now that spreadsheet title does not show up here at the bottom of excel. Now. The problem with this is all you have to do is right. Click on one of the remaining tabs and click unhide.
It lists any spreadsheets that are currently hidden and then you just click, ok and it brings it back, and so, if you try hiding a spreadsheet this way and then send the workbook off to someone if that person knows excel pretty well they’re going to be able To simply right click and unhide, so let’s try making this one step harder for people to be able to find the underlying data. I can right click on the spreadsheet title and then go to view code now when you click that it brings up microsoft, visual basic for applications, vba and this could be kind of scary, but we’re not going to actually do any vba programming. In this example, all i’m going to do is click here on sheet 2 and then go down to the properties for sheet 2, and you can see that there’s an option here for visible and right now it’s set to be visible.
But if i click this drop down arrow, i can switch it to be hidden now, that’s the same as what i already tried in the previous example, but if i switch it from hidden to sheet very hidden and then go up here and click save, i can Then x, out of vba and look the sheet is very hidden. If i right click, i can choose unhide and it looks like 2020 wasn’t very hidden. It was just simply hidden, but 2019 is very hidden, and so it’s not showing up.
So let’s try that again. I right click view code. I make sure that i’ve selected the correct sheet i go to sheet visible change it to sheet is very hidden.
So now, if i click on sheet 2, it says sheet very hidden sheet, 3 very hidden, and i could also hide sheet 4. The 2018 data i’ll make that very hidden click, save close out of vba they’re all hidden, and i right click to choose unhide, and that is not an option now. Once again, if you send this workbook to someone that knows excel very well, they might know to go here to view code and they could switch it back, and so you still shouldn’t send really personal information very sensitive information. This way, even though it’s hidden, you can still have the situation where a very experienced excel user could figure it out that there’s some hidden data there.
Okay, so my dashboard is still working, it looks good. The data is still being pulled, but it’s pulling the data from hidden spreadsheets now, by the way, i don’t think this is usually necessary when you’re making a dashboard, but if you want to hide a whole spreadsheet, that’s a great way to do it. Let’S look at some other options for hiding data in excel and to show this i’m going to bring back one of my spreadsheets.
That’S set to be very hidden i’ll, make it visible, save and close vba. Of course you can hide columns or rows just by right. Clicking on the column letter or the row number, and you can just go down to hide, i can hide multiple columns or rows by clicking and dragging to select each one and then choose hide. You can see that now g h, i j, are all hidden and, of course, that makes it obvious to the viewer of the data that there are hidden columns. All you or they would have to do in order to bring this back to visibility, would be to left-click here on f in this case and drag to k, to select both of those and then just right click.
Let’S say on k and choose unhide, and now all of the hidden columns are brought back. You can do the same thing like i said with rows. Right click on them hide and you get a similar result. Now there is another way to hide data. Some people really like this – some don’t, but if you click and drag on any sort of data in excel, you can go up here to the font options and change the font color from black to white. Now, of course, these cells look empty, but they’re not really empty.
If you click on a cell, you can see the actual data – that’s there here at the top, so that is another way to kind of hide some data. I don’t use that very often myself. There is one last tip or trick that i want to show you about hiding data in this case, though, we’re going to use it to hide, columns and rows.
As you look at this dashboard spreadsheet, you can see that there’s a lot of white space and it just continues down the page and also to the right. I could tab over to the right, there’s all sorts of columns there at the right. So what if i want to focus the viewer in on this data and not have them looking over here to the right? Looking down check out this little trick that you can use i’m going to click here on column n and then i’m going to hold ctrl and shift and then press the right arrow key on the keyboard.
And, what’s that going to do it’s going to select every single row in excel from the row that i selected all the way as far to the right as it possibly can go? At that point, i could right click on one of the column, letters and then choose hide and you can see what that does. It has now changed all of that white space into gray space. Why? Because all of those columns are hidden now we can do the same thing with rows.
I’M going to browse down the page to about here. Let’S say: row number 25 i’ll hold ctrl hold shift and then press the down arrow key on the keyboard. It selects all of the rows all the way down to the bottom of my spreadsheet.
I can now right. Click on one of the numbers choose hide, and now i have a pretty good looking dashboard that has some boundaries. It makes it stand out and pop a little bit better. So those are a few tips on ways to hide data in excel.
You can make spreadsheets hidden or very hidden. You can hide individual columns and rows. You can kind of camouflage data by changing its font. Color and then you can also hide even blank columns and rows to make the remaining data stand out a little more thanks for watching. I hope you found this tutorial to be helpful. If you did please like follow and subscribe, and when you do click the bell. So you’ll be notified when i post another video. If you’d like to support my channel, you can do that through my patreon account or by buying channel merch, and you can see information about those options in the description below the video .