Data tools

Tables

Turn a range into a table with Insert > Table or Ctrl+T. Tables grow as you type below them, carry their formulas down as calculated columns, have a total row, and use the built-in table styles or your own. Formulas can refer to them by name, such as Sales[Amount].

Sort and filter

  • Sort by values, cell color, font color or icon, on several levels, with custom lists.
  • AutoFilter with value lists, search, number, text, date and color filters, and top 10.
  • Advanced Filter with a criteria range, copying the result elsewhere when you need it.
  • Remove Duplicates, Text to Columns and Flash Fill (Ctrl+E).

PivotTables and PivotCharts

Insert > PivotTable summarizes a range or a table. Drag fields to rows, columns, values and filters; group dates and numbers; show values as percentages, running totals or differences; add calculated fields; and choose from the built-in PivotTable styles. PivotTables from Excel files open and refresh, and PivotTables made in 8sheets open in Excel.

PivotCharts follow their PivotTable, with field buttons on the chart.

Slicers and timelines

Slicers and timelines filter tables and PivotTables with buttons. They are saved in Excel's format and keep working when the file is opened in Excel.

Conditional formatting

All of Excel's rule types: highlight rules, top and bottom rules, data bars, color scales, icon sets and formula rules, managed in order with Manage Rules.

Data validation

Restrict input to whole numbers, decimals, lists, dates, times, text length or a custom formula, with input messages and error alerts. List validation shows a dropdown in the cell.

Get Data

Data > Get Data loads data into a table from:

  • text and CSV files,
  • JSON and XML,
  • other Excel workbooks,
  • a table or range in the open workbook,
  • a web address.

Each query is a list of steps, such as removing columns, filtering rows, changing types and splitting columns, which you can edit and refresh. Data is read in a background thread, and the query definition is stored in the workbook.

Power Query (M) queries from Excel files are kept in the file but are not run.

Outline and subtotals

Group rows and columns, collapse them to a level, and insert subtotals with Data > Subtotal.

What-if analysis

Goal Seek, Data Tables, the Scenario Manager and Consolidate work as they do in Excel.