Subset the worksheet

Complete the following steps to subset the worksheet.

Open the dialog box

To open the Subset Worksheet dialog box, do one of the following:
  • Choose Data > Subset Worksheet.
  • Click in the worksheet and choose Editor > Subset Worksheet > Custom Subset.
  • Right-click in the worksheet and choose Subset Worksheet > Custom Subset.

Specify how to create a subset

From How do you want to create a subset?, select the method to use to create the subset of data.

Use rows that match a condition
Subset the worksheet based on rows that match a condition (such as dates between two specified dates). Complete the following steps to specify the condition.
  1. In Do you want to include or exclude rows?, select one of the following options:
    • Include rows that match the condition: Copy only rows that match the condition to the new worksheet.
    • Exclude rows that match the condition: Omit rows that match the condition from the new worksheet.
  2. In Column, enter the column to subset by.
  3. From Condition, select the type of condition and then specify the parameters.
Use brushed rows from a graph
Subset the worksheet based on rows that correspond to points that you have brushed in a graph.
From Do you want to include or exclude rows?, select one of the following options:
  • Exclude brushed rows: Omit brushed rows from the new worksheet.
  • Include brushed rows: Copy only brushed rows to the new worksheet.
Use rows with formatted cells in a column
Subset the worksheet based on formatted cells (for example, cells that have red shading) in a column. Complete the following steps to specify the options.
  1. From Do you want to include or exclude rows?, select one of the following options:
    • Exclude rows with formatted cells: Omit rows that contain formatted cells from the new worksheet.
    • Include rows with formatted cells: Copy only rows that contain formatted cells to the new worksheet.
  2. In Column, enter the column that contains the formatted cells.
Use row numbers
Complete the following steps to specify the options.
  1. From Do you want to include or exclude rows?, select one of the following options:
    • Exclude rows: Omit the specified rows from the new worksheet.
    • Include rows: Copy only the specified rows to the new worksheet.
  2. In Range type, specify the type of range, then enter the row number or numbers to define the range. If you select Arbitrary set of rows, you can type ranges of rows with a colon. For example, if you type 1:4 15 20:22, then rows 1, 2, 3, 4, 15, 20, 21, and 22 are included or excluded.
Use formula
Complete the following steps to specify the options.
  1. From Do you want to include or exclude rows?, select one of the following options:
    • Include rows that match the condition: Copy only rows that match the condition to the new worksheet.
    • Exclude rows that match the condition: Omit rows that match the condition from the new worksheet.
  2. In Formula, enter an expression. For example, if you enter the expression C1 < 40000 And C2 >= 16, then the subset includes (or excludes) rows for which the value in C1 is less than 40000 and the value in C2 is at least 16. For more information, go to Create a formula with the Calculator.
    Tip

    The condition statement is limited to 1,024 characters (including spaces). You can reduce the number of characters in your condition statement by using column numbers instead of column names (for example, C1 instead of 'Revenue for July').

Specify the new worksheet name

In New worksheet name, you can enter a custom title for the worksheet.

By using this site you agree to the use of cookies for analytics and personalized content.  Read our policy