Excel for quantitative analysis in pharmaceutical science

A workbook is the file. Inside it are worksheets — the tabs along the bottom. Each worksheet is a grid of cells, addressed by column letter and row number: B7 is column B, row 7. A rectangular block of cells is a range, written with a colon: B7:B26 is twenty cells in one column.

Three parts of the window do most of the work, and most Excel confusion comes from ignoring them.

• Name Box — top left. Tells you which cell is selected. You can also type an address into it and press Enter to jump there.
• Formula Bar — the wide box beside it. Shows the cell's underlying content. If the cell shows 93.3 but the Formula Bar shows =AVERAGE(B7:B18), the cell holds a formula. If the Formula Bar shows 93.34166666, the cell holds a number that is merely being displayed rounded.
• Status Bar — along the bottom. Select several numeric cells and it shows their count, sum and average instantly. It is the fastest sanity check in Excel, and it costs nothing.

Moving around without losing your place

Every cell holds one of four kinds of thing. Excel decides which as you type, and its decision is not always the one you wanted.

Alignment is your free diagnostic. If a column of numbers is hugging the left edge, Excel is treating it as text. That happens constantly with data pasted out of an instrument report, a PDF or a supplier's certificate of analysis.

Repairing text that should be numbers

1. Remove the unit from the cell — units belong in the column heading, not in every value.
2. If the whole column is affected, use Data > Text to Columns > Finish. It re-parses each cell and usually converts everything in one step.
3. For stray spaces, =VALUE(TRIM(B7)) in a helper column, then paste the result back as values. A non-breaking space pasted from a web page is character 160, which TRIM does not touch — use =VALUE(TRIM(SUBSTITUTE(B7,CHAR(160)," "))) instead.
4. Re-run =COUNT() to confirm the repair actually worked.

The other direction: things that should stay text

Batch numbers, material codes and sample IDs are labels, not quantities. If a code like 0731 loses its leading zero, format the column as Text before pasting, or use a custom number format such as 0000 to display the zero while keeping the underlying number intact. Never let Excel turn RM-1042 into a date — it will try.

Most of the pain people have with Excel is not caused by Excel. It is caused by a layout designed to look like a printed table rather than to be analysed. The fix is the same one statisticians have been asking for since long before spreadsheets: tidy data.

Long or wide?

Two batches of dissolution data can be laid out either way, and the choice matters.

If your data will end up in a statistics package, collect it long. If it is staying in Excel for a small comparison, wide is fine. Deciding before you start collecting saves an afternoon later.

Tables, freezing and filtering

• Freeze Panes (View > Freeze Panes) keeps your headings on screen. Click the cell below and to the right of what you want to keep visible, then freeze.
• Format as Table (Home > Format as Table, or Ctrl+T on both platforms — on a Mac ⌘+T is the dollar-sign toggle instead) gives the range a name, adds filter buttons, and — the real benefit — makes formulas grow automatically when you add a row. Give the table a sensible name in Table Design.
• Sort by selecting one cell inside the block and using Data > Sort. Never select a single column and sort it alone: you will detach it from its row and the damage is invisible.

This is the single most useful idea in the whole unit. A cell has a value and a format, and they are independent. A cell can store 0.13425 and display 0.13. Every later calculation still uses 0.13425.

Try setting the value to 0.0735 with a percentage format: Excel shows 7.4%, because a percentage format multiplies the display by 100 and adds a sign. The cell still contains 0.0735. This is why =STDEV.S(...)/AVERAGE(...) formatted as a percentage gives you %RSD directly — and why multiplying by 100 as well gives you a number a hundred times too big.

The formats worth knowing

When you genuinely need to round the value

Use ROUND(number, digits) when a rounded number must feed into something else — a reported result, a dose calculation, a value going onto a certificate. Keep the raw measurement in its own column and round in a new one; never overwrite what you measured.

A formula always starts with =. Everything after it is an expression built from values, cell references, operators and functions. Excel evaluates it and puts the answer in the cell, keeping the formula itself in the Formula Bar.

Operators, and the order they run in

Reading an error instead of panicking at it

Excel's error values are diagnostic, not decorative. Each one names a different kind of fault.

Finding the cause

