Product and Sales Data
Product Sales
Product_1 1239
Product_2 115
Product_3 1814
Product_4 344
Product_5 903
Product_6 535
Product_7 776
Product_8 400
Product_9 1477
Product_10 1138
Product_11 1644
Product_12 1730
Product_13 636
Product_14 1544
Product_15 1502
Product_16 812
Product_17 1306
Product_18 1524
Product_19 874
Product_20 1192
Product_21 170
Product_22 670
Product_23 1901
Product_24 1127
Product_25 1589
Product_26 1825
Product_27 1396
Product_28 723
Product_29 1036
Product_30 335
Advanced Excel Lab Assisgnment 4
Q.No Question
1 Sort the Sales column in descending order (Z→A).
2 Find the product with the highest sales.
3 Find the product with the lowest sales.
4 Apply a filter to show only products with sales greater than 500.
5 Count how many products have sales greater than 500.
6 Display only products with sales less than 1000.
7 Find the top 5 products by sales.
8 Find the bottom 5 products by sales.
9 Apply a filter to show sales between 700 and 1200.
10 Find the average sales of all products.
11 Find the total sales of all products.
12 Find how many products have sales greater than 1500.
13 Highlight products with sales more than 1000.
14 Sort the products alphabetically (A→Z).
15 Find the 10th highest sales value.
16 Find the 5th lowest sales value.
17 Apply filter to display only even-numbered sales values.
18 Apply filter to display only odd-numbered sales values.
19 Find the median sales value.
20 Filter products with sales equal to exactly 1000.
Q.No Question
1 Sort the Sales column in descending order (Z→A).
2 Find the product with the highest sales.
3 Find the product with the lowest sales.
4 Apply a filter to show only products with sales greater than 500.
5 Count how many products have sales greater than 500.
6 Display only products with sales less than 1000.
7 Find the top 5 products by sales.
8 Find the bottom 5 products by sales.
9 Apply a filter to show sales between 700 and 1200.
10 Find the average sales of all products.
11 Find the total sales of all products.
12 Find how many products have sales greater than 1500.
13 Highlight products with sales more than 1000.
14 Sort the products alphabetically (A→Z).
15 Find the 10th highest sales value.
16 Find the 5th lowest sales value.
17 Apply filter to display only even-numbered sales values.
18 Apply filter to display only odd-numbered sales values.
19 Find the median sales value.
20 Filter products with sales equal to exactly 1000.
Solution
Go to Data → Sort → Sales → Largest to Smallest
The first product after sorting Z→A
The last product after sorting Z→A
Data → Filter → Number Filters → Greater than 500
Check the filtered result count at the bottom left of Excel
Filter → Number Filters → Less than 1000
After sorting Z→A, take the first 5 rows
After sorting A→Z, take the first 5 rows
Number Filters → Between → 700 and 1200
Use =AVERAGE(B2:B31)
Use =SUM(B2:B31)
Use =COUNTIF(B2:B31,">1500")
Conditional Formatting → Highlight Cell Rules → Greater Than 1000
Data → Sort → Product → A to Z
Use =LARGE(B2:B31,10)
Use =SMALL(B2:B31,5)
Add helper column with =ISEVEN(B2) → Filter TRUE
Add helper column with =ISODD(B2) → Filter TRUE
Use =MEDIAN(B2:B31)
Filter → Number Filters → Equals → 1000
