Financial Modeling School

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”

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”

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”

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”

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”

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”

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”

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”

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”

What is in this lesson?

• Need for interactive charts
• Introduction to form controls
• Using Scrollbar control
• using checkboxes
• Example interactive charts (2)

Continue reading “Interactive Charts In Excel”

What is in this lesson?

• Need for Dynamic Charts
• Dynamic charts using Filters
• Dynamic charts using camera tool
• More info. & ideas on dynamic charts

Continue reading “Dynamic Charts in Excel”

What is in this lesson?

• Bullet chart – what is it and how to make
• Incell charts – using REPT to make charts
• Min-Max chart – showing range along with average
• Waterfall charts
• Time line charts
• Thermo-meter charts
• Gauge Chart

Continue reading “7 Advanced Charting Tutorials in Excel”

What is in this lesson?

• Overlapped Bar Charts, Understanding Series Gaps
• Negative bars
• Highlighting the max or minimum item automatically
• Adding average line to the chart

Continue reading “Bar & Column Chart Formatting”

What is in this lesson?

• Making titles dynamic
• Reversing categories
• Adjusting axis – (non-date)
• Adjusting axis – (date)
• Removing axis, grid lines, legend
• Combining 2 chart types
• Adding secondary axis
• Error bars, adding and formatting them
• Choosing right colors
• Chart Templates

Continue reading “Chart Formatting – 10 Tips”

What is in this lesson?

• Understanding Excel Charting Options
• Bar Charts / Column Charts
• Line Charts
• Scatter Plots
• Pie Charts
• Other Charts in Excel

Continue reading “Introduction to Excel Charts”

What is in this lesson?

• Basics of Chart Selection – Rationale
• Common Messages for charts
• Which Charts to use
• Further help & Tips on Chart selection

Continue reading “How to Select Correct Chart”

What is in this lesson?

• Conditional Formatting & Dates
• Conditional Formatting and Duplicate Data
• Complex CF Examples

Continue reading “Conditional Formatting – 3”

What is in this lesson?

• Advanced CF Options
• Using Icons
• Using Data Bars
• Using Heat-maps
• Highlighting Top 5, above average etc.

Continue reading “Conditional Formatting – 2”

What is in this lesson?

• What is Conditional Formatting?
• Basic CF with values
• Using Formulas in CF
• Examples of CF

Continue reading “Conditional Formatting – 1”

What is in this lesson?

• Make phone numbers readable
• Make joining date readable
• Currency format sales and profits
• Understand how formats work, examples
• Conditional format codes – examples
• Hide a value without really removing it
• Make the entire table readable
• Copy and paste formats
• Column widths and row heights
• Format Painter, Paste Special
• Freezing the top row of table

Continue reading “Formatting Paste Special”