A working notebook of the Excel skills you actually use — explained with worked examples, then wired up so you can try the formulas on the page. Type, check, learn.
Excel has hundreds of functions, but in practice you'll use a small core set every day: enter data, write a formula, lock a reference, look up a value, summarize with a pivot, draw a chart. The fourteen skills below cover most of what spreadsheet work actually looks like.
Each skill below is a card: a short explanation on the left, a try-it-yourself mini-sheet on the right. Type into the highlighted cells. Hit Check. The page grades you live.
Every cell has an address: column letter + row number. A1 is the top-left. Type into a cell and it holds a value (text, number, date) or — if you start with = — a formula. Formulas can reference other cells: =A1+B1 reads two values and shows the sum.
A range uses a colon: A1:A10 means A1 through A10. Ranges feed into functions like SUM, AVERAGE, COUNT.
Add a formula in B3 that returns the value in B1 plus the value in B2.
Excel formulas use + − * / for arithmetic, with parentheses for order of operations. For totals you can write them by hand — =A1+A2+A3 — but it scales badly. Use aggregate functions instead.
In the Payroll project these handle gross pay = hours × rate then =SUM down the column. In the Gradebook, AVERAGE gives the student's mean, MIN/MAX the lowest and highest scores.
Five exam scores. Fill the four orange cells with the right aggregate formulas.
When you write =A1*B1 in row 2 and drag the formula down, Excel shifts the references with you: row 3 becomes =A2*B2. That's a relative reference, and it's the right behaviour 90% of the time.
When you want a reference to stay put as you copy the formula — say, a tax rate sitting in one cell — you anchor it with a $:
Press F4 while editing a reference to cycle through the four lock states. This is the single most common Excel mistake — the formula looks right in the first row and silently breaks when you fill it down.
Tax is 10% in B1. In C4, write a formula for Price × Tax that you could fill down to C5/C6 without breaking.
IF picks one of two values based on a test:
The test is anything that resolves to TRUE/FALSE: B2>50, A2="Yes", C2<>"" (not blank). The other arguments can be numbers, text in quotes, or further formulas.
For multi-condition tests, wrap the conditions in AND (all must be true) or OR (any one is enough):
You can also nest IFs for more than two outcomes — but past 2-3 levels switch to IFS or VLOOKUP.
A student passes only if both First Exam and Final Exam are above 50. Write the formula in D2.
These are the conditional cousins of SUM/COUNT/MAX. They take a list of (criteria range, criterion) pairs and aggregate only the rows that match all of them.
Criteria can be exact ("North"), comparison (">300"), or wildcard ("*coffee*"). The ranges must all be the same size.
In D9, total the units sold to the North region only when units are greater than 300.
When you have an ID in one place and want the matching name/group/shift from another table, you don't copy and paste — you VLOOKUP.
If the value isn't found, VLOOKUP returns #N/A. Wrap with IFERROR to handle that gracefully.
In B2, look up the Language for ID 22533 using the reference table on the right.
The three basic text-extracting functions:
To combine text, use & (or the equivalent CONCATENATE function):
Mix them together to assemble sentences from columns: =A2&" lives in "&B2&" and the phone number is "&C2. The cells join in order, with literal text in quotes between them.
The agent's 6-digit ID is glued to the end of their name. Extract just the ID into B2.
When the part you want isn't a fixed length — like the username before @ in an email — you need to find the separator's position first.
Now combine. To grab the username (before @):
For the domain (after @):
Modern Excel also has TEXTSPLIT and TEXTBEFORE/TEXTAFTER — cleaner, but only on 365 / newer versions. The FIND/LEFT/RIGHT trio works everywhere.
Pull the username (everything before @) from the email in A2 into B2.
Four close cousins that get confused all the time:
A column with names, dates, and a few empty cells:
In B7, count how many people are Female in the list above.
A pivot table takes a long, flat list of records and rolls it up by category. The same data can be summed by region, by month, by salesperson — without writing a single formula. It's the single highest-leverage Excel skill, full stop.
How to build one:
Slicers (PivotTable Analyze → Insert Slicer) give you click-to-filter buttons — much friendlier than the dropdown filters.
A flat sales log on the left collapses into the pivot summary on the right — same data, totalled per region.
Pivots are how you answer "how much did each region sell?", "who were the top three salespeople last quarter?", "which months saw the biggest drop?" — all without writing a formula.
Excel offers ~15 chart types. You only need four:
Build it in three clicks:
To add a trendline: click the chart → green plus → Trendline → More Options → pick Linear, Exponential, or Polynomial. Tick "Display R-squared" to see how well the trendline fits the data.
A small line chart comparing two years of monthly data. The series share an axis; one line wins on most months.
In Excel: select the three columns (Month, Year A, Year B with header) → Insert → Line chart → done.
Data Validation restricts what a user can type into a cell. The most common form is a dropdown list — pick one of these values, no typos, no surprises.
Basic dropdown:
Dependent dropdown — second list changes based on first. Define named ranges for each category (e.g. a range named Egypt with Egyptian cities). Then use =INDIRECT(A1) as the Source for the dependent cell. When A1 = "Egypt", INDIRECT resolves to the Egypt range and the second dropdown shows only those cities.
Pick a Country. The City list updates to show only its cities. (Same behaviour as a dependent dropdown in Excel.)
Make cells colour themselves based on what's in them. The classic case: highlight all scores below 70% in red. Pivots and reports become readable instantly.
The fast path:
Custom logic: New Rule → Use a formula to determine which cells to format. The formula evaluates row-by-row; =$F2>0.9 highlights any row where column F is greater than 90%. Mind the $ — anchor the column, leave the row relative.
A common pattern: spot rows where Budget > Actual (or any column-vs-column comparison) — that's:
Budget vs Actual — the red rows are where Budget > Actual (and the rule will follow you if you sort or filter).
Highlights stick to the data, not the cell address — sort the table and the rows that satisfy the rule keep their colour.
"Transpose" means flip rows and columns. A 4-row × 5-col table becomes 5-row × 4-col. Two ways to do it:
Static (paste once, no link):
Live (formula stays linked):
The QA Excel test Q2 wants exactly this transformation: turn a header-rows table into a header-columns table. Static transpose is fine for that — paste special is the cleanest answer.
Same data, flipped axes. Headers go where the names were, names become column headers.
Below is a collection of common situations you'll meet in spreadsheets — building a grade book, splitting names, computing margins, looking up prices, finding duplicates, building running totals. Each one is short. Each one gives you the formula, the reasoning, and the gotchas.
A column of test scores 0–100 in column B. Translate each into A / B / C / D / F using the usual 90/80/70/60 cutoffs.
Order matters. Test the highest cutoff first; once a branch is true, the rest are skipped. Reversing the order would put every score above 60 into the "D" bucket.
Same logic, flatter syntax. The final TRUE is the "catch everything else" branch. Easier to audit when you have many bands.
Column A holds "Smith, John". Pull last name into B, first name into C.
FIND locates the comma's position; LEFT takes everything before it.
MID starts one character after the comma and grabs up to 100 chars. TRIM kills the leading space.
In Microsoft 365 the modern syntax is cleaner: =TEXTBEFORE(A2,",") and =TRIM(TEXTAFTER(A2,",")).
Column B = revenue, column C = cost. Compute the profit margin as a percentage.
Format the cell as Percentage (Ctrl+Shift+5). Don't multiply by 100 — Excel handles that visually when you format. If B2 might be 0, wrap with IFERROR to avoid the #DIV/0! error:
Column B = this year, column C = last year. Compute % change.
Positive = growth, negative = decline. No need for a separate "down" formula. To label it, wrap with IF:
Result: "+12.3%" or "−4.5%". The leading + is added manually; the minus is automatic.
A sales sheet has product codes in column A. A separate Catalog sheet has code (col A) and price (col B). Pull the price for each sale.
Lock the table reference with $. The fourth argument FALSE (or 0) forces exact match — leaving it off gives approximate match, which is almost never what you want.
Modern replacement. Doesn't care which column the lookup value is in (unlike VLOOKUP, which only searches the leftmost). The fourth argument is the "if-not-found" value — no IFERROR wrap needed.
A column of names in B. How many times does each name appear?
Anchor the range with $, leave the criterion relative — that way dragging down checks each row against the full list. A result of 1 means unique; 2+ means duplicate.
Flag the duplicates with conditional formatting: select the column → Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values. Excel colours every cell whose value appears more than once.
Daily sales in column B starting at row 2. In column C, show the running total — sales so far through each row.
The half-locked range is the trick: start ($B$2) stays fixed, end (B2) moves down with each row. By row 100, the formula is SUM($B$2:B100) — the entire history.
Same trick for a running average: =AVERAGE($B$2:B2). For a running ratio like cumulative attainment: =SUM($B$2:B2)/SUM($C$2:C2).
Round a price to the nearest 5 cents. Round time to the nearest 15 minutes.
MROUND rounds to the nearest multiple of any value. CEILING always rounds up; FLOOR always rounds down.
Excel stores time as fractions of a day, so use TIME() to express the increment. 10:08 rounds to 10:15; 10:06 rounds to 10:00.
B2 = start date, C2 = end date. Count business days (Mon–Fri, excluding holidays).
Counts working days inclusive of both endpoints, skipping Saturdays and Sundays. To exclude holidays too:
For non-Western weekends (e.g. Fri–Sat off), use NETWORKDAYS.INTL with the weekend code (1 = Sat-Sun, 7 = Fri-Sat, 11–17 = single-day weekends).
A2 = customer name, B2 = product, C2 = date. Build a sentence: "John bought a Laptop on Oct 5, 2024."
The ampersand glues strings together. Always wrap dates with TEXT(value, format) — without it, the date appears as a serial number (45200 instead of "Oct 5, 2024").
Format codes you'll use most: "mmm d, yyyy", "dd/mm/yyyy", "yyyy-mm-dd", "#,##0.00" for currency-style numbers, "0.0%" for percentages.
A column of scores. What rank is each one — 1st, 2nd, 3rd?
Third argument: 0 for descending (highest = rank 1), 1 for ascending. Modern Excel prefers RANK.EQ (ties get the same rank) or RANK.AVG (ties get an averaged rank).
To break ties by a second column (e.g. tied scores broken by name alphabetically), use:
Phone numbers stored as "555-123-4567" — replace all hyphens with dots.
Replaces every hyphen. To replace only the second one: =SUBSTITUTE(A2,"-",".",2).
Ctrl+H opens Find & Replace. Find -, Replace with ., Replace All. Faster for one-off cleanups; the formula is better when you want the original column preserved.
A column of sales by salesperson. Get the top 3 values.
LARGE is like MAX but with a rank argument. Mirror: SMALL for the bottom N. To get the names alongside, combine with INDEX/MATCH:
Pulls the salesperson whose sales equal the top value. Modern Microsoft 365: =SORT(A2:B100, 2, -1) gives a sorted table directly.
Sum all sales where the product description contains the word "Pro" anywhere.
Asterisks are wildcards: "*Pro*" matches anything containing Pro. "Pro*" matches anything starting with Pro. "???" matches any three characters (question mark = single char wildcard).
Wildcards work in SUMIFS, COUNTIFS, AVERAGEIFS, VLOOKUP/XLOOKUP — but not in plain SUM or COUNT.
B2 = birthdate. Compute current age in full years.
The third argument is the unit: "Y" for full years, "M" for full months, "D" for days, "YM" for months after subtracting whole years (useful for "X years, Y months").
Fun fact: DATEDIF is undocumented in modern Excel — it's a holdover from Lotus 1-2-3 — but still works in every version. It won't autocomplete; you have to type it from memory.
Excel has a lot of named features and concepts. When you read a tutorial or a colleague's instructions, you'll meet terms that get used like everyone knows them. Below is a dictionary — short definition, what it does, and the gotcha most people miss.
One file = one workbook. A workbook contains one or more sheets (tabs at the bottom). Workbooks can be linked to each other with cross-file references like ='[Other.xlsx]Sheet1'!A1, but those break easily — prefer to keep related data in one workbook.
Each sheet has its own grid. Reference another sheet's cell with SheetName!A1. If the sheet name has spaces or special characters, wrap it in single quotes: 'Sales 2024'!A1.
Each cell has an address (A1, B2…) and one value: text, number, date, boolean, error, or a formula that evaluates to one of those. The "active cell" has the green outline.
Comma-separated lists make a "multi-area" range. Most functions accept these, but some (like LOOKUP) get confused — when in doubt, contiguous ranges are safer.
Every formula starts with =. The result is computed live whenever any referenced cell changes. To see the formula in a cell instead of its value, press Ctrl+` (toggle).
Function = name + parentheses + comma-separated arguments. Excel has 500+ functions across categories: Math, Text, Date, Logical, Lookup, Statistical, Financial, Engineering, Information.
Press F4 while editing a reference to cycle through the four lock states. The most common Excel mistake: forgetting to lock when copying a formula down. Looks right in row 1, silently wrong in row 2.
Instead of $B$1, define a name like TaxRate via Formulas → Name Manager. Formulas become readable: =Price*TaxRate beats =A2*$B$1. Useful for constants and lookup tables.
In older Excel you confirmed with Ctrl+Shift+Enter and the formula appeared in curly braces. In Microsoft 365, formulas automatically "spill" into the cells they need. =SORT(A1:A10) in one cell fills 10 cells.
When a formula returns more than one value, it "spills" into adjacent cells. The originating cell shows a blue border around the whole result. #SPILL! error means there's something in the way — clear those cells.
Select your data → Ctrl+T → check "My table has headers". Tables auto-add new rows when you type below them, formulas extend automatically, and you can reference columns by name: =SUM(Sales[Amount]).
Roll up a long flat list by category — totals per region, averages per month, counts per status — without writing a formula. Insert → PivotTable → drag fields into Rows, Columns, Values. Updates with Alt+F5.
Big tappable filter buttons instead of dropdowns. PivotTable Analyze → Insert Slicer. Multiple slicers can filter the same pivot; one slicer can drive multiple pivots (Report Connections).
Highlight numbers above a threshold, gradient-colour a range, flag duplicates. Home → Conditional Formatting. The "Use a formula" option is the most powerful — it lets you compare cells against other cells row-by-row.
Restrict a cell to a list, a date range, a number range, or a custom formula. Most common: a dropdown list. Data → Data Validation → Allow: List → Source: range or comma-separated values.
View → Freeze Panes. The most useful option: "Freeze Top Row" so headers stay visible while you scroll through thousands of rows. To freeze both the top row and left column, click cell B2 first, then Freeze Panes → Freeze Panes.
Adds dropdown arrows to the header row. Click any arrow to choose values to include, sort, or apply text/number filters. Filter doesn't delete rows — it hides them. To extract filtered rows, copy them to a new sheet.
Data → Sort. Add Level for multi-key sorting (e.g. by Region ascending, then by Sales descending). Always select the whole data block first — Excel may guess but it's safer to be explicit.
Data → Text to Columns. Choose Delimited (comma, semicolon, tab) or Fixed Width. Also useful for converting text-stored-numbers/dates into real numbers/dates — just run it with default options.
Type two or three examples of what you want, then Ctrl+E. Excel detects the pattern and fills the rest. Works for splitting names, joining cells, reformatting dates, extracting substrings — anything pattern-based.
Data → Get Data. Reads from CSVs, web pages, databases, folders of files. Records every cleanup step (rename column, split, filter) and re-runs them when you refresh. Removes the need for one-off scripts in most cases.
View → Macros → Record Macro. Excel writes VBA code for everything you do. Stop recording, then run it later or attach to a button. For real automation, learn to edit the VBA directly. Save as .xlsm to keep macros.
VLOOKUP / MATCH / XLOOKUP couldn't find the value. Wrap with IFERROR(formula, "Not found") or use XLOOKUP's built-in fourth argument. Don't suppress globally without thinking — sometimes #N/A means real data is missing.
Numerator over a zero or blank denominator. Wrap with IFERROR or check first: =IF(C2=0, 0, B2/C2). Common in margin and growth calculations when the prior period was zero.
You deleted a row or column that a formula referenced. Excel turns the address into #REF! permanently — there's no auto-fix. Undo (Ctrl+Z) immediately if it just happened. Otherwise edit the formulas manually.
You're doing math on text, or passing a range where a single value is expected. Most common cause: an apparently-numeric cell that's actually stored as text (left-aligned by default). Fix with Text to Columns or VALUE(A1).
Typo in a function name (VLOKUP), or a named range that no longer exists, or text without quotes (=IF(A1=hello,...) instead of "hello").
Rare. Caused by a space between two ranges (A1:A10 B1:B10) which Excel interprets as "the intersection of these" — and if they don't intersect, NULL. Usually a typo where you meant a comma or a colon.
Click the paintbrush icon (Home tab) → select source cell → click target. Copies fonts, borders, fills, number formats — not values. Double-click the paintbrush to "lock" it and paint multiple targets.
Ctrl+Alt+V. Choose Values (paste numbers, not formulas), Formats, Formulas, Transpose (flip rows/columns), or do arithmetic (add/subtract/multiply by a constant). The most useful Paste-Special is "Values" — converts formulas to their results.
The small green square at the bottom-right of a selected cell. Drag it to copy formulas down or right. Double-click it to auto-fill down to the end of an adjacent column. Holding Ctrl while dragging copies; without Ctrl, Excel may interpret series (1, 2, 3 → 4, 5, 6).
Most formulas only recalc when their inputs change. Volatile functions (NOW, TODAY, RAND, OFFSET, INDIRECT) recalc on every sheet change. Use sparingly in large workbooks — they make Excel sluggish.
By default a formula that references its own cell is an error (circular reference). File → Options → Formulas → Enable iterative calculation lets Excel try repeatedly. Used for goal-seek-like setups, but generally avoid — debugging becomes nightmarish.
Data → What-If Analysis → Goal Seek. Tell Excel "I want cell X to equal 100, by changing cell Y" and it finds Y by trial. For multi-variable optimization, use Solver (a free add-in).
Insert → Sparklines → Line / Column / Win-Loss. A miniature chart that fits inside one cell, showing the shape of a row's data. Great for at-a-glance trend tables — a column of sparklines next to each customer's row.
Contact centres and back-office operations have their own dialect — AHT, SL, Aux 1, NCNS, Erlang. Below is a dictionary aimed at someone joining a workforce-management or real-time-operations team. Each term has its expansion, the formula where one applies, and how it differs from the term right next to it (because mixing up Occupancy with Utilization, or Adherence with Conformance, is the classic interview mistake).
The average end-to-end time an employee spends finishing one contact. Includes speaking, hold time, and the after-contact wrap-up. Lower AHT means more contacts per hour, but cutting it aggressively hurts quality. Typical voice targets sit between 4 and 8 minutes.
Mean wait before an employee picks up. ASA of 30s sounds fine, but the average hides outliers — five customers waiting 5 minutes can drag it up. ASA paired with SL tells the fuller story.
The headline KPI of inbound operations. "80/20" means 80% answered within 20 seconds. Note the denominator: offered, not answered — abandoned contacts count against you. Some SLAs use the answered count as denominator (kinder); always check the contract.
The contract — the SL target plus the consequences of missing it. An SLA might require "80% in 20 seconds, monthly average, with credit refunded if missed two consecutive months". Often used loosely to mean "the SL target" in casual speech.
Anything above ~5% suggests the queue is too slow. Most shops exclude "short abandons" from the calculation. Some report "abandons after threshold" — counting only the people who waited long enough that you had a real chance to answer.
Customer dropped off before even getting through the greeting — usually a misdial or a quick change of mind. Industry convention: anything under 5 seconds is "short" and excluded from the abandon-rate calculation. The exact threshold is negotiated in the SLA.
Time after a contact ends where the employee finishes notes, codes the case, files follow-ups. Counts in AHT but not in talk time. Long wrap is a red flag — typically means the systems are clunky or the employee is hiding between contacts. Target 30–90 seconds.
Time the customer hears music while the employee looks something up. Counts in AHT. Long holds correlate with low satisfaction — customers feel ignored. Modern coaching encourages chat-with-supervisor or screen-share over silent hold.
Phone-system codes describing what the employee is doing. Conventions vary, but commonly: Aux 0 = Available, Aux 1 = Break, Aux 2 = Lunch, Aux 3 = Training, Aux 4 = Meeting, Aux 5 = Not Ready / System. The sum of Aux 1–5 divided by logged time = shrinkage.
How busy employees are while logged in to take contacts. The denominator excludes break, lunch, training — only "ready" and "handling" count. Sustained Occupancy above 90% causes burnout. Below 70% means you're overstaffed or routed poorly.
Like Occupancy but the denominator is everything paid (breaks, training, meetings included). Always lower than Occupancy. Mixing the two is the most common workforce-management mistake in interviews.
All the reasons a scheduled employee isn't actually answering: breaks, meetings, training, coaching, system downtime, absenteeism, attrition. Industry typical: 30–35%. To staff 100 productive seats at 30% shrinkage, you schedule 100 ÷ 0.7 ≈ 143.
Lunch was 12:00–12:30; employee took it 12:00–12:30 → adherent. Took it 12:15–12:45 → not adherent. The strict version. Most workforce contracts target 90%+ adherence.
Did the employee take the right total minutes, regardless of when? A 30-min lunch taken anywhere in the shift = conformant. More forgiving than adherence; less common as a contractual KPI.
% of cases solved without the customer needing to come back about the same thing. Strongly correlated with satisfaction and cuts volume — every repeat contact is a doubled cost. Hard to measure cleanly; requires either tagging or a 7-day callback window analysis.
Post-contact survey: "How satisfied were you?" on a 1–5 or 1–10 scale. CSAT% usually means the share of "top-2-box" responses (4 or 5 out of 5). Not the same as NPS, which asks about likelihood to recommend.
Single question: "How likely are you to recommend us, 0–10?" 9–10 = promoters, 7–8 = passives, 0–6 = detractors. The math gives a score from −100 to +100. A brand metric, not a contact-quality metric.
Employee didn't show up for their shift and didn't notify anyone. Counts as unplanned shrinkage. NCNS rate is a leading indicator of attrition — employees about to quit often NCNS first.
Annualised by × 12. Voluntary attrition (resignations) and involuntary (terminations) are tracked separately. Healthy contact-centre attrition runs 25–35% annually; over 50% indicates serious problems with hiring, training, or compensation.
0% to 100%, where 100% is perfect. Workforce teams target 90%+ at the day level and 95%+ at the week level. Below 80% means the forecast is unreliable for staffing.
Forecast Accuracy's twin: MAPE = 1 − FA on the same row. Expressed as a percentage error where lower is better (opposite of FA). Used because it averages cleanly across many periods.
A formula by A. K. Erlang (1917) predicting: given contact rate, AHT, and target SL, how many employees are needed? It assumes random arrivals, infinite queue, no abandonments. Embedded in every workforce tool. Excel doesn't ship with it; add-ins and online calculators fill the gap.
"Press 1 for billing, 2 for support." The automated front door. Containment rate (% of customers resolved without an employee) is its core KPI. A well-tuned IVR deflects 20–40% of inbound volume.
The team that watches live queues all day. Their job: when SL drops, get more bodies on the phones in 10 minutes — pull employees from offline activity, extend shifts, dial out for VTO reversals. Distinct from WFM, which forecasts and schedules in advance.
Forecast volume → calculate required staff via Erlang → publish schedules → measure performance against forecast. The long-horizon planning team. RTM is the operational arm; WFM is the strategic arm. Same numbers, different time scales.
When forecast is over actual, RTM offers VTO — go home unpaid early. When the floor needs more bodies, RTM reverses it (calls people back). A flexible lever to match staffing to volume swings.
Used when actual exceeds forecast. Either pre-scheduled (planned OT for known peaks) or RTM-offered (urgent extension). Costs around 1.5× base — last-resort lever after VTO-reverse.
Cumulative metrics from the start of the period through today. MTD smooths out one-off bad days. The half-locked range ($A$2:A2) is the Excel idiom — start anchored, end follows.
A "skill" is a queue or competency tag (Spanish, Tier-2 Technical, VIP). Skill-based routing sends each contact to the best-matched employee. Employees have skill profiles — primary and secondary — and the ACD routes accordingly.
The phone-system component that decides which ringing contact goes to which available employee. Rules: skill match, longest-idle, round-robin, priority routing. Source of truth for almost every contact-level metric.
Used to compute scheduled vs actual. If scheduled login was 09:00 and actual was 09:08, that's 8 minutes of tardiness counted against adherence. Often shown alongside ID and name in roster reports.
Headcount = number of people. FTE normalises by hours: two half-time employees = 1 FTE. Staffing models always work in FTE terms because part-time and full-time mix.
Offered = contacts that reached the queue (after IVR). Handled = contacts actually answered. The difference is abandoned. SL denominator is usually offered, not handled.
On a 1–5 survey, top-2-box = % of responses that are 4 or 5. Bottom-2-box = % that are 1 or 2. CSAT% almost always means top-2-box. The middle (3) is "passive", excluded from both.
"Contact" is channel-agnostic (call, chat, email, SMS). "Call" specifically means voice. "Interaction" means any two-way exchange. Modern omni-channel teams use "contact" to avoid voice-bias.
The formulas you actually use in a workforce-management or real-time-operations seat. Each example below pairs a daily reporting need (service level, AHT, shrinkage, forecast accuracy, adherence) with the Excel formula that produces it. Copy them, adapt the ranges, and you have your daily dashboard.
Raw contact data: each row is one contact with an interval timestamp, wait time in seconds, and status (Answered / Abandoned). Compute SL for the 10:00–10:30 interval at an 80/20 target.
Numerator: contacts in the interval answered within 20 seconds. Denominator: everything offered in the interval. The criterion operator goes inside the quotes — a syntax detail people forget.
Subtract short abandons (under 5 seconds) from the denominator. The SLA-friendly version most contracts assume.
Each contact has Talk seconds, Hold seconds, ACW seconds, and a Date. Build daily AHT.
Where A2 is the date for the row. Format the result cell as [h]:mm:ss if you want HH:MM:SS, or just keep seconds.
Why not AVERAGE on per-contact AHT? Because that's an unweighted mean — a 30-second contact counts as much as a 30-minute one. Summing the components and dividing by count is the correct weighted version.
Employee-day rows with total logged minutes and minutes in each Aux state. Compute shrinkage %.
B–F = Aux 1 (break) + Aux 2 (lunch) + Aux 3 (training) + Aux 4 (meeting) + Aux 5 (system). Some shops include only Aux 1 (paid breaks) and treat lunch separately. Confirm the local definition.
Both forms must give the same answer. If they don't, the timesheet has gaps.
Two columns of daily Forecast and Actual values. Compute FA per day, then average by day-of-week to spot systematic bias.
Wrap with IFERROR if Forecast can be zero: =IFERROR(1−ABS(...)/Forecast, "n/a").
Or add a helper column =TEXT(A2,"ddd") and pivot. If Mondays are consistently low, the forecast under-counts Monday volume — adjust the seasonality factor.
For each employee: scheduled minutes in state X, actual minutes in state X. Compute adherence.
The absolute differences capture both early-back and late-back deviations. An employee scheduled 30 min for lunch but who took 35 is 5 min off; taking 25 is also 5 min off — both count against adherence.
In older Excel, enter as an array formula with Ctrl+Shift+Enter. In Microsoft 365, just press Enter — dynamic arrays handle it.
Survey responses on a 1–5 scale, one row per response. Compute CSAT% as the top-2-box share.
COUNTA in the denominator counts non-blank cells (actual responses), ignoring rows where the survey was sent but not returned. To filter by team: =COUNTIFS(Score,">=4",Team,"Alpha") / COUNTIFS(Score,">=1",Team,"Alpha").
A back-of-envelope alternative to full Erlang C, useful for sanity checks.
Example: 1,000 contacts/day × 360s AHT = 100 employee-hours of pure work. At 85% Occupancy target ≈ 118 productive hours needed. With 7-hour productive shifts → 17 FTE. Add 30% shrinkage → schedule 17 / 0.7 ≈ 25 FTE.
Erlang C refines this with queueing math, but the rough number usually lands within ±10%.
During the day, you want a running SL that updates every interval — not a daily recalc.
The half-locked range is the same idiom as MTD%. As you drag down through intervals, the start stays at row 2 and the end follows. Each row shows SL from start of day through that interval.
This is the chart real-time teams watch on the wall. When the running SL crosses below target, it's the trigger to act.
Monthly attrition: leavers as a share of average headcount.
The compound formula is technically correct (you can't leave twice). The simple monthly × 12 over-states slightly but is what most operations dashboards use.
How many employees are qualified for each skill, per team? A simple matrix shows the gaps.
The "*"&B$1&"*" is a wildcard match — counts any cell in Skills that contains the skill name from B1. Useful when an employee's Skills cell holds a comma-list ("Spanish, Tier-2, VIP").
Drop into a 2-D table with teams down rows and skills across columns. You'll see at a glance which teams are short on which skill.
Three metrics — AHT (target 4:00), Quality (target 90%), CSAT (target 85%) — each with a weight. Build a single weighted score per employee.
Result: 0%, 30%, 40%, 60%, 70%, or 100%. Map those to letter grades (A/B/C/D) with a small VLOOKUP table if HR wants bucketed grades.
Cap each ratio at 100% (no extra credit) and weight. Fairer when employees miss a target by a little vs by a lot.
A roster has employee IDs and a separate Shifts table maps each ID to a shift letter. Return the shift for every row.
If an ID can have multiple shifts (different days), VLOOKUP returns only the first match. For multi-criteria lookup (ID + date):
Press Ctrl+Shift+Enter (older Excel) or use FILTER / XLOOKUP in Microsoft 365.
A column where each cell looks like "Jane Smith 866620" — the last 6 digits are the ID. Pull just the ID.
Works because the ID is a fixed length at the end. If the ID length varies, you need to find the last space and take everything after it:
SUBSTITUTE pads every space with 50 extra spaces; RIGHT grabs the last 50 characters (which is just the final word now); TRIM cleans the leading whitespace. A classic Excel trick for "last word of a string".
Email addresses in column A; pull the username (before @) into B and the domain (after @) into C.
The 100 is a "long enough" upper bound on the rest of the string. Microsoft 365 alternative: =TEXTBEFORE(A2,"@") and =TEXTAFTER(A2,"@").
A flat file with employees and short leave codes (P, AL, SL, UL, NCNS). A separate Structure sheet defines each code and its category (Planned, Unplanned, Productive). Enrich the flat file with description + category.
If you get #N/A on codes that "look right", the issue is usually trailing spaces. Wrap with TRIM: =VLOOKUP(TRIM($D2),...). If the lookup table itself has trailing spaces, clean the source — TRIM on the formula side won't help.
Predict next Monday 10:00–10:30 volume from the past 6 Mondays at the same interval.
Apply a seasonality factor if there's a known event (campaign, holiday). Apply a growth trend if volume is climbing month-over-month. Microsoft 365's FORECAST.ETS() can do exponential smoothing with at least two cycles of history.
First name in B, middle in C, last in D. Compose a full name in column E.
The IF handles the case where there's no middle name — without it, you'd get a double space ("Jane Smith"). Modern Microsoft 365 has TEXTJOIN(" ", TRUE, B2, C2, D2) which ignores empty cells automatically.
A table with Actual in column G and Budget in column H. Paint the whole row red where Actual exceeds Budget.
Select the full data range → Home → Conditional Formatting → New Rule → "Use a formula to determine which cells to format" → enter:
Format → Fill: red → OK. The $ in front of column letters locks the comparison to G and H regardless of which cell Excel is evaluating. Without it, the rule would compare row-relative columns and break.
The fastest way to learn is to fail fast in a small sandbox. Each challenge below has a question, the data context, and a single input where you write the formula. Hit "Check" to validate, "Reveal" to give up.
A student's score is in cell B2. Write a formula that returns "Pass" if the score is at least 60, otherwise "Fail".
A staff table sits on sheet Staff, range A2:D200. Column A = ID, column B = Name. Cell A2 on the current sheet has the ID you want to look up. Return the name with an exact match.
Sales table: Amount in column E, Product in column B, Year in column D, all rows 2–5000. Sum the amounts where the product is "Laptop" and the year is 2024.
A2 = first name, B2 = city, C2 = phone. Build the sentence: "[Name] lives in [City] and the phone number is [Phone]" using the ampersand operator.
Tasks table: Duration (in seconds) in column F, Status in column G. What share of all tasks were completed (status = "Completed") in 20 seconds or less? Express as completed-within-20s divided by the total non-blank rows. Use COUNTIFS and COUNTA.
A2 = "alex.kim@example.com". Return everything before the @. Use LEFT and FIND.
B2 = estimated value. C2 = actual value. Compute estimate accuracy as 1 minus the absolute error divided by the estimate. (A perfect estimate returns 1; an estimate that's 10% off returns 0.9.)
Daily Actuals in column B, daily Targets in column C, starting at row 2. In row 2, write a formula that gives the running cumulative attainment % — total actuals so far divided by total targets so far. When you drag it down later, the range should expand row-by-row.
Survey ratings on a 1–5 scale sit in column F. Compute the share of responses that are 4 or 5 (the "high-rating" share). Use COUNTIF and COUNTA.
Sales total is in F2. Bonus rules: 10% if 10,000 or more, 5% if 5,000+, 2% if 1,000+, otherwise 0. Return the bonus amount (sales × bonus rate) using nested IFs.
Score is in B2, completion% in C2. Return "Pass" only if score is at least 70 AND completion is at least 90%, otherwise "Fail".
In a single row: B2 = total available minutes, C2 = active minutes. Compute the idle-time share as 1 minus the active-over-available ratio. (If you were available 480 minutes and active for 360, your idle share is 25%.)
Column F holds scores. Count how many scores are at least 50 and at most 79 (inclusive). Use COUNTIFS with two conditions.
Sales numbers sit in B2:B100. Compute the sum of just the top three (largest) values. Use LARGE.
A price is in B2. Round it to the nearest multiple of 5 (so 87 → 85, 88 → 90).
B2 = start date, C2 = end date. Count the business days (Mon–Fri) between them, ignoring holidays.
A2 holds a sentence like "Hello there friend". Return just the first word ("Hello"). Use LEFT and FIND together — find the position of the first space.
B2 = numerator, C2 = denominator. Compute the ratio, but if C2 is zero (or division fails for any reason), return 0 instead of an error. Use IFERROR.
B2 = a person's birthdate. Compute their age in full completed years today. Use DATEDIF with "Y".
A2 contains "John Smith" (just first then last, one space). Return only the last name ("Smith"). Use MID and FIND, or RIGHT with LEN-FIND.
Contact log: Wait seconds in column F, Status in column G ("Answered" / "Abandoned"). Compute SL as the share of offered contacts that were answered in 20 seconds or less. Use COUNTIFS over COUNTA.
B2 = total Talk seconds for the day. C2 = total Hold seconds. D2 = total ACW seconds. E2 = contacts answered. Compute daily AHT in seconds.
B2 = total logged minutes. C2 = productive minutes (Talk + ACW + Hold + Available). Compute shrinkage as 1 minus the productive-over-logged ratio.
B2 = forecast volume. C2 = actual volume. Compute Forecast Accuracy as 1 minus the absolute error over the forecast. A perfect forecast returns 1.
Survey scores 1–5 are in column F, one row per response. Compute the top-2-box CSAT (share of responses that are 4 or 5). Use COUNTIF and COUNTA.
C2 = AHT in minutes (target ≤ 4). D2 = Quality % (target ≥ 90). Score is 50% for hitting each target, summed. Write the formula using two IFs added together.
Employee IDs in column A. A separate sheet Shifts, range A2:B500, has ID in column A and shift letter in column B. Pull the shift for the ID in A2 with an exact match.
A2 looks like "Jane Smith 866620" — last 6 characters are the employee ID. Extract just the ID.