Skip to content
Book a demo Try the demo MacWindows

Shape data with queries

Clean up and reshape a sheet with a list of steps, such as filter, sort, remove columns and remove duplicates, that you can reorder or take back at any time.

All plansUpdated

A query reshapes a sheet with a list of steps: keep only some rows, sort them, drop or rename columns, remove duplicates, tidy text. PlexiiSheets keeps a copy of the data as it was when you started, called the source, and runs the steps on that copy, so you can change, reorder or remove a step at any time and the sheet shows the new result straight away.

Use a query to clean up data you have imported or pasted in, such as a .csv export from another system, when you want to see and adjust each change rather than make it by hand.

Before you start

  • Queries are on every plan.
  • Open a spreadsheet in PlexiiSheets, or click inside a Spreadsheet widget on a desk, and click the sheet tab that holds the data. See PlexiiSheets overview.
  • A query works on the whole tab. Put the data you want to shape on a tab of its own.

Start a query

  1. Click the query button on the toolbar (Query — shape data with refreshable transform steps). The Query window opens.
  2. Click Use this sheet as the source.

The window now shows how many columns and rows the source has, and how many the result has after the steps. There are no steps yet.

Add steps

  1. In the row at the bottom of the window, choose a step from the first list.
  2. Fill in what that step needs, such as the column and a value.
  3. Click Add step.

The step joins the end of the list and the sheet changes at once.

Step What it does What you choose
Filter rows Keeps only the rows that match. A column, a test (equals, does not equal, contains, >, >=, <, <=, is not empty or is empty) and a value.
Sort Puts the rows in order. In ascending order, numbers come before text. A column and ascending or descending.
Remove column Takes a column out. The column.
Keep only column Keeps one column and takes out the rest. The column.
Rename column Gives a column a new heading. The column and the new name.
Remove duplicate rows Keeps the first of any rows that are exactly the same. Nothing.
Keep top N rows Keeps the first rows and takes out the rest. How many.
Skip first N rows Takes out the first rows. How many.
Use first row as headers Moves the first row up into the column headings. Nothing.
Trim column whitespace Removes spaces at the start and end of each value in a column. The column.
Change column case Changes a column's text to UPPERCASE or lowercase. The column and the case.

How the tests in Filter rows compare values:

  • contains ignores capital letters, so north finds North.
  • equals and does not equal compare numbers as numbers, so 5 equals 5.0. Text has to match exactly, capitals included.
  • >, >=, < and <= compare numbers. Rows whose value is not a number are left out.

Steps work on what is typed in the cells. For a cell with a formula, that is the formula itself rather than its result.

Change the steps

Each step is listed in the order it runs, numbered, with a short description. Next to each one:

  • Move up and Move down change the order. Order matters: keeping the top 10 rows and then sorting gives a different result from sorting first.
  • Remove step (×) takes the step out.

Every change runs all the steps again on the source, so the sheet always shows the source with the current steps applied. Each change is also a step in the undo history: click Undo on the toolbar to step back.

To close the window, click Close or ×, click outside it, or press Esc. The query is saved with the tab, so the next time you open the Query window on this tab, your source and steps are there.

Refresh from the sheet

Refresh from sheet takes what is on the sheet now as the new source and runs every step on it again.

Common questions

Can I get my original data back? Yes. Remove the steps one by one, and the sheet goes back to the source. You can also click Undo to step back through your changes.

A filter on a number column keeps nothing. Check the values: a test such as > compares numbers only, so values typed with words or symbols are left out. Trim column whitespace can also help when values have stray spaces.

My heading row was sorted in with the data. Add Use first row as headers and move it to the top of the list with Move up.

What is the difference between a query and a filter? A filter on the sheet hides rows while you look, and takes nothing away. A query rewrites the tab with the result of its steps. See Sort and filter.

Does the query go into an exported file? An exported .xlsx or .csv file holds the sheet as it is now, the result of the steps. The steps themselves stay in PlexiiSheets. See Import and export Excel and CSV files.

Still need help? Contact support at [email protected].