Microsoft Excel – Excel from Beginner to Advanced

Microsoft Excel 101 Course Introduction
1 Ask Questions
2 Course Exercise Files
3 Certificate of Completion

Microsoft Excel Fundamentals
4 Launching Excel
5 Microsoft Excel Startup Screen
6 Introduction to the Excel Interface
7 TIP Hide Excel Ribbon
8 TIP Customizing the Excel Quick Access Toolbar
9 More on the Excel Interface Workbook and Status Bar
10 Understanding the Structure of an Excel Workbook
11 Saving an Excel Document
12 Opening an Existing Excel Document
13 Common Excel Shortcut Keys

Entering and Editing Text and Formulas
14 Entering Text to Create Excel Spreadsheet Titles
15 Working with Numeric Data in Excel
16 Entering Date Values in Excel
17 Working with Cell References
18 Creating Basic Formulas in Excel
19 Relative Versus Absolute Cell References in Formulas
20 Understanding the Order of Operation DOWNLOAD EXERCISE FILE

Working with Basic Excel Functions
21 The structure of an Excel Function
22 Working with the SUM Function
23 Working with the MIN and MAX Functions
24 Working with the AVERAGE Function
25 Working with the COUNT Function
26 Adjacent Cells Error in Excel Calculations
27 Using the AutoSum Command
28 Excels AutoSum Shortcut Key
29 Using the AutoFill Command to Copy Formulas

Modifying an Excel Worksheet
30 Moving and Copying Data in an Excel Worksheet
31 Inserting and Deleting Rows and Columns
32 Changing the Width and Height of Cells
33 Hiding and Unhiding Excel Rows and Columns
34 Renaming an Excel Worksheet
35 Deleting an Excel Worksheet
36 Moving and Copying an Excel Worksheet

Formatting Data in an Excel Worksheet
37 Working with Font Formatting Commands
38 Changing the Background Color of a Cell
39 Adding Borders to Cells
40 Excel Cell Borders Continued
41 Formatting Data as Currency Values
42 Formatting Percentages
43 Using Excels Format Painter
44 Creating Styles to Format Data
45 Merging and Centering Cells
46 Using Conditional Formatting
47 Editing Excel Conditional Formatting

Inserting Images and Shapes into an Excel Worksheet
48 Inserting Images
49 Inserting Excel Shapes
50 Formatting Excel Shapes
51 Working with Excel SmartArt

Creating Basic Charts in Excel
52 Creating an Excel Column Chart
53 Working with the Excel Chart Ribbon
54 Adding and Modifying Data on an Excel Chart
55 Formatting an Excel Chart
56 Moving a Chart to another Worksheet
57 Working with Excel Pie Charts
58 Microsoft Excel Charts Quick Guide DOWNLOAD

Printing an Excel Worksheet
59 Viewing your Document in Print Preview
60 Changing the Margins Scaling and Orientation
61 Excel Worksheet Margins
62 Working with Page Layout View
63 Adding Header and Footer Content
64 Printing a Specific Range of Cells

Working with Excel Templates
65 Intro to Excel Templates
66 Opening an Existing Template
67 Creating a Custom Template

Excel 101 Practice What Youve Learned DOWNLOAD
68 Excel 101 Practice What Youve Learned DOWNLOAD

Congratulations Youve Built a Solid Microsoft Excel Foundation
69 Congratulations

Microsoft Excel 102 Course Introduction
70 Excel 102 Course Exercise Files DOWNLOAD

Working with an Excel List
71 Understanding Excel List Structure
72 Sorting a List Using Single Level Sort
73 Sorting a List Using MultiLevel Sorts
74 Using Custom Sorts in an Excel List
75 Filter an Excel List Using the AutoFilter Tool
76 Creating Subtotals in a List
77 Format a List as a Table
78 Using Conditional Formatting to Find Duplicates
79 Removing Duplicates

Excel List Functions
80 Introduction to Excels Function DSUM
81 Excel DSUM Function Single Criteria Continued
82 Excel DSUM Function with OR Criteria
83 Excel DSUM Function with AND Criteria
84 Excel Function DAVERAGE
85 Excel Function DCOUNT
86 Excel Function SUBTOTAL

Excel Data Validation
87 Understanding the Need for Excel Data Validation
88 Creating an Excel Data Validation List
89 Excel Decimal Data Validation
90 Adding a Custom Excel Data Validation Error
91 Dynamic Formulas by Using Excel Data Validation Techniques

Importing and Exporting Data
92 Importing Data Into Microsoft Excel
93 Importing Data from Text Files
94 Importing Data from Microsoft Access
95 NEW VERSION Import Data From Text Files into Excel
96 NEW VERSION Import Data From a Database into Excel
97 Microsoft Excel Legacy Import Options for New Excel Versions
98 Exporting Data to a Text File

