microsoft logo excel

Microsoft Excel courses

The following courses run between one and two times per month in the following locations: London, Birmingham, Bristol, Edinburgh, Glasgow, Manchester, Milton Keynes and Leeds.

We can also deliver these courses for you at your work address anywhere within the UK. 

 

Prices start from just £65 + VAT per person when a 1 day bespoke closed course is booked for up to six delegates attending.

 

 

        Course Duration            

Course Guide   

Microsoft Excel 2016 Introduction

1 day

 

BSG Training - delivering ICT Training Solutions for you

Target Audience: This course is designed for those who wish to gain the necessary skills to create, edit, format and print basic Microsoft Excel 2016 worksheets.

Prerequisites: Use a mouse; Type and use a keyboard; Navigate through Windows files and folders; Work with windows - minimise, maximise, open and close.

Delegates will learn to:
  • Get started with Excel 2016
  • Create a basic worksheet
  • Modify a workbook
  • Use formulas and functions
  • Format an Excel worksheet
  • Manage worksheets and workbooks
  • Set up printing options
Module 1: Getting Started with Excel 2016
  • Excel and the Excel Environment
  • Navigate and Select in Worksheets
  • Create a Basic Workbook
  • Open, Save and Close Workbooks
  • Use Microsoft Help
Module 2: Modifying a Workbook
  • Cut, Copy and Paste Commands
  • Flash Fill
  • AutoFill Options
  • Find and Replace
  • Spelling
  • Undo and Redo
  • Insert and Delete Options
  • Column Width and Row Height
  • Hide and Unhide Options
Module 3: Using Formulas and Functions
  • Create Relative Formulas
  • AutoCalculate
  • Insert Functions
  • Use AutoSum
  • Copy Formulas
  • Create Absolute Formulas
Module 4: Formatting an Excel Worksheet
  • Font Group
  • Add Borders and Fill Colours
  • Cell Alignment
  • Number Formatting
  • Cell Styles and Galleries
Module 5: Managing Worksheets & Workbooks
  • Manage Worksheets
  • Freeze Rows and Columns
  • View Options for Worksheets and Workbooks
Module 6: Printing Options
  • Print Preview
  • Print Worksheets and Workbooks
  • Page Setup
  • Print Titles
  • Page Layout View

close

Course Guide   

Microsoft Excel 2016 Intermediate

2 days

 

BSG Training - delivering ICT Training Solutions for you

Target Audience: This course is aimed at end users who have taken the following course or have equivalent knowledge: Microsoft Excel 2016 Introduction

Prerequisites: Create basic spreadsheets; Select and edit data; Perform basic formatting; Open, close and save files; Create basic formulas – AutoSum; Use Insert Function to create built-in functions; Work with absolute references and Use the AutoFill feature

In 2 days, Delegates will learn to:
  • Calculate with advanced formulas
  • Audit formulas using the auditing tools
  • Work with Excel tables
  • Organise worksheet data so that data can be sorted and filtered
  • Create and modify Charts
  • Create Excel templates
  • Apply Conditional Formatting
  • Analyse data with Recommended Pivot Tables
Module 1: Getting Started with Excel 2016
  • Use Excel's Quick Analysis Tools
  • Using Mixed References in Calculations
  • Work with Range Names
  • Calculating across Worksheets
  • Exploring Excel's Function Categories
  • Analysing Data Based on Criteria
  • Working with Text Functions
  • Calculating with Financial Functions
  • Using Logical Functions
  • Applying Lookup Functions
Module 2: Auditing a Worksheet
  • Finding Cells
  • Finding and Resolving Errors
  • Using the Watch Window
  • Evaluating a Formula
Module 3: Mastering Excel Tables
  • Introducing Excel Tables
  • Using Advanced Tables Tools
Module 4: Organising Worksheet Data
  • Apply Basic Sorting to a Data Range
  • Advanced Sorting
  • Summarise Data with Subtotals
Module 5: Charts
  • Understanding Charts
  • Create a Chart
  • Modify and Format a Chart
  • Analyse Data Using Sparklines
Module 6: Working with Templates
  • Create a Hyperlink
  • Adding Comments
  • Work with Templates
Module 7: Analysing Selected Data
  • Applying Basic Filters
  • Advanced Filters
  • Use Database Functions
  • Use Outlines to Organise Data
