How to color rows by a rule in a large CSV, without changing the file
To color rows by a rule in a large CSV, pick the column, set the condition (for example amount over 4,000) and choose a color and whether to paint the cell or the whole row. In BigXL right-click a cell and choose Colour by this column, or just type "colour the rows where amount is over 4000 in red". The file is not changed, every color is a step you can undo, and an Excel export keeps the colours as cell fills.
You have a 3 million row export and one question: which rows are over the limit? A filter shows you only those rows, which is useful, but it hides everything around them. Sometimes you want the whole sheet visible with the interesting rows picked out, the way a manager marks up a printout.
That is what colour rules are for.
Why Excel’s conditional formatting is not enough here
Conditional formatting is a good feature, but it needs the data inside a workbook. A CSV that big does not fit, because a worksheet stops at 1,048,576 rows. And a plain CSV file has nowhere to store a colour at all.
So the colour has to live in the tool you view the file with, not in the file. That is also the safer design: the data you send on is exactly the data you received.
Colour a column in BigXL
Right-click any cell in the column you care about and choose Colour by this column.
- Pick the rule. For a number: over, under or equal to a value. For text: contains or equals. For example: amount is over 4000.
- Pick the colour. There are ten, chosen to stay readable with dark text.
- Pick what to colour. Just the cell, or the whole row.
- Apply. It works on the full file, not just the rows on screen.
You can also say it: “colour the rows where amount is over 4000 in red”. Bix, the assistant, understands any of the ten colours, so “make the North rows blue” works too.
Several rules, in order
Add as many rules as you like, even on the same column:
- Red where amount is over 10,000
- Amber where it is over 5,000
- Green where status equals
paid
Where two rules touch the same cell, a colour on the cell itself wins over a row colour, and the most recent rule wins over an older one. Each colour appears as a chip above the sheet, matching its colour, so you can always see what is applied.
Nothing is changed, and everything can be undone
A colour is a step on the timeline, like a sort or a filter. Undo removes it. Click the chip to remove just that rule. Remove all colours in the palette clears them all, and so does asking “remove the colours”.
Your data is not edited at any point. The file you opened is the file you still have.
Keeping the colours: export to Excel
When you export the sheet as an Excel file, the colours go with it as real cell fills, and the cell’s own colour sits on top of its row’s. Send it to a colleague and they see what you saw, with numbers still stored as numbers and dates as dates.
Formats like CSV, TSV, Parquet and JSON hold data only, so they do not carry colour. Use Excel when you want the marked-up version to travel.
A quick example
A finance team exports a year of payments, 4 million rows. They colour overdue invoices red, anything disputed amber, and anything over the approval limit across the whole row. They scroll the sheet with the context intact, then export the Excel copy for the reviewer.
Colouring is part of the free version, and like everything else in BigXL it runs on your own computer, with nothing uploaded.
To pick out just the matching rows instead, see the guide on filtering and sorting.
Common questions
Can I use conditional formatting on a CSV?
A CSV file has no formatting of its own, and Excel's conditional formatting only works once the file is open as a workbook, which stops at 1,048,576 rows. In BigXL you can apply colour rules to a file of any size, and they work on the file as it is.
Does coloring change my data?
No. The colours sit on top of the sheet. Nothing in the file is edited, and each colour is a step on the timeline that you can undo or remove.
Can I use more than one colour?
Yes. There are ten colours, and you can add several rules to the same column, for example red for amounts over 10,000 and amber for amounts over 5,000.
Do the colours survive when I export?
In an Excel export, yes. They become real cell fills. A CSV, TSV, Parquet or JSON file cannot hold colour, so those exports carry the data only.
How do I take a colour off?
Click the colour chip above the sheet to remove that one rule, use Remove all colours from the palette, or ask: "remove the colours".