- Put similar items in one column
- Design the data so that all rows have similar items in the same column
- Keep the range separate
- Leave at least one blank column and one blank row between the related data range and other data on the worksheet. Excel can then more easily detect and select the range when you sort, filter, or insert automatic subtotals.
- Position critical data above or below the range
- Avoid placing critical data to the left or right of the range; the data might be hidden when you filter the range.
- Show rows and columns
- Make sure any hidden rows or columns are displayed before making changes to the range. When rows and columns in a range are not showing, data can be deleted inadvertently.
- Use formatted column labels
- Create column labels in the first row of the range of data.
- Excel uses the labels to create reports and to find and organize data.
- Use a font, alignment, format, pattern, border, or capitalization style for column labels that is different from the format you assign to the data in the range.
- Format the cells as text before you type the column labels.
- Use cell borders When you want to separate labels from data, use cell borders— not blank rows or dashed lines— to insert lines below the labels.
- Avoid blank rows and columns
- Avoid putting blank rows and columns in the range so that Excel can more easily detect and select the related data range.
- Don’t type leading or trailing spaces
- Extra spaces at the beginning or end of a cell affect sorting and searching. Instead of typing spaces, indent the text within the cell
- Extend data formats and formulas
- When you add new rows of data to the end of a data range, Excel extends consistent formatting and formulas. Three of the five preceding cells must use the same format for a format to be extended. All of the preceding formulas must be consistent for a formula to be extended.
- You can designate a contiguous range of cells on your worksheet as a list. When you create a list, data defined by the list can be manipulated independently of data outside of the list
- After you create a list, you can use list features to quickly sort, filter, total, or publish the data contained within the list.
- You can also use the list feature to compartmentalize sets of related data by organizing that data using multiple lists on a single worksheet