Excel can answer most day-to-day data questions without any add-ins. This guide uses the same 30-day sample as the rest of our guides: five columns (A day, B ad spend, C website orders, D app orders, E average basket) in rows 2 to 31. Every formula below was calculated against that sheet and returns the figure shown. You can download the data as a CSV to follow along.
Before you start: make the sheet analysable
- One header row, one record per row, no blank rows or merged cells inside the table.
- Numbers stored as numbers. If a column is left-aligned or shows a green triangle, it is probably text.
- No totals inside the data range: they get counted twice.
- Format the range as a table (Insert → Table) so formulas and PivotTables grow with the data.
The formulas that answer most questions
| Question | Formula | Result |
|---|---|---|
| Total orders, both channels | =SUM(C2:D31) | 1,772 |
| Average website orders per day | =AVERAGE(C2:C31) | 32.73 |
| Middle day | =MEDIAN(C2:C31) | 32 |
| Spread of days | =STDEV.S(C2:C31) | 7 |
| Do ad spend and orders move together? | =CORREL(B2:B31,C2:C31) | 0.723 |
| App revenue (orders × basket) | =SUMPRODUCT(D2:D31,E2:E31) | 472,295 |
| Orders in week 3 (days 15 to 21) | =SUMIFS(C2:C31,A2:A31,">=15",A2:A31,"<=21") | 254 |
| App share of all orders, % | =SUM(D2:D31)/SUM(C2:D31)*100 | 44.58 |
Three habits prevent most spreadsheet errors. Use SUMIFS and COUNTIFS (several conditions) in place of nested IFs. Use SUMPRODUCT when you need the sum of a product, as in revenue = orders × price. And keep a copy of the raw data on its own sheet so every figure can be traced back.
Find outliers and trends
=COUNTIF(C2:C31,">"&QUARTILE.INC(C2:C31,3)+1.5*(QUARTILE.INC(C2:C31,3)-QUARTILE.INC(C2:C31,1)))+COUNTIF(C2:C31,"<"&QUARTILE.INC(C2:C31,1)-1.5*(QUARTILE.INC(C2:C31,3)-QUARTILE.INC(C2:C31,1)))
=FORECAST.LINEAR(31,C2:C31,A2:A31)
=AVERAGE(C25:C31)The first formula counts days outside the usual fences (1 here: the day-17 sale). The second projects day 31 from the straight-line trend (39.75). The third is the last 7-day average (36.57). A forecast like this assumes the pattern continues, so treat it as a guide, not a promise.
Build a PivotTable
A PivotTable summarises a table by groups without writing formulas. To add a week column, put =INT((A2-1)/7)+1 in a new column, then:
- Click anywhere in the table and choose Insert → PivotTable.
- Drag the week column to Rows.
- Drag website orders and app orders to Values; make sure each is set to Sum.
- Check the grand total against =SUM(C2:D31): they must match before you trust the table.
Remember that a PivotTable does not refresh by itself: right-click it and choose Refresh after the data changes.
Turn on the Analysis ToolPak
The Analysis ToolPak is an Excel add-in that adds ready-made tools such as descriptive statistics, regression, t-tests and histograms. In Excel for Windows, go to File → Options → Add-ins, choose Excel Add-ins next to Manage, select Go, tick Analysis ToolPak and press OK. On a Mac it is under Tools → Excel Add-ins. It then appears as Data Analysis on the Data tab. Its output is static: if the data changes, run the tool again. The functions above recalculate by themselves, which is why they are usually the better choice for anything you will repeat.
Where Excel stops being the right tool
- The file becomes slow, or the data no longer fits in one sheet.
- Several people edit copies and nobody knows which is current.
- You repeat the same cleaning every week. That is a job for a query or a script, as in the Python and SQL guide.
- You want to ask a question in plain words and see the working. That is what AnalyzeIt does with an uploaded file: you can try it with five free runs and no account.
Questions people ask
- How do I analyse data in Excel?
- Put the data in a clean table, summarise each column with AVERAGE, MEDIAN and STDEV.S, compare groups with SUMIFS or a PivotTable, then check unusual values and trends. Always check one total by hand.
- How do I enable the Data Analysis ToolPak in Excel?
- On Windows: File → Options → Add-ins → Manage Excel Add-ins → Go, then tick Analysis ToolPak. On a Mac: Tools → Excel Add-ins. It then appears as Data Analysis on the Data tab.
- Is Excel good enough for data analysis?
- For tables that fit in a sheet and questions you ask occasionally, yes. For larger data, repeated work or shared results, a database query, a script or a tool built for the job is safer.
- Which Excel functions are most useful for analysis?
- SUMIFS and COUNTIFS for conditional totals, AVERAGE, MEDIAN and STDEV.S for summaries, CORREL and SLOPE for relationships, QUARTILE.INC for outliers, and FORECAST.LINEAR for a simple trend.
Read more: Excel, SQL and Python code for every statistics method, or try the live demo with no account.
Related guides
- Python and SQL for data analysis, side by side — 3 min read
- How to do data analysis: 6 steps with a worked example — 4 min read
- Spreadsheet or AI data tool: which one for which job? — 2 min read
- How to clean a messy CSV before you analyse it — 2 min read
- Data visualization techniques: which chart for which question — 2 min read
Methods used in this guide
Metrics to try
Free tools
All guides · Statistics methods · Business metrics · Free tools
Legal entity
Devansh Yadav
Moradabad, Uttar Pradesh
Governing law: Courts at Moradabad, Uttar Pradesh
Phone: +91 8433475698
Legal: legal@analyzeit.in