Categories
Microsoft Excel

How To Hide Unused Cells in Excel (Focus on Relevant Data)

Some people like to hide unused cells to eliminate the empty space in a worksheet and emphasize relevant data.

While you can change the background color of individual cells to make it look like they’ve vanished, a lot of people want to simplify spreadsheets by getting rid of all of the empty columns to the right of their data – and all of the empty rows below their data.

In this article, we’ll go over the most efficient way to hide unused cells in an Excel worksheet. If you want to know more about this topic, we cover formatting cells and working with rows and columns in our Level 1 Excel course.

Before we look at making empty cells disappear, let’s talk about selecting rows and columns in Excel.

Selecting Rows in Microsoft Excel

The method you use to select rows in Excel will really depend on how many rows you need to select.

There are 4 ways to select rows in an Excel worksheet.

  1. Select an individual row. To select one row, click the selector for the row you want to hide.
  2. Select multiple contiguous rows. Click and hold your mouse button with your pointer above a row selector then drag up or down to select additional rows.
  3. Select multiple non-contiguous rows. Click a row selector then hold the Ctrl button as you select additional rows.
  4. Select the remaining rows. To select all of the rows below your data, click a row selector then press Ctrl + Shift + Down Arrow.

In the screenshot (below), I selected row 15 by clicking the row selector, which is circled in red. When you click a row selector, the entire row is highlighted.

A row selector in Excel

Selecting Columns in Microsoft Excel

The process for selecting columns is very similar to selecting rows. Once again, the method you use will depend on the number of columns you need to select.

There are 4 ways to select columns in an Excel worksheet:

  1. Select an individual column. To select one column, click the selector for the column you want to hide.
  2. Select multiple contiguous columns. Click and hold your mouse button with your pointer above a column selector then drag left or right to select additional columns.
  3. Select multiple non-contiguous columns. Click on a column selector then hold the Ctrl button as you select additional columns.
  4. Select the remaining columns. To select all of the columns to the right of your data, click a column selector then press Ctrl + Shift + Right Arrow.

Hiding Rows and Columns in Microsoft Excel

The easiest way to hide rows or columns in Microsoft Excel is to select them using one of the techniques described in the sections above. Then, right click anywhere in the selected area and select Hide from the shortcut menu.

If you prefer keyboard shortcuts, you can press Ctrl + 9 to hide rows or press Ctrl + 0 to hide columns.

Unhiding Rows and Columns in Microsoft Excel

The method you use to unhide rows or columns in Microsoft Excel will depend on how many rows or columns you’ve hidden.

Unhiding Rows

Here is the process, if you’ve only hidden a few rows.

  1. Drag across the row selectors for the row above and below the hidden rows.
  2. Right-click anywhere in the selected range and select Unhide from the shortcut menu.
Unhide rows with the shortcut menu

You could also click the Format button in the Home > Cells group, point to Hide & Unhide and click Unhide Rows.

If you’ve hidden all of the rows below your data, here is the most efficient way to unhide them.

  1. Select all of the cells in your worksheet by pressing Ctrl + A (you may have to press Ctrl + A twice).
  2. Right-click anywhere in the selected range and select Unhide from the shortcut menu.

Unhiding Columns

To unhide a few columns, you would do this.

  1. Drag across the column selectors for the column to the left and right of the hidden columns.
  2. Right-click anywhere in the selected range and select Unhide from the shortcut menu.
Unhide columns with the shortcut menu

You could also click the Format button in the Home > Cells group, point to Hide & Unhide and click Unhide Columns.

If you’ve hidden all of the columns to the right of your data, here is the most efficient way to unhide them.

  1. Select all of the cells in your worksheet by pressing Ctrl + A (you may have to press Ctrl + A twice).
  2. Right-click anywhere in the selected range and select Unhide from the shortcut menu.

By Michael Belfry

Working as a full-time training consultant, Michael provides Microsoft Office courses to government and private sector clients across Canada.