Many companies send an Excel file and give you 30 to 60 minutes. These are the 15 tasks you will most often see, with the formula or tool that solves each one.
Data cleaning tasks
1. Remove extra spaces
Use TRIM on text columns. Hidden spaces are the number one reason lookups fail.
2. Fix text case
Use PROPER for names, UPPER for codes and LOWER for emails.
3. Remove duplicates
Use Data, Remove Duplicates, or the UNIQUE function to keep the original data safe.
4. Convert text to dates or numbers
Use DATEVALUE or VALUE, or Text to Columns, when dates and numbers are stored as text.
5. Split one column into two
Use Text to Columns, or TEXTSPLIT in newer Excel, for names, addresses and codes.
6. Fill or flag blanks
Use IF with ISBLANK to mark or replace empty cells.
Formula tasks
7. Look up a value from another table
Use XLOOKUP or VLOOKUP to bring in prices, names or categories.
8. Total by category
Use SUMIFS to add numbers by region, product or month.
9. Count by condition
Use COUNTIFS to count orders, tickets or employees that meet several rules.
10. Grade or band values
Use IFS or an approximate-match lookup to turn scores or amounts into grades and slabs.
11. Calculate age or tenure
Use DATEDIF with TODAY to get years and months of service.
Analysis and reporting tasks
12. Build a PivotTable
Summarise sales by region and month, then add a slicer so the reader can filter.
13. Add a chart
Pick the chart that matches the question: a column chart to compare, a line chart for trends.
14. Highlight top or bottom values
Use conditional formatting to colour the top 10 or any value below a target.
15. Write a short summary
Add two or three lines saying what the data shows. Many candidates skip this, and interviewers notice.
How to finish on time
- Read all the questions first, then do the easy ones.
- Never overwrite the raw data. Work on a copy.
- Check totals against the original.
- Add a one-line note when you make an assumption.