Scribd
Upload
111 views13 pages

Excel Data Entry Practice Exercises

The document contains Excel data entry practice exercises focused on sales data, including item details, pricing, and calculations for total sales, discounts, and net amounts. It outlines exercises for finding total sales, the most popular item by quantity, and the top three items by revenue using various Excel functions. Additionally, it provides reference tables for discount and sales tax amounts.

Uploaded by

varmankrishna64
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd
111 views13 pages

Excel Data Entry Practice Exercises

The document contains Excel data entry practice exercises focused on sales data, including item details, pricing, and calculations for total sales, discounts, and net amounts. It outlines exercises for finding total sales, the most popular item by quantity, and the top three items by revenue using various Excel functions. Additionally, it provides reference tables for discount and sales tax amounts.

Uploaded by

varmankrishna64
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd

Excel Data Entry Practice Exercises PDF

Date Item Price Unit Amount Discount Net Amount


28-Oct-2025 Pencil $0.27 26
28-Oct-2022 Gel Pen $1.40 6
28-Oct-2022 Eraser $1.12 13
28-Oct-2022 Marker $0.77 25
28-Oct-2022 Calculator $7.39 30
28-Oct-2022 Highlighter $1.02 5
28-Oct-2022 Stapler $5.59 6
28-Oct-2025 Sticky Notes $0.47 16
28-Oct-2022 Notebook $3.03 26
28-Oct-2022 Clipboard $2.49 21
29-Oct-2022 Ball Point Pen $0.30 26
29-Oct-2022 Gel Pen $1.40 22
29-Oct-2022 Eraser $1.12 29
29-Oct-2022 Marker $0.77 20
29-Oct-2022 Calculator $7.39 9
29-Oct-2022 Highlighter $1.02 24
29-Oct-2022 Glue $5.49 25
29-Oct-2022 Sticky Notes $0.47 15
29-Oct-2022 Notebook $3.03 20
29-Oct-2022 Pencil $0.27 24

02. Total Sales

Day Amount Exercise 02 Finding Total Sales: Your task is


Day 01 $10,000.00 amount.
Day 02 You can use the SUMIF function to get the
second value.
Total

03. Most Popular Item (By Quantity)


Exercise 03
Product name and th
You can use
find the row
Additionally
Exercise 03
name and th
Quantity You can use
find the row
Additionally
04. Top 3 Items (By Revenue)

Order Item
1 Exercise 04 Top 3
2 column.
3
You will need to co
output.
Sales Tax Total

Exercise 01 Filling the Dataset: The fast task requires to fill 4 co


and 5 columns by using formulas.
Firstly, you will need to type these values in the first 4 columns
(alignment, font size, font color, background color, etc.) helps w
visualization.
Secondly, you will find the amount by multiplying the price by t
Thirdly, find the discount amount. Less than $1 is a 3% discoun
1, it is 5%. You can use the IF function to do so.
Fourthly, subtract the previous two values to get the net amou
Then, the sales tax is 10% for all products.
After that, add the sales tax with the net amount to calcu
amount.
Finally, add conditional formatting to the top 3 revenue.

tal Sales: Your task is to find the day wise sales and total sales
F function to get the first value and the SUM function for the

Exercise 03 Most Popular Item (By Quantity): In this exercise, you will need to find the highest prod
name and the amount of it.
You can use the MAX function to find the maximum value. Then, combine it with the MATCH functio
find the row number. Finally, use the INDEX function to return the most popular item.
Additionally, using the MAX function, you can find the quantity value.
Exercise 03 Most Popular Item (By Quantity): In this exercise, you will need to find the highest prod
name and the amount of it.
You can use the MAX function to find the maximum value. Then, combine it with the MATCH functio
find the row number. Finally, use the INDEX function to return the most popular item.
Additionally, using the MAX function, you can find the quantity value.

Exercise 04 Top 3 Items (By Revenue): Your task is to find the top 3 items from the total
column.
You will need to combine the LARGE, MATCH, and INDEX functions to return the desired
output.
task requires to fill 4 columns by typing

s in the first 4 columns. The formatting


und color, etc.) helps with the

ultiplying the price by the unit sold.


han $1 is a 3% discount and for more than
do so.
es to get the net amount.
s.
he net amount to calculate the total

g to the top 3 revenue.

o find the highest product


with the MATCH function to
ar item.
o find the highest product

with the MATCH function to


ar item.

otal
ired
Reference Tables

Discount Amount Sales Tax Amount

Price Range Amount Amount


Less Than $1 3%
Flat Amount 10%
More Than $1 5%
Date Item Price Unit Amount Discount
28-Oct-2022 Pencil $0.27 26 $7.02 $0.21
28-Oct-2022 Gel Pen $1.40 6 $8.40 $0.42
28-Oct-2022 Eraser $1.12 13 $14.56 $0.73
28-Oct-2022 Marker $0.77 25 $19.25 $0.58
28-Oct-2022 Calculator $7.39 30 $221.70 $11.09
28-Oct-2022 Highlighter $1.02 5 $5.10 $0.26
28-Oct-2022 Stapler $5.59 6 $33.54 $1.68
28-Oct-2022 Sticky Notes $0.47 16 $7.52 $0.23
28-Oct-2022 Notebook $3.03 26 $78.78 $3.94
28-Oct-2022 Clipboard $2.49 21 $52.29 $2.61
29-Oct-2022 Ball Point Pen $0.30 26 $7.80 $0.23
29-Oct-2022 Gel Pen $1.40 22 $30.80 $1.54
29-Oct-2022 Eraser $1.12 29 $32.48 $1.62
29-Oct-2022 Marker $0.77 20 $15.40 $0.46
29-Oct-2022 Calculator $7.39 9 $66.51 $3.33
29-Oct-2022 Highlighter $1.02 24 $24.48 $1.22
29-Oct-2022 Glue $5.49 25 $137.25 $6.86
29-Oct-2022 Sticky Notes $0.47 15 $7.05 $0.21
29-Oct-2022 Notebook $3.03 20 $60.60 $3.03
29-Oct-2022 Pencil $0.27 24 $6.48 $0.19

