Author: Chandoo

My name is Chandoo. I am going to make you awesome in Excel, VBA, Dashboards, Power BI & Power Pivot and answering your questions. I live in Wellington, New Zealand. It is a beautiful, small city on the southern edge of the world. I run my own Excel Consulting & Training business from here. I am married to my college sweet-heart, Jo. We have 2 kids – twins. They are 10 years old now. They are busy creating new worlds with lego bricks or playing or learning. We (Jo &I) are busy with cooking, eating, walking, talking and reading.

Advanced Formulas

What is in this lesson?

Named Ranges
Using Formula Audit Tools
3D References (example: consolidate sales data)
Array Formulas – What are they? (example: MAXIF, MINIF)
Circular References – What are they? (example: log)
Various Calculation Modes and How to use them
Formula Errors and how to handle them

Continue reading “Advanced Formulas”

Excel Access Integration

What is in this lesson?

This is a guest lesson I did with famous Microsoft Excel MVP – Mike Alexander. The lesson is pure joy and full of valuable insights on using excel and access.

Excel to Access
Access to Excel
Integrating Excel & Access thru Automation

Continue reading “Excel Access Integration”

Export Excel to Other Formats

What is in this lesson?

How to Export Excel Content to PPT or Word Documents?
How to Export Excel Content to Web Pages
How to Export Excel Content to PDF

Continue reading “Export Excel to Other Formats”

Importing External Data

What is in this lesson?

What is the purpose of Excel Import?
Understanding various import options
Importing Text Files – Example: Dynamic Data Validation
Importing Web Pages – Example: Movie Tracker
Importing Access DBs – Example: Sales Pivot Report
Using Text Import Utility – Example: Converting Dates

Continue reading “Importing External Data”

Data Validation

What is in this lesson?

Uses of Data Validation
Simple Data Validation Examples
Using drop-down box to validate
Using Formulas
Showing Error Messages
Advanced DV Tricks

Continue reading “Data Validation”

Data Filters & Sorting

What is in this lesson?

Uses of Filters
Basic Filtering
Short-cuts for filtering
Visual Filters – Using Conditional Formats
Sorting
Sorting left to right

Continue reading “Data Filters & Sorting”

Pivot Table Tricks

What is in this lesson?

Guest lecture by Debra Dalgleish
Preparing your data
Pivot and chart
Group dates and data
Calculate
Focus with filters, sort

Continue reading “Pivot Table Tricks”

Excel Pivot Tables

What is in this lesson?

What Is A Pivot, Why You Should Use It
Creating Your First Pivot Report
Formatting Pivots
Summarizing Data, Playing With Pivots
Grouping Values
Calculated Fields
Calculated Items
Making A Pivot Chart
Copying Pivot Tables
Example Uses Of Pivots

Continue reading “Excel Pivot Tables”

Excel Tables

What is in this lesson?

What Is A Table?
Format As Table
Understand Table Formatting Options
Filters, Can Get Multiple Filters In The Same Sheet
Total Row, Ability To Select Type Of Summary
Structured References – What Are They?
Write A Formula For Profit %
Write A Formula For Profitability Per Customer
Advantages Of Tables
Convert Table To Named Range
Compatibility Of Tables With Excel 2003 And Earlier

Continue reading “Excel Tables”

Excel School Class Project # 1

What is in this lesson?

Class project objective
building an interactive chart to compare our performance with competition

Continue reading “Excel School Class Project # 1”