Intermediate MS Excel

Course details

TopicDetails

1. Charts and Sparklines
Combination Charts
Using Sparklines To Understand Trends
Creating a Combined Line and Column Chart
Formatting an Axis to Display Using Commas
Formatting the Chart Plot Area Using a Picture
Formatting the Chart Area Using a Picture
2. Creating and Working With Tables
Customizing the Sort Function
Sorting According To A Custom List
Applying Single / Multiple Filters To Data
Using Advanced Filter
Creating A List Of Unique Values
3. Consolidating Data
Combining Data From Various Cells Into A Single Cell
Consolidating By Position, By Category, By Using 3D Formulas, And By Using The PivotTable Wizard
4. Inserting PivotTables and Charts Into Your Excel Sheets
Creating A PivotTable
Automatically Grouping Data in a Pivot Table and Renaming Groups
Using The Recommended PivotTables Option
Adding Fields To A PivotTable
Changing The Data Source
Refreshing The Data In A PivotTable
Grouping Data
Collapsing And Expanding Fields
Using Slicers And Timelines To Filter PivotTable Data
Creating Multiple Worksheets From Report Filter Pages
Using The Drill Down Feature
Build Relationships To Analyze Multiple Tables Together
5. Advanced Data Management
Defining Data Entry Rules To Prevent Incorrect Information Being Entered
Setting Date And List Validation Rules
Circling Invalid Data
6. Macros
Macro to Change the Page Set-Up
Macro to Apply a Custom Number Format
Macro to Format a Cell Range
Macro to Insert Fields into the Header or Footer
Assigning a Macro to a Button on the Quick Access Toolbar
Deleting Macros
7. Using Formulas And Functions
Working With The Order Of Operations
Inserting A Function
Understanding The Syntax Of A Function
Statistical Functions - COUNTA, COUNTBLANK, COUNTIF, COUNTIFS, AVERAGEIF, SUMIF, LARGE, SMALL
Logical Functions - AND, OR, IF, IFERROR
Nested Functions - AND, OR, IF
Lookup Functions - VLOOKUP, HLOOKUP, INDEX, MATCH
Text Functions - PROPER, UPPER, LOWER, CONCATENATE
8. Documenting And Auditing
Password-Protecting A Worksheet
Unlocking Cells And Protecting Part Of A Worksheet
Password-Protecting A Workbook
Tracing Precedent Cells
Tracing Dependent Cells
Cells with Missing Dependents
Showing All Formulas in a Worksheet
9. Linking & Embedding
Linking Data within a Worksheet
Linking Cells between Worksheets within a Workbook
Linking Data between Workbooks
Linking Data from Excel to a Word Document
Linking an Excel Chart to a Word Document
Updating, Locking and Breaking Links
10. Importing Text Files
Importing a Delimited Text File
11. Creating Forms
Understanding The Various Form Controls and ActiveX Controls
Inserting and Configuring The Properties Of the Form Controls
12. Sorting and Filtering Data
Sorting Data by Multiple Columns at the Same Time
Applying a Pre-Installed Custom Sort
Creating a Customized List and Performing a Custom Sort
Removing a Customised List
Using AutoFilter
Using AutoFilter to Perform Multiple Queries
Top 10 AutoFilter
Removing All AutoFilters from a Worksheet
Advanced Filter Criteria
Sub-Totaling
Removing Subtotals
Expanding and Collapsing Outline Detail Levels
13. Tracking and Reviewing Changes
Enabling or Disabling the 'Track Changes' Feature
Sharing, Comparing and Merging Worksheets
Scenarios
Scenario Manager
Scenario Summary Reports
14. Validating
Data Validation - Whole Number
Data Validation - Decimal Number
Data Validation - List
Data Validation - Date
Data Validation - Time
Data Validation - Text Length
Customizing a Validation Input Message and Error Alert
Removing Data Validation
15. Logical Functions
If Function
And Function
Or Function
16. Statistical Functions
Count Function
Counta Function
Countif Function
Countblank Function
Rank Function
17. Lookup Functions
Vlookup Function
Hlookup Function
 
  Updated on 05 November, 2019