1.
Which chart type would you use to show the proportion of each item’s total sales (D2:D6) relative to the grand total (D7), and how would you create it?
2.
How would you create a chart to compare the quantity sold (C2:C6) across all items, and what chart type would be most effective?
3.
What chart type would you use to show the relationship between price (B2:B6) and total sales (D2:D6) for each item, and how would you set it up?
4.
If you write =A1 in cell E2 and copy it to E3, what value will appear in E3, and why?
5.
If you enter =$A1 in cell E2 and copy it across to F2, then down to F3, what values will appear in F2 and F3?
6.
If cell B7 contains a tax rate of 0.1 (10%), and you write =D2*$B$7 in E2, then copy it from E2 to E3:E6, what will E2 and E3 show, and why?
- Answer: Use a Pie Chart. Select A1:A6 and D1:D6, go to “Insert” > “Pie Chart,” and choose a 2D pie option. Description: A pie chart is ideal for showing proportions or percentages of a whole (e.g., each item’s contribution to 112.75). Selecting A1:A6 (items) and D1:D6 (totals) ensures labels and data are linked, with D1 as the title. This tests understanding of chart types and their purposes.
2.
Answer: Use a Column Chart. Select A1:A6 and C1:C6, go to “Insert” > “Column Chart,” and pick a 2D column style. Description: A column chart effectively compares discrete values (quantities) side-by-side, with item names (A2:A6) on the x-axis and quantities (C2:C6) as heights. This tests the ability to match chart types to data comparison needs.
3.
Answer: Use a Line Chart. Select A1:A6, B1:B6, and D1:D6, go to “Insert” > “Line Chart,” and choose a line with markers. Description: A line chart can show trends or relationships between two variables (price and total) across items, with A2:A6 as x-axis labels, B2:B6 as one line, and D2:D6 as another. Markers help distinguish data points. This tests advanced chart selection and multi-series plotting.
4.
Answer: “Notebook” will appear in E3. Because A1 is a relative reference, it adjusts to A2 when copied down one row from E2 to E3. Description: A relative reference like A1 changes based on its position when copied. In E2, A1 points to “Item,” but in E3, it shifts to A2, which is “Notebook.” This tests understanding of how relative references adapt to new locations.
5.
Answer: F2 will show “Item,” and F3 will show “Notebook.” Description: The mixed reference $A1 locks the column (A) but allows the row to change. In E2, it references A1 (“Item”). Copied to F2, it stays A1 (column fixed). Copied to F3, it becomes $A2 (row shifts), showing “Notebook.” This tests knowledge of column-locked mixed references.
6.
Answer: E2 will show 2.5 (25 × 0.1), and E3 will show 3.8 (38 × 0.1). The absolute reference $B$7 stays fixed across all cells. Description: An absolute reference like $B$7 locks both column (B) and row (7). When copied from E2 (=D2*$B$7) to E3 (=D3*$B$7), only the D-column reference adjusts, while $B$7 remains constant, multiplying each total by the fixed tax rate. This tests understanding of absolute references for consistent calculations.