Do you need an advanced Excel course? Try this skills checklist

“Advanced Excel” can mean very different things. One person needs a monthly report that updates reliably; another needs to combine several exports; a third needs to understand why a lookup returns the wrong customer. Choose training around the work you need to complete, not around a long list of impressive functions.

This is an informal self-check, not a qualification or a prediction of an employer's test. For advanced Excel training in Birmingham or online, identify which task below is both important to your work and difficult to complete independently.

Check the foundations first

Create a table with one header row, consistent date formats and no merged cells within the records. Add a new row, sort by a numeric value and filter by a category. Confirm that complete records stay together and that calculations still include the new row.

If this is unreliable, spending time on tables and references is worthwhile even if you already recognise pivot tables. An advanced report built on inconsistent source data is still inconsistent. Microsoft's table-reference guidance is useful when moving from fixed ranges to named table columns.

Task 1: repair a deliberately messy export

Create six fictional sales records. Give one category an extra space, type one date as plain text and repeat one order identifier. Before calculating anything, find the three problems and decide how each should be handled.

Do not simply delete every repeated value: an order might legitimately contain several line items. First define whether one row means an order, an item or a payment. Keep the original export unchanged and make corrections in a prepared copy so the process can be explained later.

Task 2: make a lookup fail safely

Build a product-price lookup using a unique product code. Test an existing code, a missing code and a duplicated code. Explain what should happen in each case and whether returning the first match is acceptable.

The useful skill is understanding the relationship between the two tables. A price list joined to sales records needs a reliable key. Looking up by a loosely typed product description may be fragile even when the formula itself is valid.

Task 3: summarise without double-counting

Use a fictional dataset with three East-region sales of £40, £60 and £100, and two West-region sales of £80 and £120. Build a pivot summary by region. Each region should total £200 and the overall total should be £400.

Next add a sale of £25 to East, refresh the summary and confirm the new total is £425. If the change is missing, inspect the source range and refresh process. If you want an average order value, calculate total sales divided by the relevant number of orders; do not assume an average of regional averages will give the same answer.

Task 4: build one useful report

Write the decision the reader needs to make at the top of the sheet. Perhaps they need to know which category changed most this month. Show a small comparison table and, only if helpful, one chart with clear units and labels.

Separate an actual value from a target or forecast. Avoid using colour as the only way to distinguish categories. A report should still make sense when printed without colour or explained aloud to a colleague.

Task 5: repeat the process next month

Replace the fictional input with a second dataset that includes a new category and a blank value. Can you update the output without rewriting every formula? Can someone else follow the sequence from import to final checks?

If repeated cleaning is the main difficulty, a structured import workflow may be the next topic to discuss. Power Query, macros and other automation need their own agreed scope and depend on the software and environment; they are not automatically necessary for every learner.

Turn the checklist into a training brief

Record each task as “independent”, “with prompts” or “not yet”, then choose the first task that blocks your real work. Bring the workbook, your Excel version and the desired output to a tutor. A focused brief such as “I need to update this monthly report without broken lookups” is a stronger starting point than “teach me everything advanced”.