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
Module 1: Designing Workbooks
• Define how a workbook should be developed
• Define how a dataset and report should be designed
• Understand how to improve your own workbooks
Module 2: Building Calculations to Shape & Analyze Data
• Control how calculations work with referencing
• Implement Names
• Develop nested calculations
• Troubleshoot calculations
• Introduction to SPILL ranges
• Apply functions to extract, analyze, and monitor
Note all of the following functions will be structured into examples and labs:
• Text: CONCAT, &, MID, EXACT, FIND, RIGHT, LEFT, TEXTBEFORE, TEXTAFTER, SUBSTITUTE
• Logical: IF, IFS, SWITCH, IFERROR
• Statistical: AVERAGE, SUMIF, COUNTIF
• Date & Time: NETWORKDAYS, TODAY
• Other: ISNA, ISREF, ISERROR
Module 3: Designing Datasets to ensure integrity and validity
• Design datasets correctly for analysis
• Circumvent data errors with data validation
• Cross check information with conditional formatting
Module 4: Exploring Datasets with filters and tables
• Identify key information with filters
• Explore and aggregate a column of data with Tables
• Filter tables with slicers
Examples covered in this course:
• Restructure a workbook
• Troubleshoot calculations
• When is a calculation correct
• Designing calculations that can be easily adapted and copied
• Extracting data from a list
• Ensuring text in cells is consistent so that duplicates can be found
• Design smart conditional formatting and data validation rules
• Automate calculation references being updated
• Design a simple dashboard
Pre-Requisites
It is important that you have a basic foundation in Excel to attend this course. This means you should already be able to:
• Identify mouse shapes e.g. the selection cross, AutoFill, selection arrows for columns and rows
• Insert/Delete sheets
• Insert/Delete rows and columns
• Select cells, columns and rows
• Clear formats, contents, both formats and contents
• Identify a print range
• Setup a workbook with headers and footers
• Apply AutoFilter
• Enter basic formulas and functions, e.g. add/subtract/multiplication/division, SUM/COUNT
• Edit calculations and cells with the formula bar
• Know the difference between copy/paste vs cut/paste especially when it comes to formulas
• Copy / Paste Special