User guide

Spotting and fixing problem values

See at a glance how clean each column is, and fix the values that do not fit, with a preview before anything changes.

BigXL looks for values that do not fit the rest of their column: text in a number column, a malformed email, an address that is not an address, a date that cannot be read. You see them in three places.

The notice strip

When a file opens, BigXL checks every column. If it finds values that need fixing, a strip appears below the toolbar, for example 9 values need fixing, with a See them and fix button. The count follows your work: after a step that fixes values, the number goes down, and an edit that breaks one brings it back. The same count shows in the status bar.

If a filter is hiding the problem rows, the fix panel says so (“4 problem values in salary are hidden by a filter”) instead of looking empty. Remove the filter to see them.

Column quality bars

Turn on View > Column quality bars to draw a thin bar under every column name:

• the fine part is values that fit,
• the blank part is empty cells, and
• the needs fixing part is values that do not fit.

Hover a header for the exact figures, for example “96.4% fine, 3.6% blank”. The bars are to scale, counted over every row, and recount when the sheet changes, so cleaning a column visibly clears it. They cover the first 60 columns, and masked columns get no bar. The red count is the same number the fix panel lists for that column.

Click a bar:

• with problems, it opens the fix panel for that column (the fix panel is part of the cleaning tools in Pro);
• with only blanks, it filters to the blank rows;
• on a column with nothing to fix, it does nothing.

The fix panel

Choose Clean > Convert a column to numbers or click See them and fix. The panel groups what it found by kind, such as currency formatting, placeholder text like N/A, stray symbols and blanks, with a tab per column that has problems. Pick a group, or tick individual values, then choose a fix:

• Read as money turns $70,000 or (1,200) into a number.
• Make blank or Make zero replaces the value. For a number column, money is read as a number first, so $70,000 is never blanked.
• Delete rows removes the whole row.
• Replace with puts a value you type into every one of them.
• For IP addresses and emails, Clean IP and Normalize email repair what can be repaired.

Each option shows an example of what it will do, and you see exactly what changes before it is applied. Every fix is a step in the Timeline.

Money counts as a number

Text such as $70,000, 1,200.50 and (1,200) is treated as a number everywhere: sorting, filters, totals, summaries, charts, pivots and group sums, and it is not counted as a problem. Only proper thousands groups count, so 1,200 is a number while 1,5 is not, and a decimal comma such as 1.234,56 is refused as ambiguous rather than guessed. An Excel export writes a column of money text as real numbers; one unreadable value keeps the whole column as text.