Practice 13: Sparklines & Trendline Visualizations

Key Topics & Core Concepts

In-Cell Sparklines: Inserting Line and Column sparklines to show multi-period trajectory within a single cell.
Sparkline Point Markers: Highlighting High Point, Low Point, First Point, and Negative Points using contrast markers.
Trendline Modeling: Adding Linear and Moving Average trendlines to standard line and column charts.
Forecasting & R-Squared: Extending forward forecast periods and displaying regression equations with $R^2$ fit values.

Assignment Overview & Deliverables

In this practice exercise, you will enhance executive summary tables by creating in-cell sparklines, applying marker styles, and configuring chart trendlines to project future demand.

Starter Workbook: Download PerfectRentals-04.xlsx
Completed File Name: [your initials] Practice13_Sparklines.xlsx
Active Worksheets: Summary, Monthly Trends, and Forecast Model

Step-by-Step Instructions

Step 1: Open Starter & Save Workbook

Download and open PerfectRentals-04.xlsx in Excel.
Click Enable Editing if opened in Protected View.
Save the workbook as [your initials] Practice13_Sparklines.xlsx.

Step 2: Insert In-Cell Line Sparklines (Summary Sheet)

Click the Summary worksheet tab.
Select the destination cells for the trend sparklines (G4:G15).
On the Ribbon, go to Insert > Sparklines group > Line.
In the Data Range box, select the 6-month revenue figures across columns A4:F15 (or B4:F15 depending on department labels).
Click OK. Verify that miniature sparklines appear in each cell of column G.

Step 3: Format Sparkline Markers & Axis Bounds

With cell range G4:G15 selected, go to the Sparkline tab on the Ribbon.
In the Show group, check the boxes for High Point and Low Point.
Click Marker Color:
- Set High Point to Green.
- Set Low Point to Red.
Click Axis in the Sparkline tab and under Vertical Axis Minimum/Maximum Value Options, select Same for All Sparklines to ensure true scale proportionality across rows.

Step 4: Build Trendline on Monthly Trends Chart

Click the Monthly Trends tab.
Click on the line chart series representing Total Rental Revenue.
Click the green + (Chart Elements) button at the top-right of the chart.
Hover over Trendline, click the arrow, and select Linear.
Double-click the newly created trendline to open the Format Trendline task pane:
- Under Forecast, set Forward to 2.0 periods.
- Check Display Equation on chart.
- Check Display R-squared value on chart.

Step 5: Format & Save Workbook

Move the trendline equation callout box to an uncluttered area in the upper-left of the chart plot area.
Format trendline stroke to a dashed line in CU Denver Gold (#CFB87C).
Ensure all numbers and summary cells are formatted cleanly as Currency ($#,##0).
Save your workbook.

Practice exercises are self-paced and formative to prepare you for homework submissions.

Key Skills Evaluated

Skill Area	Verification Target
Sparkline Creation	Line sparklines inserted in column G referencing multi-month range.
Marker Formatting	High and Low point markers enabled with distinct green/red color coding.
Axis Scaling	Vertical axis min/max set to 'Same for All Sparklines'.
Trendline Forecasting	Linear trendline added, forecasted forward 2 periods with equation and $R^2$ displayed.