Excel customer data: free practice file with tasks and answers

A customer list is one of the first things anyone in sales, marketing or customer service gets handed in Excel. The questions are always the same kind: how many customers do we have in this city, what does a business customer spend, who is customer 2077, how many of them actually read the newsletter? Here is a customer list you can practice those questions on, with an answer key.

The file is an export from a small online shop: 150 customers with name, email, city, segment, signup date, number of orders and total spend. It mixes text, numbers and dates, which is exactly what makes customer data good practice. You count with conditions, look up one customer, pull text apart and put it back together. Solve the tasks in the file first, then check your answers on this page.

Customer data: 150 customers of an online shop

The Customers sheet has one row per customer and ten columns:

The names are made up, and several customers share a first and last name. The customer ID is the only thing that is unique, so that is what you look up. Private customers mostly use Gmail, Outlook and the like, while business customers have an address at their company's own domain. That difference is useful in the text tasks.

Some customers have signed up but never ordered. They have 0 in Orders and 0 in Total spent. Keep them in mind when you work out averages, because they pull an average down.

How to approach a task

Customer questions usually hide a filter. "What does a business customer spend on average?" is an average with one condition. "How many business customers are in Chicago?" is a count with two. Before you type anything, ask yourself:

1. Do you count, add up or average? That decides whether you need COUNTIF, SUMIF or AVERAGEIF.
2. Which customers count? Each condition in the question becomes a pair of a range and a criterion.
3. Is it about one customer? Then it is a lookup, and you start with the customer ID.

Once you know that, the formula is half written. Then sanity-check the answer: Chicago is the biggest city in the file, so it should have more customers than Boston.

How to use the file

Open the file and go to the Tasks sheet. Each task has a number, a question and an empty cell for your answer. Write your formulas on the Customers sheet, in an empty column to the right of the data, and type the result next to the task. Then compare it with the answer on this page.

The data runs from row 2 to row 151. The answers use exact ranges like E2:E151. Save a copy of the file before you sort or delete anything, because the answers only match the data as it is in the file. The header row has filter buttons, so you can check a count by filtering and reading the row count on the status bar.

Formula tasks on the customer data

The tasks go from simple counts to lookups, text and dates. They are in the same order as in the file.

Count customers with one and two conditions

The number of customers in one city is a plain COUNTIF: =COUNTIF(E2:E151,"Denver"). Upper and lower case do not matter in the criterion, so "denver" gives the same answer.

Add a second condition and you need COUNTIFS. Business customers in Chicago are rows where the segment is Business and the city is Chicago:

=COUNTIFS(F2:F151,"Business",E2:E151,"Chicago")

Each condition is a pair of a range and a criterion, and a row only counts when all pairs match. The order of the pairs does not matter. Practice it in COUNTIFS with two conditions.

Average spend per segment

AVERAGEIF works like SUMIF: where to look, what to look for and what to average. The average spend of business customers is:

=ROUND(AVERAGEIF(F2:F151,"Business",I2:I151),0)

The ROUND around it gives a whole number. Compare the result with the same formula for Private, and you see why most shops treat the two segments differently. Practice it in Average one group with AVERAGEIF.

There is a catch with averages on customer data. The customers who never ordered have 0 in Total spent, and they are part of the average. If you want the average of paying customers only, add a condition with AVERAGEIFS: =AVERAGEIFS(I2:I151,F2:F151,"Business",H2:H151,">0"). Which one is right depends on the question. Write down which one you used.

Look up a customer by ID

XLOOKUP finds a customer by ID and returns any column you like. The email address of customer 2077 is:

=XLOOKUP(2077,A2:A151,D2:D151)

The first argument is what to find, the second is where to find it and the third is what to return. Swap D2:D151 for E2:E151 and you get the city. The ID is a number in the file, so you look it up as a number without quotes. If the IDs had been stored as text, you would need "2077" in quotes, and a number would not match. That is one of the most common reasons for #N/A in real customer lists. Practice the lookup in Look up a price with XLOOKUP.

