Visualizing and Summarizing Data

Visualizing Data with Charts

A chart can often reveal insights and trends that are difficult to see in a table of numbers. Excel offers a wide variety of chart types to help you tell your data’s story.

How to Create a Chart:

1. Select the data you want to include in your chart, including the headers.
2. Go to the Insert tab on the Ribbon.
3. In the Charts group, choose a chart type (e.g., Column, Line, Pie). Excel will even suggest some Recommended Charts for you.

Customizing Your Chart:
Once a chart is created, you can click on it to reveal two new contextual tabs: Chart Design and Format.

• Use Chart Design to change the chart type, switch rows/columns, change the color scheme, or add chart elements like titles, data labels, and a legend.
• Use Format for more detailed styling of individual chart elements, like changing the color of a single bar or adjusting text fonts.

Sparklines: Charts in a Cell

What if you want a quick visual trend next to each row of data without creating dozens of full-sized charts? Use Sparklines. These are tiny, lightweight charts that live inside a single cell.

How to Add a Sparkline:

1. Select the cell where you want the sparkline to appear.
2. Go to the Insert tab and find the Sparklines group.
3. Choose a type (Line or Column is most common).
4. In the dialog box, select the Data Range for that specific row.
5. Click OK. You can then drag the fill handle down to create sparklines for the other rows.

Introduction to PivotTables

When you have a large dataset, PivotTables are the most powerful tool for summarizing, grouping, and analyzing data without writing a single formula. They allow you to “pivot” your data, looking at it from different angles to answer questions quickly.

How to Create a PivotTable:

1. Click anywhere inside your source data table.
2. Go to the Insert tab and click PivotTable.
3. Excel will confirm your data range and ask where to place the PivotTable (a new worksheet is usually best). Click OK.

You will now see a blank PivotTable on the left and the PivotTable Fields pane on the right. This pane is your control center. It contains:

• A list of all your columns (Fields) at the top.
• Four areas at the bottom: Filters, Columns, Rows, and Values.

To build a report, you simply drag a field from the list into one of the four areas. For example, to see total sales by region:

• Drag the “Region” field to the Rows area.
• Drag the “Sales” field to the Values area.

Instantly, you have a summary table.

Working with Your PivotTable

• Refreshing Data: PivotTables do not update automatically when you change your source data. You must right-click the PivotTable and select Refresh, or go to the PivotTable Analyze tab and click Refresh.
• Changing Calculations: By default, PivotTables SUM numbers. To change this, right-click a value, go to Summarize Values By, and choose Count, Average, Max, etc.
• Grouping: You can group items by selecting them, right-clicking, and choosing Group. This is extremely useful for grouping dates into months/quarters/years or numbers into ranges.
• Slicers and Timelines: For highly intuitive filtering, go to the PivotTable Analyze tab and select Insert Slicer or Insert Timeline. These create interactive buttons that make it easy for anyone to filter the report.

PivotTables vs. Conditional Functions

When should you use a PivotTable versus a function like SUMIFS?

• Use PivotTables for exploration, when you want to quickly analyze data from multiple perspectives, or when you need to create interactive reports for others.
• Use SUMIFS/COUNTIFS when you need a fixed, specific calculation that updates automatically as part of a static dashboard or report.