Excel Practical Test Questions
Hands-on tasks commonly set in Excel skill tests.
19 questions · Updated Nov 2026
Many employers set a hands-on Excel practical test instead of (or alongside) verbal questions. You are given a dataset and asked to clean, analyse, summarise and present it under time pressure.
These Excel practical test questions describe the task and the exact approach/formulas an interviewer expects to see.
Beginner Beginner Level Questions
Select the range (or Table), go to Data → Remove Duplicates, choose the columns that define a duplicate, and confirm. To keep the original intact, use =UNIQUE(range) in Excel 365 to spill a de-duplicated copy.
Fastest: select the column and use Data → Text to Columns → Delimited → Space. With formulas: First = =LEFT(A2, FIND(" ",A2)-1), Last = =MID(A2, FIND(" ",A2)+1, 100). In 365 you can also use TEXTBEFORE/TEXTAFTER.
Select the range, go to Home → Conditional Formatting → Highlight Cells Rules → Greater Than, enter 1000, and pick a red fill. This updates automatically as values change.
Use =COUNTIF(OrdersCustomerColumn, A2) next to a unique customer list, or build a PivotTable with Customer in Rows and Order ID in Values (set to Count). The PivotTable scales better for large data.
Select the range, click the warning triangle and choose "Convert to Number", or multiply by 1 using Paste Special, or use =VALUE(A2). Text numbers often cause SUM to return 0, so this is a common test.
Intermediate Intermediate Level Questions
Use =XLOOKUP(A2, PriceList[Product], PriceList[Price], "Not found"), or in older Excel =VLOOKUP(A2, $E$2:$F$100, 2, FALSE). Lock the lookup table with absolute references so it copies down correctly.
Insert a PivotTable: put Region in Rows, Month in Columns, and Sales in Values (summarised by Sum). Alternatively use =SUMIFS(Sales, Region, "East", Month, "Jan") in a summary grid.
Add a helper column with =IF(B2>=PERCENTILE($B$2:$B$100,0.9),"Top 10%",""), or use Conditional Formatting → Top/Bottom Rules → Top 10% directly on the revenue column.
Use =TRIM(A2) to remove extra spaces, =PROPER(A2)/=UPPER/=LOWER for case, and =CLEAN(A2) to strip non-printable characters. Combine as =PROPER(TRIM(A2)), then paste as values.
Use =MID(A2, FIND("@",A2)+1, 100), or in Excel 365 =TEXTAFTER(A2,"@"). This returns everything after the @ symbol.
Add a column =IF(ThisMonth>LastMonth,"Up","Down"), or compute growth =(ThisMonth-LastMonth)/LastMonth wrapped in IFERROR, then apply conditional formatting (green for positive, red for negative).
In Excel 365: =SORT(FILTER(...),,-1) then TAKE(...,5), or =LARGE(SalesRange,{1;2;3;4;5}) with INDEX/MATCH to pull names. A PivotTable with a Top 5 value filter is the quickest manual route.
Advanced Advanced Level Questions
Import the CSV with Power Query, apply cleaning steps (types, trimming, renaming), load to the model, and build PivotTables/charts on top. Each month, drop in the new file and hit Refresh — the whole report rebuilds without manual edits.
Use a Slicer connected to PivotTables/charts, or build a data-validation drop-down feeding FILTER/SUMIFS formulas that drive charts. Reference spilled arrays with # so charts resize with the filtered data.
Load both into Power Query, align keys/types, do a full outer merge, expand, and add a column comparing the amounts; keep rows where they differ or exist on only one side. This produces a clean exception list that refreshes each time.
Running total: =SUM($B$2:B2) copied down. MoM % change: =(B3-B2)/B2 formatted as a percentage, wrapped in IFERROR for the first row. In a PivotTable, use "Show Values As → Running Total" and "% Difference From".
Prefer a Table (auto-expands). If a formula is required: =OFFSET($A$1,0,0,COUNTA($A:$A),1) or the non-volatile =INDEX($A:$A,1):INDEX($A:$A,COUNTA($A:$A)). Use it as a chart or formula source so it grows automatically.
Load the table into Power Query, select the month columns, and choose Unpivot Columns. This turns Jan/Feb/Mar columns into two columns (Month, Value), which is the correct shape for PivotTables and analysis.
With a due-date column, use =IF(AND(ISBLANK(PaidDate), TODAY()>DueDate), "Overdue", "") and highlight with conditional formatting. Add =TODAY()-DueDate to show days overdue for ageing analysis.
