Excel Chapter 5: Adding Charts and Analyzing Data

Skills 5.1 - 5.6: Chart Insertion
- 5.1 Inserting a Column Chart or a Bar Chart
- 5.2 Inserting a Pie Chart
- 5.3 Inserting a Line Chart
- 5.4 Resizing and Moving Charts
- 5.5 Applying Quick Layouts to Charts
- 5.6 Showing and Hiding Chart Elements
Skills 5.7 - 5.12: Enhancing Charts and Data Presentation
- 5.7 Applying Quick Styles and Colors to Charts
- 5.8 Changing the Chart Type
- 5.9 Filtering Chart Data
- 5.10 Inserting Sparklines
- 5.11 Converting Data into Tables
- 5.12 Applying Quick Styles to Tables
Skills 5.13 - 5.18: Managing Tables
- 5.13 Adding Total Rows to Tables
- 5.14 Removing Duplicate Rows from Tables
- 5.15 Sorting Data
- 5.16 Filtering Data
- 5.17 Filtering Table Data with Slicers
- 5.18 Converting Tables to Ranges
Skills 5.19 - 5.22: Advanced Data Analysis
- 5.19 Creating PivotTables Using Recommended PivotTables
- 5.20 Creating a PivotChart from a PivotTable
- 5.21 Analyzing Data with Data Tables
- 5.22 Analyzing Data with Goal Seek

Column Charts
- Suitable for data organized in rows and columns.
- Example: A Clustered Column Chart where each column represents a single data point, organized by category.
- Each data series shown in a different color (legend provided example).
Bar Charts
- Essentially a column chart rotated 90 degrees.
- Categories on the vertical axis and data values on the horizontal axis.
- Inserting a Bar Chart:
1. Select the desired data.
2. Navigate to the Insert tab and click ‘Insert Column or Bar Chart’.
3. Choose the specific bar chart style.

Functionality: Pie charts represent parts of a whole. They are best used to evaluate individual values in relation to a total.
Inserting a Pie Chart:
1. Select the data.
2. Select ‘Insert Pie Chart’ from the Insert tab.
3. Choose the specific pie chart style.

Line Charts show trends over time with lines connecting data points (best applied to time-based data).
Insertion Steps:
1. Select pertinent data including time-related cells.
2. Click ‘Insert Line Chart’ on the Insert tab.
3. If categories do not align correctly, switch data series using the Switch Row/Column button.

Resizing a Chart:
1. Select the chart and drag resize handles to alter size.
2. To make it smaller: drag handles inward; to enlarge, drag outward.
Moving a Chart:
1. Select the chart carefully to avoid moving the plot area.
2. Drag the chart to the desired location.
3. To move to its own sheet: Select the chart, navigate to the 'Move Chart' option, select 'New sheet', and name it.

Applying Quick Layouts:
1. Select the chart.
2. Use the Chart Tools Design tab and select the Quick Layouts button.
3. Hover over options to preview and click to apply.

Manipulation of Elements: (titles, gridlines, legends, etc.)
1. Click any empty chart area.
2. Use the Chart Elements button to toggle visibility.
3. Utilize checkboxes to manage which elements are displayed.

Applying Quick Styles: Select the chart and navigate to the Chart Styles group in the Chart Tools Design tab, and select the style or color scheme desired.

Switching Chart Types:
1. Click the ‘Change Chart Type’ button in the Chart Tools Design tab.
2. Choose the new chart category and select a specific type.

Using Chart Filters: Display data of interest without changing the data source.
1. Select the chart.
2. Use the Chart Filters button near the upper right of the chart to modify what data appears visually.

Definition: Sparklines are mini charts within cells to visualize trends.
Insertion Method:
1. Select desired data.
2. Use the Quick Analysis Tool, then select the Sparklines tab and choose the type.

Table Definition: Enhances data organization and access.
Actions To Create a Table:
1. Click any cell in the data.
2. Navigate to the Home tab, click ‘Format as Table’ in the Styles group, and select desired style.

Quick Style Application: Applies design elements for clarity and aesthetics.
Steps:
1. Select the table.
2. Navigate to the Table Tools Design tab and select Quick Styles.

Action Steps:
1. Use the Table Tools Design tab to enable the Total Row checkbox.
2. Choose a function for calculations in the Total Row cells.

Action Steps:
1. Select a cell in the table, go to Table Tools Design tab, click ‘Remove Duplicates’.
2. Customize column selection based on specific duplicates criteria.

Sorting Process:
1. Select the column to sort by.
2. Navigate to the Data tab, then click to act on sorting commands (A-Z or Z-A).

Filter Enabling:
1. Ensure filtering is enabled by clicking the Filter button on the Home tab.
2. Select desired conditions via the dropdowns for filtering specific data sets.

Slicer Utilization: Adds interactive filtering options.
Insertion Directions:
1. Select the table, click the Insert Slicer button on Table Tools Design tab, check desired columns for slicers.

Conversion Steps:
1. On Table Tools Design tab, find ‘Convert to Range’ option.
2. Confirm with Yes on the prompt to convert.

PivotTable Definition: Special report viewing tool for data summarization.
Creation Process:
1. Select a relevant cell, then on the Insert tab, click the Recommended PivotTables button.
2. Choose the desired option and confirm.

Creation Steps:
1. Select the PivotTable, then choose PivotChart from the Tools tab to visualize data via charts.

One-variable Data Table Instructions:
1. Setup your input values in a column and corresponding formula above.
2. Use the Data Table feature from the What-If Analysis menu.

Goal Seek Overview: Allows manipulation of input cell values to meet desired outcomes in formulas.
Process:
1. Select the Goal Seek from the What-If Analysis menu.
2. Input necessary cells for outcome, desired value, and changeable input.

Mastering these skills is crucial for effective data visualization and analysis in Excel, allowing for better decision-making based on analytical insights.