Assignments
on 3-D cell references in Excel
Assignment 1: Sales Data Across Quarters
Objective: Create a workbook with multiple sheets representing sales data for different quarters and calculate total sales for the year.
Task
- Create a new Excel workbook.
- Add three sheets named "Q1," "Q2," and "Q3."
- In each sheet, set up the following columns:
- A1: "Product"
- B1: "Sales"
- Enter the following data in each sheet:
Product | Sales |
Product A | 500 |
Product B | 700 |
Product C | 300 |
- Create a fourth sheet named "Total Sales."
- In cell A1 of the "Total Sales" sheet, type "Total Sales for the Year."
- In cell A2, create a formula to calculate the total sales across all quarters using a 3-D reference.
Assignment 2: Student Grades Across Semesters
Objective: Create a workbook with multiple sheets representing student grades for different semesters and calculate the average grade for each student.
Task
- Create a new Excel workbook.
- Add three sheets named "Semester 1," "Semester 2," and "Semester 3."
- In each sheet, set up the following columns:
- A1: "Student Name"
- B1: "Grade"
- Enter the following data in each sheet:
Student Name | Grade |
Alice | 85 |
Bob | 78 |
Charlie | 90 |
- Create a fourth sheet named "Average Grades."
- In cell A1 of the "Average Grades" sheet, type "Average Grade."
- In cell A2, create a formula to calculate the average grade across all semesters using a 3-D reference.
Assignment 3: Monthly Expenses Across Different Categories
Objective: Create a workbook with multiple sheets representing monthly expenses for different categories and calculate total expenses for each category.
Task
- Create a new Excel workbook.
- Add three sheets named "Groceries," "Utilities," and "Entertainment."
- In each sheet, set up the following columns:
- A1: "Item"
- B1: "Amount"
- Enter the following data in each sheet:
Item | Amount |
Item 1 | 100 |
Item 2 | 150 |
Item 3 | 200 |
- Create a fourth sheet named "Total Expenses."
- In cell A1 of the "Total Expenses" sheet, type "Total Expenses."
- In cell A2, create a formula to calculate the total expenses across all categories using a 3-D reference.
How to access the cell from one sheet to another sheet
Accessing cells from one sheet to another in Excel can be done using references that include the sheet name.
Step-by-Step Instructions
Step 1: Open Your Workbook
- Open your Excel workbook that contains multiple sheets.
Step 2: Identify the Source and Destination Sheets
- Decide which sheet contains the data you want to reference (let's call this Sheet1) and which sheet you want to place the reference in (let's call this Sheet2).
Step 3: Access the Cell from Another Sheet
- Go to Sheet2 where you want to reference a cell from Sheet1.
- Click on the cell in Sheet2 where you want the data from Sheet1 to appear.
- Type the formula using the following syntax:
=Sheet1!A1
- Sheet1 is the name of the sheet you are referencing.
- A1 is the cell in Sheet1 that you want to access.
Example
- If Sheet1 contains the value 100 in cell A1, and you want to display this value in Sheet2:
- In Sheet2, click on cell B1.
- Type the formula:
=Sheet1!A1
- Press Enter. The value 100 from Sheet1 will now appear in B1 of Sheet2.
Step 4: Using Formulas with References
You can also use cell references from other sheets in formulas. For example:
- To sum values from Sheet1 and another cell in Sheet2:
=Sheet1!A1 + B2
- This adds the value in A1 from Sheet1 to the value in B2 of Sheet2.
Important Notes
- Sheet Names with Spaces: If the sheet name contains spaces or special characters, enclose the sheet name in single quotes. For example:
='Sheet 1'!A1
- Referencing Other Workbook Sheets: If you want to reference a cell in a different workbook, use the following syntax:
=[WorkbookName.xlsx]Sheet1!A1
Ensure the other workbook is open when doing this.
Practice Example
- Create Two Sheets: Create a workbook with two sheets named "Sales" and "Summary."
- Enter Data: In Sales, enter 200 in cell B2.
- Reference in Summary:
- In Summary, click on cell A1.
- Type:
=Sales!B2
- Press Enter. Cell A1 in Summary should now show 200, the value from Sales sheet.
Example Scenario
Workbook Structure:
- Sheet 1: Named "Sales"
- Sheet 2: Named "Summary"
Step-by-Step Instructions
Step 1: Create the Sheets and Enter Data
- Create the "Sales" Sheet:
- Open Excel and create a new workbook.
- Rename the first sheet to "Sales".
- In the Sales sheet, enter the following data:
A | B | C |
Product | Sales Q1 | Sales Q2 |
Product A | 150 | 200 |
Product B | 100 | 250 |
Product C | 300 | 150 |
- Create the "Summary" Sheet:
- Add a new sheet and rename it to "Summary".
- In the Summary sheet, set up the following structure:
A | B | C |
Product | Total Sales Q1 | Total Sales Q2 |
Step 2: Access Cells from "Sales" in "Summary"
Example 1: Accessing a Single Cell
- Reference Sales Q1 for Product A:
- In the Summary sheet, click on cell B2 (the cell for Product A's total sales in Q1).
- Type the following formula:
=Sales!B2
- Press Enter.
- Now, B2 in the Summary sheet should display 150, which is the sales figure for Product A in Q1 from the Sales sheet.
- Reference Sales Q2 for Product A:
- In the Summary sheet, click on cell C2.
- Type the following formula:
=Sales!C2
- Press Enter.
- C2 in the Summary sheet should now display 200, the sales figure for Product A in Q2.
Example 2: Accessing Multiple Cells
- Reference Sales for Product B:
- In the Summary sheet, click on cell B3.
- Type the formula:
=Sales!B3
- Press Enter.
- B3 should now display 100 (Sales Q1 for Product B).
- Reference Sales Q2 for Product B:
- In the Summary sheet, click on cell C3.
- Type:
=Sales!C3
- Press Enter.
- C3 should show 250 (Sales Q2 for Product B).
- Repeat for Product C:
- For Product C, enter:
- In B4:
=Sales!B4
- In C4:
=Sales!C4
- After pressing Enter on both, the Summary sheet should display 300 for Q1 and 150 for Q2 for Product C.
Summary of Results
After entering all the formulas, your Summary sheet will look like this:
A | B | C |
Product | Total Sales Q1 | Total Sales Q2 |
Product A | 150 | 200 |
Product B | 100 | 250 |
Product C | 300 | 150 |
Additional Notes
- Referencing Cells in Different Workbooks: If the Sales data were in a different workbook named "SalesData.xlsx", you would use:
=[SalesData.xlsx]Sales!B2
Ensure the SalesData.xlsx file is open when using this reference.
- Using Named Ranges: If you often reference certain cells, you can name them in Excel (using the Name Box) for easier referencing. For instance, if you named the range of Q1 sales as "Q1Sales", you could access it like this:
=Q1Sales
Practice Exercise
- Create a New Sheet: Add a third sheet named "Analysis".
- Reference Data: In Analysis, create a table similar to Summary, but calculate total sales by adding both Q1 and Q2 for each product:
- In cell B2 of Analysis, type:
=Sales!B2 + Sales!C2
- Drag the fill handle down to apply this formula for Products B and C.
- Check Results: Ensure that it sums the sales for Q1 and Q2 for each product.
Assignment 1: Accessing Single Cell Values
Objective: Reference specific sales figures from the "Sales" sheet in the "Summary" sheet.
Data Setup
Sales Sheet ("Sales"):
A | B | C |
Product | Sales Q1 | Sales Q2 |
Product A | 150 | 200 |
Product B | 100 | 250 |
Product C | 300 | 150 |
Summary Sheet ("Summary"):
A | B | C |
Product | Total Sales Q1 | Total Sales Q2 |
Product A | ||
Product B | ||
Product C |
Assignment 2: Summing Values Across Sheets
Objective: Calculate total sales for each product by summing sales from Q1 and Q2 in the "Analysis" sheet.
Data Setup
Sales Sheet ("Sales"):
A | B | C |
Product | Sales Q1 | Sales Q2 |
Product A | 150 | 200 |
Product B | 100 | 250 |
Product C | 300 | 150 |
Analysis Sheet ("Analysis"):
A | B |
Product | Total Sales |
Product A | |
Product B | |
Product C |
Assignment 3: Creating a Summary Table
Objective: Create a summary of total sales for Q1 and Q2 in the "Summary" sheet by accessing the data from the "Sales" sheet.
Data Setup
Sales Sheet ("Sales"):
A | B | C |
Product | Sales Q1 | Sales Q2 |
Product A | 150 | 200 |
Product B | 100 | 250 |
Product C | 300 | 150 |
Summary Sheet ("Summary"):
A | B | C |
Product | Total Sales Q1 | Total Sales Q2 |
Product A | ||
Product B | ||
Product C | ||
Grand Total |
Assignment 4: Using Functions Across Sheets
Objective: Use the AVERAGE function to calculate the average sales for each product in the "Summary" sheet, pulling data from the "Sales" sheet.
Data Setup
Sales Sheet ("Sales"):
A | B | C |
Product | Sales Q1 | Sales Q2 |
Product A | 150 | 200 |
Product B | 100 | 250 |
Product C | 300 | 150 |
Summary Sheet ("Summary"):
A | B | C | D |
Product | Average Sales Q1 | Average Sales Q2 | Overall Average |
Product A | |||
Product B | |||
Product C |
Assignment 5: Creating Dynamic Reports with Drop-Down Lists
Objective: Use data validation to create a drop-down list for selecting products and displaying their sales data in the "Report" sheet.
Data Setup
Sales Sheet ("Sales"):
A | B | C |
Product | Sales Q1 | Sales Q2 |
Product A | 150 | 200 |
Product B | 100 | 250 |
Product C | 300 | 150 |
Report Sheet ("Report"):
A | B | C |
Select Product | Sales Q1 | Sales Q2 |
(Dropdown) |
Assignment 6: Conditional Formatting Based on Values from Another Sheet
Objective: Apply conditional formatting in the "Sales" sheet to highlight sales figures in Q2 that are above a certain threshold, using a value from the "Threshold" sheet.
Data Setup
Sales Sheet ("Sales"):
A | B | C |
Product | Sales Q1 | Sales Q2 |
Product A | 150 | 200 |
Product B | 100 | 250 |
Product C | 300 | 150 |
Threshold Sheet ("Threshold"):
A | B |
Threshold | Value |
Sales Q2 | 200 |
Assignment 7: Comparing Data Across Sheets
Objective: Compare sales figures from the "Sales" sheet to determine if each product's sales in Q2 exceed those in Q1.
Data Setup
Sales Sheet ("Sales"):
A | B | C |
Product | Sales Q1 | Sales Q2 |
Product A | 150 | 200 |
Product B | 100 | 90 |
Product C | 300 | 450 |
Comparison Sheet ("Comparison"):
A | B | C |
Product | Q2 > Q1? | Result |
Product A | ||
Product B | ||
Product C |
Assignment 1: Sales Data Across Quarters
Objective: Create a workbook with multiple sheets representing sales data for different quarters and calculate total sales for the year.
Task
- Create a new Excel workbook.
- Add three sheets named "Q1," "Q2," and "Q3."
- In each sheet, set up the following columns:
- A1: "Product"
- B1: "Sales"
- Enter the following data in each sheet:
Product | Sales |
Product A | 500 |
Product B | 700 |
Product C | 300 |
- Create a fourth sheet named "Total Sales."
- In cell A1 of the "Total Sales" sheet, type "Total Sales for the Year."
- In cell A2, create a formula to calculate the total sales across all quarters using a 3-D reference.
Solution Steps
- Create the Workbook:
- Open Excel and create a new workbook.
- Add three sheets named "Q1," "Q2," and "Q3."
- Set Up the Sales Data:
- In each sheet, enter the column headers in A1 and B1.
- Enter the sales data as provided in the table above.
- Create the Total Sales Sheet:
- Add a fourth sheet and name it "Total Sales."
- In A1, type "Total Sales for the Year."
- Create the 3-D Reference Formula:
- In A2, enter the formula:
=SUM(Q1:Q3!B2)
- This formula adds the sales figures for Product A from each quarterly sheet.
- Copy the Formula for Other Products:
- In A3, enter:
=SUM(Q1:Q3!B3)
- In A4, enter:
=SUM(Q1:Q3!B4)
- This will calculate total sales for Product B and Product C respectively.
- Final Result:
- Your "Total Sales" sheet will display total sales for each product.
Total Sales for the Year |
1500 |
2100 |
900 |
Assignment 2: Student Grades Across Semesters
Objective: Create a workbook with multiple sheets representing student grades for different semesters and calculate the average grade for each student.
Task
- Create a new Excel workbook.
- Add three sheets named "Semester 1," "Semester 2," and "Semester 3."
- In each sheet, set up the following columns:
- A1: "Student Name"
- B1: "Grade"
- Enter the following data in each sheet:
Student Name | Grade |
Alice | 85 |
Bob | 78 |
Charlie | 90 |
- Create a fourth sheet named "Average Grades."
- In cell A1 of the "Average Grades" sheet, type "Average Grade."
- In cell A2, create a formula to calculate the average grade across all semesters using a 3-D reference.
Solution Steps
- Create the Workbook:
- Open Excel and create a new workbook.
- Add three sheets named "Semester 1," "Semester 2," and "Semester 3."
- Set Up the Grades Data:
- In each sheet, enter the column headers in A1 and B1.
- Enter the grades as provided in the table above.
- Create the Average Grades Sheet:
- Add a fourth sheet and name it "Average Grades."
- In A1, type "Average Grade."
- Create the 3-D Reference Formula:
- In A2, enter the formula:
=AVERAGE('Semester 1:Semester 3'!B2)
- This formula calculates the average grade for Alice across all semesters.
- Copy the Formula for Other Students:
- In A3, enter:
=AVERAGE('Semester 1:Semester 3'!B3)
- In A4, enter:
=AVERAGE('Semester 1:Semester 3'!B4)
- This will calculate average grades for Bob and Charlie respectively.
- Final Result:
- Your "Average Grades" sheet will display average grades for each student.
Average Grade |
85 |
78 |
90 |
Assignment 3: Monthly Expenses Across Different Categories
Objective: Create a workbook with multiple sheets representing monthly expenses for different categories and calculate total expenses for each category.
Task
- Create a new Excel workbook.
- Add three sheets named "Groceries," "Utilities," and "Entertainment."
- In each sheet, set up the following columns:
- A1: "Item"
- B1: "Amount"
- Enter the following data in each sheet:
Item | Amount |
Item 1 | 100 |
Item 2 | 150 |
Item 3 | 200 |
- Create a fourth sheet named "Total Expenses."
- In cell A1 of the "Total Expenses" sheet, type "Total Expenses."
- In cell A2, create a formula to calculate the total expenses across all categories using a 3-D reference.
Solution Steps
- Create the Workbook:
- Open Excel and create a new workbook.
- Add three sheets named "Groceries," "Utilities," and "Entertainment."
- Set Up the Expenses Data:
- In each sheet, enter the column headers in A1 and B1.
- Enter the expenses data as provided in the table above.
- Create the Total Expenses Sheet:
- Add a fourth sheet and name it "Total Expenses."
- In A1, type "Total Expenses."
- Create the 3-D Reference Formula:
- In A2, enter the formula:
=SUM(Groceries:Entertainment!B2)
- This formula adds the expenses for Item 1 from each category sheet.
- Copy the Formula for Other Items:
- In A3, enter:
=SUM(Groceries:Entertainment!B3)
- In A4, enter:
=SUM(Groceries:Entertainment!B4)
- This will calculate total expenses for Item 2 and Item 3 respectively.
- Final Result:
- Your "Total Expenses" sheet will display total expenses for each item.
Total Expenses |
450 |
450 |
450 |
Key Points for Students
- Understanding 3-D References: A 3-D reference allows you to refer to the same cell or range across multiple worksheets, which can simplify calculations when dealing with similar data spread over several sheets.
- Formula Syntax: The syntax for a 3-D reference includes the sheet names followed by an exclamation point and the cell or range being referenced (e.g., Sheet1:Sheet3!A1).
- Practical Applications: Using 3-D references is especially useful for summarizing data from different time periods, categories, or locations in a cohesive manner.
How to access the cell from one sheet to another sheet
Accessing cells from one sheet to another in Excel can be done using references that include the sheet name.
Step-by-Step Instructions
Step 1: Open Your Workbook
- Open your Excel workbook that contains multiple sheets.
Step 2: Identify the Source and Destination Sheets
- Decide which sheet contains the data you want to reference (let's call this Sheet1) and which sheet you want to place the reference in (let's call this Sheet2).
Step 3: Access the Cell from Another Sheet
- Go to Sheet2 where you want to reference a cell from Sheet1.
- Click on the cell in Sheet2 where you want the data from Sheet1 to appear.
- Type the formula using the following syntax:
=Sheet1!A1
- Sheet1 is the name of the sheet you are referencing.
- A1 is the cell in Sheet1 that you want to access.
Example
- If Sheet1 contains the value 100 in cell A1, and you want to display this value in Sheet2:
- In Sheet2, click on cell B1.
- Type the formula:
=Sheet1!A1
- Press Enter. The value 100 from Sheet1 will now appear in B1 of Sheet2.
Step 4: Using Formulas with References
You can also use cell references from other sheets in formulas. For example:
- To sum values from Sheet1 and another cell in Sheet2:
=Sheet1!A1 + B2
- This adds the value in A1 from Sheet1 to the value in B2 of Sheet2.
Important Notes
- Sheet Names with Spaces: If the sheet name contains spaces or special characters, enclose the sheet name in single quotes. For example:
='Sheet 1'!A1
- Referencing Other Workbook Sheets: If you want to reference a cell in a different workbook, use the following syntax:
=[WorkbookName.xlsx]Sheet1!A1
Ensure the other workbook is open when doing this.
Practice Example
- Create Two Sheets: Create a workbook with two sheets named "Sales" and "Summary."
- Enter Data: In Sales, enter 200 in cell B2.
- Reference in Summary:
- In Summary, click on cell A1.
- Type:
=Sales!B2
- Press Enter. Cell A1 in Summary should now show 200, the value from Sales sheet.
Example Scenario
Workbook Structure:
- Sheet 1: Named "Sales"
- Sheet 2: Named "Summary"
Step-by-Step Instructions
Step 1: Create the Sheets and Enter Data
- Create the "Sales" Sheet:
- Open Excel and create a new workbook.
- Rename the first sheet to "Sales".
- In the Sales sheet, enter the following data:
A | B | C |
Product | Sales Q1 | Sales Q2 |
Product A | 150 | 200 |
Product B | 100 | 250 |
Product C | 300 | 150 |
- Create the "Summary" Sheet:
- Add a new sheet and rename it to "Summary".
- In the Summary sheet, set up the following structure:
A | B | C |
Product | Total Sales Q1 | Total Sales Q2 |
Step 2: Access Cells from "Sales" in "Summary"
Example 1: Accessing a Single Cell
- Reference Sales Q1 for Product A:
- In the Summary sheet, click on cell B2 (the cell for Product A's total sales in Q1).
- Type the following formula:
=Sales!B2
- Press Enter.
- Now, B2 in the Summary sheet should display 150, which is the sales figure for Product A in Q1 from the Sales sheet.
- Reference Sales Q2 for Product A:
- In the Summary sheet, click on cell C2.
- Type the following formula:
=Sales!C2
- Press Enter.
- C2 in the Summary sheet should now display 200, the sales figure for Product A in Q2.
Example 2: Accessing Multiple Cells
- Reference Sales for Product B:
- In the Summary sheet, click on cell B3.
- Type the formula:
=Sales!B3
- Press Enter.
- B3 should now display 100 (Sales Q1 for Product B).
- Reference Sales Q2 for Product B:
- In the Summary sheet, click on cell C3.
- Type:
=Sales!C3
- Press Enter.
- C3 should show 250 (Sales Q2 for Product B).
- Repeat for Product C:
- For Product C, enter:
- In B4:
=Sales!B4
- In C4:
=Sales!C4
- After pressing Enter on both, the Summary sheet should display 300 for Q1 and 150 for Q2 for Product C.
Summary of Results
After entering all the formulas, your Summary sheet will look like this:
A | B | C |
Product | Total Sales Q1 | Total Sales Q2 |
Product A | 150 | 200 |
Product B | 100 | 250 |
Product C | 300 | 150 |
Additional Notes
- Referencing Cells in Different Workbooks: If the Sales data were in a different workbook named "SalesData.xlsx", you would use:
=[SalesData.xlsx]Sales!B2
Ensure the SalesData.xlsx file is open when using this reference.
- Using Named Ranges: If you often reference certain cells, you can name them in Excel (using the Name Box) for easier referencing. For instance, if you named the range of Q1 sales as "Q1Sales", you could access it like this:
=Q1Sales
Practice Exercise
- Create a New Sheet: Add a third sheet named "Analysis".
- Reference Data: In Analysis, create a table similar to Summary, but calculate total sales by adding both Q1 and Q2 for each product:
- In cell B2 of Analysis, type:
=Sales!B2 + Sales!C2
- Drag the fill handle down to apply this formula for Products B and C.
- Check Results: Ensure that it sums the sales for Q1 and Q2 for each product.
Assignment 1: Accessing Single Cell Values
Objective: Reference specific sales figures from the "Sales" sheet in the "Summary" sheet.
Data Setup
Sales Sheet ("Sales"):
A | B | C |
Product | Sales Q1 | Sales Q2 |
Product A | 150 | 200 |
Product B | 100 | 250 |
Product C | 300 | 150 |
Summary Sheet ("Summary"):
A | B | C |
Product | Total Sales Q1 | Total Sales Q2 |
Product A | ||
Product B | ||
Product C |
Steps
- Open Excel: Create a new workbook and add two sheets named Sales and Summary.
- Enter Data in the Sales Sheet: Populate the Sales sheet with the data provided above.
- Access Sales Q1 for Product A:
- In the Summary sheet, click on cell B2.
- Type the formula:
=Sales!B2
- Press Enter. Cell B2 should now display 150.
- Access Sales Q2 for Product A:
- Click on cell C2 in the Summary sheet.
- Type:
=Sales!C2
- Press Enter. Cell C2 should now display 200.
- Repeat for Products B and C:
- For Product B:
- In B3, type:
=Sales!B3
- In C3, type:
=Sales!C3
- For Product C:
- In B4, type:
=Sales!B4
- In C4, type:
=Sales!C4
Final Results in Summary Sheet:
A | B | C |
Product | Total Sales Q1 | Total Sales Q2 |
Product A | 150 | 200 |
Product B | 100 | 250 |
Product C | 300 | 150 |
Assignment 2: Summing Values Across Sheets
Objective: Calculate total sales for each product by summing sales from Q1 and Q2 in the "Analysis" sheet.
Data Setup
Sales Sheet ("Sales"):
A | B | C |
Product | Sales Q1 | Sales Q2 |
Product A | 150 | 200 |
Product B | 100 | 250 |
Product C | 300 | 150 |
Analysis Sheet ("Analysis"):
A | B |
Product | Total Sales |
Product A | |
Product B | |
Product C |
Steps
- Create the Analysis Sheet: Add a new sheet named Analysis.
- Enter Product Names: Populate column A of the Analysis sheet with the product names as shown above.
- Calculate Total Sales for Product A:
- Click on cell B2 in the Analysis sheet.
- Type the formula:
=Sales!B2 + Sales!C2
- Press Enter. Cell B2 should now display 350 (150 + 200).
- Repeat for Products B and C:
- For Product B:
- In B3, type:
=Sales!B3 + Sales!C3
- For Product C:
- In B4, type:
=Sales!B4 + Sales!C4
Final Results in Analysis Sheet:
A | B |
Product | Total Sales |
Product A | 350 |
Product B | 350 |
Product C | 450 |
Assignment 3: Creating a Summary Table
Objective: Create a summary of total sales for Q1 and Q2 in the "Summary" sheet by accessing the data from the "Sales" sheet.
Data Setup
Sales Sheet ("Sales"):
A | B | C |
Product | Sales Q1 | Sales Q2 |
Product A | 150 | 200 |
Product B | 100 | 250 |
Product C | 300 | 150 |
Summary Sheet ("Summary"):
A | B | C |
Product | Total Sales Q1 | Total Sales Q2 |
Product A | ||
Product B | ||
Product C | ||
Grand Total |
Steps
- Create the Summary Sheet: Use the previously created Summary sheet.
- Enter Product Names: Populate column A with the product names.
- Calculate Total Sales Q1:
- In B2, type:
=Sales!B2
- In B3, type:
=Sales!B3
- In B4, type:
=Sales!B4
- Calculate Total Sales Q2:
- In C2, type:
=Sales!C2
- In C3, type:
=Sales!C3
- In C4, type:
=Sales!C4
- Calculate Grand Total for Q1:
- In B5, type:
=SUM(B2:B4)
- Calculate Grand Total for Q2:
- In C5, type:
=SUM(C2:C4)
Final Results in Summary Sheet:
A | B | C |
Product | Total Sales Q1 | Total Sales Q2 |
Product A | 150 | 200 |
Product B | 100 | 250 |
Product C | 300 | 150 |
Grand Total | 550 | 600 |
Assignment 4: Using Functions Across Sheets
Objective: Use the AVERAGE function to calculate the average sales for each product in the "Summary" sheet, pulling data from the "Sales" sheet.
Data Setup
Sales Sheet ("Sales"):
A | B | C |
Product | Sales Q1 | Sales Q2 |
Product A | 150 | 200 |
Product B | 100 | 250 |
Product C | 300 | 150 |
Summary Sheet ("Summary"):
A | B | C | D |
Product | Average Sales Q1 | Average Sales Q2 | Overall Average |
Product A | |||
Product B | |||
Product C |
Steps
- Create the Summary Sheet: Use the previously created Summary sheet.
- Enter Product Names: Populate column A with the product names.
- Calculate Average Sales for Q1:
- In B2, type the formula:
=AVERAGE(Sales!B2:B4)
- Press Enter. Cell B2 should now display 250 (Average of 150, 100, 300).
- Calculate Average Sales for Q2:
- In C2, type:
=AVERAGE(Sales!C2:C4)
- Press Enter. Cell C2 should now display 200 (Average of 200, 250, 150).
- Calculate Overall Average:
- In D2, type:
=AVERAGE(B2:C2)
- Press Enter. Cell D2 should now display 225 (Average of 250 and 200).
- Repeat for Other Products:
- For Product B:
- In B3, type:
=AVERAGE(Sales!B3:B3)
- In C3, type:
=AVERAGE(Sales!C3:C3)
- In D3, type:
=AVERAGE(B3:C3)
- For Product C:
- In B4, type:
=AVERAGE(Sales!B4:B4)
- In C4, type:
=AVERAGE(Sales!C4:C4)
- In D4, type:
=AVERAGE(B4:C4)
Final Results in Summary Sheet:
A | B | C | D |
Product | Average Sales Q1 | Average Sales Q2 | Overall Average |
Product A | 250 | 200 | 225 |
Product B | 100 | 250 | 175 |
Product C | 300 | 150 | 225 |
Assignment 5: Creating Dynamic Reports with Drop-Down Lists
Objective: Use data validation to create a drop-down list for selecting products and displaying their sales data in the "Report" sheet.
Data Setup
Sales Sheet ("Sales"):
A | B | C |
Product | Sales Q1 | Sales Q2 |
Product A | 150 | 200 |
Product B | 100 | 250 |
Product C | 300 | 150 |
Report Sheet ("Report"):
A | B | C |
Select Product | Sales Q1 | Sales Q2 |
(Dropdown) |
Steps
- Create the Report Sheet: Add a new sheet named Report.
- Create a Drop-Down List:
- Click on cell A2 in the Report sheet.
- Go to the Data tab.
- Click on Data Validation.
- In the Data Validation dialog, choose List from the Allow dropdown.
- In the Source field, enter:
=Sales!A2:A4
- Click OK.
- Link Sales Data to the Selected Product:
- In B2, type:
=IF(A2="", "", VLOOKUP(A2, Sales!A2:C4, 2, FALSE))
- In C2, type:
=IF(A2="", "", VLOOKUP(A2, Sales!A2:C4, 3, FALSE))
- Press Enter for both cells.
- Test the Drop-Down:
- Click on the drop-down arrow in cell A2 and select a product.
- The corresponding sales data for Sales Q1 and Sales Q2 should automatically populate in cells B2 and C2.
Example Results in Report Sheet (after selection):
A | B | C |
Select Product | Sales Q1 | Sales Q2 |
Product A | 150 | 200 |
Assignment 6: Conditional Formatting Based on Values from Another Sheet
Objective: Apply conditional formatting in the "Sales" sheet to highlight sales figures in Q2 that are above a certain threshold, using a value from the "Threshold" sheet.
Data Setup
Sales Sheet ("Sales"):
A | B | C |
Product | Sales Q1 | Sales Q2 |
Product A | 150 | 200 |
Product B | 100 | 250 |
Product C | 300 | 150 |
Threshold Sheet ("Threshold"):
A | B |
Threshold | Value |
Sales Q2 | 200 |
Steps
- Create the Threshold Sheet: Add a new sheet named Threshold.
- Enter the Threshold Value: In cell B2, enter 200.
- Apply Conditional Formatting:
- Go back to the Sales sheet.
- Select the range C2
(Sales Q2).
- Go to the Home tab and click on Conditional Formatting.
- Choose New Rule.
- Select Use a formula to determine which cells to format.
- In the formula box, enter:
=C2>Threshold!B2
- Click on Format, choose a fill color (e.g., light red), and click OK.
- Click OK again to apply the formatting.
Final Results in Sales Sheet (with conditional formatting applied):
A | B | C |
Product | Sales Q1 | Sales Q2 |
Product A | 150 | 200 |
Product B | 100 | 250 |
Product C | 300 | 150 |
Assignment 7: Comparing Data Across Sheets
Objective: Compare sales figures from the "Sales" sheet to determine if each product's sales in Q2 exceed those in Q1.
Data Setup
Sales Sheet ("Sales"):
A | B | C |
Product | Sales Q1 | Sales Q2 |
Product A | 150 | 200 |
Product B | 100 | 90 |
Product C | 300 | 450 |
Comparison Sheet ("Comparison"):
A | B | C |
Product | Q2 > Q1? | Result |
Product A | ||
Product B | ||
Product C |
Steps
- Create the Comparison Sheet: Add a new sheet named Comparison.
- Enter Product Names: Populate column A with the product names.
- Determine if Q2 Exceeds Q1:
- In B2, type the formula:
=IF(Sales!C2 > Sales!B2, "Yes", "No")
- Press Enter. Cell B2 should display "Yes" or "No depending on the comparison.
- Copy the Formula for Other Products:
- Drag the fill handle from B2 down to B4 to apply the formula for other products.
- Provide a Result Summary:
- In C2, type:
=IF(B2="Yes", "Sales increased", "Sales decreased")
- Drag down the fill handle to apply to C3 and C4.
Final Results in Comparison Sheet:
A | B | C |
Product | Q2 > Q1? | Result |
Product A | Yes | Sales increased |
Product B | No | Sales decreased |
Product C | Yes | Sales increased |