In a real sheet you rarely type the ID into the formula. Put it in a cell of its own, such as L1, and point to it: =XLOOKUP(L1,A2:A151,D2:D151). Now anyone can type a new ID in L1 and see the email change. Add the same lookup for name, city and spend next to it, and you have a small customer card that looks up everything about one customer from a single cell.

If someone types an ID that doesn't exist, the card shows #N/A in every field. Give XLOOKUP a fourth argument and it says something friendlier: =XLOOKUP(L1,A2:A151,D2:D151,"Unknown customer"). The fourth argument is only used when the ID is missing, so every real lookup works as before. Practice it in XLOOKUP with a not-found value, then type an ID above 2150 in L1 to see it work.

Join the first and last name

A lookup returns one value. To get a full name, you do two lookups and join them with & and a space in quotes:

=XLOOKUP(2033,A2:A151,B2:B151)&" "&XLOOKUP(2033,A2:A151,C2:C151)

To build a full name column for every customer, it is simpler: =B2&" "&C2 in a new column, filled down. Forget the space and the names run together. Practice it in Join first and last name with &.

Take an email address apart

Everything after the @ in an email address is the domain. FIND gives the position of the @, and MID takes the text from the next character:

=MID(D2,FIND("@",D2)+1,50)

The 50 is just a number large enough to reach the end of any address. MID stops at the end of the text, so it does no harm to ask for too many characters. FIND is case-sensitive and returns #VALUE! when the character is missing, which is a quick way to spot an email address without an @. Practice it in Find the @ in an email with FIND.

The part before the @ works the same way with LEFT: =LEFT(D2,FIND("@",D2)-1) gives everything up to the @, minus the @ itself. Try it in Take the prefix with LEFT.

Count email domains with a wildcard

You don't need to take every address apart to count Gmail users. An asterisk in a COUNTIF criterion stands for any text, so this counts every address that ends in @gmail.com:

=COUNTIF(D2:D151,"*@gmail.com")

Swap the domain to count Outlook or Hotmail. Put the asterisk at both ends, as in "gmail", and you count addresses that contain the word anywhere. Wildcards work in COUNTIF, SUMIF, AVERAGEIF and their IFS versions, and in XLOOKUP if you ask for it.

Dates as conditions

The customers who signed up in 2024 are the rows with a signup date from Jan 1, 2024 up to, but not including, Jan 1, 2025. That is two conditions on the same column:

=COUNTIFS(G2:G151,">="&DATE(2024,1,1),G2:G151,"<"&DATE(2025,1,1))

The comparison goes in quotes and the date is joined on with &. "Less than the first of next year" is safer than "less than or equal to Dec 31", because it also works if a date has a time in it. More on dates in the guide to Excel dates.

The biggest customer and the average order

To find which customer has spent the most, find the highest spend with MAX and its position with MATCH, then return the ID from the same position with INDEX:

=INDEX(A2:A151,MATCH(MAX(I2:I151),I2:I151,0))

The average order value is total spend divided by total orders, not the average of each customer's average. =ROUND(SUM(I2:I151)/SUM(H2:H151),0) gives it in one go. The two numbers differ because customers with many orders weigh more in the first one.

Shares and big customers

The share of customers who get the newsletter is the number of Yes values divided by the number of customers:

=ROUND(COUNTIF(J2:J151,"Yes")/COUNTA(A2:A151),3)

Format the cell as a percentage to read it as one. The guide to calculating percentages in Excel has more on shares.

SUMIF with a number condition adds up the spend of customers who match it. Customers with 10 or more orders are the loyal core of most shops:

=SUMIF(H2:H151,">=10",I2:I151)

Divide it by the total spend and you have the share of revenue that comes from them. Practice the condition in SUMIF with a number condition.

Sort customers into groups with IFS

Many shops split their customers into tiers by spend, so the best customers get a call and the rest get an email. IFS checks the conditions in order and returns the value for the first one that is true:

=IFS(I2>20000,"Gold",I2>5000,"Silver",TRUE,"Bronze")

Put the formula in a new column next to row 2 and fill it down. The order matters: if you check for more than 5,000 first, every Gold customer lands in Silver, because 25,000 is also more than 5,000. Start with the strictest condition. The TRUE at the end catches everyone who is left. Practice the structure in Grades with IFS.

