Excel practice data: free datasets with tasks and answers

It is hard to get good at Excel on a sheet with five rows. The real problems only show up with a few hundred rows: a range that stops too early, a date stored as text, a condition that catches one row too many. So here are two datasets the size of what you meet at work, with tasks that have an answer key.

One file is a year of sales from an office equipment dealer, with 240 orders. The other is an employee list with 60 people in five departments. Both files have a sheet of tasks, and under each task on this page you can see the answer and a formula that gives it. Solve the task yourself first, then look.

Sales data: 240 orders from a full year

The Sales sheet has one row per order and nine columns:

The data is clean on purpose: one header row, no blank rows, dates as real dates and numbers as real numbers. That way you can focus on the formulas. If you want to practice cleanup, the guide to cleaning data in Excel is the place to start.

How to approach a task

A task on hundreds of rows gets easier when you break it down before you type anything. Ask yourself three things:

1. Which column do you calculate on? Revenue, units or just the number of rows. That decides whether you need SUM, AVERAGE or COUNT.
2. Which rows count? Each condition in the question becomes a pair of a range and a criterion. "Furniture in the East region" is two conditions.
3. Does the answer need rounding or a particular format? An average with eight decimals or a share of 0.435 is correct, but it is not what the task asks for.

Once you have answered those three questions, the formula is almost written. Then sanity-check the result: is the revenue for one region roughly a quarter of the total, and is the number of orders in a quarter roughly a quarter of 240? An answer that is ten times too big is usually a wrong range, not a math mistake.

How to use the files

Open the file and go to the Tasks sheet. Each task has a number, a question and an empty cell for your answer. Write the formula on the data sheet, in an empty column to the right of the data, or on a new sheet, and type the result next to the task. Then look up the answer on this page.

Save a copy of the file before you start. That way you can sort, delete and change things freely and always start over with the clean data. The answers on this page only match the data as it is in the file, so check your answers before you start editing rows.

The data rows run from 2 to 241 in the sales file and from 2 to 61 in the employee file. The answers on this page use the exact ranges, like I2:I241, because they are the easiest to read. You can also use whole columns like I:I, and then the formula keeps working when you add rows. SUM and SUMIF skip the header, but COUNTA counts it, so subtract 1 there.

The header row has filter buttons, and it stays in view when you scroll down. Use the filters to check an answer by eye: filter on the North region, select the Revenue column and read the sum on the status bar at the bottom. It should match your formula.

Formula tasks on the sales data

The first tasks need one function each. Later ones combine two conditions, work with dates and round the result. The tasks are in the same order as in the file.

Totals with one condition

SUMIF is the function you will use most on data like this. It takes three arguments: where to look, what to look for and what to add up. Revenue in the North region is =SUMIF(C2:C241,"North",I2:I241). The two ranges should be the same length and start on the same row, or the formula adds up the wrong rows.

COUNTIF counts rows instead of adding numbers, and it only needs two arguments. The criterion can also be a comparison in quotes: =COUNTIF(I2:I241,">10000") counts orders over 10,000. Leave out the thousands separator in the criterion.

Two conditions at once

SUMIFS takes as many conditions as you like, but the order is different from SUMIF: the range to add up comes first, followed by pairs of range and criterion. Furniture sold in the East region is =SUMIFS(I2:I241,F2:F241,"Furniture",C2:C241,"East"). A row only counts when both conditions match.

COUNTIFS works the same way, just without a range to add up. Practice both in SUMIFS with two conditions and COUNTIFS with two conditions.

Dates as conditions

Revenue in March is every order from March 1 up to, but not including, April 1. That is two conditions on the same column:

=SUMIFS(I2:I241,B2:B241,">="&DATE(2025,3,1),B2:B241,"<"&DATE(2025,4,1))

The comparison goes in quotes, and the date is joined on with &. DATE builds the date from year, month and day, so the formula works however dates are displayed on your computer. Use "less than the first of the next month" rather than "less than or equal to the last day of the month". That way you never have to remember how many days a month has. More on working with dates in the guide to Excel dates.

Averages and rounding

AVERAGE gives the average with all its decimals. The tasks ask for a rounded number, so wrap the average in ROUND: =ROUND(AVERAGE(I2:I241),0). The second argument is the number of decimals. Don't just format the cell to show fewer decimals. The number would look rounded, but every formula that uses it would still calculate with the decimals.

AVERAGEIF is the average of the rows that match a criterion, with the same three arguments as SUMIF. Try it in Average one group with AVERAGEIF.

Look up an order

The order ID is in the first column, so VLOOKUP can find an order directly: =VLOOKUP(1150,A2:I241,5,FALSE) looks for 1150 in column A and returns the value from the fifth column of the range, which is the product. FALSE means exact match, and that is what you want almost every time. XLOOKUP does the same without counting columns. The differences are explained in the guide to VLOOKUP, XLOOKUP and INDEX/MATCH.

Build a monthly summary

Once you have solved the tasks, try pulling it all together in a small summary with one row per month. Type the first day of each month in column K, from Jan 1, 2025 in K2 to Dec 1, 2025 in K13. Next to it, add up the revenue for the month:

=SUMIFS($I$2:$I$241,$B$2:$B$241,">="&K2,$B$2:$B$241,"<"&EDATE(K2,1))

EDATE returns the same day one month later, so the formula takes every order from the first of the month up to the first of the next. The ranges are locked with $, while K2 has to move. Fill the formula down and you have twelve totals that add up to the revenue for the year. Check that with a SUM under the table.

Add columns for the number of orders and the average order per month with COUNTIFS and AVERAGEIFS, and make a column chart of the revenue. That gives you the same thing a pivot table would, but with formulas you wrote yourself that update when the data changes.

