Détails
- 8 Sections
- 164 Lessons
- 2 Weeks
Expand all sectionsCollapse all sections
- Module 1: Starting with Excel22
- 1.1Introduction to Excel Course – Section Overview
- 1.2Learning Outcomes
- 1.3What is Microsoft Excel?
- 1.4Who is this Course for.
- 1.5Microsoft Excel Marketplace.
- 1.6Microsoft Excel Job Opportunities.
- 1.7Microsoft Excel Job Roles.
- 1.8Finding and Opening Microsoft Excel.
- 1.9Excel’s Start Screen.
- 1.10Explaining the Excel Interface.
- 1.11The Excel Interface, Continued.
- 1.12Excel Workbook versus Excel Worksheet.
- 1.13Saving and Opening Excel Documents.
- 1.14Customizing the Excel Quick Access Toolbar.
- 1.15Customizing the Excel Ribbon.
- 1.16Excel Shortcut Keys.
- 1.17Creating Excel Labels.
- 1.18Entering Numeric Values in Excel.
- 1.19Formatting Date Values in Excel.
- 1.20Building Basic Formulas in Excel.
- 1.21Order of Operations.
- 1.22Relative versus Absolute Cell References.
- Module 2 Function Worksheet and Cell Formatting21
- 2.0The Excel Functions Explained.
- 2.1The SUM Function
- 2.2The Min and Max Functions
- 2.3The Average Functions
- 2.4The Count Functions
- 2.5Moving and Copying Data
- 2.6Insert and Delete Rows and Columns
- 2.7Adjusting the Width and Height of Cells
- 2.8Hiding and Unhiding Excel Rows and Columns
- 2.9Renaming, Moving and Deleting Excel Worksheets
- 2.10Adding Protection to Specific Cells
- 2.11Protecting the Structure of a Workbook
- 2.12Adding a Workbook Password to Open File.
- 2.13Formatting Fonts and Cell Background Colour
- 2.14Adding Cell Borders
- 2.15Formatting Data Appropriately
- 2.16The Magic behind Excel’s Format Painter
- 2.17Creating Styles for Formatting Efficiency
- 2.18Merging Cells for a Cleaner Look
- 2.19The Power of Conditional Formatting
- 2.20Lesson Summary
- Module 3: Images, Charts and Templates16
- 3.0Learning Outcomes
- 3.1Grab Users’ Attention Using Illustration
- 3.2Customizing Icons
- 3.3Creating Compelling Graphics with SmartArt.
- 3.4The Commonly Used Column Chart
- 3.5Changing the Chart Design
- 3.6Formatting Elements of a Chart.
- 3.7Modifying the Data, Type and Location of a Chart.
- 3.8Little Slice of Pie Charts.
- 3.9Print Preview Options
- 3.10Excel’s Page Layout View
- 3.11Printing a Specific Range of Cells.
- 3.12Converting Spreadsheets to PDF Files.
- 3.13Why Create an Excel Template?
- 3.14How to Create an Excel Template.
- 3.15LESSON SUMMARY
- Module 4: Excel Database20
- 4.0Learning Outcomes
- 4.1How to Prepare Data for Analysis
- 4.2How to Sort Data in Excel.
- 4.3Multi-Level Sorting
- 4.4Custom Sorting Datasets in Excel.
- 4.5Applying Filters to Datasets.
- 4.6Creating Subtotals within a Dataset
- 4.7Converting Datasets into Tables.
- 4.8Finding and Removing Duplicate Values
- 4.9The SUMIF Function.
- 4.10The DSUM Function.
- 4.11The DSUM Function, Continued.
- 4.12The SUBTOTAL Function.
- 4.13What is Excel Data Validation?
- 4.14Creating a Drop Down List with Data Validation.
- 4.15Different Types of Excel Data Validation.
- 4.16Adding Custom Alerts to Data Validation.
- 4.17Creating a Dynamic Drop Down List.
- 4.18Complex Validation Dependent Drop Down List.
- 4.19Lesson Summary
- Module 5 : Specific Excel Functions24
- 5.0Learning Outcomes
- 5.1Creating an Excel PivotTable.
- 5.2Modifying Excel PivotTables.
- 5.3Grouping and Filtering PivotTable Data.
- 5.4Drilling Down into PivotTable Data.
- 5.5Creating PivotCharts and Utilizing Slicers.
- 5.6What is PowerPivot?
- 5.7Activating the Excel PowerPivot Add-In.
- 5.8Creating Relationships between Data Tables.
- 5.9Using Data Models to Create PivotTables.
- 5.10How to Create PowerPivot KPIs.
- 5.11Excel’s IF Function.
- 5.12IF Function with AND Criteria.
- 5.13IF Function with OR Criteria.
- 5.14Nestling Multiple IF Functions.
- 5.15The COUNTIF Function
- 5.16Key Benefits of Named Ranges.
- 5.17VLOOKUP Function.
- 5.18The Beauty of Excel’s IFERROR Function.
- 5.19HLOOKUP Function.
- 5.20INDEX Function.
- 5.21MATCH Function.
- 5.22INDEX and MATCH Combined.
- 5.23Two-Way Lookup with INDEX and MATCH
- Module 6: Functions, Tools and Formula Auditing15
- 6.0Learning Outcomes
- 6.1Left, Right and Mid Function.
- 6.2Extracting Specific Text Using LEN and Search.
- 6.3Combining Text with CONCATENATE.
- 6.4Quick Tips and other Text Based Functions.
- 6.5Tracing Precedents and Dependents in Formulas
- 6.6Showing Formulas
- 6.7Grouping Data.
- 6.83D Referencing in Formulas.
- 6.9Utilizing the Watch Window in Excel.
- 6.10How to Freeze Panes in Excel
- 6.11Excel’s Scenarios Manager Tool.
- 6.12Goal Seek in Excel.
- 6.13Compare Results with Excel Data Table
- 6.14Solver Tool.
- Module 7: Microsoft Excel VBA23
- 7.0Learning Outcomes
- 7.1The Power Behind Excel VBA.
- 7.2A Look Inside the Visual Basic Editor (VBE).
- 7.3Recording a Macro
- 7.4Saving and Opening a Macro Enabled Workbook
- 7.5Modules and Procedures.
- 7.6Objects, Methods and Properties.
- 7.7Excel VBA Variables.
- 7.8Referencing a Range.
- 7.9InputBox and MsgBox.
- 7.10Using Variables in VBA Code.
- 7.11If Then Else Statement
- 7.12Worksheet Functions Inside VBA.
- 7.13Creating User Defined Functions.
- 7.14User Defined Functions Within VBA Scripts.
- 7.15Find Last Row of Data.
- 7.16Find Last Column of Data.
- 7.17With Statement.
- 7.18Debugging and Error Handling.
- 7.19Debugging and Error Handling, Continued.
- 7.20Do Until Loop.
- 7.21For Next Loop.
- 7.22For Each Loop
- Module 8: Macros, UserForms and Careers in Excel23
- 8.0Learning Outcomes
- 8.1Assigning Macros to Shapes
- 8.2From Control versus Active X Controls.
- 8.3Worksheet Events.
- 8.4Workbook Events.
- 8.5Fun with VBA Events.
- 8.6Creating an Excel UserForm.
- 8.7Adding Control to UserForms
- 8.8How to Show a UserForm.
- 8.9Passing TextBox Values to Desired Cells.
- 8.10Passing Option Buttons to Desired Cells.
- 8.11UserForm ComboBoxes
- 8.12Clearing Values from UserForm Controls.
- 8.13How to Close a UserForm.
- 8.14UserForms and Protected Sheets
- 8.15Creating an Excel Resume
- 8.16Getting Started with Freelancing.
- 8.17Top Freelance Websites
- 8.18Personal Branding.
- 8.19Networking Do’s and Don’ts.
- 8.20Importance of Having a Website.
- 8.21How to Become an Excel Freelancer.
- 8.22Getting your First Client.