When the column is filled, count each tier with COUNTIF and add up the spend per tier with SUMIF. In most customer lists a small Gold group stands for a large part of the revenue. That is the number a sales manager wants to see first.

Build a customer summary

Once you have solved the tasks, pull it together in a small summary. Type the five cities in column L, from L2 to L6. Next to them, count the customers, add up the spend and work out the average:

=COUNTIF($E$2:$E$151,L2)

=SUMIF($E$2:$E$151,L2,$I$2:$I$151)

The ranges are locked with $, while L2 has to move when you fill down. Add a column that splits the count into private and business customers with COUNTIFS, and a row at the bottom with the totals. The totals should match the number of rows in the file and the sum of Total spent. If they don't, a city is spelled differently somewhere.

A summary like this is what a pivot table gives you in a few clicks. Building it with formulas once teaches you what the pivot table does behind the scenes, and the formulas update when the data changes.

Clean before you count

The customer data in this file is tidy. Real exports rarely are. The same customer appears twice with two spellings, a city has a trailing space, an ID is stored as text in half the rows. Every one of those gives a wrong count without an error message, because "Denver " and "Denver" are two different cities to COUNTIF.

So before you trust a count on a real list, check it: filter the city column and look at the list of values. Five cities should show five entries, not seven. If you want to practice fixing that kind of mess, the messy data file has all of it, with tasks and answers.

Customer lists also show up in Excel tests for jobs in sales, marketing and customer service. The questions look like the ones here: a count with two conditions, an average per group, a lookup on an ID and a share. More in the guide to Excel for job interviews.

Practice without an answer key

These exercises don't have a single number as the answer, but they are what you do with a customer list at work:

• Build a full name column with =B2&" "&C2, and an email domain column with MID and FIND. Then sort by domain.
• Make a table per city with the five cities in one column and COUNTIFS for private and business customers next to it. Lock the ranges with $ so you can fill both directions.
• Sort customers into tiers with IFS: Gold above 20,000 spent, Silver above 5,000 and Bronze for the rest. Then count each tier.
• Find customers who have gone quiet: signed up before 2023 with 0 orders. Filter on both columns, or count them with COUNTIFS.
• Highlight the top ten customers with conditional formatting on the Total spent column. The guide to conditional formatting shows how.
• Build a pivot table with cities in rows, segments in columns and the sum of Total spent as values.

When an answer doesn't match

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

• A number looked up as text. "2077" in quotes does not match the number 2077 in column A. Leave out the quotes.
• Customers with no orders forgotten. They are in every average unless you leave them out on purpose.
• A missing asterisk. "@gmail.com" without the asterisk only counts cells that say exactly @gmail.com, which is none.
• Dates typed as text. ">=1/1/2024" can be read differently on another computer. Use DATE.
• The wrong average. Average spend per customer and average order value are different numbers. Read the question twice.

Customer data in Google Sheets

The file works in Google Sheets. Upload it to Google Drive and open it with Google Sheets. Every task can be solved with the same formulas, and Google Sheets adds SPLIT, which takes an email address apart at the @ in one step. See the differences in Google Sheets vs. Excel.

More practice data

This file is one of several free datasets with tasks and answers:

• Sales and employee data: 240 orders and 60 employees, for SUMIF, COUNTIFS, averages and dates. The sales file also feeds the pivot table practice.
• Inventory data: 80 products with stock and prices, for XLOOKUP and SUMPRODUCT.
• Messy data: 60 orders full of extra spaces, text numbers and duplicates, for cleanup practice.
• Finance data: a year of bank transactions, for income, expenses, results and percentages.

In short

A customer list mixes numbers, text and dates, and that makes it great practice. COUNTIF and COUNTIFS count customers, AVERAGEIF and SUMIF work out spend per group, and XLOOKUP finds one customer by ID. FIND, MID and LEFT take an email address apart, and & puts a name back together. Solve the tasks in the file, check your answers here and work out where it went wrong when a number is off.

To drill 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.