Loading color scheme

Fundamentals of Spreadsheet Analysis

starts 01.30 - 17:30
Steve Lewis, BSc

Students will learn how to use advanced formulas. Methods used to reference cells in formulas including relative, absolute, and 3D will be discussed. How cells can be referenced in arrays and using range names are also covered. Students will be exposed to using various Excel functions, followed by an overview of tools and techniques available to diagnose and fix errors in formulas. 

Additionally, students will be exposed to working with tools to organize data, conditional formatting to analyse, represent data graphically as well as discover how Excel spreadsheets are constructed.
This course is therefore ideal for those who are looking to improve their job prospects by adopting Microsoft® Excel 2010/2013/2016 advanced skills and knowledge. Microsoft Excel is the most commonly used spreadsheet application. Learning how to use Excel is an investment in both your personal and professional development. Excel makes it easier to monitor financial performance, calculate payments or plan a budget.
Microsoft® Excel is a spreadsheet program that is used to manage, analyze, and present data graphically. The foundation of Excel and locations where you do your work are cells, rows, and columns within a worksheet, and worksheets as part of a workbook. Many of the tools you use while working in Excel are located on the ribbon, running across the top of the window. The ribbon is organized into task-oriented command tabs. Each tab is divided into task-specific command groups appropriate for the type of work the user is currently performing. Microsoft® Excel 2010/2013/2016 is an essential tool for any business. When applied effectively it is a powerful tool that can be used to organize your data, manipulate large amounts of data, present complex information, perform calculations, create professional looking charts, enhance the appearance of worksheets, and automate tasks.

Topics covered include:

  • Creating and Formatting A Worksheet ‒ Describe the Excel worksheet, Enter text and numbers, Edit a workbook, Enter a simple formula, Use the AutoSUM button to sum a range of cells, Create a column and 3-D pie chart, Use the AutoCalculate area to display statistics

  • Formulas and Functions ‒ Enter a formula using the keyboard and point mode, Set mathematical order of operations in a formula, Summarize data using the AVERAGE, COUNT, COUNTA, MAX and MIN
    functions, Use date functions, Verify a formula using Range Finder, Add conditional formatting to cells, Change margins and header in a page layout

  • Working with Large Worksheets ‒ Rotate text in a cell, Create a series of month names, Format numbers using format symbols, Use absolute, mixed, relative, and 3D references in a formula, Use the IF function to perform a logical test, Freeze and unfreeze rows and columns

  • Lookup Functions and Range Names ‒ Organize and evaluate data in vertical and horizontal lookup tables, Retrieve data by matching the relative position of an item in a list, Define and apply range names in formulas, Use IFERROR function to resolve errors, Use date functions, Create a column chart
  • Charts ‒ Create various types of Excel charts, Use quick layouts and chart styles to design a chart, Edit chart elements including titles, data labels, and source data, Format chart elements, Create combination (dual-axis) chart
  • Applying Logic in Decision Making ‒ Write IF 4 functions to evaluate TRUE/FALSE values, Build formulas with relational operators and nested functions, Use Boolean functions such as AND, OR, Create complex logical functions for solving problems, Apply conditional formatting to highlight key information
  • Creating, Querying and Sorting a Table ‒ Create and manipulate a table, Convert table to a range, Add calculated columns to a table, Use icon sets with conditional formatting, Sort a table based on one field or multiple fields, Sort, query, and search a table using AutoFilter, Summarize data using Database, Statistical and Maths and Trigonometry functions
  • Financial Functions, Data Tables and Amortization Schedules ‒ Determine the monthly payment of a loan using the PMT function, Use the financial function s PV (present value) and FV (future value), Create a data table to analyze data in a worksheet, Create an amortization schedule
  • PivotTable Reports and PivotCharts ‒ Create and Format a PivotTable report, Analyse Worksheets data using PivotTable and PivotCharts reports
  • Working with Multiple Worksheets and Workbooks ‒ Use the ROUND function, Add a worksheet to a workbook, Add data to multiple worksheets at the same time, Create formulas that use 3-D cell references, Consolidate data by linking workbooks
  • Creating Templates, Importing Data and Data Validation ‒ Create and use a template, Import data from a text file, Access database, webpage, and a Word document, Convert
    text to columns, Use the Quick Analysis tool.