• Excel Book Excerpt

Excel How to Set up Your Data for Easy Sorting and Subtota

This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

How to Set up Your Data
for Easy Sorting and Subtotals

Problem: I want to be able to use the powerful data commands such as Sort, Filter, Subtotal, Consolidate, and PivotTable. Is there any special way I should set up the data to begin with?

Strategy: You need to follow all the rules to keep your data in list format:

Rule 1: Use only a single row of headings above your data. If you need to have a two-row heading, set it up as a single cell with two lines in the row. See “How to Fit a Multiline Heading
into One Cell" on page 282

Rule 2: Never leave one heading cell blank. You will find that you do this if you add a temporary column. If you forget to add a heading before you sort, this will completely throw off the IntelliSense, and Excel will sort the headings down into the data.

Rule 3: There should be no entirely blank rows or blank columns in the middle of your data. It is okay to have an occasional blank cell, but you should have no entirely blank columns.

Rule 4: If your heading row is not in row 1, be sure to have a blank row between the report title and the headings.

Rule 5: Formatting the heading cells in bold will help the Excel’s IntelliSense module understand that these are headings.

Gotcha: List format won’t help at all if your data is only two columns wide.

Results: If you follow the list format rules, Excel’s IntelliSense will allow all the data commands to work flawlessly.

<-Previous Topic Next Topic->

For more resources for Microsoft Excel: