Duration: 2 Days
Course Overview
Business data is critical to the way we operate our business and the key decisions that we make. The way we process data depends on how it is stored and this is often by using an eclectic source of storage options, which can typically be based on the user’s favored application.
In this course we will look at how Excel can be used to effectively manage data within Excel or from other data sources. By the end of this course, you will be able to ensure the integrity and validity of data maintained in Excel.
This course is intended to provide an intermediate to advanced level of Excel knowledge.
How can I attend my course?
Course Content
Ensuring Correctness of Excel Datasheets
• Validating data inputs
• Preventing duplicates
• Highlighting erroneous data
• Formatting datasets with Table formats
• Protecting cell validation
Controlling and Querying Datasets
• Retaining an audit trail when identifying duplicates
• Extracting unique entries with the Advanced Filter
• Distinguishing key items with Conditional Formatting
• Implementing form controls
Importing and Cleaning Data
• Importing data with “Get External Data”
• Controlling imports with Power Query
• Transforming data with Power Query
• Validating data with the Power Query language
Developing Data Models
• Defining relationships between Excel tables
• Constructing data mash ups
• Visualising the data model in Power Pivot.