Module 5D.1

Creating Visual Representations of Data in Excel

Overview of Data Visualization in Excel

  • Importance of visual data representation
  • Introduction to Target vs. Achieve data sets

Initial Setup

  • Visual chart showcasing Targeted vs. Achieved RPE:
    • Dark orange indicates the session RPE of the athlete.
    • Light orange denotes the program session RPE.
  • Utilization of a scroller to navigate through different athletes' data.

Creating a New Spreadsheet

  • Steps to create a new sheet:
    • Add a new sheet and name it "Targeted vs. Achieved Exercise One."
    • Start with a blank sheet for data entry.
  • Data generation:
    • Use the RAND BETWEEN function to randomly generate RPE values for the athletes.
    • Paste data into the spreadsheet based on previous exercises

Enabling Developer Tab in Excel

  • Accessing Developer Tab:
    • Navigate to Excel Preferences.
    • Select "Ribbon and Toolbar" and add the Developer tab by checking the appropriate box.

Adding a List Box

  • Steps to Insert a List Box:
    • Locate the List Box option in the Developer toolbar.
    • Drag the List Box into the spreadsheet.
    • Position the List Box in a convenient location for ease of use.
  • Formatting List Box Control:
    • Right-click the List Box to format the control.
    • Specify the input range using defined list names from the table.
    • Set a designated cell for output reference.

Data Input and Formatting

  • Organizing data:
    • Days of the week entered as headers.
    • Use COPY and PASTE SPECIAL with TRANSPOSE to align days in the working table correctly.
  • Utilizing Functions in Excel:
    • Employ the HLOOKUP function for data retrieval based on specific criteria:
    • Syntax: =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
    • Example: =HLOOKUP("Monday", table_range, 2, FALSE) retrieves the target session RPE for Monday.

Fixing Formula Issues

  • Troubleshooting Common Formula Errors:
    • Ensure the correct use of locking cell references with dollar signs (e.g., B$1:I$1) when dragging formulas downwards.
    • Adjusting the index range by adding to the position lookup where necessary (e.g., adding +2 to skip irrelevant header rows).
  • Verify calculations using sample data to ensure formulas work correctly.

Creating Charts

  • Inserting a Chart:
    • Select the relevant data and choose to insert a stacked column chart.
  • Chart Formatting Adjustments:
    • Resize the chart for better visibility.
    • Change series colors for clarity:
    • Lighter color for Planned Session RPE.
    • Darker color for Achieved RPE.

Enhancing Chart Visuals

  • Adding Chart Elements:
    • Remove unnecessary axis labels for clarity and aesthetic appeal.
    • Add data labels to clarify RPE values directly on the chart.
    • Format data series colors to maintain a consistent visual theme (e.g., orange color scheme).
  • Managing Chart Area and Background:
    • Apply a solid border to the chart area for definition.
    • Set a white background for a cleaner appearance.

Finalizing the Chart

  • Review and refine for final presentation:
    • Toggle through various athletes’ data to ensure functionality of the dynamic elements.
    • Confirm the naming of the chart as "Targeted vs. Achieved Session RPE" for clear identification.
  • This process provides a flexible and informative way to visualize and compare athletes’ RPE data.