Excel for Business Analysts: Skills and Test Tasks

Excel for business analyst roles is about answering questions from data quickly and in a way others can check: totals by segment, a trend over time, a share of the whole and a clear recommendation. The core tools are SUMIFS, COUNTIFS, XLOOKUP and pivot tables. This guide shows each one on a small orders table, then walks through the kind of tasks a business analyst Excel test usually sets.

How business analysts use Excel#

Business analysts sit between the business and the data. A manager asks "is the Pro plan growing?" and the analyst turns that into numbers. In Excel, that usually means:

• Summarising sales, costs or customers by region, product or month.
• Joining data from two sources, such as orders and a price list.
• Measuring trends: month-over-month change and share of total.
• Presenting a short table or chart with a clear takeaway.

The overlap with data analysis is large. Excel skills for data analysts leans more on cleaning and statistics, while business analysts spend more time on summaries and recommendations.

The examples use this orders table in A1:E7:

Summaries by segment with SUMIFS and COUNTIFS#

SUMIFS adds the values that meet every condition, and the range to add comes first.

=SUMIFS(E2:E7,D2:D7,"Pro")

returns 3720, total Pro revenue (900 + 950 + 1000 + 870). Add a second condition for one segment:

=SUMIFS(E2:E7,C2:C7,"North",D2:D7,"Pro")

returns 1950. COUNTIFS counts the matching rows: =COUNTIFS(D2:D7,"Pro",E2:E7,">=900") returns 3 (O-2, O-3 and O-5).

The real power comes from a summary grid. Put regions down G2:G3 and products across H1:I1, then type one formula in H2 and fill it across and down:

=SUMIFS($E$2:$E$7,$C$2:$C$7,$G2,$D$2:$D$7,H$1)

The mixed references ($G2 and H$1) keep each formula pointing at its own row and column header. See the SUMIFS function guide.

Joining tables with XLOOKUP#

XLOOKUP brings a value from one table into another by matching a key, much like a join in a database. Say a second table lists each product's monthly price, with products in K2:K3 and prices in L2:L3. To bring the price into the orders table:

=XLOOKUP(D2,$K$2:$K$3,$L$2:$L$3,"No price")

XLOOKUP finds "Basic" in the product list and returns its price. The fourth argument shows "No price" instead of #N/A when a product is missing, which makes gaps easy to spot. XLOOKUP needs Excel 2021 or Microsoft 365; in older versions use INDEX MATCH or VLOOKUP with FALSE. The XLOOKUP function guide covers the optional arguments.

Trends: percent change and % of total#

Percent change shows how much a number grew or shrank compared with before:

=(new-old)/old

Monthly revenue is Jan 1300, Feb 1330 and Mar 1870. February's change is =(1330-1300)/1300, about 2.3%. March's change is =(1870-1330)/1330, about 40.6%. That jump is worth a sentence in your summary.

Share of total divides a part by the whole: =SUMIFS(E2:E7,D2:D7,"Pro")/SUM(E2:E7) returns 3720 ÷ 4500, about 82.7%. Format both results as percentages (Ctrl+Shift+%; Mac: Control+Shift+%) instead of multiplying by 100.

Pivot tables for fast breakdowns#

A pivot table summarises the whole table in seconds. Select the data, choose Insert > PivotTable, drag Region to Rows, Product to Columns and Revenue to Values. North shows 1950 for Pro and 400 for Basic; South shows 1770 for Pro and 380 for Basic.

Two features matter most for analysts:

• Show Values As > % of Grand Total turns revenue into shares without any formulas.
• Refresh (Data > Refresh All, or Ctrl+Alt+F5 on Windows) updates the pivot when the source changes. Turning the source into a table first (Ctrl+T; Mac: ⌘T) makes sure new rows are included.

Use a pivot to explore and a SUMIFS grid when the report must update instantly or follow a fixed layout.

Checking the data before you trust it#

Analysts check the data before they summarise it, because one bad row can change the answer. A few quick checks catch most problems:

• Count rows and blanks. =COUNTA(A2:A7) should match the number of orders, and =COUNTBLANK(E2:E7) should be 0 if every order has revenue.
• Look for duplicates. =COUNTIF(A2:A7,A2) returns more than 1 if an order ID appears twice.
• Check the spelling of categories. =UNIQUE(C2:C7) (Excel 2021 or Microsoft 365) lists each region once, so "North" and "north " show up as two entries if they differ.
• Reconcile totals. The sum of your summary grid should equal =SUM(E2:E7). If it doesn't, a category is missing or misspelled.

Mention these checks in an interview. They show you think about whether a number is right, not just how to calculate it.

Typical business analyst Excel test tasks#

A business analyst Excel test often gives you a dataset and a set of questions like these:

1. Total revenue by region and product (SUMIFS or a pivot).
2. The month with the highest revenue, and its change from the month before.
3. Each product's share of total revenue.
4. Bring a value in from a second table (XLOOKUP or INDEX MATCH).
5. Flag rows that meet a rule, such as orders over a target (IF with AND).
6. Write two or three sentences on what the numbers show.

That last step is easy to rush. A good answer names the finding, the number and a likely next step, for example: "Pro is 83% of total revenue, and revenue grew 41% in March, mostly because South sales rose from 380 to 870." For more on this format, see the data analyst Excel test guide. To level up from there, read advanced Excel skills for jobs and the intermediate Excel skills list.

Practise it#

Analyst skills come from answering real questions on real tables. Practise COUNTIFS and SUMIFS in a live spreadsheet: you type the formula, and it checks it instantly. Then join tables with XLOOKUP and go further with Pivot tables 2, which adds % of grand total and refreshing. When you're ready, try the timed data analyst Excel mock test or the Excel case study test.