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.
- 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.
- 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.