MS Excel Online Course- Master Excel In 3 Hours |
MS Excel 2016 Advanced |
Introduction |
|
Introduction |
|
00:01:00 |
Using Advanced IF Statements |
|
Summarize Data with SUMIF |
|
00:04:00 |
|
Summarize Data with AVERAGIF |
|
00:03:00 |
|
Summarize Data with COUNTIF |
|
00:02:00 |
Using Advanced Lookup Functions |
|
Using VLOOKUP with TRUE to find an Approximate Match |
|
00:04:00 |
|
Using HLOOKUP TRUE to find an Approximate Match |
|
00:01:00 |
|
Using the Index Function |
|
00:03:00 |
|
Using the Match Function |
|
00:02:00 |
|
Creating a Combined Index and Match Formula |
|
00:04:00 |
|
Comparing Two Lists with VLOOKUP |
|
00:02:00 |
|
Comparing Two Lists with VLOOKUP and ISNA |
|
00:04:00 |
Using Complex Logical and Text Functions |
|
Creating a Nested IF Function |
|
00:03:00 |
|
Using the IFERROR Function |
|
00:02:00 |
|
Using the LEN Function |
|
00:02:00 |
|
Using the TRIM Function |
|
00:01:00 |
|
Using the Substitute Function |
|
00:02:00 |
Formula Auditing |
|
Showing Formulas |
|
00:01:00 |
|
Tracing Cell Precedents and Dependents |
|
00:03:00 |
|
Adding a watch Window |
|
00:02:00 |
|
Error Checking |
|
00:02:00 |
What-If Analysis Tools |
|
Using the Scenario Manager |
|
00:03:00 |
|
Using Goal Seek |
|
00:02:00 |
|
Analyzing with Data Tables |
|
00:02:00 |
Worksheet and Workbook Protection |
|
Protection Overview |
|
00:02:00 |
|
Excel File Password Encryption |
|
00:03:00 |
|
Allowing Specific Worksheet Changes |
|
00:01:00 |
|
Adding Protection to only Certain Cells in a Worksheet |
|
00:03:00 |
|
Additional Protection Features |
|
00:02:00 |
Advanced Use of PivotTables and PowerPivot |
|
Using the Pivot Tables Charts Wizard |
|
00:02:00 |
|
Adding a Calculated Field |
|
00:02:00 |
|
Adding a Calculated Item |
|
00:02:00 |
|
Apply Conditional Formatting to a Pivot Table |
|
00:03:00 |
|
Using Filters in the Pivot Table Fields Pane |
|
00:02:00 |
|
Creating Filter Pages for a Pivot Table |
|
00:01:00 |
|
Enabling a Power Pivot Add In |
|
00:04:00 |
Automating with Macros |
|
What are Macros |
|
00:03:00 |
|
Displaying the Developer Tab and Enabling Macros in Excel |
|
00:03:00 |
|
Creating a Basic Formatting Macro |
|
00:03:00 |
|
Running a Macro |
|
00:02:00 |
|
Assigning a Macro to a Button |
|
00:02:00 |
|
Creating a More Complex Macro |
|
00:02:00 |
|
Viewing and Editing the VBA Code for an Existing Macro |
|
00:03:00 |
|
Adding a Macro to the Quick Access Toolbar |
|
00:02:00 |
Working with Form Controls |
|
What are Form Controls |
|
00:02:00 |
|
Adding Spin Buttons and Check Boxes to a Spreadsheet |
|
00:02:00 |
|
Adding a Combo Box to a Spreadsheet |
|
00:02:00 |
Ensuring Data Integrity |
|
What is Data Validation |
|
00:01:00 |
|
Restricting Data Entries to Whole Numbers |
|
00:02:00 |
|
Data Validation Restricting Data Entry to a List |
|
00:01:00 |
|
Data Validation Restricting Data Entry to a Date |
|
00:01:00 |
|
Data Validation Restricting Data Entry to Different Text Lengths |
|
00:01:00 |
|
Composing Input Messages |
|
00:02:00 |
|
Composing Error Alters |
|
00:02:00 |
|
Finding Invalid Data |
|
00:02:00 |
|
Editing and Deleting Data Validation Rules |
|
00:01:00 |
Collaborating in Excel |
|
Working with Comments |
|
00:03:00 |
|
Printing Comments and Errors |
|
00:02:00 |
|
Sharing a Workbook |
|
00:03:00 |
|
Tracking Changes in a Workbook |
|
00:03:00 |
|
Working with Versions |
|
00:02:00 |
|
Sharing Files via Email |
|
00:02:00 |
Importing and Exporting Data to a Text File |
|
Importing a Text File |
|
00:03:00 |
|
Exporting a Text File |
|
00:01:00 |
Conclusion |
|
Course Recap |
|
00:01:00 |
MS Excel 2016 Intermediate |
Introduction |
|
Introduction |
|
00:01:00 |
Customizing Excel |
|
Customizing the Ribbon |
|
00:06:00 |
|
Customizing the Quick Access Tool Bar |
|
00:04:00 |
|
Customizing the General and Formula Options |
|
00:02:00 |
|
Customzing the Auto Correct Options |
|
00:04:00 |
|
Customizing the Save Default |
|
00:02:00 |
|
Customizing Advanced Excel Options |
|
00:03:00 |
Linking Workbooks and Worksheets |
|
Linking Workbooks and Worksheets |
|
00:05:00 |
|
Linking Worksheets with 3D References |
|
00:03:00 |
|
Understanding the Consolidate Feature |
|
00:05:00 |
Working with Range Names and Advanced Formulas |
|
What are Range Names |
|
00:03:00 |
|
Creating Range Names Using the Name Box and Define Name |
|
00:07:00 |
|
Creating Range Names Using Create from Selection |
|
00:03:00 |
Analyzing Data with Common Logical Functions |
|
Working with the Most Common Logical Functions |
|
00:03:00 |
|
Evaluate Data with the AND Function |
|
00:03:00 |
|
Evaluate Data with the OR Function |
|
00:03:00 |
|
Understanding IF Functions |
|
00:04:00 |
|
Nesting AND and OR Inside and IF Function |
|
00:06:00 |
Analyzing Data with Common Lookup Functions |
|
What are Lookup Functions |
|
00:02:00 |
|
Using VLookup |
|
00:08:00 |
|
Using HLookup |
|
00:02:00 |
Sorting and Filtering Range Data |
|
Differences Between Sorting & Filtering |
|
00:02:00 |
|
Sorting Lists |
|
00:04:00 |
|
Filtering Lists |
|
00:03:00 |
Analyzing and Organizing with Tables |
|
Creation and Benefits of a Table |
|
00:02:00 |
|
Elements of a Table |
|
00:03:00 |
|
Formatting a Table |
|
00:03:00 |
|
Sorting Tables |
|
00:02:00 |
|
Filtering Tables |
|
00:03:00 |
|
Filtering with Slicers |
|
00:03:00 |
|
Calculating with Tables |
|
00:04:00 |
|
Removing Erroneous Table Data |
|
00:02:00 |
|
Exporting, Refreshing, Converting Tables |
|
00:02:00 |
Using Conditional Formatting |
|
Using Custom Conditional Formatting |
|
00:03:00 |
|
Conditional Formatting and Highlight Cells and Top Bottom Rules |
|
00:04:00 |
|
Conditional Formatting Data Bars, Icon Sets, and Color Scales |
|
00:04:00 |
|
Using Custom Fonts and Colors |
|
00:02:00 |
|
Using Custom Conditional Formatting |
|
00:03:00 |
|
Modify or Remove Conditional Formatting |
|
00:02:00 |
Outlining with Subtotals and Grouping |
|
What are Subtotals and Grouping |
|
00:01:00 |
|
Creating Subtotals |
|
00:03:00 |
|
Grouping and Ungrouping Data |
|
00:02:00 |
Displaying Data Graphically |
|
What are Charts |
|
00:04:00 |
|
Creating Charts |
|
00:04:00 |
|
Understanding Chart Elements |
|
00:03:00 |
|
Modifying Chart Elements |
|
00:07:00 |
|
Changing and Moving Charts |
|
00:04:00 |
|
Filtering a Chart |
|
00:02:00 |
|
Formatting Charts |
|
00:07:00 |
|
Adjusting Numbering |
|
00:03:00 |
|
Creating Dual Axis Charts |
|
00:02:00 |
|
Forecasting with Trendlines |
|
00:05:00 |
|
Creating a Chart Template |
|
00:01:00 |
|
Displaying Trends with Sparklines |
|
00:04:00 |
Understanding PivotTables and PivotCharts and Filtering with Slicers |
|
What is a Pivot Table |
|
00:04:00 |
|
Creating a Pivot Table |
|
00:03:00 |
|
Working with the Pivot Table Fields Pane |
|
00:07:00 |
|
Basic Data Organization and Analysis with Pivot Table |
|
00:04:00 |
|
Formatting Pivot Tables |
|
00:06:00 |
|
Creating a Pivot Chart |
|
00:04:00 |
|
Modifying and Formatting a Pivot Chart |
|
00:03:00 |
|
Adding Slicers and Timeline Slicers |
|
00:03:00 |
|
Formatting Slicers |
|
00:03:00 |
Working with Flash Fill |
|
What is Flash Fill |
|
00:01:00 |
|
Using Flash Fill and Autofill |
|
00:03:00 |
|
Filling Various Series |
|
00:04:00 |
Working with Date and Time Functions |
|
What are Date and Time Functions- |
|
00:02:00 |
|
Using TODAY, NOW, and DAY Functions |
|
00:02:00 |
|
Using NETWORKDAYS and YEARFRAC Functions |
|
00:02:00 |
Working with Common Text Functions |
|
What are Text Functions |
|
00:01:00 |
|
Using CONCATENATE |
|
00:03:00 |
|
Using Text to Columns |
|
00:02:00 |
|
Using LEFT, RIGHT, & MID Functions |
|
00:02:00 |
|
Using UPPER, LOWER, & PROPER Functions |
|
00:02:00 |
Conclusion |
|
Course Recap |
|
00:02:00 |