Remove rows with missing values

You can subset a column of data by copying it to a new column in the active worksheet or to a new worksheet. In this example, suppose C1 contains numeric data with missing values, and you want to store the subset of data in C2.


To remove rows from more than one column based on missing values in C1, enter all the columns in step 2 (for example, C1-C5), and specify an equal number of empty columns in step 3 (for example, C6-C10).

  1. Choose Data > Copy > Columns to Columns.
  2. In Copy from columns, enter C1.
  3. Under Store Copied Data in Columns, choose In current worksheet, in columns. Then enter C2.
  4. Click Subset the Data.
  5. Under Include or Exclude, choose Specify which rows to exclude.
  6. Under Specify which Rows to Exclude, choose Rows that match, and then click Condition.
  7. Enter C1='*', and then click OK in each dialog box. Ensure that the * symbol is surrounded by single quotation marks.
By using this site you agree to the use of cookies for analytics and personalized content.  Read our policy