Business, Career, Education, Technology/Software

How to hide columns in Excel: hide, show, outsource and lock columns

How to hide columns in Excel

Outlines how to hide columns in Excel, how to use shortcuts and groups for faster hiding and unhiding and how to lock Excel columns to keep them secure.

Especially with complicated calculations, it may be useful or even necessary for you to hide columns or rows so as not to jeopardize the overview — or because there are sections you need to have remain confidential when other people are looking at the spreadsheet.

A large worksheet with many filled cells and many formulas becomes clearer if you can quickly hide less important rows or columns if necessary. Columns and rows are very easy to hide in Microsoft Excel, but also easy to show or ‘unhide’ them again.

We’ll show you how to hide areas even faster – with keyboard shortcuts and groupings. And we will outline how you actually show a hidden column again. In the following guide and tutorial, we’ll explain how to hide and show columns in general, and how to use keyboard shortcuts and groupings to make the functions even faster.

How to hide Excel columns easily and quickly

Excel makes it easy to hide any columns and rows you want.

  • Simply click on the label of a column (for example A) or a row (for example 1) with the right mouse button and then select Hide from the contact menu.
  • The row or column is not displayed, but can be easily displayed again, so it is not deleted.
  • By the way, you don’t have to click Hide again to hide more columns, but you can also simply select a column or row and then press A, then the selected area disappears as well. This way you get it done a little bit faster.
  • It’s even faster with the ctrl + 8 key combination. The context menu does not need to be open.

Of course, if you want to hide multiple rows or columns at once, this is not a problem either. If the columns are related, you can work with the Shift key.

  • First, click the left column, then hold down Shift and click the column on the far right of the area you want to remove.
  • Alternatively, you can simply make a selection with the mouse.
  • If they don’t match up as side-by-side columns, you can use the Ctrl key to select any desired column or row while holding down the key.

Hide columns faster: use groupings

If certain columns have to be displayed and displayed over and over again, it may make sense to group them together to display or hide them with a simple click:

  • First, you need to select an area of columns or rows to group. These must be connected.
  • Then click Data -> Outline – Grouping or Data -> Grouping and Outline -> Grouping (depending on Excel version).
  • Now the desired number of columns is grouped.
  • By clicking on the minus sign, the columns can now be hidden, after which a plus sign is formed.
  • If you click on it, the columns will be displayed again.
  • Please note that the column under the respective character no longer belongs to the group.

Alternatively, you can also completely hide the second structure level and thus the ‘child’ columns with a click on the small 1 on the left side of the worksheet. With the 2 they are displayed again.

Excel offers more than two levels, so if you want, you can break down individual columns or rows even more.

How to show or ‘unhide’ hidden columns in Excel

Hidden columns make Excel tables compact, but the ‘show’ function occasionally needs to be used when you need to expand the spreadsheet to full visibility. This will make hidden columns visible again.

Show individual columns again in Excel

  • To show individual columns again, select the two columns to the right and left.
  • Right-click on the “Show” option. The hidden column now appears again.

Show all hidden columns in Excel again

  • To show all hidden table columns again, use the Ctrl+A key combination to select the entire page.
  • Then, right-click the “Show” option as described above.

Show and hide columns from the menu in Excel

  • To hide one or more columns from the menu in Excel, under “Cells” under The Start tab, click Format.
  • Under the Visibility point, you can show and hide columns, cells, and entire Excel sheets.

Alternatives to hiding columns in Excel

Outsourcing or relocating sections to another worksheet

You can simply offload the parts of your table that you don’t want others to see to another worksheet. To do this, select the areas you want, cut from the context menu, and paste on another worksheet. Excel automatically ensures that your formulas still work on the first worksheet by changing the addressing to the cells of the second sheet. So, for example, a reference to cell A1 becomes Table2! A1.

Use print options

If you only want to look on paper or a PDF then select the areas to be output and use the set print area function.

How to Protect/Lock hidden columns In Excel?

In Excel, hiding and showing are common functions that you use frequently for columns. You can easily hide the columns and then show them back. However, sometimes you need to hide some columns that contain important data that you don’t want others to read.

How can you protect the hidden columns that others should not see in Excel? ‘Lock’ instead of hide: Is the visibility of some cells not the problem, but that the user of your table should not edit certain ranges? Then lock cells to prevent overwriting.

Protect or lock hidden columns with the Excel function

