Excel Pivot Tables

Excel Pivot Tables and Charts

Excel Pivot Tables and Charts

Turning Raw Data into Insights with Ease

PivotTables allow you to quickly summarize, analyze, and visualize large amounts of data. When combined with PivotCharts, you can create interactive dashboards and reports that make decision-making easier.

🗂️ Sample Dataset

Order IDDateRegionProductSales
10012025-01-01NorthLaptop1200
10022025-01-02EastKeyboard150
10032025-01-02WestMouse80
10042025-01-03SouthMonitor300
10052025-01-03NorthLaptop1300
10062025-01-04EastLaptop1150
10072025-01-04WestKeyboard140
10082025-01-05SouthMouse90
10092025-01-05NorthMonitor320
10102025-01-06EastLaptop1250
10112025-01-06WestLaptop1180
10122025-01-07SouthKeyboard160
10132025-01-07NorthMouse85
10142025-01-08EastMonitor310
10152025-01-08WestMonitor290
10162025-01-09SouthLaptop1220
10172025-01-09NorthKeyboard155
10182025-01-10EastMouse95
10192025-01-10WestLaptop1200
10202025-01-10SouthMonitor305

🔑 Step 1: Creating a PivotTable

  1. Select any cell in the dataset.
  2. Go to Insert → PivotTable.
  3. In the dialog box, choose the table range (A1:E21) and select “New Worksheet”.
  4. Click OK.

🛠️ Step 2: Building the PivotTable

Example: Show Total Sales by Region and Product

  • Drag Region → Rows
  • Drag Product → Columns
  • Drag Sales → Values (Sum)

Example PivotTable Output

RegionKeyboardLaptopMonitorMouseTotal
East3102400310953115
North1552500320853060
South1601220605902075
West1402380580803180
Total7658500181535011430

🔄 Step 3: Adding Filters

  • Drag Date into the Filters area to filter sales by a specific date range.
  • Drag Product into Filters to view only selected products.

📈 Step 4: Creating a PivotChart

  1. Select the PivotTable.
  2. Go to Insert → PivotChart.
  3. Choose a chart type (Column or Pie is common).
  4. Click OK.

🔥 Advanced Features

  • Group Data: Right-click on dates → Group by Month/Quarter/Year
  • Calculated Fields: Add custom formulas (e.g., Profit = Sales * 20%)
  • Slicers & Timelines: Add interactive filters to your PivotTable/Chart

✅ Summary

  • PivotTables help you summarize large datasets easily.
  • You can filter, group, and calculate totals dynamically.
  • PivotCharts give you visual insights from PivotTables.

Comments

Post a Comment

Popular posts from this blog

Excel Vba Intro

  How to Start Learning Excel VBA How to Start Learning Excel VBA – A Beginner’s Guide to Mastering Automation in Excel Introduction Are you tired of doing repetitive tasks in Excel? Want to automate reports, dashboards, or data processing? Then it's time to learn Excel VBA (Visual Basic for Applications) — the hidden engine that powers Excel automation. This guide will show you exactly how to begin, even if you have no prior programming experience. What is Excel VBA? VBA (Visual Basic for Applications) is a programming language built into Microsoft Excel and other Office applications. With VBA, you can write macros — small programs that automate actions like formatting, calculations, data entry, and more. Why Learn Excel VBA? 🚀 Boost Efficiency – Automate repetitive Excel tasks. 🔄 Create Custom Functions – Go beyond Excel’s built-in formulas. 🧩 Build User Interfaces – Add forms, buttons, menus. ...
Read more

Introduction To Excel VBA

  Module 1: Introduction to Excel VBA Programming Module 1: Introduction to Excel VBA Programming Overview Microsoft Excel is one of the most powerful tools in business environments, used extensively for data analysis, financial modeling, reporting, and automation. While Excel’s built-in functions are impressive, its true potential is unlocked through VBA (Visual Basic for Applications)—a programming language that allows users to automate tasks, customize functionality, and build powerful applications. This first module introduces you to the world of Excel VBA programming. You’ll learn what VBA is, why it’s important, and how to begin your journey as an Excel VBA developer. 1. What is VBA? VBA stands for Visual Basic for Applications, a programming language developed by Microsoft. It is embedded in most Microsoft Office applications, including Excel, Word...
Read more

Learn Excel VBA Basics

Excel Basics Review – Complete Guide Excel Basics Review – Complete Guide Microsoft Excel is a powerful tool for data analysis, management, and visualization. Whether you're a student, data analyst, administrator, or small business owner, mastering Excel's basic tools can dramatically increase your efficiency. 1. Lookup Formulas: VLOOKUP, INDEX/MATCH, XLOOKUP Basic Overview VLOOKUP: Searches a value in the first column and returns a value from a specified column. INDEX & MATCH: A flexible combination that allows reverse lookups. XLOOKUP: Modern replacement for both, easier and more powerful. Detailed Example Dataset Example: Name Department Salary John HR 3000 Mary IT 4000 Steve Finance 4500 Grace HR 3200 ...
Read more