# Data tools: filter, sort, clean up, solve, pivot What the Data tab does to a block of rows and what Insert > PivotTable makes of one: the filter and its menu, a sort with levels, the two clean-up wizards an export needs, the solver, and a pivot written as cells. Each one is a dialog the shell carries, a definition the document keeps, and a function you can call without the shell. [Getting started](https://svgrid.com/docs/help/sheet/start/) shows the document; [Formulas](https://svgrid.com/docs/help/sheet/formulas/) the engine these read. The examples share a sales log, the shape a filter and a pivot are made for. ```svelte ``` ## Filter `Ctrl+Shift+L`, or Data > Filter, puts an arrow on every header cell of the region around the active cell. The arrow drops Excel's menu: Sort A to Z and Z to A, Clear Filter, Text Filters or Number Filters (equals, begins with, contains, greater than, between and the rest, two joined with And or Or), Date Filters on a column of dates (Today, This Week, Last Month, Year to Date and the rest), Filter by Color when the column's cells carry more than one fill, Top 10 on numbers, a search box, and the column's values with their counts. OK folds the rows that fail; the arrow turns into a funnel, the row numbers turn blue and the status bar reads "N of M records found". A filter is part of the document, so a sheet can open already filtered. `autoFilter` is the region and a filter per column index: `values` with the texts left unticked, `condition` with one or two operators, `date` with a period, `color` with a fill, or `top` with a count. ```svelte ``` Open deals over 6,000. Type `Won` into one of them: it folds away at once, since the rows are worked out again after every change. A folded row is the filter's, not a hidden row: Unhide leaves it, and `Ctrl+Shift+L` again shows it while a row hidden by hand stays hidden. The filter rides in `getState()` as `autoFilter`, reports `{ kind: 'filter' }` on `onChange`, and goes into the `.xlsx` with its criteria. > Live demo: Support ticket log: AutoFilter - https://svgrid.com/demos/464-ticket-log-autofilter/ ## Sort Data > Sort A to Z and Z to A sort the region around the active cell by the column the cell is in, the header row staying where it is (the filter menu's Sort does the same). Data > Sort opens the dialog with levels: sort by Region, then by Amount largest to smallest. The format store keys on the row's id, so a sorted row keeps its fills and number formats. `sortOrder` and `guessHeaderRow` are the same sort as functions, for a shell of your own. ## Text to Columns and Remove Duplicates An export lands in column A, `;`-separated, with the same people in it twice. Data > Text to Columns opens a wizard that has already guessed the delimiter and previews the split; Data > Remove Duplicates lets you tick the columns that decide identity, compares without regard to case as Excel does, and reports how many it removed and how many unique rows remain. Each is one `Ctrl+Z`. ```svelte ``` Select A1:A6, Data > Text to Columns, Finish; then with the block selected, Data > Remove Duplicates with every column ticked: two rows go. Both dialogs are the shell's; a host with a wizard of its own takes `text-to-columns` or `remove-duplicates` over in `onAction` and writes inside `cmd.batch`, so its version is one undo too. > Live demo: Data cleanup: Text to Columns, Remove Duplicates - https://svgrid.com/demos/458-data-cleanup/ ## Goal Seek Data > Goal Seek: set a formula cell to a value by changing one input cell. The solver is a secant search with a bisection fallback; the sheet does not move until the status dialog says it found a solution and OK is pressed, and OK writes through the grid's command context, so it is one `Ctrl+Z`. The same search is `goalSeekCell` on a workbook, which tries values in the input cell and puts the original back, returning what it found: ```svelte Set profit to 20,000 by changing the price {found} ``` A write with `setRaw` is one the shell cannot see, hence `refresh()`; a dialog of your own should write through `cmd.setCellValue` from `onAction` instead, which repaints and lands in the undo history. > Live demo: What-if analysis: Goal Seek - https://svgrid.com/demos/457-goal-seek/ ## PivotTable from a range Insert > PivotTable summarises the selected block, or the region around a single cell, on the same pivot engine the grid's pivot mode uses. The dialog takes the source (its first row the field names), where the result goes, and which field is a row, a column, a filter or a measure, with Sum, Average, Count, Distinct count, Min and Max. What the sheet keeps is the definition; what it writes is cells. The result is an ordinary block, so it can be formatted, charted, filtered and saved to an `.xlsx` like anything typed. Insert > Refresh rebuilds it from the source, Show Details writes the rows behind a cell to a sheet of their own, and opening the dialog from inside a block edits that pivot. A document can ship with one: `pivotBlock` computes the cells, `pivotWrittenRect` the rectangle they cover, and the definition goes in `pivots`. ```svelte ``` Change an Amount in the log, click inside the block, then Insert > Refresh: the block is rewritten (Refresh rebuilds the PivotTable the cursor is in). The block sits two rows under the log, Total column and grand total in view. Pivots ride in `getState()` as `pivots`, report `{ kind: 'pivots' }`, move with an insert or a delete, and are dropped when their source or target cell is deleted. > Live demo: PivotTable from a range - https://svgrid.com/demos/487-sheet-pivot-range/ ## See also - [Tables and structured references](https://svgrid.com/docs/help/cells/tables/) - Format as Table, which gives a block a name, banding and `=SUM(Orders[Amount])`. - [Charts, sparklines and pictures](https://svgrid.com/docs/help/sheet/charts-and-objects/) - a chart over the pivot's block. - [Pivot tables on the grid](https://svgrid.com/docs/help/pivot/) - the grid's own pivot mode, for rows that are not a sheet. - [Filtering on the grid](https://svgrid.com/docs/help/filtering/overview/) - the Excel-style filter compiler the sheet's conditions run through. - [The spreadsheet shell](https://svgrid.com/docs/help/cells/spreadsheet-shell/) - the AutoFilter menu item by item, and the pivot dialog's Filter area and Show Details. --- SvGrid is the Svelte 5 data grid (`npm install @svgrid/grid`, MIT). This page: https://svgrid.com/docs/help/sheet/data-tools/ . All docs: https://svgrid.com/llms.txt