Excel Intermediate – Pivot Tables and Charts

In this 3 hour workshop from Forrest training you will be, Creating and Formatting Pivot Tables PivotTables are powerful tools for summarizing data. They allow you to organize data by dragging fields into rows, columns, values, and filters. Formatting includes adjusting number formats, layout styles, and subtotals. Changing Value Field Settings This allows you to change the summary function used in a PivotTable, such as switching from Sum to Count, Average, Max, etc. It's key for adapting PivotTables to different reporting needs. Filtering and Slicers Filters narrow down PivotTable data based on specific criteria. Slicers provide a visual interface to filter data interactively. They are easier to use and more intuitive than standard dropdown filters. Grouping Data by Date Date grouping allows you to combine data into weeks, months, quarters, or years. This is essential for trend analysis. If dates won’t group, check for invalid or text-based date entries. Creating Charts from PivotTables You can insert a PivotChart from a PivotTable to visualize data. Charts can be adjusted independently, including layout, style, and data labels. Linked to the PivotTable, they update dynamically with filters. Understanding Chart Types Excel offers Column, Line, Pie, Bar, and more. Choosing the right chart depends on the story you want to tell. Column and Line charts are good for comparisons over time, Pie for proportions. REPT Function for In-Cell Bars The REPT function can display simple bar charts in cells by repeating characters. For example, =REPT("|",A1) draws a bar based on the value in A1. It's a text-based visual tool for quick reviews. Sparklines These are mini charts that fit in a single cell, perfect for showing trends in rows of data. They are great for dashboards where space is limited and fast comparisons are needed. Conditional Formatting – Data Bars Data Bars visually represent values with colored bars inside cells. Use them for quick visual impact on value comparison without inserting full charts.

Creating and Formatting Pivot Tables

PivotTables are powerful tools for summarizing data. They allow you to organize data by dragging fields into rows, columns, values, and filters. Formatting includes adjusting number formats, layout styles, and subtotals.

Changing Value Field Settings

This allows you to change the summary function used in a PivotTable, such as switching from Sum to Count, Average, Max, etc. It's key for adapting PivotTables to...