Note: The other languages of the website are Google-translated. Back to English
English English

How to auto outline data in Excel?

When your data in a worksheet is extremely complicated, sometimes it’s even difficult for you to group them manually. In this case, we can group and summarize the data by creating an outline of rows and columns. This tutorial will introduce to you a quick way to auto outline data in Excel.
doc auto-outline-data 1

Auto outline data in Excel


To auto outline the below data so you can quickly read and analyze the summary rows or columns or display the detailed data for each group, please do as follows:
doc auto-outline-data 2

1. Go to the Data tab and click Group > Auto Outline.
doc auto-outline-data 3

2. Now you can see the data is organized into groups and the outline is displayed.
doc auto-outline-data 4

Now check the minus (-) button or the plus (+) button to collapse or expand the groups of rows and columns. Or click the appropriate outline number symbols to collapse or expand that entire level.
doc auto-outline-data 5

In the screenshot above, I collapse the rows of sale products in each city and the columns of the specific months in Q1 and Q2.

Notes

  1. The Auto Outline feature is so handy, but it has its limitations. To use it, please make sure there is no blank rows or columns in the data.
  2. There should be a summary row or column with formulas for each subset of rows or columns.
  3. When the auto outlined groups are not correctly organized, you should change the style the outline is performed.
    • On the Data tab, click the dialog box launcher, a small arrow in the lower-right corner of the Outline group.
      doc auto-outline-data 6
    • The Settings dialog box pops up, check the Automatic styles box and uncheck the rest of the boxes, then click OK.
      doc auto-outline-data 7
    • After the setting, the data will be correctly auto outlined.

Other Operations (Articles)

How To Create A Stacked Clustered Column / Bar Chart In Excel?
This article, I will talk about how to create a stacked clustered column chart in Excel as below screenshot shown.

How To Create A Unique Alphabetically Sorted List From A Column In Excel?
In some cases, you need to extract all unique cells from a list, and locate these cells in a new column in alphabetical order. In this article, we will show two methods to achieve it.

How To Split A Long List Into Equal Groups In Excel?
If you have a long list of data needed to be split into multiple equal groups as following screenshot shown, how could you deal with this task quickly and easily in Excel?

Quickly Generate Random Groups For List Of Data In Excel
Sometimes, you may want to randomly assign data to groups as screenshot 1 shown, or generate groups for a list of names as below screenshot 2 shown, but how can handle these jobs quickly? Actually, in Excel, you can use formulas to solve them easily.



  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...
kte tab 201905
  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations