When you come across a spreadsheet where columns or rows are collapsed, it can limit your view of the entire dataset. This guide offers in-depth techniques for expanding all content in Excel across three different situations, enabling you to fully access and analyze all the data.

Expanding hidden rows or columns

Clearing filters to reveal data

Expanding grouped columns or rows

Expanding hidden rows or columns

In most cases, columns and rows may be hidden using the Hide feature, as shown in the screenshot below, please follow methods below to expand all columns and rows.

Method 1: Using Unhide command

To expand all collapsed columns and rows, do as these:

1. Click at the arrow in the top left of the worksheet to select the entire worksheet.

   

2. Right click at any column header and choose Unhide to expand all columns

   

   Then all hidden columns are shown.

   

3. Right click at any row header to choose Unhide to expand all rows.

   

   Then all hidden rows are shown.

   

Method 2: Drag the mouse to expand the hidden columns or rows

Manually drag the borders of column or row headers to widen them, revealing the hidden content.

Method 3: One-click to unhide all ranges

The Unhide All Ranges feature of Kutools for Excel unhides all hidden ranges with only one click.

After installing Kutools for Excel, click Kutools > View > Unhide All Ranges, the collapsed columns and rows are expanded.

Clearing filters to reveal data

In some cases, data may be collapsed by specific criteria using the Filter function, as shown in the screenshot below. You can clear the filter to expand the columns and rows.

Activate the worksheet you want to expand the collapsed columns and rows, click Data > Filter to deactivate the Filter function, revealing all cells at once.

Expanding grouped columns or rows

Sometimes, the data range may be grouped to collapsed columns or rows, in this case, please follow below steps to expand all of them.

Method 1: Click the plus sign to expand the columns and rows

For counting specific character with case sensitivity, here we provides two different methods.

1. Click on the plus sign above the column header to expand the columns.

   

2. Click the plus sign beside the row header to expand the rows.

   

Method 2: Click Clear Outline button to expand the columns and rows

Select the data range that you want to expand the columns and rows, click Data tab, go to Outline group, click the drop-down arrow in Ungroup, choose Clear Outline.

Now, all grouped columns and rows are visible. However, please note that once the groups are removed, you cannot undo to restore them

This guide aims to arm you with various methods to tackle hidden or collapsed data in Excel, ensuring you always have complete visibility of your spreadsheets for effective data analysis. For more game-changing Excel strategies that can elevate your data management, explore further here..