Scribd
Upload
114 views3 pages

Advanced Excel Exam Paper: Hard Level

The document outlines an advanced Excel exam paper with three main questions focusing on functions, data manipulation, visualization, and validation techniques. It includes tasks such as using SUMIFS, AVERAGEIFS, MAX, INDEX/MATCH, creating pivot tables and charts, applying conditional formatting, and utilizing advanced functions like XLOOKUP and RANK.EQ. A sample dataset is provided for analysis, containing product information, sales, profit, and region data.

Uploaded by

f2024266316
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
114 views3 pages

Advanced Excel Exam Paper: Hard Level

The document outlines an advanced Excel exam paper with three main questions focusing on functions, data manipulation, visualization, and validation techniques. It includes tasks such as using SUMIFS, AVERAGEIFS, MAX, INDEX/MATCH, creating pivot tables and charts, applying conditional formatting, and utilizing advanced functions like XLOOKUP and RANK.EQ. A sample dataset is provided for analysis, containing product information, sales, profit, and region data.

Uploaded by

f2024266316
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

Advanced Excel Exam Paper (Hard Level)

Question 2-1: Advanced Excel Functions and Data Manipulation (10 Marks) CLO-2

1. Use SUMIFS with multiple criteria:

o Calculate the total sales for each product in the "Electronics" category and "Appliances"
category by region and product.

o Create a summary table to show the total sales for each region, product category, and
product.

2. Use AVERAGEIFS to find the average sales value for all products with sales greater than
$25,000 in the “Electronics” category.

o Consider only products with sales above $25,000.

o Create a table with the average sales values for products in the "Electronics" category.

3. Use MAX and INDEX/MATCH to identify the product with the highest profit and the sales value
for that product.

o Use the MAX function to find the highest profit, then use INDEX and MATCH to find the
product name and the corresponding sales value.

4. Apply conditional formatting to highlight products where sales exceed $40,000 and the profit
is greater than $10,000.

o Format products that meet both criteria in bold red.

5. Create a pivot table showing the total profit per region and product category, including
filtering for dates.

o Filter data by product category and region.

o Include a slicer for the Date field to filter by specific months.

Question 2-2: Advanced Visualization and Validation (10 Marks) CLO-3

1. Create a 3D clustered column chart comparing sales for each region by product category (e.g.,
Electronics, Appliances, Furniture).

o Display the data in 3D format.

o Use sales by region and category for the X and Y axes.

2. Add data labels to the chart with customized formatting (font size, color, etc.).

3. Use data validation to restrict input for the "Discount" column to percentages between 0% and
30%.
o Apply the data validation rule to ensure only percentages between 0 and 30 can be
entered.

4. Highlight the top 3 performing products by sales value with conditional formatting (use
different colors).

o Format the top 3 products with green, yellow, and red.

5. Create a pivot chart showing the total sales by region and product category.

o Use a line graph to visualize trends over time.

Question 2-3: Advanced Functions and Features (10 Marks) CLO-3

1. Use XLOOKUP to retrieve the sales amount for a given Product ID and return the sales value.

o Create a lookup formula where the Product ID is entered in a separate cell and the sales
amount is returned.

2. Apply the TEXT function to format sales and profit values with a dollar sign, thousands
separator, and two decimal places.

o Format sales and profit values to appear as $12,345.67.

3. Use [Link] to rank products based on their profit in descending order.

o Apply [Link] to rank products by profit and display the rank in a separate column.

4. Use CONCATENATE to combine the Product Name and Product Category into a single column
(e.g., "Product A - Electronics").

o Concatenate Product Name (from column A) and Product Category (from column B) into
a new column.

5. Insert a sparklines chart in the "Profit" column to show the trend for profit values across
different periods.

o Create a sparkline chart to visualize the trend of profits for each product.

Data for Analysis

Here’s the sample dataset to apply the above tasks. It contains product information, sales, profit, and
region data.

Product Product Quantity


Product ID Region Sales Profit Discount Date
Name Category Sold

P001 Product A Electronics North $30,000 $12,000 10% 500 01/01/2025

P002 Product B Electronics South $25,000 $8,000 15% 400 02/01/2025


Product Product Quantity
Product ID Region Sales Profit Discount Date
Name Category Sold

P003 Product C Appliances West $35,000 $15,000 5% 300 03/01/2025

P004 Product D Appliances East $20,000 $7,000 20% 200 04/01/2025

P005 Product E Furniture North $40,000 $20,000 10% 150 05/01/2025

P006 Product F Furniture South $50,000 $25,000 8% 100 06/01/2025

P007 Product G Electronics East $45,000 $22,000 12% 250 07/01/2025

P008 Product H Appliances West $55,000 $18,000 10% 150 08/01/2025

P009 Product I Furniture North $15,000 $5,000 25% 500 09/01/2025

P010 Product J Electronics South $30,000 $10,000 18% 400 10/01/2025

Instructions:

Common questions

Powered by AI

Create a pivot chart using a line graph to visualize trends over time for different regions and product categories in Excel. Insert sales data into a pivot table, then transform it into a pivot chart. This line graph will help illustrate trends and changes in sales across selected timelines and categories .

Use sparklines in Excel to analyze profit trends for each product. Insert sparklines in the 'Profit' column to create small charts that depict the trend over individual rows of data. Sparklines provide a quick, clear visualization of performance trends across periods without consuming much space .

To find the average sales for 'Electronics' category products with sales greater than $25,000, use the AVERAGEIFS function. The function should average the 'Sales' column with conditions that 'Product Category' is 'Electronics' and 'Sales' is greater than 25,000. Set these conditions as the criteria in the AVERAGEIFS formula .

Data validation in Excel can be used to restrict inputs. To limit the 'Discount' column values between 0% and 30%, apply a data validation rule. Set the criteria to 'Decimal' and specify a minimum value of 0 and a maximum of 0.3. This ensures only valid percentages within this range can be entered .

Use the RANK.EQ function to rank products by profit in descending order. Apply the function to the 'Profit' column, compare each product’s profit against others, and then display the rank in a new column. This helps in sorting products based on profitability .

To identify the product with the highest profit, use the MAX function to find the maximum profit value in the 'Profit' column first. Once you have the maximum value, use INDEX and MATCH to retrieve the corresponding product name and sales value. MATCH locates the position of the highest profit value, and INDEX fetches the product name and sales from this position in their respective columns .

The TEXT function in Excel can format numerical data to match a specific display need. For sales and profit figures, use TEXT to format these numbers as currency. Utilize a format string such as "$#,##0.00" to ensure numbers are displayed with a dollar sign, thousand separators, and two decimal places, e.g., $12,345.67 .

To create a dynamic sales comparison across regions, use a 3D clustered column chart in Excel. Plot 'Region' on the X-axis and 'Product Category' on the Y-axis. Input sales data for each category and region to visually compare sales figures. Enhance this chart with data labels and customized formatting to improve readability .

Use conditional formatting in Excel to highlight cells that meet specific criteria. For emphasizing products with sales exceeding $40,000 and profits greater than $10,000, apply a formatting rule that formats these products in bold red. This visual cue helps quickly identify high-performing products .

To calculate total sales for 'Electronics' and 'Appliances' categories by region in Excel, use the SUMIFS function. The SUMIFS function allows you to apply multiple criteria to sum the sales based on the product category and region. For instance, you would sum the 'Sales' column where 'Product Category' equals 'Electronics' or 'Appliances', segmented by 'Region' .

You might also like