• Formulas > Trace Precedents draws arrows to the cells a formula depends on. Trace Dependents does the reverse. Remove Arrows clears them.
• Formulas > Evaluate Formula steps through a long formula one operation at a time and shows you the intermediate result. It is the best debugging tool in Excel and almost nobody uses it.
• Formulas > Show Formulas (Ctrl+` on Windows and Mac alike) displays every formula instead of its result — the fastest way to spot one cell that was overtyped with a constant.

When you copy a formula, Excel adjusts its references by however far the formula moved. That is usually exactly what you want — copy =B7*C7 down a row and it becomes =B8*C8, which is the point. It stops being what you want the moment a formula needs to keep pointing at one cell: a target weight, a dilution factor, a VAT rate, a response factor.

To add or remove the dollar signs, click inside the reference in the Formula Bar and press F4 on Windows or ⌘+T on a Mac. Each press cycles through the four combinations. You can also just type them.

Named ranges — the readable alternative

Select a cell, type a name into the Name Box, press Enter. Now =B7/Target_weight works anywhere on the sheet and is absolute automatically. Formulas > Name Manager lets you edit them.

Names make a spreadsheet far easier for someone else to check, which matters when that someone else is your supervisor or an examiner. Keep them short, avoid spaces, and name the thing rather than its location: Dilution_factor, not Cell_C5.

A function takes arguments in brackets, separated by commas, and returns one answer. Start typing =AV and Excel offers the matching functions; press Tab to accept one and it shows you the arguments it expects as you go.

Summarising a range

Counting and averaging with conditions

These are the workhorses of batch data. A comparison criterion goes in quotation marks; a plain value does not need them.

To compare against a limit held in a cell rather than typed into the formula — which is what Section 13 argues you should always do — join the operator to the reference with &:

Note the argument order differs between them: COUNTIFS takes range/criterion pairs, while SUMIFS and AVERAGEIFS put the range being summed or averaged first. SUMIF and AVERAGEIF, confusingly, put it last. Let Excel's argument tooltip guide you rather than trusting memory.

Making a spreadsheet decide something

IF takes a test, an answer when it is true, and an answer when it is false. AND requires every condition; OR requires at least one. IFS works through conditions in order and stops at the first true one — the final TRUE acts as "otherwise". It is much easier to read than four nested IFs, and much easier to correct.

Lookups answer two different questions, and knowing which one you are asking prevents most lookup disasters.

• Exact match — "what is the supplier for material RM-1042?"
• Approximate match — "a Carr's index of 15.9% falls into which flow band?"

XLOOKUP

Only the first three are required. The rest are where the real value is.

Exact match — pulling a record across

Lock the lookup and return ranges with $ so the formula can be filled down without the reference table sliding out from under it. That single omission is the most common lookup fault there is.

Approximate match — banding a value

Build a table of lower limits, sorted ascending, beside the labels. Then match_mode = -1 finds the largest limit that does not exceed your value.

A blend with a Carr's index of 15.86% returns Good, not Fair — because 15.86 has not reached 16. Round it to one decimal place first and you would get 15.9, still Good. Round it to a whole number and you get 16, and the wrong answer. This is the stored-value-versus-display idea from Section 4 doing real damage.

A dissolution test gives you twelve results, not one. Two batches can share a mean of 93% and behave completely differently: one tightly clustered, the other with three vessels failing and three over-performing. The mean hides exactly the thing a regulator, a supervisor and a patient care about.

The core set

SD or SEM? They answer different questions

SD describes how much the individual results vary. SEM describes how precisely you have located the mean, and it shrinks as you collect more data — SEM = SD ÷ √n. Neither is more correct; they are answers to different questions.

• Describing how variable a batch is → SD.
• Describing how confident you are about a mean → SEM or, better, a confidence interval.
• Plotting error bars → whichever you like, but state which in the caption. An unlabelled error bar is meaningless, and SEM bars are roughly a third the length of SD bars at n = 12, which makes an unlabelled chart quietly misleading.

Confidence intervals

A 95% confidence interval is the mean plus and minus a margin. Use CONFIDENCE.T, which uses the t distribution — correct when you estimated the SD from a small sample, which you always have. CONFIDENCE.NORM assumes you already know the population SD, which you never do.

Report it as 93.3 ± 1.2% (mean ± 95% CI, n = 12). Loosely: if you repeated the whole study many times, about 95% of the intervals you calculated that way would contain the true mean.

Two batches give mean assays of 99.9% and 97.1%. Is that a real difference, or the sort of wobble you would expect from ten determinations of identical material? A t-test answers exactly that question and nothing beyond it.

Running it in Excel

Choosing between type 2 and type 3

=F.TEST(array1,array2) compares the two variances and returns its own p-value. A large value means the spreads are similar; a small one means they are not.

Many textbooks tell you to use that result to pick between type 2 and type 3, and you will see it done that way constantly. It is worth knowing that statisticians increasingly advise against it: the F-test is very sensitive to non-normality, and choosing your test on the basis of the data distorts the error rate of the test that follows. The simpler modern advice is to use Welch's test (type 3) by default — it costs almost nothing when the variances really are equal, and protects you when they are not. Use F.TEST to describe the spreads, not to decide for you.

Significant is not the same as important

In the workshop you will compare two batches whose means differ by 2.8% of label claim. With n = 10 and tight data, p comes out at around two in a million — overwhelming evidence that the difference is real. Both batches sit comfortably inside a 95–105% specification.

So the difference is real, and it does not justify rejecting anything. It might well justify a look at the process: a real, reproducible drift is worth understanding before it becomes large enough to matter. The statistics tell you the difference exists; pharmaceutical judgement tells you what to do about it.

Collect enough data and almost any difference becomes statistically significant. That is why you report the size of the difference and a confidence interval for it, not a p-value alone.

A calibration line is the bridge between what an instrument measures and what you want to report. Fit it carelessly and every downstream number inherits the fault — the assay, the release decision, the conclusion in your dissertation.

Fitting the line

Excel's regression functions take the y range first, then the x range. Getting them the wrong way round produces a perfectly plausible number that is completely wrong, so check it every time. RSQ is the exception — it gives the same answer either way, which is precisely why it will never warn you that the others are reversed.

Together these give y = mx + c. Build the equation as a live formula rather than typing the numbers in, so it updates if a standard is corrected:

Back-calculating a sample

Rearranging y = mx + c gives x = (y − c) ÷ m. Do it in visible stages rather than one heroic formula, so each step can be checked:

Is the line good enough?

• R² tells you how much of the variation in y the line accounts for. For a routine calibration you would expect 0.999 or better. It is necessary, not sufficient.
• STEYX tells you, in real units, how far a typical point sits from the line. Unlike R² it does not improve just because you widened the concentration range.
• The residuals. Calculate observed − fitted for each standard and plot them against concentration. Randomly scattered around zero is what you want. A curve, a funnel, or one point far out tells you something R² cannot: that a straight line is the wrong model, that variance grows with concentration, or that one standard was misprepared.
• The intercept. A large one relative to your lowest standard suggests baseline drift, a co-eluting peak, or a blank that is not blank.

Limits of detection and quantitation

The ICH Q2 approach based on the calibration line uses the residual standard deviation as an estimate of the noise:

Other approaches use the standard deviation of the intercept, or replicate measurements of a blank, and will give you different numbers. Whichever you use, say which — an LOD quoted without its method is not a result, it is a rumour.

Dr Parsons' session looks at what makes a data graphic effective or misleading. This section is the mechanics: how to build the chart you have decided on, and how to judge your own before anyone else does.

Scatter or line? The distinction that matters most

This is the mistake that appears most often in dissertations, and it is invisible until someone points it out.

If your x values are numbers with real distances between them, use a scatter chart. Excel's Line chart treats them as labels no matter how numeric they look — with one exception: genuine Excel dates get a date-scaled axis, which is spaced proportionally. Elapsed minutes and concentrations are not dates, so they do not get that treatment.

Building an XY scatter properly

1. Select the x column and the y column together. Insert > Chart > Scatter, markers only.
2. Add a second series for a fitted line: right-click the chart > Select Data > Add, with the same x values and your calculated fitted-y column. Format it as a line with no markers.
3. Chart Design > Add Chart Element > Axis Titles. Put the unit in brackets: Concentration (µg/mL).
4. Give the chart a title that says what the reader is looking at, not "Chart 1".
5. Delete the legend if there is only one series. It is telling the reader nothing they cannot see.

Error bars that mean something

1. Click the series, then Chart Design > Add Chart Element > Error Bars > More Options.
2. Choose Custom > Specify Value, and point both the positive and negative boxes at your SD column. Never use the "Standard Deviation" preset — it calculates one value from the plotted series rather than using your per-point SDs.
3. Say in the caption what the bars are: mean ± 1 SD, n = 12. SD, SEM and 95% CI bars look identical and can differ by more than threefold: at n = 12 with an SD of 1.91, the SEM is 0.55 and the 95% CI half-width is 1.21.

Judging your own chart

At some point your supervisor, an examiner or a colleague will open your workbook and try to follow what you did. Whether that takes them five minutes or leaves them unable to reproduce your result is decided by choices you make now, not later.

Checking before you trust

• COUNT everything. If you should have 12 results, confirm you have 12 numbers.
• Estimate the answer first. If the mean of numbers around 93 comes out as 9.3 or 930, you have a decimal or a range problem. This one habit catches more errors than any other.
• Recalculate one row by hand. One. It validates the whole column.
• Show Formulas (Ctrl+` on both platforms) and scan the column. A cell someone overtyped with a constant stands out immediately — and a hard-coded value hiding in a column of formulas is the most expensive bug in spreadsheets.
• Check the extremes. Sort by your result column and look at the top and bottom few. Impossible values surface instantly.

Moving results into a report

• Charts into Word: copy the chart, then Paste Special > Picture. A pasted picture cannot silently change when you edit the workbook. Add alternative text and a numbered caption.
• Tables into Word: paste the values, not a live link, and round at the point of reporting rather than in the source data.
• Always state: n, what any error bars represent, which statistical test you used and its p-value, and what software produced the analysis. Every one of those is a question a marker will ask if you leave it out.