Excel for Data Analysis: Pivot Tables, XLOOKUP and Formulas That Matter

Excel is still the most used analytics tool in the world. In many Indian companies, the first test in an analyst interview is a laptop, an Excel file and 30 minutes. The good news: a small set of features covers nearly everything you will be asked.

We will use one dataset throughout — a sheet of orders from an electronics store chain:

1. Turn the range into a Table first

Click inside the data and press Ctrl + T. A Table gives you filters, banded rows, formulas that copy themselves down, and ranges that grow automatically when new rows arrive. Name it Orders (Table Design → Table Name). Now formulas can say Orders[Amount] instead of G2:G5000.

2. Conditional totals: SUMIFS and COUNTIFS

Total sales in Pune:                =SUMIFS(Orders[Amount], Orders[City], "Pune")
Laptop sales in Pune:               =SUMIFS(Orders[Amount], Orders[City], "Pune", Orders[Category], "Laptops")
Orders above ₹20,000:               =COUNTIFS(Orders[Amount], ">20000")
Average order value in Delhi:       =AVERAGEIFS(Orders[Amount], Orders[City], "Delhi")

The pattern is always: what to add up, then pairs of where to look, what to match. Put the city name in a cell (say J2) and use J2 instead of "Pune" so one formula works for every city.

3. Bringing data together: XLOOKUP

Product names and costs live on another sheet, Products. To bring the product name into each order:

=XLOOKUP([@ProductID], Products[ProductID], Products[Name], "Not found")

Read it as: look for this order's ProductID in the Products ID column, return the Name from the same row, and say "Not found" if it is missing. XLOOKUP replaces VLOOKUP: it can look left, does not break when columns are inserted, and handles missing values cleanly. If your company still uses older Excel, learn INDEX + MATCH as the fallback.

4. Pivot tables: the analyst's favourite tool

Insert → PivotTable → select the Orders table. Then drag fields:

• Rows: City · Columns: Category · Values: Sum of Amount

In ten seconds you have sales by city and category — a summary that would take dozens of formulas. A few moves every analyst uses:

• Group dates by month or quarter (right-click a date → Group).
• Show values as % of grand total or % of row total to compare share, not just size.
• Add a slicer (PivotTable Analyze → Insert Slicer) for one-click filters by city or month.
• Change "Sum" to "Count" or "Average" in Value Field Settings — count of orders and average order value tell different stories.
• Refresh after new data arrives (right-click → Refresh). Because the source is a Table, new rows are included automatically.

5. Conditional formatting: make problems visible

Select a column and use Home → Conditional Formatting: colour scales for sales by city, data bars for quantities, or a rule like "Amount less than 0" in red to catch data errors. Use it to guide the eye to one thing — a sheet where every cell is coloured guides it nowhere.

6. A few more functions worth knowing

• IF and IFS — categorise: =IF([@Amount]>=20000, "High value", "Regular")
• TEXT — extract a month name from a date: =TEXT([@Date], "mmm-yy")
• UNIQUE, SORT and FILTER — dynamic lists: =SORT(UNIQUE(Orders[City]))
• IFERROR — replace errors with something readable.