Data tools

The Data tools group on the Data tab holds commands that clean up and combine data: Duplicates, Split, Flip, Flash fill, Consolidate… and Validation….

Data validation

Data validation limits what can be typed into cells — a choice from a list, a whole number in a range, text no longer than a limit.

Add a validation rule

1. Select the cells the rule should cover.
2. Click Data › Data tools › Validation….
3. In the Data validation dialog, which shows the cells it Applies to, choose what to Allow:

4. For Whole number, Decimal and Text length, choose the condition from the list labelled Between: between, not between, equal to, greater than, less than, at least or at most. For every condition except between and not between, type the value to compare with in Minimum. A bound can be a number or a formula.
5. Optionally, type your own Message when it is refused. It is shown instead of the generated message.
6. Leave Allow the cell to be cleared on unless the cells must never be empty.
7. Click Apply.

The status bar says Validation added to followed by the cells’ address. Adding a rule to exactly the same cells replaces the rule that was there.

What happens when a value is refused

When you type a value the rule doesn’t allow, the cell keeps its old contents and the status bar shows why:

Formulas are always accepted, because their result isn’t known until they are calculated. Validation is checked when you type into a cell; an ordinary paste and the fill commands don’t check it.

Rules in workbooks made in other apps are enforced too, including lists that point at a range of cells on the same sheet, and date and time rules.

Change or remove a rule

1. Click a cell that has the rule.
2. Click Data › Data tools › Validation…. The dialog opens with the rule’s settings.
3. Change the settings and click Apply, or click Clear to remove the rule.

To find every validated cell, use Go to Special › Data validation — see Selecting and navigating.

Remove duplicate rows

1. Select the list, including its header row. If you select a single cell, all the data on the sheet is used.
2. Click Data › Data tools › Duplicates.

A row is a duplicate when every selected column matches an earlier row. Capitals and spaces before and after the text are ignored. The first occurrence of each row is kept, the rows below move up to close the gaps, and the header row is never compared. The status bar says Removed followed by the number of rows, or No duplicate rows found.

Split text into columns

Split is Text to Columns: it splits the text in one column across several columns at a delimiter.

1. Select the cells in the column to split. The first column of the selection is the one split.
2. Open Data › Data tools › Split and choose the delimiter: Comma, Semicolon, Tab, Space or Pipe.

Each piece goes into its own cell, starting in the original column. Spaces around each piece are removed, and a piece that reads as a number becomes a number. Text inside double quotes is kept together, so "Leeds, UK" stays in one piece. The status bar says Split into followed by the number of columns, or Nothing to split — no cell contained followed by the delimiter.

Flip rows and columns

Flip is Transpose: it turns the selected block’s rows into columns, in place.

1. Select the block.
2. Click Data › Data tools › Flip.

The flipped block starts at the same top-left cell. For a block that isn’t square, the flipped block has a different shape: it overwrites cells beside or below the original, and cells of the original that fall outside the new shape keep their old contents. Clear them afterwards if you don’t want them.

To flip while pasting, use Home › Clipboard › Paste › Transposed.

Flash Fill

Flash Fill fills a column by following the examples you type — taking first names out of full names, or joining a code and a number.

1. Keep the data to work from in one or more columns, with a column for the results beside them.
2. In the results column, type the result you want for the first row, and preferably the second.
3. Select the block that includes the source columns and the column to fill, and put the current cell in the column to fill. If you select a single cell, all the data on the sheet is used.
4. Click Data › Data tools › Flash fill, or choose Home › Editing › Fill › Flash Fill.
5. Read the Flash fill dialog. It describes the pattern it found in words — which pieces of which columns it takes and joins, and any change of capitals — and lists the first cells it would fill.
6. Click Fill to write the values, or Cancel.

Flash Fill builds its results from pieces of the other columns found by a delimiter or by character position, text it copies between them, and changes of capitals. It fills only the empty cells of the column, writes text, and doesn’t change the examples. The status bar says how many cells were filled.

If it can’t find a pattern, the dialog says why and Fill is unavailable:

Consolidate

Consolidate combines several blocks of numbers — for example, the same report on four regional sheets — into one summary block.

1. Click the cell where the top-left corner of the result should go.
2. Click Data › Data tools › Consolidate….
3. Under FUNCTION, choose how to combine the numbers: Sum, Count, Average, Max, Min, Product, Count numbers, StdDev, StdDevp, Var or Varp.
4. Under SOURCES, type the address of the first block, such as North!B2:D5. Leave out the sheet name for a block on the current sheet. Click Add source for each further block, and × (Remove this source) to take one away.
5. Choose how the blocks are matched:
   • Leave both label options off to match by position: the top-left cell of every source is the same item.
   • Turn on Labels in the top row, Labels in the left column or both to match by label: each source’s row and column labels are matched up, so sources can list items in different orders. Include the labels in each source address.
6. Optionally, turn on Link to the sources to write formulas that refer to the source cells instead of fixed numbers. This works only when matching by position.
7. Click Consolidate.

The status bar says the size of the result and how many ranges it came from, for example 4 by 3 written from 4 ranges. When matching by label, it also names labels that were missing from at least one source, so you can spot a spelling difference. A cell that no source has a value for is left empty rather than 0.

If something is wrong, the dialog stays open and explains: