User guide

Cleaning and fixing data

Trim, re-case, split, find and replace, remove duplicates, merge similar spellings, group and total rows, turn columns into rows, fix dates, and force columns to numbers, each with a preview before it runs.

Where to find cleaning tools

You can access data cleaning tools in three ways. Right-click a column header for actions that apply directly to that column. Open the Clean menu in the menu bar for the full list of options. Cleaning tools are part of Pro. You can also press Cmd+K (Ctrl+K on Windows and Linux) and type what you want, like “trim spaces” or “remove duplicates”.

Text cleanup

Inconsistent text can cause problems when you analyze data. The text cleanup tools fix these variations quickly.

• Trim spaces removes leading and trailing whitespace from every value in the column.
• UPPER CASE converts all text to uppercase.
• lower case converts all text to lowercase.
• Title Case capitalizes the first letter of each word.

Making values usable

Sometimes data contains extra characters that prevent proper analysis. These tools remove the noise so you can sort and summarize effectively.

• Clean currency formatting reads a currency column as numbers. It removes symbols such as $, euro, pound, yen and rupee, removes thousands separators, and treats accounting brackets as negative, so ($1,500.00) becomes -1500.00. Values it cannot read without guessing are left exactly as they are rather than mangled. The same conversion appears as Read as money inside the bad values panel. See Money and ambiguous separators below.

• Clean IP addresses strips port numbers and brackets from IP addresses so they parse correctly.

• Normalize emails lowercases and trims email addresses. This ensures that duplicates that differ only in case are treated as the same address.

• Convert a column to numbers scans a column for values that are not valid numbers and shows you what it found. You can then choose what to do with the bad values. You can make them blank, make them zero, replace them with a custom value, delete the rows, or correct each one by hand in the panel. The bad values panel groups them by category. Categories include currency formatting, placeholder text like N/A or TBD, stray symbols, blanks, and whitespace. Click any row to jump to that cell in the grid.

  This works even when the column is mostly text. BigXL classifies columns automatically, and a column of $1,234.56, (200) and oops may not look numeric to it. Choosing Convert a column to numbers is an explicit instruction, so the cast is offered regardless of how the column was classified.

Standardizing dates

Dates come in many formats and can be tricky to work with. Standardizing them makes filtering and sorting much easier.

Select a column and use Change date format from the Clean menu. BigXL scans the column and detects what date formats are present. It handles the ambiguity between day/month/year and month/day/year by looking at column-wide patterns.

Pick your target format from the options provided. The options are ISO (2024-03-15), Day first (15/03/2024), Month first (03/15/2024), Written month (15 Mar 2024), ISO with time (2024-03-15 14:30), 12-hour clock (2:30 PM), or ISO timestamp (2024-03-15T14:30:00). Values that cannot be parsed are left as they are rather than being replaced with errors.

Splitting a column

You often need to separate combined information into distinct parts. For example, you might want to split a full name into a first name and a last name.

Right-click a column and choose Split, or open it from the Clean menu. Pick what to split on from options like comma, space, tab, pipe, dash, or a custom separator.

BigXL previews the split result before you commit. It shows the rows that split into the most parts first so you can see every resulting column, not just the first few rows. To put split columns back together, add a computed column with the & operator. For example: =[full_name 2] & “ “ & [full_name 3]. See Formulas.

Find and replace

Cmd+R opens Find and Replace. You can target a specific column or search every column in your data.

Toggle match case and whole cell options to refine your search. A live counter shows how many cells match and will change before you confirm the operation.

Removing duplicate rows

Duplicate data can inflate your totals and skew your analysis. Open Clean > Remove duplicate rows to fix this. The panel has two tabs, Exact repeats and Near repeats.

Exact repeats. Pick which columns count toward a duplicate. Rows that match on all selected columns are removed, keeping the first occurrence. A preview shows how many rows will be removed before you confirm.

Near repeats. Exact matching misses Alice Smith against alice smith or Smith, Alice. On the Near repeats tab, tick the columns that identify a record and BigXL compares the rows after tidying capitals, spacing, punctuation and word order. You can also switch on close spelling to catch typos such as Smyth for Smith. Numbers in the text must agree, so Unit 4 and Unit 5 are never merged.

The matches appear as groups to check, with the first row of each group staying. Groups found by close spelling start unticked until you have looked at them. Removing is one step in the Timeline. Near repeats work on up to 500,000 rows and 500 groups at a time; if nearly every row looks alike, BigXL asks you to choose more specific columns. You can also ask the assistant: “remove near duplicate rows”.

Merging similar spellings

The same company, city or person is often typed several ways: Acme Inc, ACME, Inc. and acme inc. Totals and pivots then count them as three different things. Open Merge similar spellings from the Clean menu, or press Cmd+K and type “similar”.

Pick a column and BigXL groups the values that differ only in capitals, spacing, punctuation or word order. It also finds close spellings such as Blue Harbour Ltd and Blue Harbor Ltd. Close spellings start unticked, because two values that look alike can still be two different things.

In each group the most common spelling is kept. Click any other spelling to keep that one instead. The message at the top says how many cells will change before you press Merge. Values that hold different numbers, such as Invoice 1001 and Invoice 1002, are never grouped.

The merge is one step in the Timeline, and it is saved in recipes, so next month’s file can be fixed the same way.

Group and total rows

Sometimes you need a summary table rather than the raw rows: how many invoices per vendor, or the total spent per department. Open Group and total rows from the Clean menu, or press Cmd+K and type “group”.

Tick the columns that define a group, then add as many totals as you like: sum, average, lowest, highest, median, or how many different values. The result always has a rows column that counts the members of each group. A preview of the first rows and the size of the result appear before anything is made.

The result opens in a new tab and the file you started from does not change. A column that is masked cannot be grouped or added up, because masking leaves too little of each value to tell people apart. Unmask it first, or pick another column.

Turn columns into rows

Reports often put one period in each column: Jan, Feb, Mar. Pivot tables, charts and most databases want one row per period instead. Open Turn columns into rows from the Clean menu, or type “columns into rows” in the command palette.

Tick the columns to keep as they are (such as a name or an ID), then the columns to turn into rows. Choose names for the two new columns: one holds the old column titles, the other holds the values. Empty cells are left out unless you untick that option. Rows stay in file order, and within each original row the columns keep their order.

Like grouping, this opens a new tab and leaves your file untouched.

Deleting rows

Select one or more rows, right-click, and choose Delete this row or Delete these N rows. You can also use Edit > Delete selected rows.

Money and ambiguous separators

Money arrives in formats a plain number cast rejects. BigXL reads the ones that have a single correct interpretation and refuses the ones that do not.

Read as numbers:

• Currency symbols and spaces, so $1,234.56 and £90 become 1234.56 and 90.
• Thousands separators in proper groups of three, so 1,000 becomes 1000.
• Accounting brackets, which mean a negative amount, so (200) becomes -200 and ($1,500.00) becomes -1500.00.

Refused, and handed back to you:

• 1.234,56 is one thousand two hundred thirty four in much of Europe and one point two three elsewhere. Nothing in the value says which.
• 1,23 and 1,2345 have commas that are not thousands separators.

These appear in the bad values panel with everything else that could not be read, where you decide what they mean. BigXL applies the same rule to money that it applies to ambiguous dates: where a value has two credible readings, it asks rather than picks. A tool that silently turns 1.234,56 into 1.23456 is inventing your numbers.

Combining debit and credit columns

Bank and ledger exports often split one amount over two columns, where Debit holds money out, Credit holds money in, and each row fills only one of them. In that shape the amount cannot be totalled, sorted or charted, because it is not a number in a column.

Choose Combine debit and credit into one amount from the Clean menu. This is a Pro feature. Pick which column is money out and which is money in, and name the result. BigXL shows the first rows before you commit so you can check the signs.

The new column is money in minus money out, so money leaving the account reads as a negative number and a row that is empty on both sides reads as zero. Money formats are read on the way through, so $1,200.00 and (50) both work without a separate cleaning step.

Tick the box to remove the two original columns afterwards. The whole operation is three steps in the Timeline, so it reverses cleanly.

Where problem values show up

BigXL also flags values that do not fit their column before you go looking: a notice strip, a quality bar under each column name, and a panel to fix what it found. See Spotting and fixing problem values.

Every step previews first

Nothing in this list changes a value until you confirm it. Every cleaning action shows what it would do before it runs. Your source file is never touched. You can step back from any change using the Timeline.