Separate data delimited in cells onto different rows

Use this technique in Excel to parse out data that is separated with a delimiter inside a single cell on to new rows, with information from adjacent columns repeating on the new rows.

1. On the Data tab, click 'From Table/Range'

2. Select the full range of data that you need to analyze.

3. Power Query will open. Select the column with the data that you need to separate on to new rows.

4. Go to Split Column . . . By Delimiter.

5. In the new dialog box that opens, set the delimiter for your data; choose the radial button for 'Each occurrence of the delimiter', and then in Advanced options choose 'Split into Rows'

6. Click OK, the data will be re-arranged. In this example each email address gets listed on a new row.

7. Finally in Power Query, click on 'Close & Load' and the data will be placed on a new worksheet in Excel.

Excel Worksheets That Hide Themselves

Most litigation support professionals reviewing produced Excel files, will take the trouble to check for hidden worksheets. Simply, right click on any one 'tab', and select 'Unhide' and click on the worksheets you want to display.

Beware of vba code that automatically re-hides Excel worksheets. Spreadsheets can be set to make a worksheet you unhid disappear again.

See this demonstration. After the worksheet named, 'Data' is unhidden, it disappears again after the user clicks on a different worksheet.

A worksheet can be set to behave this way with the following function:

Private Sub Worksheet_Deactivate()

ThisWorkbook.Sheets("Data").Visible = False

End Sub

. . . but other visual basic code can cause the same behavior.

Don't get tripped up by this setting.

Excel's TEXT Formula

Don't miss Excel's versatile TEXT formula, which can help you quickly format columns of data. The TEXT formula begins with a cell reference or value, and then ends with a code. This version . . .

=TEXT(A2,"mm/dd/yy")

. . . will convert the value in cell A2 into a date format.

When written this way:

=TEXT(A3,"000000000")

. . . the formula can easily convert a column of numbers of varying lengths into a standard format with a consistent number of digits.