Data Analysis in Excel: Sort, Filter, Summarise and What-If
Last updated: November 2026 · Works in Excel for Microsoft 365, Excel 2021 and Excel 2019
Excel is one of the most widely used data analysis tools in the world. You do not need advanced skills to get answers from your data: sorting, filtering, a handful of summary functions and the What-If tools will answer most everyday business questions. This guide uses a simple sales list as the example.
| Date | Branch | Salesperson | Product | Qty | Amount (AED) |
|---|---|---|---|---|---|
| 02/03/2026 | Dubai | Sara | Laptop | 3 | 8,400 |
| 02/03/2026 | Sharjah | Omar | Printer | 5 | 3,250 |
| 03/03/2026 | Dubai | Ravi | Toner | 12 | 1,560 |
| 04/03/2026 | Ajman | Omar | Laptop | 2 | 5,600 |
Sort your data
Click any cell in the Amount column and choose Data > Sort Largest to Smallest (Z→A). Excel keeps each row together. For more than one level, such as by Branch then by Amount, use Data > Sort, click Add Level and set each column and order. You can also sort by cell colour or a custom list, such as Jan, Feb, Mar.
Filter to see only what you need
Press Ctrl + Shift + L to add filter arrows to the headers. Click the arrow on Branch, untick Select All and tick Dubai to show only Dubai sales. Number and date filters offer options such as Greater Than, Top 10, This Month or Between. The row numbers turn blue when a filter is on. Use Data > Clear to show everything again.
To total only the visible rows, use =SUBTOTAL(109,F2:F500). Unlike SUM, it ignores rows hidden by the filter. In Microsoft 365 you can also use the FILTER function to return matching rows to another place, for example =FILTER(A2:F500,B2:B500="Dubai").
Advanced Filter for complex conditions
When you need "Dubai and amount over 5,000, or any Laptop sale", use a criteria range. Copy the headers above the data, type the conditions underneath (conditions on the same row mean AND; separate rows mean OR), then choose Data > Advanced, set the List range and Criteria range, and optionally copy the results to another location. To show all rows again, choose Data > Clear.
Summarise with conditional functions
| Question | Formula |
|---|---|
| Total sales in Dubai | =SUMIFS(F:F,B:B,"Dubai") |
| Total laptop sales by Omar | =SUMIFS(F:F,C:C,"Omar",D:D,"Laptop") |
| Number of sales over 5,000 | =COUNTIFS(F:F,">5000") |
| Average sale in Sharjah | =AVERAGEIFS(F:F,B:B,"Sharjah") |
| Largest single sale | =MAX(F:F) |
| List of unique branches (365) | =UNIQUE(B2:B500) |
Put the criteria in cells, such as =SUMIFS(F:F,B:B,H2), so you can change the branch in H2 and watch the answer update.
Look up values
XLOOKUP finds a value in one column and returns the matching value from another. To find the price of the product named in H2 from a price list in columns K and L:
=XLOOKUP(H2,K:K,L:L,"Not found")
In older versions use =VLOOKUP(H2,K:L,2,FALSE). The FALSE at the end is important: it forces an exact match.
What-If Analysis
Goal Seek
Goal Seek works backwards from the answer you want. If B5 calculates monthly profit from units sold in B2, choose Data > What-If Analysis > Goal Seek, set cell B5 to value 20000 by changing B2, and Excel finds the number of units needed.
Scenario Manager
Save sets of inputs, such as Best, Expected and Worst case prices and volumes, under Data > What-If Analysis > Scenario Manager, then switch between them or create a summary report comparing results side by side.
Data Tables
A one- or two-variable Data Table shows how a result changes across a range of inputs, for example a loan repayment for every combination of interest rate and term. Set up the inputs around a formula, select the block and choose What-If Analysis > Data Table.
Analyze Data and the Analysis ToolPak
In Microsoft 365, click inside your data and choose Home > Analyze Data. Excel suggests charts and pivot tables and lets you ask questions in plain English, such as "total amount by branch". For statistics such as descriptive statistics, histograms, correlation and regression, enable the free Analysis ToolPak via File > Options > Add-ins > Manage Excel Add-ins > Go; it then appears as Data > Data Analysis.
Summary
- Sort and filter to focus on the rows that matter; use SUBTOTAL for totals of filtered data.
- SUMIFS, COUNTIFS and AVERAGEIFS answer most "how much" and "how many" questions.
- XLOOKUP pulls matching values from another table.
- Goal Seek, Scenarios and Data Tables test "what if" questions without rebuilding your model.
Next: summarise thousands of rows in seconds with Pivot Tables.