01. Total Sales

Day Amount
Day 01 $469.07 Exercise 02 Finding Total Sales: Your task is to fi
Day 02 $407.16
You can use the SUMIF function to get the first
Total $876.23

02. Most Popular Item (By Quantity)


Exercise 03 Most Popular Item
Product Calculator amount of it.
Quantity 30 You can use the MAX function
number. Finally, use the INDEX
Additionally, using the MAX fu
amount of it.
You can use the MAX function
number. Finally, use the INDEX
Additionally, using the MAX fu
04. Top 3 Items (By Revenue)

Order Item
1 #VALUE!
Exercise 04 Top 3 Items (By Revenue
2 #VALUE! You will need to combine the LARGE,
3 #VALUE!
Net Amount Sales Tax Total
$6.81 $0.68 $7.49
$7.98 $0.80 $8.78
$13.83 $1.38 $15.22
$18.67 $1.87 $20.54
Exercise 01 Filling the Dataset: The fast task r
$210.62 $21.06 $231.68 5 columns by using formulas.
$4.85 $0.48 $5.33 Firstly, you will need to type these values in th
(alignment, font size, font color, background c
$31.86 $3.19 $35.05
Moreover, there should be a dropdown list for
$7.29 $0.73 $8.02 Secondly, you will find the amount by multiply
$74.84 $7.48 $82.33 Thirdly, find the discount amount. Less than $
1, it is 5%. You can use the IF function to do so
$49.68 $4.97 $54.64 Fourthly, subtract the previous two values to g
$7.57 $0.76 $8.32 Then, the sales tax is 10% for all products.
After that, add the sales tax with the ne
$29.26 $2.93 $32.19
amount.
$30.86 $3.09 $33.94 Finally, add conditional formatting to th
$14.94 $1.49 $16.43
$63.18 $6.32 $69.50
$23.26 $2.33 $25.58
$130.39 $13.04 $143.43
$6.84 $0.68 $7.52
$57.57 $5.76 $63.33
$6.29 $0.63 $6.91

tal Sales: Your task is to find the day wise sales and total sales amount.
F function to get the first value and the SUM function for the second value.

ise 03 Most Popular Item (By Quantity): In this exercise, you will need to find the highest product name and
nt of it.
an use the MAX function to find the maximum value. Then, combine it with the MATCH function to find the ro
er. Finally, use the INDEX function to return the most popular item.
tionally, using the MAX function, you can find the quantity value.
nt of it.
an use the MAX function to find the maximum value. Then, combine it with the MATCH function to find the ro
er. Finally, use the INDEX function to return the most popular item.
tionally, using the MAX function, you can find the quantity value.

Top 3 Items (By Revenue): Your task is to find the top 3 items from the total column.
d to combine the LARGE, MATCH, and INDEX functions to return the desired output.
set: The fast task requires to fill 4 columns by typing and
as.
e these values in the first 4 columns. The formatting
olor, background color, etc.) helps with the visualization.
a dropdown list for the date column.
amount by multiplying the price by the unit sold.
mount. Less than $1 is a 3% discount and for more than
IF function to do so.
ous two values to get the net amount.
or all products.
ales tax with the net amount to calculate the total

nal formatting to the top 3 revenue.

product name and the


nction to find the row
nction to find the row

Common questions

Powered by AI

Conditional formatting is added to visually distinguish the top 3 items by revenue, enhancing data readability and decision-making. This is achieved by using a combination of the LARGE, MATCH, and INDEX functions to identify top revenues and then applying conditional formatting rules to highlight them .

INDEX and MATCH are preferred because they offer more flexibility than VLOOKUP, allowing lookups in any direction and not limited to the order of columns. This adaptability is crucial for tasks like finding the most popular items where column order may vary .

The sales tax, applied at a flat rate of 10% on all products, is added to the net amount obtained by subtracting the discount from the calculated amount (price multiplied by units). This inclusion allows for the complete calculation of the total payable amount, reflecting real-world transactions .

The net amount is derived by subtracting the discount amount from the calculated total price, which is obtained by multiplying the unit price by the number sold. This operation accounts for reduced pricing due to discounts .

The exercises require entering values in the first 4 columns and proper formatting like alignment, font size, color, and background color for visualization purposes. Additionally, a dropdown list should be created for the date column to ensure data consistency .

To compute the total sales amount for each day, use the SUMIF function to calculate day-wise sales totals. Then apply the SUM function to get the total sales amount for all days combined .

The combination of LARGE, MATCH, and INDEX functions is recommended to find the top 3 items by revenue. The LARGE function identifies the top three highest revenue values, MATCH locates their positions, and INDEX retrieves the corresponding item names .

Functions like SUMIF allow for efficient aggregation of sales data based on specific criteria, such as day-wise totals, while MAX helps identify peak values such as highest quantities sold. This reduces manual error and speeds up data analysis, allowing for quicker insights .

The document suggests using the MAX function to find the maximum quantity, then combining it with the MATCH function to locate the row number. Use the INDEX function to retrieve the most popular item by quantity .

To calculate the discount amount, use the IF function. For items priced less than $1, apply a 3% discount, and for items priced $1 or more, apply a 5% discount .

You might also like