6. Range Names

Create a named range for Sales Amount in the Orders sheet:

• Task 6.1: Name the range of sales data as "SalesData".
• Task 6.2: Use the named range "SalesData" to calculate the total sales using the SUM function.

7. IF Function

Using the Orders sheet:

• Task 7.1: Use the IF function to create a new column called "High Value Order" that flags orders with a sales amount greater than $500 as "YES" and those below as "NO".

8. PMT Function

Suppose you are financing the purchase of a bulk order. Using the PMT function:

• Task 8.1: Assume a loan of $10,000, an interest rate of 5% per year, and a loan term of 2 years. Calculate the monthly payment.

9. VLOOKUP Function

Using the Returns sheet:

• Task 9.1: Use the VLOOKUP function to check if an order from the Orders sheet was returned. Look up the Order ID from the Returnssheet to see if it exists.

10. SUM Function

Using the Orders sheet:

• Task 10.1: Calculate the total Sales Amount using the SUM function.

11. Concatenating Text and Numbers

Using the Orders sheet:

• Task 11.1: Combine the Order Date and Order ID in a single cell, separated by a hyphen (e.g., "2024-10-17 - 12345").

9. Pivot Tables

• Worksheet Name: Pivot Tables
• Task 9.1: Create a Pivot Table to display Sales by Category and Region:
• Task 9.2: Add a Calculated Field:
  1. Add a calculated field to the Pivot Table:
     • Formula: Profit Margin = (Sales - Costs) / Sales
  2. Format the result as a percentage by right-clicking the column > Format Cells > Percentage.

10. Sparklines

• Worksheet Name: Sparklines
• Task 10.1: Add Sparklines:
  1. Insert a new worksheet and rename it Sparklines.
  2. Insert Line Sparklines in a column adjacent to the Sales data to visualize the trend.
  3. Highlight High Points and Low Points in Sparklines:
     • Use Sparkline Tools > High Point/Low Point Options.

11. Insert a Sheet Called "Cities" and Calculate Sales

• Copy the City Column: Select the entire "City" column from your dataset.
• Copy it to a new sheet or a different area within the same sheet.
• Remove Duplicates: With the copied column still selected, go to the Data tab on the Ribbon. Click on Remove Duplicates.
• Add a Header for Sales Total: In the next column (e.g., Column B), add a header called Total Sales.