Module 8: Apply Conditional Formatting
  • Conditional Formatting
  • Customising Conditional Formatting
  • Sorting and Filtering by Colour
Module 9: An Introduction to Pivot Tables
  • Understanding Pivot Tables
  • Analyse Data with Recommended Pivot Tables
Module 10: Appendix – Illustrations
  • SmartArt
  • Insert Images into a Spreadsheet
  • Insert Shapes into a Spreadsheet
  • Group and Layer Graphics
Module 11: Appendix – Excel Options
close

Course Guide   

Microsoft Excel 2016 Advanced

2 day

 

BSG Training - delivering ICT Training Solutions for you

Target Audience: This course is aimed at end users who want to develop their skills so they can use advanced techniques to manipulate Excel data. Delegates should have attended Excel 2016 Intermediate course or have equivalent knowledge.

Prerequisites: Create, edit and format spreadsheets; Navigate within worksheets and books; Use Insert Function to create built in functions; Work with absolute references, Create named ranges: Create tables within Excel; Sort and filter data

In 2 days, Delegates will learn to:
  • Create macros to automate common tasks
  • Collaborate with others by merging and tracking workbooks
  • Create and manipulate PivotTables and PivotCharts
  • Work with array and advanced lookup and reference functions
  • Use advanced techniques to analyse data
  • Work with data validation tools
  • Import and export data
Module 1: Creating Macros
  • Creating Macros
  • Comparing Relative and Absolute Macros
  • Running a Macro
  • Assigning Shortcuts to Macros
  • Macro Security
  • Enabling Macros in the Backstage View
  • Copying a Macro between Workbooks
  • Document Properties and the Document Inspector
Module 2: Collaborating With Others
  • Protection
  • Track Changes
  • Workbook Merging
Module 3: Creating PivotTables, PivotCharts and Slicers
  • Creating a PivotTable
  • Amending the Fields in a PivotTable
  • Updating the PivotTable
  • Adding Calculations to a PivotTable
  • Add Grouping to a Pivot Table
  • Slicers
  • PivotTable Timelines
  • Creating PivotTables from Tables and Related Tables
Module 4: Array Functions
  • An Array Formula
  • Array Functions
  • Frequency
  • Transpose
  • Single Cell or Multiple Cell Arrays
  • Single Cell Array Functions
  • Multiple Cell Array Functions
Module 5: Advanced Lookup and Reference Functions
  • GETPIVOTDATA
  • MATCH
  • VLOOKUP
  • ROW/COLUMN
  • INDEX
  • OFFSET
  • INDIRECT
  • LOOKUP (Array format)
Module 6: Analysing Data
  • Creating a Single Input Data Table
  • Create a Two-Variable Data Table
  • Creating Scenarios
  • What-If Analysis Using Goal Seek
  • Consolidating Data
  • Linking to External Workbooks
Module 7 – Data Validation
  • Number Validation
  • Data List Validation
  • Message Prompts and Alerts
  • Conditional Data Validation
  • Data Validation Errors
Module 8 – Import/Export Data
  • Exporting Excel Data
  • Import Delimited Text by Connecting to It
  • Importing a Web Query

close

Course Guide   

Microsoft Excel Advanced Function Writing

1 day

 

BSG Training - delivering ICT Training Solutions for you

Target Audience: This course is designed for people who want to get the most out of their data by learning various analysis functions. Whether you are an account manager, an IT worker or someone who needs to regularly manipulate Microsoft Excel worksheet data, this course will show many functions and their capabilities.

Prerequisites: A good working knowledge of Microsoft Windows; A good working knowledge of Microsoft Excel 2007, 2010, 2013 or 2016; Understand basic functions such as Sum and Average and how they are written; Write and edit a variety of basic formulas using Excel functions.

In 1 day - Delegates will learn to:
  • Work with IF and related functions with IF in their name
  • Nest functions together, not just nested IFs
  • Work with array functions
  • Calculate with both nested and array combined functions to maximise your formula
  • Manage and manipulate date and time functions in a spreadsheet
  • Control text values with text related functions
  • Use a variety of lookup and reference functions
  • Learn the Aggregate function for filtering and conditional formatting
