MS Excel [Week 17-18]

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 to Insert > PivotTable.

Example:

Analyze sales data by region:

  1. Place regions in Rows.
  2. Place sales amounts in Values.
  3. 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:

  1. Select the cells you want the dropdown in.
  2. Go to Data > Data Validation > List.
  3. 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 to Data > 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.