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.
Course Content
MS Excel Level 1:
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
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
THE FOLLOWING FUNCTIONS WILL BE STRUCTURED INTO EXAMPLES & 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
EXAMPLES IN THE 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
COURSE PREREQUISITES
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
To book this course please call
+44 (0) 1444 410296 or email: Info@kplknowledge.co.uk
Short Description