Module 1 - Function Writing Review
  • Reading Syntax
  • Function Families
  • Using Range Names
  • SUM, AVERAGE, AVERAGEA, MIN / MAX
  • COUNT / COUNTA, LARGE / SMALL, RANK
Module 2 - The IF Functions
  • IF
  • SUMIF / AVERAGEIF
  • COUNTIF / COUNTIFS
  • SUMIFS / AVERAGEIFS
  • IFERROR
Module 3 - Nested Functions
  • What is a Nested Function?
  • Nested IFs
  • AND
  • OR
Module 4 - Array Functions
  • An Array Formula
  • Array Functions
  • FREQUENCY
  • TRANSPOSE
  • Single Cell or Multiple Cell Arrays
  • Single Cell or Multiple Cell Array Functions
Module 5 - Lookup and Reference Functions
  • GETPIVOTDATA
  • MATCH
  • VLOOKUP
  • ROW / COLUMN
  • INDEX
  • OFFSET
  • INDIRECT
Module 6 - The Aggregate Function
  • AGGREGATE (new Function in Excel 2010)
  • AGGREGATE
Module 7 - Date and Time Functions
  • Dates are a Serial Number
  • TODAY / NOW
  • DAY / MONTH / YEAR / HOUR / MINUTE / SECOND
  • DATE
  • EDATE
  • EOMONTH
  • NETWORKDAYS
  • WORKDAY
  • DATEDIF
Module 8 - Working with Text Functions
  • CONCATENATE
  • LEFT / RIGHT
  • MID
  • LEN
  • FIND / SEARCH
  • UPPER / LOWER / PROPER
  • VALUE
  • TEXT

close
 

Course Guide   

Microsoft Excel 2013 Introduction

1 day

 

BSG Training - delivering ICT Training Solutions for you

Target Audience: This course is designed for those who wish to gain the necessary skills to create, edit, format and print basic Microsoft Excel 2013 worksheets.

Prerequisites: Use a mouse; Type and use a keyboard; Navigate through Windows files and folders; Work with windows - minimise, maximise, open and close

Delegates will learn to::
  • Get started with Excel 2013
  • Create a basic worksheet
  • Modify a workbook
  • Use formulas and functions
  • Format an Excel worksheet
  • Manage worksheets and workbooks
  • Set up printing options
Module 1: Getting Started with Excel 2013:
  • Excel and the Excel Environment
  • Navigate and Select in Worksheets
  • Create a Basic Workbook
  • Open, Save and Close Workbooks
  • Use Microsoft Help
Module 2: Modifying a Workbook:
  • Cut, Copy and Paste Commands
  • Flash Fill
  • AutoFill Options
  • Find and Replace
  • Spelling
  • Undo and Redo
  • Insert and Delete Options
  • Column Width and Row Height
  • Hide and Unhide Options
Module 3: Using Formulas and Functions:
  • Create Relative Formulas
  • AutoCalculate
  • Insert Functions
  • Use AutoSum
  • Copy Formulas
  • Create Absolute Formulas
Module 4: Formatting an Excel Worksheet:
  • Font Group
  • Add Borders and Fill Colours
  • Cell Alignment
  • Number Formatting
  • Cell Styles and Galleriesv
Module 5: Managing Worksheets & Workbooks:
  • Manage Worksheets
  • View Worksheets and Workbooks
Module 6: Printing Options:
  • Print Pane
  • Print Worksheets and Workbooks
  • Page Setup
  • Page Layout View

close

Course Guide   

Microsoft Excel 2013 Intermediate

2 days

 

BSG Training - delivering ICT Training Solutions for you

Target Audience: This course is aimed at end users who have taken the following course or have equivalent knowledge: Microsoft Excel 2013 Introduction

Prerequisites: Create basic spreadsheets; Select and edit data; Perform basic formatting; Open, close and save files; Create basic formulas – AutoSum; Use Insert Function to create built-in functions; Work with absolute references and Use the AutoFill feature

In 2 days, Delegates will learn to:
  • Calculate with advanced formulas
  • Audit formulas using the auditing tools
  • Work with Excel tables
  • Organise worksheet data so that data can be sorted and filtered
  • Create and modify Charts
  • Create Excel templates
  • Apply Conditional Formatting
  • Analyse data with Recommended Pivot Tables
