Section 1: Manage Worksheets and Workbooks (20%) Create a n…
Section 1: Manage Worksheets and Workbooks (20%) Create a new workbook and save it as Assessment.xlsx. Rename Sheet1 to SalesData. Insert a new worksheet and name it Summary. Move the Summary worksheet to be the first tab in the workbook. Hide the SalesData worksheet. Section 2: Manage Data Cells and Ranges (30%) In the SalesData worksheet: Enter the following data starting in cell A1: Product Sales Q1 Sales Q2 Sales Q3 Sales Q4 Widget A 5000 7000 8500 9000 Widget B 6000 7500 8200 8800 Widget C 5500 6800 7200 7700 Format the headers in bold and center-align them. Apply the “comma” number style to the numerical data Apply a conditional format to highlight cells in the Sales Q4 column that are greater than 8500. Create a named range called Q4Sales for the Sales Q4 data. Section 3: Manage Tables and Table Data (20%) Convert the data range in SalesData into an Excel Table. Name the table SalesTable. Add a Total Row to SalesTable and configure it to calculate the sum of Sales Q4. Sort the table by Sales Q1 in descending order. Filter the table to display only rows where Sales Q3 is greater than 8000. Section 4: Perform Operations with Formulas and Functions (20%) In the Summary worksheet: Use a formula to calculate the total sales for each product across all quarters. Place this formula in column B, starting from cell B2. In cell C2, calculate the average sales per quarter for each product. Use a function to find the highest sales value from Sales Q1 to Sales Q4. Display the result in cell D1. Section 5: Create Charts and Objects (10%) Create a clustered column chart in the Summary worksheet: Use the product names as the category axis. Use the total sales (calculated in column B) as the values. Add a chart title: Total Sales by Product. Insert a text box next to the chart with the following note:”Data sourced from SalesData worksheet.”
Read Details