Intermediate MS Excel

Course details

This Intermediate MS Excel course is designed to enhance your spreadsheet skills by teaching you advanced techniques for data management, analysis, and visualization. Through practical exercises and real-life examples, you’ll master essential tools like PivotTables, advanced formulas, charts, data validation, and automation with macros. Whether you are looking to streamline daily tasks, analyze data more effectively, or enhance your professional skill set, this course will equip you with the tools to unlock Excel’s full potential.

Course Outline

1. Charts and Sparklines

• Combination Charts for better data comparison.
• Using Sparklines to track trends in data.
• Creating a Combined Line and Column Chart.
• Formatting axes with commas for readability.
• Customizing the Chart Plot Area and Chart Area with images.

2. Creating and Working with Tables

• Customizing the Sort function for data organization.
• Sorting data using Custom Lists.
• Applying Single/Multiple Filters to data.
• Utilizing the Advanced Filter feature.
• Creating a list of unique values.

3. Data Consolidation

• Combining data from various cells into one.
• Consolidating by position, category, 3D formulas, and PivotTable Wizard.

4. PivotTables and Pivot Charts

• Creating and managing PivotTables.
• Grouping and renaming data in PivotTables.
• Using Recommended PivotTables for quick analysis.
• Adding and modifying PivotTable fields.
• Refreshing data, grouping, and using Slicers/Timelines.
• Analyzing multiple tables using relationships.

5. Advanced Data Management

• Setting Data Entry Rules to ensure accurate input.
• Applying Date and List Validation.
• Circling invalid data for correction.

6. Macros in Excel

• Automating tasks with Macros for page setup, formatting, and custom number formats.
• Assigning Macros to buttons and managing them.

7. Advanced Excel Formulas and Functions

• Understanding the Order of Operations.
• Using Statistical, Logical, and Lookup functions: VLOOKUP, HLOOKUP, INDEX, MATCH.
• Text functions: PROPER, UPPER, LOWER, CONCATENATE.

8. Documenting and Auditing

• Password-protecting worksheets and tracking dependent cells.
• Displaying and tracing all formulas within a worksheet.

9. Linking and Embedding Data

• Linking data across worksheets and workbooks.
• Embedding and linking data from Excel to other applications (e.g., Word).

10. Importing Text Files

• Importing delimited text files into Excel.

11. Creating Forms in Excel

• Understanding and using Form Controls and ActiveX Controls for interactive forms.

12. Sorting and Filtering Data

• Sorting by multiple columns and applying custom sorts.
• Using AutoFilter and Advanced Filters for managing large datasets.
• Applying Subtotals and managing outline details.

13. Tracking and Reviewing Changes

• Enabling Track Changes and managing shared worksheets.
• Using Scenarios and the Scenario Manager for data analysis.

14. Data Validation

• Setting Data Validation rules for numbers, lists, and dates.
• Customizing validation messages and removing rules.

15. Logical Functions

• Utilizing the IF, AND, and OR functions for decision-making.

16. Statistical Functions

• Working with COUNT, COUNTA, COUNTIF, COUNTBLANK, and RANK functions.

17. Lookup Functions

• Mastering VLOOKUP and HLOOKUP for searching and retrieving data.