Module 1: Calculating Advanced Formulas:
  • Use Excel's Quick Analysis Tools
  • Using Mixed References in Calculations
  • Work with Range Names
  • Calculating across Worksheets
  • Exploring Excel's Function Categories
  • Analysing Data Based on Criteria
  • Working with Text Functions
  • Calculating with Financial Functions
  • Using Logical Functions
  • Applying Lookup Functions
Module 2: Auditing a Worksheet:
  • Display formulas in worksheet cells
  • Show precedent and dependent cells
  • Finding and Resolving Errors
  • Using the Watch Window
  • Evaluating a Formula
Module 3: Mastering Excel Tables:
  • Introducing Excel Tables
  • Creating Calculated Columns
  • Formatting a Table
  • Using Advanced Table Tools
  • Using Slicers
  • Converting Tables to Ranges
  • Using Advanced Tables Tools
Module 4: Organising Worksheet Data:
  • Apply Basic Sorting to a Data Range
  • Advanced Sorting
  • Summarise Data with Subtotals
Module 5: Charts:
  • Understanding Charts
  • Create a Chart
  • Modify and Format a Chart
  • Add and Remove Chart Data
  • Add a Trendline to a Chart
  • Analyse Data Using Sparklines
Module 6: Working with Templates:
  • Create a Hyperlink
  • Adding Comments
  • Work with Templates
Module 7: Analysing Selected Data:
  • Applying Basic Filters
  • Advanced Filters
  • Use Database Functions
  • Use Outlines to Organise Data
Module 8: Apply Conditional Formatting:
  • Conditional Formatting
  • Customising Conditional Formatting
  • Sorting and Filtering by Colour
Module 9: An Introduction to Pivot Tables:
  • Understanding Pivot Tables
  • Analyse Data with Recommended Pivot Tables
Module 10: Appendix – Illustrations:
  • SmartArt
  • Insert Images into a Spreadsheet
  • Insert Shapes into a Spreadsheet
  • Group and Layer Graphics
Module 11: Appendix – Excel Options
close

Course Guide   

Microsoft Excel 2013 Advanced

2 days

 

BSG Training - delivering ICT Training Solutions for you

Target Audience: This course is aimed at end users who want to develop their skills so they can use advanced techniques to manipulate Excel data. Delegates should have attended Excel 2013 Intermediate course or have equivalent knowledge.

Prerequisites: Create, edit and format spreadsheets; Navigate within worksheets and books;Use Insert Function to create built-in functions; Work with absolute references; Create named ranges; Create Tables within Excel and Sort and filter data.

In 2 Days - Delegates will learn to:
  • Create macros to automate common tasks
  • Collaborate with others by merging and tracking workbooks
  • Create and manipulate PivotTables and PivotCharts
  • Writing functions as an array
  • Write advanced Lookup and Reference Functions
  • Use advanced techniques to analyse data
  • Ensure acceptable data entry with Data Validation
  • Import and export data
Module 1: Creating Macros:
  • Creating Macros
  • Comparing Relative and Absolute Macros
  • Running a Macro
  • Assigning Shortcuts to Macros
  • Macro Security
  • Enabling Macros in the Backstage View
  • Copying a Macro between Workbooks
  • Document Properties and the Document Inspector
Module 2: Collaborating With Others:
  • Protection
  • Track Changes
  • Workbook Merging
Module 3: Creating PivotTables, PivotCharts and Slicers:
  • Creating a PivotTable
  • Amending the Fields in a PivotTable
  • Updating the PivotTable
  • Adding Calculations to a PivotTable
  • Add Grouping to a Pivot Table
  • Slicers
  • PivotTable Timelines
  • Creating PivotTables from Tables and Related Tables
Module 4: Array Functions:
  • An Array Formula
  • Array Functions
  • Frequency
  • Transpose
  • Single Cell or Multiple Cell Arrays
  • Single Cell Array Functions
  • Multiple Cell Array Functions
Module 5: Advanced Lookup and Reference Functions:
  • ROW/COLUMN
  • INDEX
  • OFFSET
  • INDIRECT
  • LOOKUP (Array format)
  • GETPIVOTDATA
  • MATCH
  • VLOOKUP
