Short Course: Excel
This four-module course is designed to bring your Microsoft Excel skills to the next level.
Module 1: Data Cleansing in Excel
Cleansing data can be an extremely time-consuming and laborious task. Excel has a range of tools and functions which can help cleanse and prepare data more efficiently. Replacing values, removing duplicates and separating data into multiple columns are just some of the common tasks which will be covered in this session. You will also learn how to use Text Functions to help cleanse data and use Query Editor to access some additional cleansing tools, not available on the standard Excel menu.
- Know how to split columns, replace values and remove duplicates from large datasets
- Use a range of shortcut keys and commands to speed up selection, editing and navigating a large dataset
- Use Query Editor to profile data and to cleanse a dataset
- Use a range of Text functions such as LEFT, MID, TRIM, CONCAT
Current Excel users wanting to gain a better understanding of how to work with and prepare large datasets for analysis in a time-efficient manner
Module 2: Lookup Functions and Pivot Tables
Two of the most powerful and popular tools in Excel are lookup functions and pivot tables. This course will teach you how to write a VLOOKUP function and also an XLOOKUP (Office 365/Excel 2019 only). You will learn how to build and customise pivot tables so you can provide an insightful summary of large datasets
- Know how to write a VLOOKUP function
- Understand the VLOOKUP arguments, including TRUE/FALSE
- Understand why you might need an XLOOKUP instead of VLOOKUP
- Create a pivot table from a large dataset
- Customise pivot table design and layout
- Use slicers and timelines to filter a pivot table
Current Excel users who want to learn more about how pivot tables can be used and to understand more about the VLOOKUP/XLOOKUP Functions
Module 3: Advanced Excel: Using Functions to Create Dynamic Worksheets
Excel functions are a great way to enrich a dataset by generating additional information from the data. On this course you will learn how to work with a wide range of functions and how to nest them together to further enhance the data.
You will also learn how to create more dynamic worksheets by combining functions with features such as Named ranges, Format as Table and Conditional Formatting Rules.
- Use Named Ranges to enhance formula writing and create dynamic drop down lists
- Nest INDEX, MATCH and VLOOKUP to perform 2d lookups
- Use IF Function combined with AND/OR/ISBLANK/ISTEXT
- Explore Date/Time functions such as DATE, NETWORKDAYS, YEAR,MONTH, TODAY()
- Use NEW Office 365 Functions: FILTER, UNIQUE, SORT
- Create Custom Rules for Conditional Formatting
Excel users, who are already familiar with basic formula and function writing and want to enrich their understanding of what is available and how to write nested functions. Users should be familiar with Absolute Referencing (locking cells with $), Basic IF Function and basic VLOOKUP
Module 4: Visualizing Data using Excel Dashboards
Dashboards are visually appealing and a great way to present a series of charts to an audience. With a few design tricks and tips, you can build an engaging and interesting dashboard in Excel to provide insight into the key patterns and trends in your data.
On this course you will learn how to create and format charts, applying Best Practice. You will then bring the charts together into a stunning dashboard for presentation
- Create visuals from datasets using a range of chart types
- Apply Best Practice to chart design (chart type, formatting)
- Build a dashboard using charts, pivot tables, slicers and timelines