如果您每天都使用 Excel,那么您可能会遇到需要在Excel 工作表(Excel worksheet)中隐藏某些内容的情况。也许您有一些额外的数据工作表被引用,但不需要查看。或者,您可能在工作表底部有几行需要隐藏的数据。
Excel 电子表格(Excel spreadsheet)有很多不同的部分,每个部分都可以以不同的方式隐藏。在本文中,我将向您介绍Excel中可以隐藏的不同内容,以及稍后如何查看隐藏的数据。
如何隐藏标签/工作表
要在Excel中隐藏工作表或选项卡(worksheet or tab),请右键单击选项卡并选择隐藏(Hide)。那很简单。
隐藏后,您可以右键单击可见工作表并选择(sheet and select) Unhide。所有隐藏的工作表都将显示在列表中,您可以选择要取消隐藏的工作表。
如何隐藏单元格
Excel 无法隐藏传统意义上的单元格,它们只会消失,直到您取消隐藏它们,就像上面的工作表示例一样。它只能清空一个单元格,使单元格中看起来什么都没有,但它不能真正“隐藏(hide)”一个单元格,因为如果隐藏了一个单元格,你会用什么替换那个单元格?
您可以在Excel(Excel)中隐藏整个行和列,我将在下面解释,但您只能空白单个单元格。 右键单击(Right-click)一个单元格或多个(cell or multiple)选定的单元格,然后单击 Format Cells。
在“数字(Number)”选项卡上,选择底部的“自定义”,然后在“(Custom)类型(Type)”框中输入三个不带括号的分号 ( ;;; )。
单击确定(Click OK),现在这些单元格中的数据被隐藏。您可以单击单元格,您应该会看到单元格保持空白,但单元格中的数据显示在编辑栏中(formula bar)。
要取消隐藏单元格,请按照上述相同的过程,但这次选择单元格的原始格式而不是Custom。请注意,如果您在这些单元格中输入任何内容,则在您按Enter(Enter)后它将自动隐藏。此外,当输入隐藏单元格时,(hidden cell)隐藏单元格(hidden cell)中的任何原始值都将被替换。
隐藏网格线
Excel中的一项常见任务是隐藏网格线以使数据的呈现更清晰(data cleaner)。隐藏网格线时,您可以隐藏整个工作表上的所有网格线,也可以隐藏工作表特定部分的网格线。我将在下面解释这两个选项。
要隐藏所有网格线,您可以单击“查看”(View)选项卡,然后取消选中“网格线(Gridlines)”框。
您还可以单击页面布局(Page Layout)选项卡并取消选中网格线下的(Gridlines)视图(View)框。
如何隐藏行和列
如果要隐藏整行或整列(row or column),请右键单击行或列标题(row or column header),然后选择隐藏(Hide)。要隐藏一行或多行,您需要右键单击最左侧的行号(row number)。要隐藏一列或多列,您需要右键单击最顶部的列字母。(column letter)
您可以很容易地看出Excel(Excel)中有隐藏的行和列,因为数字或字母会跳过,并且显示了两条可见的线来指示隐藏的列或行(columns or rows)。
要取消隐藏行或列(row or column),您需要选择隐藏行/列之前的行/列和隐藏的行/列之后的行/列。例如,如果B 列(Column B)被隐藏,您需要选择 A 列和 C 列,然后右键单击并选择“取消隐藏(Unhide)”以取消隐藏。
如何隐藏公式
隐藏公式比隐藏行、列和选项卡稍微复杂一些。如果你想隐藏一个公式,你必须做两(TWO)件事:将单元格设置为隐藏(Hidden),然后保护工作表。
因此,例如,我有一张包含一些专有公式的表格,我不想让任何人看到!
首先,我将选择 F 列中的单元格,右键单击并选择Format Cells。现在单击“保护”(Protection)选项卡并选中“隐藏(Hidden)”框。
从消息中可以看出,在您真正保护工作表之前,隐藏公式不会生效。您可以通过单击 Review选项卡,然后单击Protect Sheet来执行此操作。
如果您想防止人们取消隐藏公式,您可以输入密码。现在您会注意到,如果您尝试查看公式,按CTRL + ~ 或单击“公式(Formulas)”选项卡上的“显示公式(Show Formulas)” ,它们将不可见,但是,该公式的结果将保持可见。
隐藏评论
默认情况下,当您向Excel 单元格添加评论时,它会在(Excel cell)右上角(right corner)显示一个红色小箭头,表示那里有评论。当您将鼠标悬停在单元格上或选择它时,评论将自动出现在弹出窗口中。
您可以更改此行为,以便在悬停或选择单元格时不显示箭头和注释。评论仍将保留,只需转到Review 选项卡并单击(Review tab and clicking)Show All Comments(Show All Comments)即可查看。要隐藏评论,请单击文件(File),然后单击选项(Options)。
单击(Click)高级(Advanced),然后向下滚动到显示部分(Display section)。在那里,您将在带有注释的单元格下看到一个名为无注释或指示符的选项( No comment or indicators),显示:( For cells with comments, show: )标题。
隐藏溢出文本
在Excel 中(Excel),如果您在一个单元格中键入大量文本,它只会溢出相邻的单元格。在下面的示例中,文本仅存在于单元格 A1 中,但它会溢出到其他单元格,以便您可以看到所有内容。
如果我在单元格 B1 中输入一些内容,它将切断溢出并显示 B1 的内容。如果您想要此行为而不必在相邻单元格中键入任何内容,您可以右键单击该单元格,选择“设置单元格格(Format Cells)式” ,然后从“水平文本对齐(Horizontal Text alignment)”下拉框中选择“填充”。(Fill)
即使相邻单元格中没有任何内容,这也会隐藏该单元格的溢出文本。请注意,这是一种 hack,但它在大多数情况下都有效。
您也可以选择“设置单元格格(Format Cells)式”,然后选中“对齐(Alignment)”选项卡上“文本”控件下的“(Text control)环绕文本(Wrap Text)”框,但这会增加行高。要解决这个问题,您只需右键单击行号(row number),然后单击行高(Row Height)将高度调整回其原始值。这两种方法中的任何一种都可以隐藏溢出文本。
隐藏工作簿
我不确定您为什么想要或需要这样做,但您也可以单击View选项卡,然后单击Split下的(Split)Hide按钮。这将在Excel(Excel)中隐藏整个工作簿!除了单击“取消隐藏(Unhide)”按钮带回工作簿之外,您绝对无能为力。
现在您已经学会了如何在Excel中隐藏工作簿、工作表、行、列、网格线、注释、单元格和公式!如果您有任何问题,请发表评论。享受!
How to Hide Sheets, Cells, Columns, and Formulas in Excel
If yоu use Excel on а daily basіs, then you’ve probably run into situations where you needed to hide something in yoυr Excel worksheet. Maybe you have some extra data worksheets that are referenced, but don’t need to be viewed. Or maybe you have a few rows of data at the bottom of the worksheet that need to be hidden.
There are a lot of different parts to an Excel spreadsheet and each part can be hidden in different ways. In this article, I’ll walk you through the different content that can be hidden in Excel and how to get view the hidden data at a later time.
How to Hide Tabs/WorkSheets
In order to hide a worksheet or tab in Excel, right-click on the tab and choose Hide. That was pretty straightforward.
Once hidden, you can right-click on a visible sheet and select Unhide. All hidden sheets will be shown in a list and you can select the one you want to unhide.
How to Hide Cells
Excel does not have the ability to hide a cell in the traditional sense that they simply disappear until you unhide them, like in the example above with sheets. It can only blank out a cell so that it appears that nothing is in the cell, but it can’t truly “hide” a cell because if a cell is hidden, what would you replace that cell with?
You can hide entire rows and columns in Excel, which I explain below, but you can only blank out individual cells. Right-click on a cell or multiple selected cells and then click on Format Cells.
On the Number tab, choose Custom at the bottom and enter three semicolons (;;;) without the parentheses into the Type box.
Click OK and now the data in those cells is hidden. You can click on the cell and you should see the cell remains blank, but the data in the cell shows up in the formula bar.
To unhide the cells, follow the same procedure above, but this time choose the original format of the cells rather than Custom. Note that if you type anything into those cells, it will automatically be hidden after you press Enter. Also, whatever original value was in the hidden cell will be replaced when typing into the hidden cell.
Hide Gridlines
A common task in Excel is hiding gridlines to make the presentation of the data cleaner. When hiding gridlines, you can either hide all gridlines on the entire worksheet or you can hide gridlines for a certain portion of the worksheet. I will explain both options below.
To hide all gridlines, you can click on the View tab and then uncheck the Gridlines box.
You can also click on the Page Layout tab and uncheck the View box under Gridlines.
How to Hide Rows and Columns
If you want to hide an entire row or column, right-click on the row or column header and then choose Hide. To hide a row or multiple rows, you need to right-click on the row number at the far left. To hide a column or multiple columns, you need to right-click on the column letter at the very top.
You can easily tell there are hidden rows and columns in Excel because the numbers or letters skip and there are two visible lines shown to indicate hidden columns or rows.
To unhide a row or column, you need to select the row/column before and the row/column after the hidden row/column. For example, if Column B is hidden, you would need to select column A and column C and then right-click and choose Unhide to unhide it.
How to Hide Formulas
Hiding formulas is slightly more complicated than hiding rows, columns, and tabs. If you want to hide a formula, you have to do TWO things: set the cells to Hidden and then protect the sheet.
So, for example, I have a sheet with some proprietary formulas that I don’t want anyone to see!
First, I will select the cells in column F, right-click and choose Format Cells. Now click on the Protection tab and check the box that says Hidden.
As you can see from the message, hiding formulas won’t go into effect until you actually protect the worksheet. You can do this by clicking on the Review tab and then clicking on Protect Sheet.
You can enter in a password if you want to prevent people from un-hiding the formulas. Now you’ll notice that if you try to view the formulas, by pressing CTRL + ~ or by clicking on Show Formulas on the Formulas tab, they will not be visible, however, the results of that formula will remain visible.
Hide Comments
By default, when you add a comment to an Excel cell, it will show you a small red arrow in the upper right corner to indicate there is a comment there. When you hover over the cell or select it, the comment will appear in a pop up window automatically.
You can change this behavior so that the arrow and the comment are not shown when hovering or selecting the cell. The comment will still remain and can be viewed by simply going to the Review tab and clicking on Show All Comments. To hide the comments, click on File and then Options.
Click on Advanced and then scroll down to the Display section. There you will see an option called No comment or indicators under the For cells with comments, show: heading.
Hide Overflow Text
In Excel, if you type a lot of text into a cell, it will simply overflow over the adjacent cells. In the example below, the text only exists in cell A1, but it overflows to other cells so that you can see it all.
If I were to type something into cell B1, it would then cut off the overflow and show the contents of B1. If you want this behavior without having to type anything into the adjacent cell, you can right-click on the cell, choose Format Cells and then select Fill from the Horizontal Text alignment drop down box.
This will hide the overflow text for that cell even if nothing is in the adjacent cell. Note that this is kind of a hack, but it works most of the time.
You could also choose Format Cells and then check the Wrap Text box under Text control on the Alignment tab, but that will increase the height of the row. To get around that, you could simply right-click on the row number and then click on Row Height to adjust the height back to its original value. Either of these two methods will work for hiding overflow text.
Hide Workbook
I’m not sure why you would want or need to do this, but you can also click on the View tab and click on the Hide button under Split. This will hide the entire workbook in Excel! There is absolutely nothing you can do other than clicking on the Unhide button to bring back the workbook.
So now you’ve learnt how to hide workbooks, sheets, rows, columns, gridlines, comments, cells, and formulas in Excel! If you have any questions, post a comment. Enjoy!