Module 6: Analysing Data:
  • Creating a Single Input Data Table
  • Create a Two-Variable Data Table
  • Creating Scenarios
  • What-If Analysis Using Goal Seek
  • Consolidating Data
  • Linking to External Workbooks
Module 7 – Data Validation:
  • Number Validation
  • Data List Validation
  • Message Prompts and Alerts
  • Conditional Data Validation
  • Data Validation Errors
Module 8 – Import/Export Data:
  • Exporting Excel Data
  • Importing Delimited Text Importing a Web Query

close

Course Guide   

Microsoft Excel Data Analysis and Power Tools Part 1

2 days

 

BSG Training - delivering ICT Training Solutions for you

This is the first of a two part series of courses examining the data tools and features found in Microsoft Excel 2013 Professional and Pro Plus.

The course is designed to examine a combination of new and revised features which are available to help you work with data in Microsoft Excel 2013. Features covered range from data importing to analysis leading to subsequent output in Excel. Analysis and outputs include data and graphical tools available in Microsoft Excel 2013.

Target Audience: Anyone who needs to work with Excel in order to connect to, process, analyse and display data in order to produce suitable output at all levels within an organisation.
It is assumed that the majority of attendees on the course have either migrated to Microsoft Excel 2013 from a previous version or whose role has changed requiring the use of data tools.
*Some of the tools and features are only available in Microsoft Excel 2013 and include add-ins. This course is not suitable for anyone using Microsoft Excel Standard, Home or Student Editions.

Prerequisites: Please note that this course is not suitable for new Excel users; An understanding of basic data concepts; Ability to create, format and save worksheets and workbooks; Be able to create formulas using standard aggregate function.

In 2 days - Delegates will learn to:
  • Import data into Excel 2013
  • Create connections to external data sources
  • Use Workbook Analysis
  • Utilise new Excel 2013 features to analyse data
  • Present external and Excel based organisational data using a combination of graphical and data views
Module 1: Getting the data to your workbook
  • Import or Connection
  • Data from other Excel Sources
  • Retrieving data from other applications
  • Using web based data
Module 2: Preparing Data, Organising Data and Workbook Analysis
  • Tabular Structures and Data Properties
  • Creating and working with Data Models, Relationships, and Sets
  • Workbook Analysis with Inquire
Module 3: Using Data Tables and Functions
  • Use Data Table Functions and Slicers
  • Analysis with Excel Functions
Module 4: Working with Sparklines, Charts and Trends
  • Visualise Trends with Sparklines
  • Using Charts and Trendlines
  • Adding Error Bars
Module 5: PivotCharts
  • Introduction to PivotTables (Mod 5 portion - overview of concepts)
  • Create PivotCharts
  • Shaping and Filtering the data using PivotCharts
Module 6: Power View
  • Creating Power View Reports
  • Using Tables, Cards and Matrices
  • Charting in Power View Reports
  • Mapping geo-data

close

Course Guide   

Microsoft Excel Data Analysis and Power Tools Part 2

2 days

 

BSG Training - delivering ICT Training Solutions for you

This is the second of a two part series of courses examining the data tools and features found in Microsoft Excel 2013 Professional and Pro Plus*.

The course is designed to examine a combination of new and revised features which are available to help you work with data in Microsoft Excel 2013. Technologies covered include PivotTables, Power Query, PowerPivot and Power Map.

Target Audience: - Anyone who needs to work with Excel in order to connect to, process, analyse and display data in order to produce suitable output at all levels within an organisation. It is assumed that the attendees on the course have either have attended the first course in the series or have equivalent knowledge.

*Some of the tools and features are only available in Microsoft Excel 2013 and include add-ins. This course is not suitable for anyone using Microsoft Excel Standard, Home or Student Editions.

Prerequisites: Attendance on Working with Data in Microsoft Excel 2013 Part 1; Able to import data and work with the Data Model and Relationship; Be able to create formulas using standard aggregate functions

In 2 days - Delegates will learn to:

  • Create PivotTables and analyse data
  • Connect to and shape external data using Power Query
  • Use PowerPivot for data connection and analysis
  • Use Power Map to create visualisations of geo-dat

Module 1: Create and Work With PivotTables

  • Creating PivotTables from tables
  • Working with PivotTables to create views and analysis
  • Dynamic Filters with Slicers and Timelines
  • Use the Data Model to create PivotTables
  • PivotTables from related data sources

