WPS Office

Free All-in-One Office Suite with PDF Editor

correct-icon

Edit Word, Excel, and PPT for FREE.

correct-icon

Read, edit, and convert PDFs with the powerful PDF toolkit.

correct-icon

Microsoft-like interface, easy to use.

Free download

Windows • MacOS • Linux • iOS • Android

banner

How to Hide Rows in Excel

October 21, 2021
5.4K Views

Excel is a widely used application for storing data, altering it, and doing much more. It has various useful features and functions that can help a person in day-to-day or professional work. However, many people are unaware of its features and how to use them. Hiding rows is one feature people don’t know how to use. Hiding rows in Google Sheets or Excel is helpful as it streamlines the data and increases its readability. Does the question remain how to hide rows in Excel using formulas and shortcuts based on a cell value and unhide them later? This article will guide you thoroughly on this.

1. How to Hide Rows in Excel Using Shortcut Keys

Utilizing keyboard shortcuts provides a swift means to hide or unhide rows in Excel. Let's do this:

Steps

Step 1: Select the row number(s) on your worksheet's left side and press Ctrl + 9.

Step 2: To unhide a hidden row, click the row numbers above and below the hidden one(s) and hit Ctrl + Shift + 9.

Tip: Unfortunately, in some Windows versions, these key combinations might inexplicably fail to function. In such cases, you must resort to alternative methods given below.
logo

2. How to Hide and Expand Rows Using the Group Feature

This approach necessitates the creation of row groups before hiding them.

Steps

Step 1: To create a group, proceed to highlight the specific rows you intend to hide. Navigate to the "Data" tab.

The Data tab



Step 2: In the "Outline" group, locate and click the "Group" button.

Click the



Step 3: An indicator line and a subtraction sign (-) box will appear next to the grouped rows.

An indicator line and a subtraction sign (-) box



Step 4: Click on the minus sign box to hide the group of rows. Once successfully hidden, the box will display a plus (+) sign.

Click on the minus sign box to hide the group of rows



Step 5: A straightforward click on the plus sign (+) box will suffice to unhide the grouped rows when needed.

Tip: If you are a professional working with large data sets, this is the best way to solve your problem without making any mistakes.
logo

3. How to Hide Rows Using Filters in Excel

The "Filters" is a valuable tool often overlooked. So, don't miss it.

Steps

Step 1: Begin by selecting the dataset that you intend to filter.

Filter option



Step 2: You'll easily spot the Data tab in the menu section. Click on it to reveal the Filters function.

Filter function tab



Step 3: Activate the Filter function by clicking on it, prompting a dropdown menu to appear. Subsequently, select either Number Filters or Text Filters, depending on your specific filtering criteria.

select either Number Filters or Text Filters



Step 4: A dialogue box will pop up, allowing you to input your chosen criterion into the text box adjacent to the "greater than" option.

dialogue box



Step 5: Once your criteria are defined, click "Ok." You'll witness an immediate transformation as only the filtered data remains visible.

click on Ok



Tip: This functionality is particularly beneficial for data operators, analysts, and professionals who strive to enhance data readability by filtering extraneous information.
logo

4. How to Hide a Selection of Rows in Excel

Like hiding rows in Excel with shortcuts, hiding a range of rows with buttons is straightforward. So, let's find out how to hide rows in Excel with the button:

Steps

Step 1: Begin by highlighting multiple rows you intend to hide. For example, 5, 6, and 7.

Select the rows



Step 2: Proceed to right-click within the highlighted area.

Step 3: From the contextual menu, opt for the "hide" option, and watch as your chosen rows disappear from view within the Excel spreadsheet.

The hide button


5. How to Hide Rows Content with Conditional Formatting

Hiding specific rows in Excel can become more sophisticated when employing conditional formatting, particularly when you want to exclude headers from this process.

Steps

Step 1: Begin by obtaining your dataset. We've utilized sample data for illustration purposes.

the sample dataset



Step 2: Navigate to the Home tab in the Excel menu and locate the "Conditional Formatting" option.

Conditional Formatting



Step 3: Click on "Conditional Formatting" and proceed to select the "New Rule" option.

The



Step 4: A dropdown window will appear. Opt for the "Use a formula to determine which cells to format" choice.

"Use a formula to determine which cells to format"  option



Step 5: Now, it's time to input your target formula in the formula bar. Then press Ok.

Click on Ok



Step 6: Select "Custom Format" from the dropdown menu within the Format option. Under the Numbers Tab, choose "Custom" at the bottom of the list. Within the Type field, input three semicolons ";;;" and then confirm your selection by clicking "Ok."

