Excel Data Entry Practice Exercises

Excel skills for data entry are learned by doing, not by reading formula syntax. The exercises below use a blank workbook you build yourself, each with a clear expected outcome so you can check your own work. They progress from plain manual entry through validation, cleanup, lookups, and a basic PivotTable — a realistic skill arc for entry-level roles.

Setting up

Open a new blank workbook in Excel (desktop or Excel for the web both work). You don't need a template — building the layout yourself is part of the practice.

Exercise 1: Manual entry with formatting discipline

1. 1

   Create a header row

   Row 1: Order ID, Customer, Product, Quantity, Unit Price, Order Date.

2. 2

   Freeze the header

   View → Freeze Panes → Freeze Top Row.

3. 3

   Enter 30 rows of sample orders

   Use consistent formatting: Order ID as text (e.g. ORD-0001), dates in one consistent format, prices with two decimal places.

4. 4

   Format the Unit Price column as currency

   Select the column, then Home → Number Format → Currency.

Expected outcome: 30 complete rows, header frozen, prices displaying with a currency symbol and two decimals, no inconsistent date formats.

Exercise 2: Data validation for controlled input

1. 1Add a Status column with header in row 1.
2. 2Select the Status cells for your 30 rows, then Data → Data Validation → List.
3. 3Enter source values: Pending, Shipped, Delivered, Cancelled.
4. 4Fill every row using the dropdown that now appears.
5. 5Attempt to type an unlisted value directly and note the warning Excel shows.

Expected outcome: every Status cell filled via dropdown only, and a confirmed understanding of what an Excel validation warning looks like when you try to bypass it.

Exercise 3: Find and fix deliberate errors

Copy your sheet to a new tab (right-click the tab → Move or Copy → Create a copy). In the copy, deliberately introduce five specific errors: two duplicate rows, one Quantity typed as text instead of a number, one Unit Price with a stray space, and one blank required cell.

1. 1

   Remove duplicates

   Select your range, then Data → Remove Duplicates, and confirm which columns define a duplicate row.

2. 2

   Fix the text-as-number cell

   Excel usually flags this with a small green triangle and an error option to 'Convert to Number' — use it.

3. 3

   Clean the stray space

   Use =TRIM() in a helper column, then paste the result back as values with Paste Special → Values.

4. 4

   Resolve the blank cell

   Decide whether to research the missing value or flag it clearly rather than leaving it ambiguous.

Exercise 4: VLOOKUP across two sheets

Add a new sheet named Products with two columns: Product and Category, listing five to eight products and a category for each. Back on your main sheet, add a Category column and use VLOOKUP to pull the matching category for every order automatically.

• Formula pattern: =VLOOKUP(C2, Products!A:B, 2, FALSE) where C2 holds the product name.
• Copy the formula down the full column and confirm every row resolves to a category rather than showing #N/A.
• If a row shows #N/A, check for a spelling mismatch between the two sheets — this is the most common real-world cause.

Expected outcome: every order row shows a correct category pulled automatically, with no #N/A errors remaining.

Exercise 5: A basic PivotTable summary

1. 1Select your full data range including headers.
2. 2Insert → PivotTable → New Worksheet.
3. 3Drag Category to Rows and Quantity to Values (set to Sum).
4. 4Add Status to Filters and try filtering to only 'Delivered' orders.

Expected outcome: a summary table showing total quantity ordered per category, which updates when you change the Status filter — a first practical taste of turning entered data into a report.

Building a sample you can reference later

Keep your finished workbook, including the deliberately-broken tab and your corrected version. Being able to describe specifically what you fixed and how — duplicate removal, a VLOOKUP mismatch, a text-formatted number — is a concrete example to bring up in an interview even without formal work history.