Module 2: Introduction to Power Query for Excel

  • What is Power Query?
  • Power Query Data Sources
  • Data Import Process and Query Editor Overview
  • Data Transforms using the Query Editor
  • Column, Row and Calculated Transform Actions
  • Combining Data Sources as a Mash Up

Module 3: PowerPivot

  • Concepts and Components
  • Working with the Manage Console
  • Creating and Editing Data Relationships
  • Calculated Items and KPIs
  • Creating PivotTables
  • Dashboards using PowerPivot

Lesson 4: Power Map

  • Overview of Power Map
  • What is geo-data?
  • Creating Maps and Applying Options
  • Tours and Scenes in Power Map
  • Exporting Maps to other applications


close

Course Guide   

Microsoft Excel Power Query

1 day

 

BSG Training - delivering ICT Training Solutions for you

This course is delivered using Microsoft Excel 2013. Power Query is available for Excel 2010 and Excel 2013. Please note this course is not suitable to new users of Excel.

Target Audience: Anyone who needs to work with Microsoft Excel in order to connect to external data and then process, analyse and display the data in order to produce suitable output at all levels within an organisation. Attendees on the course will be existing users of Microsoft Excel 2010 or 2013 who are new to Power Query and whose role requires the use of data tools for analysis purposes.

Prerequisites: An understanding of basic data concepts; Understand PivotTables; Be able to create formulas using standard aggregate functions

In 1 day - Delegates will learn to:
  • Import data into Excel Using Power Query
  • Create connections to external data sources and use the Data Model
  • Edit Power Queries
  • Work with Tables and Fields
Module 1: Introduction to Microsoft Power Query for Excel
  • Concepts and Components
  • Compatible Data Sources
  • Data Import Process
Module 2: Import Data from External Data Sources
  • Connect To and Query Compatible Data Sources
  • Using the Default Excel Table Output
Module 3: Transforming Table Data
  • Filter, Sort and Group Data
  • Group Rows
  • Shape Data
  • Replacing Values, Text Transforms, Number Transforms, Date and Time Transforms
  • Data Aggregation
  • Unpivoting
  • Transposing a Table
  • Work with Columns
  • Name, Move, Split, Merge
Module 4: Using the Query Editor
  • Edit Query Steps
  • Edit Query Step Settings
  • Refresh a Query
Module 5: Combine Multiple Queries
  • Merge and Append Queries
  • Manage Queries
Module 6: Shape Data from Multiple Data Sources
  • Power Query and Table Relationships
  • Table Links and Navigation
  • Choose a Destination for your Data
  • Add a Query to an Excel Worksheet
  • Add a Query to the Data Model
  • Advantages of using the Data Model
Module 7: An Introduction to Advanced Queries
  • Creating an Advanced Query
  • Modify a Formula
  • Power Query Formula Language

close
 

Course Guide   

Microsoft Excel 2010 Introduction

1 day

 

BSG Training - delivering ICT Training Solutions for you

Target Audience: Target Audience: This course is designed for those who wish to gain the necessary skills to create, edit, format and print basic Microsoft Excel 2010 worksheets.

Prerequisites: Use a mouse; Type and use a keyboard; Navigate through Windows files and folders; Work with windows - minimise, maximise, open and close

In this 1 day course, Delegates will learn to:
  • Get started with Excel 2010
  • Create a basic worksheet
  • Modify a worksheet
  • Format an Excel worksheet
  • Manage worksheets and workbooks
  • Set up printing options
Module 1: Getting Started with Excel 2010
  • Identify the Elements of the Excel Interface
  • Navigate and Select Cells in Worksheets
  • Customize the Excel Interface
  • Create a Basic Worksheet
Module 2: Performing Calculations in an Excel Worksheet
  • Create Formulas in a Worksheet
  • Insert Functions in a Worksheet
  • Reuse Formulas
Module 3: Using Formulas and Functions
  • Edit Worksheet Data
  • Find and Replace Data
  • Manipulate Worksheet Elements
Module 4: Modifying the Appearance of a Worksheet
  • Apply Font Properties
  • Add Borders and Colors to Cells
  • Align Content in a Cell
  • Apply Number Formatting
  • Apply Cell Styles
