Vòng 1

• At the zoo
• Khoanh tròn vào câu trả lời đúng
• Chọn thứ tự đúng
• Chọn thứ tự đúng - Đề 2
• Leave me out (Loại chữ cái thừa) - Đề 1
• Leave me out (Loại chữ cái thừa) - Đề 2
• Chọn thứ tự đúng - Đề 3
• Find the honey
• Chọn thứ tự đúng - Đề 4

Course Introduction

• Ask Questions
• Course Exercise Files
• Certificate of Completion

Microsoft Excel Fundamentals

• Launching Excel
• Microsoft Excel Startup Screen
• Introduction to the Excel Interface
• Customizing the Excel Quick Access Toolbar
• More on the Excel Interface
• Understanding the Structure of an Excel Workbook
• Saving an Excel Document
• Opening an Existing Excel Document
• Common Excel Shortcut Keys

Entering and Editing Text and Formulas

• Entering Text to Create Spreadsheet Titles
• Working with Numeric Data in Excel
• Entering Date Values in Excel
• Working with Cell References
• Creating Basic Formulas in Excel
• Relative Versus Absolute Cell References in Formulas
• Understanding the Order of Operation (DOWNLOAD EXERCISE FILE)

Working with Basic Excel Functions

• The structure of an Excel Function
• Working with the SUM() Function
• Working with the MIN() and MAX() Functions
• Working with the AVERAGE() Function
• Working with the COUNT() Function
• Adjacent Cells Error in Excel Calculations
• Using the AutoSum Command
• Excel's AutoSum Shortcut Key
• Using the AutoFill Command to Copy Formulas

Modifying an Excel Worksheet

• Moving and Copying Data in an Excel Worksheet
• Inserting and Deleting Rows and Columns
• Changing the Width and Height of Cells
• Hiding and Unhiding Excel Rows and Columns
• Renaming an Excel Worksheet
• Deleting an Excel Worksheet
• Moving and Copying an Excel Worksheet

Formatting Data in an Excel Worksheet

• Working with Font Formatting Commands
• Changing the Background Color of a Cell
• Adding Borders to Cells
• Formatting Data as Currency Values
• Formatting Percentages
• Using Excel's Format Painter
• Creating Styles to Format Data
• Merging and Centering Cells
• Using Conditional Formatting

Inserting Images and Shapes into an Excel Worksheet

• Inserting Images
• Inserting Excel Shapes
• Formatting Excel Shapes
• Working with Excel SmartArt

Creating Basic Charts in Excel

• Creating an Excel Column Chart
• Working with the Excel Chart Ribbon
• Adding and Modifying Data on an Excel Chart
• Formatting an Excel Chart
• Moving a Chart to another Worksheet
• Working with Excel Pie Charts

Printing an Excel Worksheet

• Viewing your Document in Print Preview
• Changing the Margins, Scaling and Orientation
• Working with Page Layout View
• Adding Header and Footer Content
• Printing a Specific Range of Cells

Working with Excel Templates

• Intro to Excel Templates
• Opening an Existing Template
• Creating a Custom Template

Congratulations! You've Built a Solid Microsoft Excel Foundation

• Congratulations!

Microsoft Excel 102 Course Introduction

• Excel 102 Course Exercise Files -- DOWNLOAD

13. Working with an Excel List

• Understanding Excel List Structure
• Sorting a List Using Single Level Sort
• Sorting a List Using Multi-Level Sorts
• Using Custom Sorts in an Excel List
• Filter an Excel List Using the AutoFilter Tool
• Creating Subtotals in a List
• Format a List as a Table
• Using Conditional Formatting to Find Duplicates
• Removing Duplicates

14. Excel List Functions

• Introduction to Excels Function DSUM()
• Excel DSUM Function Single Criteria Continued
• Excel DSUM Function with OR Criteria
• Excel DSUM Function with AND Criteria
• Excel Function DAVERAGE()
• Excel Function DCOUNT()
• Excel Function SUBTOTAL()

15. Excel Data Validation

• Understanding the Need for Excel Data Validation
• Creating an Excel Data Validation List
• Excel Decimal Data Validation
• Adding a Custom Excel Data Validation Error
• Dynamic Formulas by Using Excel Data Validation Techniques

16. Importing and Exporting Data

• Importing Data Into Microsoft Excel
• Importing Data from Text Files
• Importing Data from Microsoft Access
• NEW VERSION -- Import Data From Text Files into Excel
• NEW VERSION -- Import Data From a Database into Excel
• Microsoft Excel Legacy Import Options for New Excel Versions
• Exporting Data to a Text File

17. Excel PivotTables

• Understanding Excel PivotTables
• Why PowerPivot
• Activating the Excel PowerPivot AddIn
• Creating an Excel PivotTable
• Modifying Excel PivotTable Calculations
• Creating Data Models with PowerPivot
• Creating PivotTables based on Data Models
• Grouping PivotTable Data
• Formatting PivotTable Data
• PowerPivot Calculated Fields
• PowerPivot KPIs
• Drilling Down into PivotTable Data
• Creating PivotCharts
• Filtering PivotTable Data
• Filtering with the Slicer Tool

18. Working with Excel's PowerPivot Tools

19. Working with Large Sets of Excel Data

• Using the Freeze Panes Tool
• Grouping Data (Columns andor Rows)
• Print Options for Large Sets of Data
• Linking Worksheets (3D Formulas)
• Consolidating Data from Multiple Worksheets