Employee data: 60 people in five departments

The Employees sheet has one row per person and nine columns:

The names are made up, and several people can share a first name. The employee ID is the only thing that is unique, so that is what you look up.

The file is smaller than the sales data, but it has different kinds of columns. There is text that needs joining, dates going back many years, and salaries where the median and the average are not the same.

Formula tasks on the employee data

Text with wildcards

Every manager title in the file ends in "manager": sales manager, finance manager, IT manager and so on. An asterisk in a criterion stands for any text, so =COUNTIF(E2:E61,"*manager") counts every title that ends in manager. "manager*" would count titles that start with manager, and "*manager*" titles that contain the word anywhere. Upper and lower case do not matter in a criterion.

Join text together

First and last names are in separate columns. A lookup returns one value, so to get the full name you do two lookups and join them with & and a space:

=VLOOKUP(1025,A2:I61,2,FALSE)&" "&VLOOKUP(1025,A2:I61,3,FALSE)

Forget the space in quotes and the names run together.

Median or average

The average salary is pulled up by the few managers with high salaries. The median is the salary in the middle when all salaries are in order, and a few high numbers do not move it. Compare AVERAGE and MEDIAN on the Annual salary column and see how big the gap is. That is why salary statistics usually use the median.

Shares

The share working part time is the number of No values under Full-time divided by the number of employees. COUNTA counts cells that are not empty, so it counts employees whatever the column holds. Format the result as a percentage, or multiply by 100 if you want a plain number. The guide to calculating percentages in Excel has more examples.

Years of service and raises

The start date lets you work out years of service. DATEDIF counts whole years between two dates: =DATEDIF(F2,DATE(2025,12,31),"y") gives how many full years the person in row 2 has been employed at the end of 2025. The function does not show up in the list as you type formulas in Excel, but it works.

Try a what-if question too: what does it cost if everyone gets a 3% raise? Put the rate in a cell of its own, such as L1, and work out the new payroll with =SUM(G2:G61)*(1+L1). With the rate in its own cell, you can try 2% or 4% without touching the formula.

Practice without an answer key

Not everything in Excel is a formula. These exercises don't have a single number as the answer, but they train what you do every day with data like this:

• Sort the sales data by revenue with the largest order at the top, then sort by region and date at the same time under Data, Sort.
• Filter on one salesperson and one quarter, and read the row count on the status bar.
• Highlight every order over 10,000 with conditional formatting, and color the rows from the North region with a formula. See how in the guide to conditional formatting.
• Build a summary table with the regions in one column and SUMIF next to it so you can fill the formula down. Lock the ranges with $: =SUMIF($C$2:$C$241,K2,$I$2:$I$241). Practice it in Total per region, ready to fill down.
• Make a chart of revenue per month, using a summary table or a pivot table as the source. The guide to charts shows which type fits.
• Turn the data into a table with Ctrl+T, and try writing formulas with column names instead of ranges. A table grows by itself when you add rows.
• Find the three largest orders with LARGE, then look up their order IDs.
• Find the salesperson with the most orders by building a small table of salespeople with COUNTIF. Sort the table afterward.
• Find the employees who have been with the company for more than ten years with a filter on the start date.

Pivot tables on the sales data

The sales data is built for pivot tables: there are categories to group by, dates to roll up into months and quarters, and numbers to add up. The Tasks sheet in the sales file has 15 more tasks that you solve with a pivot table instead of formulas. They are listed with answers and the right field layout in the pivot table practice.

It is good practice to solve the same task both ways. A pivot table is fastest when you want to see many totals at once. A formula is best when the number has to sit in a specific cell and update itself. If you get two different answers, one of the methods has a condition or a range that is off, and that is often where you learn the most.

When an answer doesn't match

These are the mistakes that most often give a wrong number on data like this:

• Ranges that don't line up. In SUMIFS, every range must be the same size, or the formula returns #VALUE!. SUMIF gives no error, but if the ranges don't start on the same row, it adds up the wrong rows.
• A typo in the criterion. "Est" instead of "East" returns 0 without an error message. Put the criterion in a cell and point to it, or copy the text from the data.
• A date typed as text. ">=3/1/2025" can be read differently depending on your computer's settings. Use DATE.
• The wrong column in a lookup. The column number in VLOOKUP counts from the first column of the range, not from column A of the sheet.
• Rounding too early. Round only the final result. If you round each row first, the total can drift.
• Extra spaces. Data copied from another system often has a trailing space, so "North " is not the same as "North". The data in these files is clean, but it is worth knowing about when you work with your own.

Practice data and Excel tests

Many employers test Excel with exactly this kind of task: a file with a few hundred rows of sales or employees and a handful of questions to answer within a time limit. The questions look like the ones here: a total for one region, a count with two conditions, a lookup and a share. What separates the fast from the slow is rarely the formulas. It is whether they know the data, check their answers and lock their ranges. Solve the tasks here against the clock and see how long it takes you. More on what gets tested in the guide to Excel for job interviews.

Practice data in Google Sheets

Both files work in Google Sheets. Upload the file to Google Drive and open it with Google Sheets. Every task can be solved with the same formulas. The filter buttons come along, and you make pivot tables under Insert. See the differences in Google Sheets vs. Excel.

In short

Practice on data that looks like the real thing: hundreds of rows, several categories and dates. The sales file trains SUMIF, SUMIFS, COUNTIF, dates as conditions, averages, rounding and lookups. The employee file trains wildcards, joining text, the median and shares. Solve the task yourself, check the answer on this page, and find the mistake if the number doesn't match. After that, the sales data is ready for pivot tables.

To practice one formula at a time on a small sheet, start with the exercises below. They check your answer right away and show a hint when the formula is wrong. The first exercise in each topic is free.