Advanced Excel: Mastering the Tools [Week 18]
4.1 Pivot Tables & Pivot Charts
Pivot Tables allow you to quickly summarize large datasets.
- Drag and drop fields to rows, columns, values, and filters to create summaries.
- Use Pivot Charts to visualize pivot table data.
Go toInsert > PivotTable
.
Example:
Analyze sales data by region:
- Place regions in Rows.
- Place sales amounts in Values.
- Use Filters to filter sales by specific products or date ranges.
4.2 Advanced Excel Formulas
- SUMIFS: Add cells based on multiple conditions.
=SUMIFS(sum_range, criteria_range1, criteria1, ...)
- COUNTIFS: Count cells based on multiple conditions.
=COUNTIFS(range1, criteria1, range2, criteria2, ...)
- ARRAY FORMULAS: Perform complex calculations over arrays of data.
=SUM(IF(A1:A10 > 100, 1, 0))
4.3 Data Validation
Data Validation ensures that users input only valid data (e.g., selecting values from a dropdown list).
- Go to
Data > Data Validation
to restrict cell inputs.
Example:
Create a dropdown for selecting a product category:
- Select the cells you want the dropdown in.
- Go to
Data > Data Validation > List
. - Input the categories you want available.
4.4 Excel Dashboards
Dashboards in Excel allow you to create interactive reports that summarize key data.
- Combine Pivot Tables, Pivot Charts, and other visual elements (like Slicers).
- Use charts, sparklines, and slicers to make the dashboard interactive.
Example:
Build a dashboard to track company sales, broken down by region, product, and date.
4.5 Power Query
Use Power Query to import, clean, and transform data from various sources.
- Automate repetitive data transformation tasks.
- Import data from external sources such as CSV files, SQL databases, and web APIs.
Go toData > Get & Transform Data > Get Data
.
4.6 Power Pivot
Power Pivot helps in dealing with large datasets beyond Excel’s row limits.
- Allows for creating complex data models, using multiple tables with relationships.
- Use DAX (Data Analysis Expressions) for powerful data calculations and custom aggregations.
Example:
In Power Pivot, connect your sales and customer tables to analyze customer purchasing behaviors over time.