How to Unhide Rows in Excel?

Uncovering hidden rows is a straightforward process. You can even unhide multiple rows simultaneously.

Steps

Step 1: Locate the hidden row.

Step 2: Examine the row numbers on the left side as you scroll through your document. If you notice a gap in the sequence, the row in between is hidden. You'll also see a double line between the two-row numbers.

Double line between the two-row numbers



Step 3: Right-click the area between the two-row numbers. This action triggers a dropdown menu to appear.

Step 4: Select "Unhide" from the dropdown menu. This action will reveal the hidden row.

Unhide option


Comparison of the Above Methods

Here's a comparison of all the above methods for hiding rows in Excel:

1. Shortcut Keys:

Ease: Using keyboard shortcuts (Ctrl + 9 to hide, Ctrl + Shift + 9 to unhide) is the quickest way; no formulas or Menus are needed.

Efficiency: These shortcuts work well, but not universally on all Windows versions.  

2. Group Feature:

Ease: Requires creating row groups before hiding, introducing an additional step.

Control: Offers precise control by organizing rows into groups for easy toggling.

3. Filter Function:

Convenience: Filtering data helps hide rows based on specific criteria.

Usefulness: Ideal for managing large datasets, enhancing data readability, and retaining relevant information.

4. Hiding a Selection of Rows:

Simplicity: Select and right-click to hide multiple rows at once.

Versatility: Useful when concealing a range of rows and maintaining spreadsheet structure.

5. Conditional Formatting:

Complexity: Involves setting up conditional formatting rules.

Precision: Effective when hiding rows while preserving headers and organizing data.

Each method serves a distinct purpose. The choice depends on the explicit requirements of the Excel task. You can choose based on condition, whether about speed, precision, or managing complex datasets. Ultimately, having these options at one's disposal empowers one to work more efficiently and maintain well-organized spreadsheets.

Free Editing Excel, Word, PowerPoint - WPS Office

WPS Office interface



The methods described can be used in both Microsoft Excel and WPS Office Spreadsheet, which means they work in two popular spreadsheet software applications. However, the WPS office is recommended for several reasons:

1. Price:

WPS Office offers cost advantages. It's often more budget-friendly or free than Microsoft Excel, making it accessible to a broader audience.

2. Compatibility:

WPS Office is engineered to maintain compatibility with the formats used by Microsoft Office. This means you can work seamlessly with files created in Excel or Word without worrying about format issues.

3. Features:

WPS Office provides a comprehensive set of features similar to Excel. You can perform complex tasks, create charts, and work with data effectively.

4. User-friendly Interface:

WPS Office is known for its user-friendly interface, making it simple for beginners and experienced users to navigate and work efficiently within the software.

Trustpilotstars4.8
WPS Office- Free All-in-One Office Suite
  • Use Word, Excel, and PPT for FREE, No Ads.

  • Edit PDF files with the powerful PDF toolkit.

  • Microsoft-like interface. Easy to learn. 100% Compatibility.

  • Boost your productivity with WPS's abundant free Word, Excel, PPT, and CV templates.

5,820,008 User
avator
Algirdas Jasaitis
logo

FAQs

1. Can hidden rows still be referenced in formulas?

Yes, hidden rows can still be used in formulas. You can add a special column called 'Visible' in your formula. This column helps filter out the hidden rows, so your formula works as expected, even when some are hidden due to filters.

2. Can hidden rows be printed or exported?

Yes, when you print or export an Excel sheet, it typically includes the hidden rows. So, even if you've hidden some rows for organization, they'll still appear in your document's printed or exported version. This applies whether you're using Excel's printing feature or exporting to a different program, like SQL Server.

3. How can I hide rows based on cell values in a specific column?

Step 1. First, identify the cell value in that column that you want to use as a condition.

Step 2. Select the rows you want to hide based on this condition.

Step 3. Go to the 'Format' option in the menu.

Step 4. Choose 'Hide' from the visibility options.

Step 5. Set the condition for hiding rows, and apply the formula you've created to these hidden rows.

Step 6. Tap on "Use a Formula to Determine Which Cell to Format" and apply the formatting option you want for these hidden rows.

Summary

Have you ever needed to hide confidential information or declutter your workspace in Excel? Mastering the art of hiding rows in Excel is a skill worth acquiring. This guide will lead you through several methods, each with unique advantages, from speed to precision. And for an enhanced experience, consider using WPS Office. Known for its affordability, compatibility, extensive features, and user-friendly interface, it presents an impressive workspace that makes handling tasks a breeze.


15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.