How to Prepare for an Excel Test in One Day

If you have an Excel test in one day, the best plan is not to learn everything. It's to find your gaps fast, then spend the day typing the handful of skills that most job tests check. This guide gives you an hour-by-hour plan for the day before, what to skip, and what to do in the test itself. It's written for job seekers who use Excel a little and need to be ready by tomorrow.

Can you really prepare for an Excel test in one day?#

Yes, if you're realistic about the goal. One day is enough to get solid on the basics and to practise the formulas tests ask for most. It isn't enough to become an advanced user, so don't try to learn macros, Power Query and dynamic arrays tonight.

Two things matter more than anything else:

• Practise by doing. Reading about VLOOKUP feels like learning, but you only find your mistakes when you type it. Spend most of the day with a spreadsheet open.
• Know the test format. Read the invitation again. Note the time limit, the number of questions, and whether the test runs in real Excel or in a simulation in your browser.

Hour 1: find your level#

Start with a check, not a lesson. In a few minutes, the 12-question Excel level test shows you which topics you already know and which ones you don't. Write down every topic you missed or guessed.

If you'd rather work through basics in a sheet, the basics skill check asks you to fix, calculate, fill and format a supplier order on your own, with no hints. Anything you can't do quickly goes to the top of today's list. The Excel skills checklist is a good reference for what each level usually includes.

Hours 2 to 3: core formulas and $ references#

Almost every Excel test starts here, so make these automatic:

The $ sign is the one that catches people most. When a formula works in the first row but gives nonsense further down, a missing $ is usually why. Press F4 on Windows (⌘T on Mac) to add it while editing.

Hours 4 to 5: IF, SUMIF and COUNTIF#

Next, learn the logic and conditional formulas. These turn up in most tests beyond the very basic level.

IF returns one value when a test is true and another when it's false:

=IF(C2>=5000,"Bonus","No bonus")

SUMIF adds the numbers that match one condition, and COUNTIF counts them:

=SUMIF(B2:B20,"East",D2:D20)
=COUNTIF(B2:B20,"East")

Watch the order in SUMIF: the range to check, then the condition, then the numbers to add. Text conditions need double quotes. The IF function guide and the SUMIF function guide have the syntax and examples to copy. Practise each one at least five times on different data until you don't need to look anything up.

Hour 6: VLOOKUP#

VLOOKUP finds a value in the first column of a table and returns a value from the same row. It is one of the most tested functions in job assessments, so give it a full hour.

=VLOOKUP(E1,A2:C7,3,FALSE)

Read it as: look for E1, in the table A2:C7, return column 3, exact match. Learn the three errors people make most:

1. Leaving out FALSE. Without it, VLOOKUP does an approximate match and can return the wrong row.
2. Counting columns wrong. The column number counts from the left edge of the table, not from column A of the sheet.
3. The lookup value isn't in the first column. VLOOKUP can't look to the left. If you have Excel 2021 or Microsoft 365, XLOOKUP can.

The VLOOKUP function guide covers #N/A errors and approximate matches if you have time.

Hour 7: sort, filter and a pivot table#

These are menu skills, so practise the clicks, not just the idea:

• Sort by one column, then by two (Data > Sort).
• Filter with the arrows (Windows: Ctrl+Shift+L; Mac: ⌘⇧F) and filter by a number condition such as "greater than 5,000".
• Pivot table: Insert > PivotTable, then drag a category to Rows and a number to Values. Change Sum to Count or Average.

If you have extra time, add one chart: select the data, Insert, choose a column chart, add a title.

Evening: one timed mock test#

Finish the day with a test under real time pressure. Pick a mock test that matches your role, such as the basic Excel test for admin and entry-level jobs or the Excel screening quiz for a quick check. The guide to online Excel mock tests explains how to choose.

Then do the most useful thing of the day: review every mistake and redo it without looking at the answer. If your employer has named the test platform, read the matching prep page, such as the Indeed Excel assessment guide or the Kenexa Prove It Excel test guide. These describe the format and the skills to practise; ExcelReady doesn't have the providers' real questions and isn't affiliated with them.

Stop by your usual bedtime. Sleep helps you remember what you practised.

On the day: how to use your time#

Your preparation only counts if you finish the test. A few habits help:

• Read the whole question before typing. Tests often ask for a specific function or cell.
• Skip and come back if a question takes more than a couple of minutes, if the test allows it.
• Check the result looks sensible. A total that's smaller than one of its parts means a wrong range.
• Don't panic about one hard question. One miss rarely decides the result.

The guide to managing your time in an Excel test goes deeper, and the practice tasks to do before a test give you more drills if your test is a few days away. For a longer plan, read how to learn Excel fast for a job.

Practise it#

Start now with the exercise on this page: a VLOOKUP exactly like a test question. Then practise VLOOKUP in a live spreadsheet, practise IF with real data, and run through the basics skill check. Each lesson checks your formula instantly, so you find mistakes today instead of in the test.