Create an Excel sheet with 20 agricultural items, showing payments, products bought, and include questions to practice various Excel formulas and conditional formatting.

Agricultural Inventory and Payments

Excel Sheet Structure:

Key Features for Practice:

1. Basic Formulas:

   • Total Cost: =C2*D2 (for all rows)
   • Final Cost: =E2-(E2*F2/100) (for all rows)

2. Conditional Formatting:

   • Highlight cells in the Payment Status column that are "Unpaid".
   • Color-code Final Cost based on high (> $700), medium ($300-$700), and low (< $300) values.

3. Data Validation:

   • Restrict entries in the Discount (%) column to be between 0 and 20.
   • Create a dropdown list for the Supplier column with predefined suppliers (e.g., Supplier A, Supplier B, Supplier C).

4. VLOOKUP/HLOOKUP:

   • Add a separate table listing Supplier and Contact Info. Use VLOOKUP to fill in the Contact Info for each item in the main table.

5. SUMIF/SUMIFS:

   • Calculate the total Final Cost for each supplier using SUMIF.

6. Pivot Tables:

   • Create a Pivot Table to summarize total Quantity Bought, Total Cost, and Final Cost by Supplier.

7. Charts:

   • Create a bar chart to visualize the total Quantity Bought of each product.
   • Create a pie chart to show the distribution of Total Cost by Supplier.

8. Sorting and Filtering:

   • Sort the table by Final Cost in descending order.
   • Apply filters to show only products with Quantity Bought greater than 300.

Questions for Practice:

1. Basic Formulas:

   • Calculate the Total Cost for each product.
   • Calculate the Final Cost for each product after applying the discount.

2. Conditional Formatting:

   • Highlight the rows where the Payment Status is "Unpaid".
   • Apply a three-color scale to the Final Cost column to differentiate high, medium, and low values.

3. Data Validation:

   • Set a validation rule to allow only numbers between 0 and 20 in the Discount (%) column.
   • Create a dropdown list for the Supplier column with the options: Supplier A, Supplier B, Supplier C.

4. VLOOKUP:

   • Use VLOOKUP to populate the Contact Info for each supplier in a new column based on a separate table of supplier contact details.

5. SUMIF/SUMIFS:

   • Calculate the total Final Cost for each supplier using the SUMIF function.

6. Pivot Tables:

   • Create a Pivot Table to summarize total Quantity Bought, Total Cost, and Final Cost by Supplier.

7. Charts:

   • Create a bar chart to visualize the total Quantity Bought for each product.
   • Create a pie chart to show the distribution of Total Cost by Supplier.

8. Sorting and Filtering:

   • Sort the table by Final Cost in descending order.
   • Filter the table to show only products with Quantity Bought greater than 300.