Excel PivotTables
99 Understanding Excel PivotTables
100 Creating an Excel PivotTable
101 Modifying Excel PivotTable Calculations
102 Grouping PivotTable Data
103 Formatting PivotTable Data
104 Modifying PivotTable Calculations
105 Drilling Down into PivotTable Data
106 Creating PivotCharts
107 Filtering PivotTable Data
108 Filtering with the Slicer Tool

Working with Excels PowerPivot Tools
109 Introduction to Excel Power Pivot
110 Why PowerPivot
111 Activating the Excel PowerPivot AddIn
112 Creating Data Models with PowerPivot
113 Excel Power Pivot Data Model Relationships
114 Creating PivotTables based on Data Models
115 Excel Power Pivot KPIs

Working with Large Sets of Excel Data
116 Using the Freeze Panes Tool
117 Grouping Data Columns andor Rows
118 Print Options for Large Sets of Data
119 Linking Worksheets 3D Formulas
120 Consolidating Data from Multiple Worksheets
121 Microsoft Excel Managing Large Excel Datasets Quick Guide DOWNLOAD

Excel 102 Additional Practice Exercises
122 Excel 102 Practice What Youve Learned DOWNLOAD

Congratulations Youre an Intermediate Excel User
123 Congratulations

Microsoft Excel 103 Course Introduction
124 Excel 103 Course Exercise Files DOWNLOAD

Working with Excels Conditional Functions
125 Working with Excel Name Ranges
126 Advantages and Disadvantages of Excel Name Ranges
127 Editing an Excel Name Range
128 Using Excels IF Function
129 Excels IF Function with a Name Range
130 Nesting Functions with Excel
131 Nesting Excels AND Function within the IF Function
132 Using Excels COUNTIF Function
133 Using Excels SUMIF Function
134 Using Excels IFERROR Function

Working with Excels Lookup Functions
135 Microsoft Excel VLOOKUP Function
136 Microsoft Excel HLOOKUP Function
137 Microsoft Excel INDEX Function
138 Microsoft Excel MATCH Function
139 Microsoft Excel INDEX and MATCH Function Combined
140 Microsoft Excel INDEX and MATCH Function Combined Continued
141 Creating a Dynamic HLOOKUP with the MATCH Function

Working with Excels Text Based Functions
142 Using Excels LEFT RIGHT and MID Functions
143 Using Excels LEN Function
144 Using Excels SEARCH Function
145 Using Excels CONCATENATE Function
146 Microsoft Excel Adv Functions and Formulas Quick Guide DOWNLOAD

New Excel Functions for Excel 2019 and Microsoft 365 Excel Editions
147 Introduction to New Excel Functions
148 DOWNLOAD Microsoft Excel 365 Functions Exercise File
149 Microsoft Excel 365 FILTER Function
150 Microsoft Excel 365 SORT Function
151 Microsoft Excel 365 UNIQUE Function
152 Microsoft Excel Dynamic FILTER Function with Data Validation
153 Microsoft Excel 365 XLOOKUP Function
154 EXERCISE Dynamic Employee Order List
155 Microsoft Excel 365 SWITCH Function
156 TIP TRUE Expressions with Microsoft Excel 365 SWITCH Function
157 Microsoft Excel 365 TEXTJOIN Function
158 TIP TEXTJOIN Function with Criteria
159 Microsoft Excel 365 TEXTSPLIT Function

Auditing an Excel Worksheet
160 Tracing Precedents in Excel Formulas
161 Tracing Dependents in Excel Formulas
162 Working with the Watch Window
163 Showing Formulas

Protecting Excel Worksheets and Workbooks
164 Protecting Specific Cells in a Worksheet
165 Protecting the Structure of a Workbook
166 Adding a Workbook Password

Mastering Excels What If Tools
167 Working with Excels Goal Seek Tool
168 Working with Excels Solver Tool
169 Building Effective Data Tables in Excel
170 Creating Scenarios in Excel

Automating Repetitive Tasks in Excel with Macros
171 Understanding Excel Macros
172 Activating the Developer Tab in Excel
173 Creating a Macro with the Macro Recorder
174 Editing a Macro with VBA
175 Creating Buttons to Run Macros

Excel 103 Additional Practice Exercises
176 Excel 103 Practice What Youve Learned DOWNLOAD

Congratulations
177 Congratulations on Completing the Microsoft Excel 103

Microsoft Excel Macros and VBA Course Introduction
178 Welcome to the Course
179 Understanding the Why and How Behind Excel Macros

Project 1 Using Excels Macro Recorder Tool
180 Introduction to Project 1 Inserting and Formatting Text
181 Project 1 Section Exercise Files DOWNLOAD
182 Activating the Excel Developer Tab
183 Project 1 Start Recording
184 Running a Microsoft Excel Macro
185 Project 1 Running an Excel Macro with a Button
186 BONUS Create a Custom Button Using Excel Shapes
187 Adding the Excel Macro to the Quick Access Toolbar
188 Project 1 Editing the VBA Recorded by the Macro Recorder
189 Save a Workbook with a MacroVBA Code
190 Practical Uses of Excel Macros