Module 5: Managing Worksheets & Workbooks
  • Manage Worksheets
  • View Worksheets and Workbooks
Module 6: Printing Options
  • Define the Page Layout
  • Print a Workbook

close

Course Guide   

Microsoft Excel 2010 Intermediate

1 day

 

BSG Training - delivering ICT Training Solutions for you

Target Audience: This course is aimed at end users who have taken the following course or have equivalent knowledge: Microsoft Excel 2010 Introduction

Prerequisites: Create basic spreadsheets; Select and edit data; Perform basic formatting; Open, close and save files; Create basic formulas – AutoSum; Use Insert Function to create built-in functions; Work with absolute references and Use the AutoFill feature

In this 1 day course, Delegates will learn to:
  • Use advanced formulas
  • Organise worksheet and table data using various techniques
  • Create and modify charts
  • Analyse data using PivotTables, Slicers, and PivotCharts
  • Insert and modify graphic objects in a worksheet
  • Customise and enhance workbooks and the Microsoft Office Excel environment
Module 1: Calculating Data with Advanced Formulas
  • Apply Cell and Range Names
  • Calculate Data Across Worksheets
  • Use Specialized Functions
  • Analyze Data with Logical and Lookup (vLookup) Function
Module 2: Organizing Worksheet and Table Data
  • Create and Modify Tables
  • Format Tables
  • Sort or Filter Data
  • Use Functions to Calculate Data
Module 3: Presenting Data Using Charts
  • Create a Chart
  • Modify Charts
  • Format Charts
Module 4: Analyzing Data Using PivotTables, Slicers and PivotCharts
  • Create a PivotTable Report
  • Filter Data Using Slicers
  • Analyze Data Using PivotCharts
Module 5: Inserting Graphic Objects
  • Insert and Modify Pictures and Clipart
  • Draw and Modify Shapes
  • Illustrate Workflow Using SmartArt Graphics
  • Layer and Group Graphic Objects
Module 6: Customizing and Enhancing the Excel Environment
  • Customize the Excel Environment
  • Customize Workbooks
  • Manage Themes
  • Create and Use Templates

close

Course Guide   

Microsoft Excel 2010 Advanced

1 day

 

BSG Training - delivering ICT Training Solutions for you

Target Audience: Trained in and use of Microsoft Office Excel 2010 has provided you with a solid foundation in the basic and intermediate skills for working in Excel. You have already used Excel to perform tasks such as running calculations on data and sorting and filtering numeric data.

Prerequisites: Create, edit and format spreadsheets; Navigate within worksheets and books; Use Insert Function to create built-in functions; Work with absolute references; Create named ranges; Create Tables within excel; Insert SmartArt graphics; Work with Themes and Sort and filter data

In this 1 day course, Delegates will learn to:
  • Enhance productivity and efficiency by streamlining workflow
  • Collaborate with others workbook users
  • Audit worksheets
  • Analyse data
  • Work with multiple workbooks
  • Import and export data
  • Integrate Excel data with the web
  • Structure workbooks with XML
Module 1: Streamlining Workflow
  • Update Workbook Properties
  • Create a Macro
  • Edit a Macro
  • Apply Conditional Formatting
  • Add Data Validation Criteria
Module 2: Collaborating With Others
  • Protect Files
  • Share a Workbook
  • Set Revision Tracking
  • Review Tracked Revisions
  • Merge Workbooks
  • Administer Digital Signatures
  • Restrict Document Access
Module 3: Auditing Worksheets
  • Trace Cells
  • Troubleshoot Invalid Data and Formula Errors
  • Watch and Evaluate Formulas
  • Create a Data List Outline
Module 4: Analyzing Data
  • Create a Trendline
  • Create Sparklines
  • Create Scenarios
  • Perform a What-If Analysis
  • Perform a Statistical Analysis with the Analysis ToolPak
Module 5: Working with Multiple Workbooks
  • Create a Workspace
  • Consolidate Data
  • Link Cells in Different Workbooks
  • Edit Links
Module 6: Importing and Exporting Data
  • Export Excel Data
  • Import a Delimited Text File
Module 7: Integrating Excel Data with the Web
  • Publish a Worksheet to the Web
  • Import Data from the Web
  • Create a Web Query

close
 

 

 Book a Course