“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.
What counts as advanced Excel skills?
For this checklist, advanced means being able to turn imperfect data into a report that another person can update and check. That involves choosing a reliable key, handling missing or duplicate records, building a suitable summary and testing the next refresh. Recognising a formula name alone does not demonstrate those skills.
Mark each of the five tasks below as independent, with prompts or not yet. Write down the evidence: the workbook you made, the expected answer and the check you used. These labels help plan learning; they are not a certificate or an employer's pass mark.
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.
Practical guide · Updated
Test the report with a dataset designed to break it
A workbook that handles a tidy sample may fail when next month’s export contains an unexpected category or no records. A deliberate edge-case test is a useful advanced skill.
A practical example
Create a copy with one blank date, a duplicated identifier, an unfamiliar category and an empty reporting period. Decide in advance what the report should display for each case. Refresh or recalculate and compare the output with those expectations.
Record whether the workbook flags the problem, handles it intentionally or produces a misleading result. Do not use the exercise to change the live source. A reliable handover should explain the checks and limitations, so another person knows when an output needs investigation rather than assuming a successful refresh means the data is correct.
Write a reconciliation check outside the main report
For a practice report, calculate a simple independent total from the source data and compare it with the report’s total under the same stated filters. Then introduce a duplicated row and confirm that your checking process notices the change.
Do not build both checks from the same mistaken summary and assume agreement proves correctness. Record the assumptions, excluded rows and expected result for the test dataset. This assesses the reliability of the workflow, not the number of advanced functions in the workbook, and should be practised with fictional data.
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”.
Your next step: Excel training in Birmingham explains the relevant support. Ask about a lesson when you are ready to discuss your needs.