O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Excel 2010 Advanced

Video Description

Microsoft Excel. It's a mainstay for many Office users. Unfortunately, many people know the BASICS of spreadsheets but they never get to the next level, integrating and advancing basic skills to escalate worksheets to elegant, powerful solutions. In this series, new and longtime Excel users will learn how to take core skills to the next level by exploring advanced functions and chart features, connecting to external data, utilizing data tools, leveraging auditing features and gaining insight with what-if features. You'll also learn how to create macros and work with basic VBA code to customize and automate your work. Does EVERY Excel user need to know these skills? No. But if you want to use Excel beyond simple data entry and calculations to its fullest potential and therefore be able to reach your own full potential, this series will help get you there.

Table of Contents

  1. ADVANCED CALCULATIONS
    1. Introduction 00:00:06
    2. Using Lookup Functions 00:13:30
    3. Using Text Functions 00:11:03
    4. Breaking Apart Text Entries 00:11:03
    5. Using Date & Time Functions 00:07:50
    6. Using the Net Workdays Function 00:09:23
    7. Using Logical Functions & Fixing Errors 00:14:01
    8. Using Text in Calculations 00:07:23
    9. Using Financial Functions 00:11:03
    10. Using the Cumulative Interest Payment Function 00:04:54
    11. Using Aggregate vs. Math Functions 00:07:18
  2. SUBTOTALS & OUTLINES
    1. Introduction 00:00:06
    2. Creating Subtotals 00:08:21
    3. Working with Outlined Data 00:05:32
    4. Manually Creating Subtotals & Outlines 00:07:13
    5. Creating Custom Views 00:03:42
  3. PIVOTTABLES©
    1. Introduction 00:00:06
    2. Constructing PivotTables 00:13:21
    3. Modifying PivotTable Calculations 00:07:10
    4. Setting PivotTable Properties 00:07:53
    5. Using Filters & Slicers in the PivotTable 00:09:50
    6. Using the Report Filter & Updating Data 00:08:57
    7. Working with PivotTable Data in Formulas & PivotCharts 00:10:00
  4. ADAPTING CHARTS FOR COMPLEX DATA
    1. Introduction 00:00:06
    2. Adding a Secondary Chart Axis 00:05:24
    3. Working with a Scatter Chart 00:04:48
    4. Hidden & Empty Cell Settings, Formatting Error Bars 00:05:53
    5. Adding a Trendline 00:02:55
  5. ADVANCED DATA TOOLS
    1. Introduction 00:00:06
    2. Importing Data & Adding Hyperlinks 00:11:18
    3. Converting Text to Columns 00:03:52
    4. Linking to Databases & Removing Duplicate Records 00:08:24
    5. Consolidating Data 00:10:37
    6. Validating & Protecting Data 00:10:19
  6. AUDITING CALCULATED VALUES
    1. Introduction 00:00:06
    2. Working with Circular References 00:09:34
    3. Tracing Precedents & Dependents 00:03:50
    4. Evaluating Formulas 00:03:12
    5. Using the Watch Window 00:05:07
    6. Error Checking 00:12:00
  7. PERFORMING A WHAT-IF ANALYSIS
    1. Introduction 00:00:06
    2. Finding an Input Value with Goal Seek 00:09:02
    3. Displaying Options via Scenarios 00:11:35
    4. Including Variable Values through Data Tables 00:07:51
  8. MACROS, VBA & SECURITY
    1. Introduction 00:00:06
    2. Macro Concepts 00:08:47
    3. Creating a Basic Macro 00:09:57
    4. Manipulating Cell & Range References 00:07:11
    5. A Brief Introduction to VBA 00:06:40
    6. Viewing & Editing VBA in Excel 00:07:02
    7. Making Macros Accessible 00:05:13
    8. Applying Security Settings 00:14:37
    9. Credits 00:00:12