Excel Skills for Procurement and Purchasing Jobs

The Excel skills for procurement come down to a handful of tasks: find the agreed price for an item, total how much you spend with each supplier, compare quotes, and spot when prices move. You don't need macros for that. You need lookups, SUMIF, MINIFS and a few percentage formulas on a tidy purchase order log. This guide is for buyers, purchasing assistants and job seekers preparing for a procurement Excel test.

What Excel skills does procurement need?#

Procurement (also called purchasing) is the team that chooses suppliers, agrees prices and raises purchase orders (POs). This table maps the daily work to Excel tools:

Many of these overlap with nearby roles. Compare Excel skills for supply chain and logistics and Excel skills for accounts payable, which handles the invoices that procurement's orders turn into.

The examples below use this PO log in A1:F7:

The Total column is =D2*E2, filled down.

Look up agreed prices with VLOOKUP or XLOOKUP#

A lookup pulls a value from a reference table, such as the contract price for an item. Say a price list sits in H2:I4, with items in H and agreed prices in I. To find the agreed price for the item in C2:

=VLOOKUP(C2,$H$2:$I$4,2,FALSE)

• C2 is the item to find.
• $H$2:$I$4 is the price list, locked with $ so it doesn't move when you fill down.
• 2 returns the second column of the list (the price).
• FALSE asks for an exact match. Leave it out and VLOOKUP may return a wrong price without warning.

If you have Excel 2021 or Microsoft 365, XLOOKUP does the same job and can look to the left: =XLOOKUP(C2,$H$2:$H$4,$I$2:$I$4,"Not on list"). The VLOOKUP function guide covers the arguments, and the VLOOKUP lesson lets you practise them.

Total spend by supplier with SUMIF#

Spend analysis means adding up how much you buy from each supplier or in each category. SUMIF adds the numbers that match one condition:

=SUMIF(B2:B7,"Apex Supply",F2:F7)

The result is 572.00 (168 + 248 + 156). Brightline comes to 469.50 and Crestway to 202.50, for a total spend of 1,244.00. To total by item instead, check the Item column: =SUMIF(C2:C7,"Paper A4",F2:F7) gives 489.00.

For two conditions, such as Apex Supply and Toner, use SUMIFS: =SUMIFS(F2:F7,B2:B7,"Apex Supply",C2:C7,"Toner") returns 248.00. The SUMIF function guide has the full syntax.

Spend share shows how much of the total goes to one supplier. Divide the supplier's spend by the total and format as a percentage: =SUMIF(B2:B7,"Apex Supply",F2:F7)/SUM(F2:F7) returns about 46%. A high share can mean a strong negotiating position, or a risky dependence on one supplier.

Compare supplier quotes with MINIFS, MAXIFS and AVERAGEIF#

When several suppliers quote for the same item, you want the lowest, the highest and the typical price. These functions take a condition, like SUMIF, but return a minimum, maximum or average. Using the PO log:

Watch the argument order. MINIFS and MAXIFS put the range to measure first, while AVERAGEIF puts it last, like SUMIF. MINIFS and MAXIFS need Excel 2019 or later, including Microsoft 365.

To name the cheapest supplier, combine the result with a lookup. In Excel 2021 or Microsoft 365, =XLOOKUP(3.95,E2:E7,B2:B7) returns Brightline. In a real sheet, replace 3.95 with the MINIFS cell. Practise these in the AVERAGEIF, MAXIFS and MINIFS lesson.

Track price variance and price changes#

Purchase price variance (PPV) is the gap between what you paid and what you planned to pay. If the standard price for Toner is 60.00 and Apex charged 62.00:

=(E4-60)/60

The result is 0.0333, which shows as 3.3% when you format it as a percentage with one decimal place. The percentage shortcut is Ctrl+Shift+% on both Windows and Mac. A positive variance means you paid more than planned. In a real sheet, keep the standard price in its own column and point the formula at it.

The same formula measures price changes over time: =(New-Old)/Old. Add a conditional formatting rule (Home > Conditional Formatting > Highlight Cells Rules > Greater Than) to colour any variance above, say, 5%, so price rises stand out in a long list.

Track late deliveries and open orders#

Procurement also chases orders that arrive late. With the promised date in one column and the received date in the next, =Received-Promised gives the days late, and a positive number means late. COUNTIFS counts open orders for one supplier, such as =COUNTIFS(B2:B7,"Brightline",G2:G7,"Open") if column G holds a status. The operations analyst Excel test guide shows how these tasks are often asked in assessments.

Pivot tables tie it all together. Select the PO log, choose Insert > PivotTable, put Supplier in Rows and Total in Values, and you have spend by supplier in seconds. They are the fastest way to answer "who do we spend the most with?" in a meeting.

How to prepare for a procurement Excel test#

Most procurement and purchasing Excel tests use a small order or quote table and ask you to look up prices, total spend, compare quotes and calculate a percentage. Practise doing each task without searching for the syntax:

1. Write a VLOOKUP with FALSE and an XLOOKUP from memory.
2. Total spend by supplier with SUMIF, then with two conditions using SUMIFS.
3. Find the lowest and highest quote with MINIFS and MAXIFS.
4. Calculate a percentage change and format it.
5. Build a pivot table of spend by supplier.

Timed practice helps most. Try the operations case study test for order and supplier tasks, or take the Excel level test to see where you stand. Retail buying roles use similar skills, as shown in Excel skills for retail jobs.

Practise it#

Start with the exercise on this page: one MINIFS formula finds the cheapest Toner quote. Then practise SUMIF in a live spreadsheet to total spend by supplier. You type each formula and it checks it instantly, so you build the speed a procurement Excel test needs.