Tracking blood work over time in a spreadsheet: columns, dates and units
A blood work spreadsheet keeps every result with its marker, collection date, value, unit and the range printed on its own report. Two layouts work: long, with one row per result, and wide, with one row per marker and one column per date. Long suits filters and pivot tables; wide suits reading a marker across time. Choose one as the master.
Not medical advice. This guide describes what the parts of a lab report are and how they are recorded. It does not say what any result means for a person, and it does not recommend treatment, supplements or targets.
Long and wide layouts, and the columns to keep
In a long sheet every row is one result. It grows downward, takes any number of markers and dates, and works with filters and pivot tables. In a wide sheet every row is a marker and every column a date, so one marker's history reads left to right. That is easier to scan and chart, but one cell per marker and date leaves no room for the range or for a unit that changed.
Whichever you choose, keep what the report printed in its own columns and add your own fields beside them, never in place of them. A workable set of columns is:
- date: the collection date, as YYYY-MM-DD.
- marker: your standard name, with the name as printed beside it.
- value: the result as printed, with any < or > sign.
- unit: the unit as printed.
- range: the range text as printed, with low and high split out for a simple interval.
- lab flag: the mark the lab printed, if any.
- source: the report file or order number, plus a notes column for fasting status and specimen remarks.
Setting up the sheet
Six steps, in this order.
Pick a master layout
Choose long if you will filter, pivot or import the data elsewhere, and wide if you mostly read it. Keep the other as a view built from the master, never as a second place to type.
Fix the columns first
Settle the column set above and freeze the header row before the first entry. Adding a column later means going back through every old row.
Write dates one way
Use YYYY-MM-DD for the collection date. A date typed as 03/04/26 is read as March or April depending on the computer's region settings.
Settle units per marker
Record the unit printed on each report. Where two differ, convert only with an exact factor (see the units guide) and keep the printed value too.
Enter one report at a time
Copy every row of a report before starting the next, and log each by collection date and file name. Half-entered reports cause most gaps.
Check before relying on it
Sort by marker and date, look down each marker's unit column for a jump, and compare a few cells with the original. Repeat after any bulk paste.
Long and wide layouts compared
Pick by how you will use the sheet.
| Question | Long layout | Wide layout |
|---|---|---|
| Shape | One row per result | One row per marker, one column per date |
| Best for | Filters, pivot tables, importing elsewhere | Reading one marker across time, quick charts |
| Adding a report | Append rows at the bottom | Add a date column, then fill each marker's row |
| Printed range | A range column on every row | A second block or a note, because a cell holds one value |
| A unit that changes | A unit column on every row | One unit column per marker, so a change needs a note |
| Results such as <0.5 | Text in the value column | Text in the cell, which stops that marker being charted |
| More than one person | A person column on every row | One sheet per person |
What goes wrong
Most problems come from detaching a value from what the report printed with it.
- Mixed date styles. 03/04/26 can be March or April. Mixed styles in one column sort wrongly and cannot be repaired without the original.
- Dropping the unit. A bare number cannot be compared with a later report in another unit.
- Overwriting a range. Replacing an old range with a newer one judges the old result against a limit that was not printed for it.
- Renaming a marker midway. LDL, LDL-C and LDL Chol Calc become three rows, and one history splits into three.
- Using the report date. It can fall days after the draw. The collection date is the one that places a result in time.
- Mixing people. A shared wide sheet makes it easy to put a value on the wrong row. Use a person column in a long sheet, or one sheet per person.
How BioPanel helps
BioPanel builds both layouts from your reports. A lab report PDF or photo becomes one row per result in eleven fixed columns, and the reports you add are merged in your browser into a wide CSV, with markers down the side and dates across, or a long CSV with one row per marker and date. The wide layout matches the free blood test tracker template. Free: 1 file per day, up to 10 pages, no account. Files over 10 pages are converted up to page 10 on the free allowance; a page code converts the whole file, up to 150 pages. See pricing.
Questions people ask
Should I use a long or a wide layout?
Use long if you will filter, pivot or move the data elsewhere, and wide if you mostly read each marker across time. Many people keep long as the master and build a wide view from it.
Which date goes in each column?
The collection date, written YYYY-MM-DD. A report date can fall days after the draw. If a report prints only one date, record it and note which kind it is.
How do I enter a result like <0.5 or Not detected?
Type it as printed in a text column, and add a numeric column only if you plan to chart that marker. Keep the original wording so the sign is never lost.
What if my lab changes units between reports?
Record the unit printed on each report. Convert only where an exact factor exists, and keep the printed value and unit beside the converted one.
Do I need to store the reference range?
It helps. Each report prints its own range, so keep it with its value. Otherwise an old result ends up beside a range that was not printed for it.
Does a spreadsheet tell me what my results mean?
No. It keeps numbers in order and shows nothing about their meaning for you. A clinician who knows your history is the person to ask. This is not medical advice.