Scribd
Upload
7 views2 pages

Instructionto Accounting Test

The document outlines a series of Excel test questions divided into sections A and B, covering basic and intermediate skills. It includes tasks such as calculating total sales, using formulas, applying formatting, creating charts, and data validation. Additionally, it emphasizes data analysis techniques, logical functions, and security measures for the workbook.

Uploaded by

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

Instructionto Accounting Test

The document outlines a series of Excel test questions divided into sections A and B, covering basic and intermediate skills. It includes tasks such as calculating total sales, using formulas, applying formatting, creating charts, and data validation. Additionally, it emphasizes data analysis techniques, logical functions, and security measures for the workbook.

Uploaded by

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

Instruction:

Enter this table in Excel starting from Cell A1.

📝 EXCEL TEST QUESTIONS

🔹 SECTION A: BASIC (10 Marks)

1. In Column F, calculate Total Sales


(Total Sales = Quantity × Unit Price)

2. Use a formula to calculate the total Quantity sold.

3. Use a formula to find the average Unit Price.

4. Which sales person sold Printers?

5. How many items did Mary sell?

6. Use AutoSum to calculate total sales amount.

7. Apply bold formatting to the table headers.

8. Change the Unit Price column to Currency format (KES).

9. Sort the table by Sales Person (A–Z).

10. Save the file as:


Excel_Test_Sales.xlsx

🔹 SECTION B: INTERMEDIATE (10 Marks)

11. Use IF function in Column G to show:

 “High Sale” if Total Sales ≥ 20,000

 “Low Sale” if below 20,000

12. Use COUNT to count how many sales persons are listed.

13. Use MAX to find the highest Unit Price.

14. Use MIN to find the lowest Unit Price.


15. Use SUMIF to calculate total sales for Flash Disk.

16. Apply borders to the entire table.

17. Insert a Column Chart showing Sales Person vs Total Sales.

18. Freeze the top row of the worksheet.

19. Rename the worksheet to Sales Report.

20. Protect the worksheet so data cannot be edited.

21. Use COUNTIF to count how many items have Quantity greater than 3.

22. Use IF + AND to display:

 “Target Met” if Quantity ≥ 2 AND Total Sales ≥ 30,000

 “Target Not Met” otherwise.

25. Use RANK function to rank sales persons based on Total Sales (Highest = Rank 1).

📌 DATA ANALYSIS

26. Create a Pivot Table that shows:

 Rows: Sales Person

 Values: Sum of Total Sales

27. From the Pivot Table, identify the top-performing sales person.

28. Insert a Pivot Chart from the Pivot Table.

29. Apply Conditional Formatting to highlight Total Sales above 40,000.

30. Use Data Validation to restrict Quantity column to whole numbers only.

GIVE A TRY

📌 LOGICAL & TEXT FUNCTIONS

31. Use LEFT or MID function to extract the first 3 letters of each Item Sold.

32. Use CONCAT or TEXTJOIN to combine Sales Person and Item Sold
(Example: John – Laptop)

33. Use UPPER function to convert all Item names to uppercase.

34. Use LEN function to count characters in each Sales Person name.

35. Use TRIM to remove extra spaces from Sales Person names (assume spaces exist).

📌 WHAT-IF & SECURITY

36. Use Goal Seek to find the Quantity needed to achieve Total Sales of 100,000 for Laptop.

37. Protect the workbook so:

 Data cells cannot be edited

 Charts can still be viewed

38. Hide the Unit Price column and protect the sheet.

39. Create a drop-down list for Item Sold using Data Validation.

40. Save the workbook with a password for opening.

You might also like