20. Congratulations! You're an Intermediate Excel User

• Congratulations!

21. Microsoft Excel 103 Course Introduction

• Excel 103 Course Exercise Files -- DOWNLOAD

22. Working with Excel's Conditional Functions

• Working with Excel Name Ranges
• Using Excel's IFERROR() Function
• Advantages and Disadvantages of Excel Name Ranges
• Editing an Excel Name Range
• Using Excel's IF() Function
• Excel's IF() Function with a Name Range
• Nesting Functions with Excel
• Nesting Excels AND() Function within the IF() Function
• Using Excel's COUNTIF() Function
• Using Excel's SUMIF() Function

23. Working with Excel's Lookup Functions

• Microsoft Excel VLOOKUP() Function
• Microsoft Excel HLOOKUP() Function
• Microsoft Excel INDEX() Function
• Microsoft Excel MATCH() Function
• Microsoft Excel INDEX() and MATCH() Function Combined
• Microsoft Excel INDEX() and MATCH() Function Combined Continued
• Creating a Dynamic HLOOKUP() with the MATCH() Function

24. Working with Excel's Text Based Functions

• sing Excel's LEFT(), RIGHT() and MID() Functions
• sing Excel's LEN() Function
• sing Excel's SEARCH() Function
• sing Excel's CONCATENATE() Function

25. Auditing an Excel Worksheet

• Tracing Precendents in Excel Formulas
• Tracing Dependents in Excel Formulas
• Working with the Watch Window
• Showing Formulas

26. Protecting Excel Worksheets and Workbooks

• Protecting Specific Cells in a Worksheet
• Protecting the Structure of a Workbook
• Adding a Workbook Password

27. Mastering Excel's What If Tools

• Working with Excel's Goal Seek Tool
• Working with Excel's Solver Tool
• Building Effective Data Tables in Excel
• Creating Scenarios in Excel

28. Automating Repetitive Tasks in Excel with Macros

• Understanding Excel Macros
• Activating the Developer Tab in Excel
• Creating a Macro with the Macro Recorder
• Editing a Macro with VBA
• Creating Buttons to Run Macros

29. Congratulations!

• Congratulations on Completing the Microsoft Excel 103

30. Microsoft Excel Macros and VBA Course Introduction

• Welcome to the Course
• Understanding the Why and How Behind Excel Macros

31. Project #1 Using Excel's Macro Recorder Tool

• Introduction to Project #1 Inserting and Formatting Text
• Project #1 Start Recording!
• Running A Macro
• Project #1 Running a Macro with a Button
• Project #1 Oops! I Need to Edit My Macro
• Practical Uses of Excel Macros

32. Excel VBA Concepts

• Excel VBA Concepts
• The Visual Basic Editor (VBE)
• Excel VBA Modules
• Creating an Excel VBA Procedure
• Adding Code to a VBA Procedure
• Understanding Excel VBA Variables
• Building Logic with an IF Statement
• Working with Excel VBA Loops to Repeat Blocks of Code

33. Project #2 Moving Beyond the Basics and into VBA

• Introduction to Project #2 Interacting with the User
• Project #2 Breaking Down the VBA Code
• Project #2 Prompting the User for Information
• Continue Excel VBA InputBox
• Project #2 Building Logic into Your Macros
• Project #2 Alerting the User of Errors
• Other Practical Uses of Message and Input Boxes

34. Project #3 Preparing and Cleaning Up Data with a Little VBA

• Introduction to Project #3 Cleaning Up and Formatting Data
• Project #3 Preparing to the Use the Macro Recorder
• Project #3 Inserting the Headers
• Project #3 Formatting the Headers
• Project #3 Putting it all Together with a Little VBA
• Running the Loop

35. Project #4 Using VBA to Automate Excel Formulas

• Introduction to Project #4 Automate Excel Formulas
• Project #4 Automate the SUM() Function through Code
• Continue Automate SUM Function
• Test the SUM Function
• Project #4 Loop the SUM() Function Over Multiple Worksheets

36. Project #5 Bringing it All Together and a Weekly Report

• Introduction to Project #5 Creating the Final Report
• Project #5 Creating the Final Report Loop
• Project #5 - Copying and Pasting Data with VBA
• Project #5 - Running the Final Report Procedure

37. Project #6 Working with Excel VBA User Forms

• Introduction to Project #6 Working with Excel VBA User Forms
• Project #6 Creating an Excel User Form
• Project #6 Adding Controls to the Form
• Project #6 Adding VBA Code to the Initialize Event
• Project #6 Adding VBA Code to the ComboBox Change Event
• Project #6 Adding VBA Code to the Add Worksheet Button
• Change Worksheet Name
• Project #6 Adding VBA Code to the Create Report Button
• Project #6 Showing the Form

38. Project #7 Importing Data from Text Files

• Project #7 Opening a Text File for Import
• Project #7 Opening a Text File for Import
• Project #7 Get Data from Text File
• Project #7 Importing Multiple Text Files with the GetOpenFilename Method
• Project #7 Creating a Loop to Read Each File
• Project #7 Adding a New Sheet for Imported Data
• Project #7 Clear the Clipboard
• Project #7 Working with the ScreenUpdating Property
• Project #7 Creating Reusable Code with a VBA Function

39. Course Wrap Up

• Congratulations! You're an Excel MacroVBA Master