Excel VBA Concepts
191 Intro to Excel VBA Concepts
192 Intro to Excel VBA Object Oriented Programming Concepts
193 Intro to Excel VBA Exercise Files DOWNLOAD
194 The Visual Basic Editor VBE
195 Introduction to the Excel VBA Immediate Window
196 Excel VBA Modules
197 Creating an Excel VBA Procedure
198 Adding Code to a VBA Procedure
199 Including Excel VBA Comments
200 Excel VBA MSGBOX Function
201 Excel VBA MsgBox Microsoft Help Information
202 Understanding and Working with Excel VBA Variables
203 REMINDER Saving an Excel Workbook with VBA Code

Working with Excel VBA Logic Statements
204 Working with Excel VBA Logic Exercise Files DOWNLOAD
205 Building Logic with an Excel VBA IF Statement
206 Including an Else Statement in the VBA IF Statement
207 Expanding the IF Logic with Multiple ElseIf Statements
208 Working with Excel VBA Select Case Statements
209 Working with an Excel VBA Do While Loop
210 Excel VBA Do While Loop Dynamic Condition
211 Working with an Excel VBA For Each Loop
212 Working with an Excel VBA For Next Loop

Project 2 Moving Beyond the Basics and into VBA
213 Introduction to Project 2 Interacting with the User
214 Beyond the Basics Sort Project Exercise Files DOWNLOAD
215 Project 2 Introduction to the Excel VBA RangeSort Method
216 Creating the Excel VBA Sort Procedures for this Project
217 Project 2 Prompting the User for Information
218 Continue Excel VBA InputBox
219 Project 2 Building Logic into Your Macros
220 Project 2 Alerting the User of Errors
221 Using Excel VBA Error Control Statements
222 Create a Button to Run the Sort Procedure and Save

Project 3 Preparing and Cleaning Up Data with Excel VBA
223 Introduction to Project 3 Cleaning Up and Formatting Data
224 Project 3 Exercise Files DOWNLOAD
225 Project 3 Preparing to the Use the Macro Recorder
226 Project 3 Inserting the Headers
227 Project 3 Formatting the Headers
228 Project 3 Testing the Macros
229 Project 3 Using an Excel VBA Loop to Loop through all Worksheets
230 Project 3 Testing the Excel VBA Loop
231 Project 3 Adding Logic to an Excel VBA Loop

Project 4 Using VBA to Automate Excel Formulas
232 Introduction to Project 4 Automate Excel Formulas
233 Project 4 Automate Sum Function Exercise Files DOWNLOAD
234 Project 4 Start the Procedure to Automate the SUM Function
235 Working with the Range Address Property
236 Creating the SUM Function in VBA
237 Run the Excel VBA Procedure to SUM Records
238 Project 4 Loop the SUM Function Over Multiple Worksheets

Project 5 Bringing it All Together and a Weekly Report
239 Introduction to Project 5 Creating the Final Report
240 Project 5 Final Report Loop Exercise Files DOWNLOAD
241 Project 5 Creating the Final Report Excel VBA Loop
242 Project 5 Copying Content with Excel VBA
243 Project 5 Pasting Content with Excel VBA
244 Cleaning Up the Final Report VBA Loop Code
245 Adding Logic to Stop the Loop from Running Multiple Times on a Worksheet
246 Project 5 Running the Final Report Procedure

Project 6 Working with Excel VBA User Forms
247 Introduction to Project 6 Working with Excel VBA User Forms
248 Project 6 Working with Excel VBA User Forms Exercise Files DOWNLOAD
249 Project 6 Creating an Excel User Form
250 Project 6 Working with Form and Control Properties
251 Project 6 Formatting Excel VBA User Form Controls
252 Project 6 Adding VBA Code to the Initialize Event
253 Project 6 Using the AddItem Method within a Loop
254 Project 6 Adding VBA Code to the ComboBox Change Event
255 Project 6 Adding VBA Code to the Add Worksheet Button
256 Project 6 Change Worksheet Name
257 Project 6 Catch Errors When Adding a Worksheet
258 Add Sheet Procedure Catch Errors
259 Project 6 Additional Conditions on the Loop
260 Project 6 Running the Final Report Procedure
261 Project 6 Showing the VBA User Form On Workbook Open

Project 7 Importing Data from Text Files
262 Project 7 Opening a Text File for Import
263 Project 7 Opening a Text File for Import
264 Project 7 Get Data from Text File
265 Project 7 Importing Multiple Text Files with the GetOpenFilename Method
266 Project 7 Creating a Loop to Read Each File
267 Project 7 Adding a New Sheet for Imported Data
268 Project 7 Clear the Clipboard
269 Project 7 Working with the ScreenUpdating Property
270 Project 7 Creating Reusable Code with a VBA Function

Course Wrap Up
271 Congratulations Youre an Excel MacroVBA Master