📊 Excel-17

✨ Project Description

Excel-17 is your guide to mastering Pivot Tables in Microsoft Excel.
Learn practical tips, step-by-step instructions, and see illustrated examples for:

• Pivot Tables
• Grouping
• Multi-level Pivot Tables
• Multiple Report Filter Fields
• Frequency Distribution
• Pivot Charts
• Slicers
• Calculated Field/Item
• GETPIVOTDATA function

📚 Goal: Help you analyze data efficiently and create insightful Excel reports—ideal for learners and power users!

---

📒 Table of Contents

• Pivot Tables
• Sort & Filter Pivot Table
• Change Summary Calculation
• Two-dimensional & Multi-level Pivot Tables
• Grouping Items & Dates
• Multiple Value & Report Filter Fields
• Frequency Distribution
• Pivot Charts
• Slicers
• Update Pivot Table
• Calculated Field/Item
• GETPIVOTDATA Function
• Screenshots
• Requirements
• Author

---

📊 Pivot Tables

Pivot tables are a powerful Excel feature for summarizing large data sets.

To create a pivot table:

1. Click any cell in your data set.
2. Go to the Insert tab → Tables group → PivotTable. Excel auto-selects your data. Default is a new worksheet.

3. Click OK.

The PivotTable Fields pane opens.
To summarize exported amounts by product:

• Drag Product to Rows
• Drag Amount to Values
• Drag Country to Filters

Result:

---

🔍 Sort & Filter Pivot Table

Sort:

1. Click a cell in the Sum of Amount column.
2. Right-click → Sort → Sort Largest to Smallest.

Filter: Click the filter drop-down and select France.

Result:

---

🧮 Change Summary Calculation

1. Click any cell in Sum of Amount.
2. Right-click → Value Field Settings.
3. Choose calculation type (e.g., Count).

4. Click OK.

16 out of the 28 orders to France were 'Apple' orders.

---

🔀 Two-dimensional & Multi-level Pivot Tables

Two-dimensional:

• Drag Country to Rows
• Drag Product to Columns
• Drag Amount to Values
• Drag Category to Filters

Result:

Multi-level: Drag more than one field to Rows (e.g., Category and Country).

Result:

---

🔗 Grouping Items & Dates

Group Products:

• Select items, right-click → Group.

Click minus signs to collapse.

Group Dates: Add Date to Rows.
Right-click a date, Group → Quarters.

Quarter 2 is the best!

---

➕ Multiple Value & Report Filter Fields

Multiple Values:

• Country to Rows
• Amount to Values (twice)

To show % of Grand Total:

• Value Field Settings → Custom Name: Percentage
• Show Values As: % of Grand Total

Multiple Filters:

• Order ID to Rows
• Amount to Values
• Country & Product to Filters

Select United Kingdom and Broccoli in filters.

Result:

---

📈 Frequency Distribution

Pivot tables can create frequency distributions (histograms).

• Amount to Rows
• Amount to Values

Change Values to Count.

Group amounts (e.g., by 1000):

Result:

Create a pivot chart for visualization:

---

📊 Pivot Charts

Pivot charts visualize pivot tables.

Use your 2D pivot table:

1. Click a cell in the pivot table
2. PivotTable Analyze → Tools → PivotChart → OK

Changes sync between chart and table!

Filter Pivot Chart: Use Country filter to show only US exports.

Use Category filter to show vegetables.

Change Chart Type: Select chart → Design → Change Chart Type → Pie → OK

Pie charts show one data series.

---

🥒 Slicers

Slicers make filtering pivot tables intuitive.

Insert a slicer:

1. Click a cell in your pivot table
2. PivotTable Analyze → Filters → Insert Slicer
3. Check Country → OK

Click United States for product details.

Insert a Product slicer, select style, use CTRL to select multiple.

Connect slicers to multiple tables:

• Insert a second pivot table
• Select slicer → Slicer tab → Report Connections → select table

Click the icon in a slicer to clear its filter:

No beans or carrots were exported to Canada!

---

🔄 Update Pivot Table

Pivot tables do not auto-refresh.
To update after changing data:

1. Click any cell in the pivot table
2. Right-click → Refresh

Or set "Refresh data when opening file" in PivotTable Options.

---

🧮 Calculated Field/Item

Calculated Field:
Uses values from other fields.

1. Click a cell in the pivot table
2. PivotTable Analyze → Calculations → Fields, Items & Sets → Calculated Field

Insert formula, e.g.,

=IF(Amount>100000, 3%*Amount, 0)

Excel adds the field to Values.

Calculated Item:
Uses values from other items.

1. Click a Country in the pivot table
2. PivotTable Analyze → Calculations → Fields, Items & Sets → Calculated Item

Insert formula, e.g.,

=3%*(Australia+'New Zealand')

Created groups: Sales and Taxes.

---

📑 GETPIVOTDATA Function

GETPIVOTDATA returns visible data from a PivotTable.

📚 Official Documentation

• GETPIVOTDATA function — Microsoft Docs

---

📷 Screenshots

All screenshots are in the /Screenshots folder.

---

ℹ️ Requirements

• Microsoft Excel (recommended: 2021/365)
• Windows OS

---

👨‍💻 Author

Project and documentation by Kuba27x
Repository: Kuba27x/Excel-17

---