Excel Skills for Sales and Operations Roles
By ExcelReady TeamUpdated 6 min read
The short answer
The key Excel skills for sales and operations roles are totalling revenue by region, rep or product with SUMIF and SUMIFS, calculating percent change and % of total, finding the top order and its rep with MAX and XLOOKUP, totalling by month with date criteria, and flagging stock or targets with IF. Answers should update when data changes.
Key takeaways
- Most sales questions are totals by group, so SUMIF and SUMIFS do most of the work.
- Percent change is (new - old) / old: always divide by the old value.
- A % of total column needs the total locked with $, like =D2/$D$9, before you fill it down.
- Total by month with two date conditions, not by sorting and adding rows by hand.
On this page+
The Excel skills for sales and operations roles are the ones that answer a manager's questions fast: revenue by region or rep with SUMIF, growth with percent change, each order's share of the total, the top deal and who made it, and totals by month. This guide is for people applying to sales, sales operations, account management, inventory or operations coordinator roles who want to practise the tasks those teams use every week.
What sales and operations teams use Excel for#
Sales and operations work runs on lists of transactions: orders, deals, shipments and stock counts. Common reports built from them:
- Revenue by region, rep, product or month.
- Growth versus last year or last quarter.
- Each rep's attainment against target.
- Top deals, top customers and top products.
- Stock levels, reorder flags and how many boxes or pallets an order needs.
The examples below use seven orders: the order number in column A, region in B, rep in C and revenue in D, rows 2 to 8.
| Order | Region | Rep | Revenue |
|---|---|---|---|
| 1001 | North | Kofi | 3600 |
| 1002 | South | Lina | 1250 |
| 1003 | North | Tess | 500 |
| 1004 | East | Ravi | 4800 |
| 1005 | South | Lina | 1200 |
| 1006 | North | Kofi | 7200 |
| 1007 | East | Ravi | 2000 |
The Excel skills for sales reports: totals by group with SUMIF#
SUMIF adds the numbers in rows that match one condition. Its shape is SUMIF(range, criteria, sum_range).
=SUMIF(B2:B8,"North",D2:D8)returns 11300 (3600 + 500 + 7200). Swap the range to total by rep: =SUMIF(C2:C8,"Lina",D2:D8) returns 2450.
For two conditions, such as North orders by Kofi, use SUMIFS, which puts the sum range first:
=SUMIFS(D2:D8,B2:B8,"North",C2:C8,"Kofi") returns 10800.
Point to a cell instead of typing the region. With "North" in G1, =SUMIF(B2:B8,G1,D2:D8) gives the same answer, and
changing G1 to East gives 6800.
For every region at once, a pivot table is quicker: put Region in Rows and Revenue in Values, and Excel lists each
region with its total and a grand total. Use the pivot to explore and SUMIF for a number that must sit in a fixed cell
of a report, because a pivot needs refreshing when the data changes. The SUMIF function guide and the
full SUMIF tutorial cover criteria like ">1000" and "<>North".
Percent change and growth#
Percent change shows how much a value grew or fell compared with its old value:
=(C2-B2)/B2With last year's sales in B2 (42000) and this year's in C2 (46200), the result is 0.1, or 10% once formatted with
Ctrl+Shift+% (Mac: Control+Shift+%). The parentheses make Excel subtract before dividing. Always divide by the old
value: a fall from 18500 to 17205 is -7%. To grow a number by a percent, multiply by (1 + percent):
=C2*(1+B9) with an 8% goal in B9. See the percent change formula guide for more
examples. If the old value can be zero, the division returns #DIV/0!, so wrap it:
=IF(B2=0,"New",(C2-B2)/B2).
Share of total and attainment#
A % of total column divides each row by one fixed total, so the total must be locked with $. With the total revenue
20550 in D9, type this in E2:
=D2/$D$9Fill it down to E8 with Ctrl+D (Mac: ⌘D). Kofi's 7200 order shows 35% once formatted as a percentage. Without $, the second row would divide by D10, which is empty, and show
#DIV/0!. Attainment against target works the same way: actual divided by target, such as =D2/E2 with targets in
column E.
Finding the top order and its rep with XLOOKUP#
Managers often ask "what was our biggest order, and who made it?" Answer in two steps:
=MAX(D2:D8)returns 7200. Then find that amount in the revenue column and return the rep:
=XLOOKUP(MAX(D2:D8),D2:D8,C2:C8)returns Kofi. The rep column is to the left of revenue, which XLOOKUP handles; VLOOKUP can't. XLOOKUP needs Excel
2021 or Microsoft 365. In older versions, use =INDEX(C2:C8,MATCH(MAX(D2:D8),D2:D8,0)). If two orders tie for the top,
both formulas return the first one. See the XLOOKUP function guide.
Totals by month and operations calculations#
To total a month, give SUMIFS the date column twice: on or after the first day, and before the first day of the next month. With dates in A2:A16 and revenue in G2:G16:
=SUMIFS(G2:G16,A2:A16,">="&DATE(2025,1,1),A2:A16,"<"&DATE(2025,2,1))This adds every January 2025 order, even if the list is re-sorted or orders are added in the range.
Operations roles add a few calculations of their own:
- Reorder flag:
=IF(D2<E2,"Reorder","OK")with stock in D and the reorder level in E. - Boxes needed:
=ROUNDUP(G1/G2,0). An order of 130 units in boxes of 24 needs 6 boxes, not 5.4. - Full boxes only:
=ROUNDDOWN(G1/G2,0)gives 5.
How to show and prepare these skills#
Sales tests commonly hand you an order list and a set of manager questions, much like the examples above. The guide to what Excel assessments test and how they're scored explains the common formats. For planning your study time, see how long it takes to learn Excel for a job, and when you can do these tasks without help, list them on your application with the advice in how to show Excel skills on your resume.
Practise it#
Total by condition in SUMIF, calculate growth and grow a target in Percentages and % change, then build a full report under test conditions in the sales report interview drill: revenue, totals by region and month, the top order and its rep, and a % of total column. The exercise below is the region total from this guide.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
How much revenue did the region in G1 bring in? Click G2 and use SUMIF: check the regions in B2:B8 against G1, and add the revenue in D2:D8.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Revenue | North | ||||||
| 2 | Revenue | |||||||
| 3 | ||||||||
| 4 | ||||||||
| 5 | ||||||||
| 6 | ||||||||
| 7 | ||||||||
| 8 | ||||||||
| 9 | ||||||||
| 10 | ||||||||
| 11 |
Tip: SUMIF takes the range to check, the criteria, then the range to add.
Practise this in a lesson
Frequently asked questions
What Excel skills are needed for sales jobs?
Sales roles commonly use SUM, SUMIF and SUMIFS, percent change, % of total, sorting and filtering, a lookup like XLOOKUP or VLOOKUP, and pivot tables for reports by region, rep or product.
How do I calculate sales growth in Excel?
Use =(new-old)/old, for example =(C2-B2)/B2 with last year in B2 and this year in C2, then format the cell as a percentage. A negative result means sales fell.
How do I total sales by region in Excel?
Use SUMIF: =SUMIF(region_range,"North",revenue_range). Point to a cell with the region name so one formula works for any region, or use a pivot table with Region in Rows.
What Excel skills do operations roles need?
Operations roles add stock and capacity calculations to the sales basics: IF flags for reorder levels, ROUNDUP for boxes or shifts, lookups for supplier details, and date math for lead times.