Excel for Consultants: The Skills Case Work Needs

Excel for consultants is less about rare functions and more about speed and accuracy with a few core tools. Client data arrives as exports, and your job is to summarise it, compare periods, join it with other files, test a few scenarios and check every number before it goes on a slide. This guide covers those skills with small worked examples, so you can prepare for a consulting role or a case-style Excel test.

What do consultants use Excel for?#

Consultants use Excel to answer a client's question with numbers: where sales grew, which costs are out of line, what happens if prices rise. A typical project loop looks like this:

1. Clean the client's export: remove blank rows, fix text that should be numbers, trim spaces.
2. Summarise it by region, product or month with a PivotTable or SUMIFS.
3. Compare periods or segments with percent change and shares of total.
4. Join it with other data, such as a store list or a price file, using XLOOKUP.
5. Model a few scenarios from clearly labelled assumptions.
6. Check totals, then chart the result for the deck.

The same loop appears in many analyst roles. The guide to Excel skills for business analysts covers the overlap, and the Excel skills for data analysts go deeper into cleaning and lookups.

Summarise client data with PivotTables and SUMIFS#

A PivotTable is the fastest way to explore a large data set, and SUMIFS is the best way to build a fixed summary table that updates. Here is a small extract of store sales, in thousands:

To total 2025 sales for South:

=SUMIFS(D2:D7,A2:A7,"South")

The result is 916 (612 + 304). With a region name in a cell, say F2, use =SUMIFS($D$2:$D$7,$A$2:$A$7,F2) and fill it down for every region. The SUMIFS function guide explains each argument.

For a first look at the data, insert a PivotTable (Insert > PivotTable), drag Region to Rows and the year columns to Values. The Pivot tables 2 lesson covers what consultants use most: two row fields with subtotals, a report filter, values shown as a % of the grand total, and refreshing when new rows arrive. Use the PivotTable to explore, then rebuild the final table with SUMIFS if it must keep a fixed layout for a slide.

Compare periods with percent change#

Percent change answers "how much did it grow?", and the formula is (new - old) / old. For North:

=(826-800)/800

North went from 800 to 826, so the result is 0.0325. Click % on the Home tab, then Increase Decimal once, to show 3.3%. Here are all three regions:

Two habits keep this right. Divide by the old number, never the new one. And don't average the regional percentages to get the total: the total change is (2,377 - 2,210) / 2,210, which weights each region by its size. The percentages and % change lesson drills both.

Join two client files with XLOOKUP#

XLOOKUP pulls a matching value from another table using a shared ID, which is how you combine a sales export with a store list. If a Stores sheet has store IDs in column A and the store manager in column C:

=XLOOKUP(B2,Stores!$A$2:$A$50,Stores!$C$2:$C$50,"Missing")

This returns the manager for store N1 in B2, or the word Missing if the ID isn't in the store list. Count the Missing rows before you trust any joined analysis. They usually mean the two files spell IDs differently, such as "N1" and "N01". XLOOKUP needs Excel 2021, Excel 2024 or Microsoft 365; in older versions use INDEX and MATCH. The XLOOKUP function guide has the full syntax.

Build scenarios from input cells#

A scenario model changes one or two assumptions and shows the effect, so keep every assumption in its own labelled cell. Say G1 holds a growth assumption of 5% and D2:D7 hold 2025 sales. Projected 2026 sales for the first store are:

=D2*(1+$G$1)

For store N1, that is 465 × 1.05 = 488.25. The $ signs lock G1, so when you fill the formula down (Ctrl+D on Windows, ⌘D on a Mac), every row still uses the same assumption. Change G1 to 10% and the whole model updates. Press F4 on Windows or ⌘T on a Mac to add the $ signs while typing.

Keep inputs in one coloured block at the top, never type numbers inside formulas, and label units ("thousands", "%"). A reviewer should be able to change an assumption without reading a single formula.

Check every number before it reaches a slide#

A wrong number in a client deck costs more than a slow one, so build checks into the workbook. Useful ones:

• Totals tie out. =SUM(D2:D7) should equal the sum of your regional SUMIFS. A difference means a region is misspelled or missing.
• Row counts match. Compare =COUNTA(A2:A7) with the row count the client says they sent.
• No duplicates. =COUNTIF($B$2:$B$7,B2)>1 returns TRUE for a store ID that appears twice.
• Percentages add to 100% when you show shares of a total.

Put the checks in a small block beside the analysis so you see a failure at once.

How to prepare for a consulting Excel case#

Case-style tests give you a small data set and a business question, then time you while you summarise, compare and recommend. Practise in the same format:

1. Work through a full case such as the real estate listings case study: price per square foot, averages and counts by area, a filtered list and the top listing.
2. Take a timed Excel case study test or the operations case study to practise under the clock.
3. Not sure where your gaps are? The Excel level test shows which skills to work on first.

The skills transfer across industries. See how the same tools apply in Excel for ecommerce, Excel for marketing jobs and Excel for real estate.

Practise it#

Start with the exercise on this page, then practise percent change in a live spreadsheet, where you type each formula and it is checked instantly. Next, build PivotTables with subtotals and % of total, and finish with the real estate case study to put the whole consulting loop together.