Excel for operations

Total a month of sales with SUM

Add up a column of daily sales figures to get the monthly total.

SUM

Count how many orders shipped with COUNT

Use COUNT to find how many rows contain a numeric value.

COUNT

Check code length with LEN

Use LEN to validate that product codes are the right length.

LEN

Find the second-lowest quote with SMALL

Pick out the second-cheapest supplier quote rather than simply the cheapest.

SMALL

Build a product code with CONCAT

Join a category prefix and an item number into a single SKU.

CONCAT

Convert loose units into full cases

Divide a unit count by the case size to see how many cases were ordered.

Count the headcount in one department

Count how many people sit in a single department on a staff list.

COUNTIF

Work out remaining leave days

Subtract leave taken from entitlement to see what each person has left.

Edit the cell you are standing on

Open a cell for editing without reaching for the mouse or the formula bar.

Fill a formula down a column

Copy the cell above into the cells you have selected, without dragging.

Type today's date without typing it

Stamp the current date into a cell as a fixed value.

Count orders above a threshold with COUNTIF

Count how many rows meet a numeric condition.

COUNTIF

Build a mailing address with TEXTJOIN

Combine separate address fields into one line, skipping blanks.

TEXTJOIN

Flag orders needing special handling with OR

Mark an order when either of two independent conditions is true.

OR · IF

Assemble a delivery date with DATE

Turn separate year, month and day columns into a real date Excel understands.

DATE

Find where a word starts with SEARCH

Locate a keyword inside a longer description without worrying about capitals.

SEARCH

What does the dollar sign actually do?

Pin down what $ changes about a reference, and what it does not.

What is Excel telling you with #N/A?

Read an error code as a diagnosis rather than a failure.

Select to the bottom of the data

Extend a selection to the last filled row in one press.

Count orders meeting two conditions

Count rows that satisfy several criteria at once.

COUNTIFS

Approve orders with AND

Require two conditions to be true at once.

IF · AND

Pull the year out of an order code with MID

Extract characters from the middle of a text value.

MID

Standardise messy codes with TRIM and UPPER

Clean inconsistent spacing and casing so values can be matched.

Calculate invoice age with TODAY

Work out how many days old each invoice is.

TODAY

Measure the spread, ignoring the extremes

Compare the second-best and second-worst weeks to get a fairer range.

LARGE · SMALL

Split a city away from its state

Cut a combined location field at the comma without counting characters by hand.

LEFT · FIND

Count backorders on three conditions

Narrow a count with three criteria at once rather than filtering by hand.

COUNTIFS

Skip blanks when joining an address

Join address parts into one line without leaving gaps where a field is empty.

TEXTJOIN

Work out fill rate without a divide error

Guard a division so cancelled lines do not fill the report with errors.

IF

Count orders inside a value band

Apply an upper and a lower bound to the same column at once.

COUNTIFS

Total overtime hours for one team

Add up overtime for a single team out of a mixed timesheet.

SUMIF

Count staff overdue a review

Count people in one department whose last review was long enough ago.

COUNTIFS

Flag accounts for review with AND and OR

Combine two logical tests so one condition can override the other.

AND · OR · IF

Look up a shipping band with VLOOKUP

Match a weight to a rate band, where the weight will never appear in the table.

VLOOKUP

Spot duplicate order numbers with COUNTIF

Find rows whose order number appears more than once in the column.

COUNTIF · IF

Turn a product code into a category

Read a code's prefix and translate it into a readable category name.

IFS · LEFT

Why does a lookup fail on clean data?

Diagnose a match failure where both values look identical on screen.

XLOOKUP

Reconcile a list with hidden spaces

Match two lists where one side has trailing spaces you cannot see.

COUNTIF · IF

Extract a lot number from a messy code

Pull a fixed-length value out of the middle of a code that varies in shape.

MID · FIND

Name the third-fastest supplier

Return a name from one column based on a ranked value in another.

INDEX · MATCH · SMALL