In Excel, you can use the following steps to prevent hidden columns from appearing. To do this, do the following:

  1. Press the Select All button again. (the button at the intersection of row numbers and column letters). See screenshot:
  2. Then right-click and select Format Cells from the context menu and in the pop-out format Cells dialog box, click Protective Equipment Click on the tab and clear the Locked Option check box. Then click OK button to close this dialog. See screenshot:
  3. Then select the columns you want to protect, and right-click Format Cells in the context menu again.
  4. In the Format Cells dialog box, click Protective Equipment tab, and then check the Locked Option. And then click OK information to close the dialog box, see the illustration:
  5. In this step you can hide the selected columns and then click Continue Review > Protective Sheet.
  6. And in the protective sheet dialog, enter your password and confirm it. See screenshots:

And if you show the columns with the Show function, the Show The tool turns gray so that you can’t show the hidden columns. See screenshot:

Note: Because you removed the locked state from all cells, you can enter data into the worksheet’s cells as usual.

Save settings for custom views in Excel

If you need to switch to the table more often between the different views, you can save those views. It is a good idea to create a view first, where all rows and columns are displayed. To save custom views, do the following in Excel:

  • Show or hide the columns and rows as required for the view.
  • On the View tab in the Workbook Views group, click the User Def button. Views.
  • Click the Add button.
  • Give the view a meaningful name, e.B. Everything Visible and click OK
  • For each view you want to save, follow steps 1 through 4.

Calling up saved settings

If you want to switch to a saved view, do the following:

  • On the View tab in the Workbook Views group, click the User Def button > Views.
  • Select the view you want and click View.
  • Alternatively, you can activate the view by double-clicking the name.
  • You can now easily switch between different displays without having to show or hide the rows or columns individually each time.

How to automatically organize an Excel worksheet

A large worksheet with many filled cells and many formulas becomes clearer if you can quickly hide less important rows or columns if necessary. If you have detailed and summary data on your worksheet, you can use the automatic outline to assign rows and columns to different levels of outline, allowing you to quickly hide and show them again.

When you automatically outline a worksheet, Excel is based on existing aggregate functions such as .B. SUM, VALUE, MIN, or MAX. To automatically organize the worksheet, please do the following:

  • Activate a single, arbitrary cell.
  • On the ribbon, click the Data tab in the Outline group on the down arrow at the Group button and select the AutoOutline command.
  • To the left of the row numbers and above the column labels will be buttons with a minus sign and numbers that show each level of outline.
  • When you click a button with the minus sign, its rows or columns disappear and the minus sign turns into a plus sign.
  • Clicking on the plus sign will reappear the corresponding rows or columns.

After the automatic outline, there are outline levels at the rows you designated in the organisation of the Excell sheet. By clicking the outline level buttons, you can quickly hide rows and columns.

If, after the automatic organisation, the rows or columns contain a different formatting, you have automatic formatting enabled during the outline. You can view or change the setting as follows.

  • On the ribbon, click the Data tab in the Outline group on the arrow at the bottom right.
  • If you do not want the cells to be automatically formatted, turn off the Automatic Formatting.
  • Click the OK button.
  • This does not remove the automatic formatting of the cells from an existing outline. This option is evaluated the next time you create an automatic outline.

You can remove the auto-outline as follows:

  • Activate a single, arbitrary cell.
  • On the ribbon, click the Data tab in the Outline group on the down arrow to the right of the Group button.
  • Click the Remove Outline button.

Notes

  • You cannot undo the auto-outline button. You can only remove outline, as described above.
  • The key combination can be used to hide and re-show the outline symbols. The outline itself is preserved.
  • If you selected more than one cell before you created an automatic outline, only the selected range of cells is considered.
  • If the worksheet is protected after an automatic outline is created, you cannot show and hide rows and columns from the outline icons.
  • If you call the automatic outline for a worksheet a second time without first removing the existing outline, you will be asked beforehand if you want to change the previous outline.
  • In a worksheet, there can be a maximum of 8 levels of outline at row or column level.
  • If there are no aggregate functions on the worksheet that guide automatic outline, you can manually organize the worksheet.
More Excel guides and tutorials

How to enable macros in Excel: complete step-by-step guide
How to use SUMIF in Excel: step by step guide and examples
How to freeze columns in Excel: easy step by step guide
How to use COUNTIF in Excel: guide and examples
How to find circular references in Excel: step by step