Excel 2024: Set Up Your Data for Data Analysis
April 17, 2024 - by Bill Jelen
Make sure to follow these rules when you set up your data for sorting, subtotals, filtering and pivot tables.
- 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 by using Alt+Enter or Word Wrap as discussed in Bonus Tip: Use Accounting Underline to Avoid Tiny Blank Columns.
- Rule 2: Never leave one heading cell blank. This often happens to me when I set up a temporary column.
- 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. It is fine to make this blank row have a row height of 1 so it is barely visible.
- Rule 5: If you have a total row below your data, leave one blank row above the totals.
- Rule 6: Formatting the heading cells in bold will help the Excel's IntelliSense module understand that these are headings.