Convenient one, two or three day onsite or online courses.
Flexible scheduling to suit your timing requirements.
Choose the topics you need to get your job done.
Microsoft Excel – Introductory
Topic 1: Microsoft Excel training basics
• The Excel 2010 & 2013 Ribbon customizable interface
• New features in Excel 2013, “Sparklines””Flash fill”
• New Charts suggestions, Pivot Timelines, Apps for Excel and more
Topic 2: Formatting a worksheet in Microsoft Excel
• Default General, Text, Number, Currency, Percentage formats etc.
• Copying and pasting formats using the format painter (a very useful tool)
• Conditional Formatting (New Features/Options in 2013)
• Adding comments & text boxes to worksheets
Topic 3: Entering data and navigating in a worksheet
• Inputting numbers & Text
• Editing cell contents after input
• Navigation short cut keys (e.g. Control End: goes to last cell)
Topic 4: Creating Formulas & Functions in Microsoft Excel
• Creating Excel formulas (SUM, AVERAGE, MAX, MIN etc)
• Editing & checking formulas & functions (New functions in 2013)
• Using Absolute references and when are they needed
• Creating, defining named ranges (Defined Names) & why use them
Topic 5: Copy and Move Cells
• Inserting/deleting entire rows and columns versus cells
• (Copy cells or the entire worksheet)
• Moving data Copying data (shortcut keys, Control + C etc.)
• Using the Autofill feature to list days of the week
• Adding Custom Lists
Topic 6: Printing worksheets
• Spell checking in MS Excel
• Print Preview and Page breaks
• Examining print options
Topic 7: Multiple worksheet workbook in Excel
• Creating a multiple-sheet workbook
Microsoft Excel – Intermediate
Topic 1: Creating Excel Charts
• Creating a new chart in MS Excel (labels & data)
• Selecting Row or Column orientation
• New “Sparkline Charts in Excel 2010 & 2013)
Topic 2: Modifying charts in Microsoft Excel
• Selecting Chart types (Column, Bar, Line Charts etc.)
• Modifying charts elements
• Adding, modifying and/or deleting chart items
• Moving and sizing chart items
Topic 3: Formatting Elements of a chart
• Formatting chart text in MS Excel
• Formatting the chart regions
Topic 4: Using graphic objects
• Inserting graphic objects & AutoShapes
• Modifying graphic objects
• Using graphic objects to enhance worksheets and charts
Topic 5: Sorting data in Excel
• Single-level sorting in MS Excel
• Adding sorting levels
• New sorting options: cell colour, font colour or icon
• Sorting options for Custom Sort (a non-alphabetic sort)
• Design considerations
Topic 6: Filtering data in Microsoft Excel
• Creating a Filtered list
• Add Custom criteria
• Create multiple criteria
• New filtering options: cell colour, font colour or icon
• Managing a filtered list
Topic 7: Useful Summary Functions to save time
• SUMIF to sum by label criteria (a legacy function)
• SUMIFS Excel 2010 & 2013 sum by multiple criteria
• The SUBTOTAL function creates formulas that you specify
Microsoft Excel – Advanced
Topic 1: Customizing your Work Area
• Creating and saving Custom Views
• Saving and using Templates
• Protecting Worksheets from unauthorized changes
Topic 2: Named Ranges in Formula Construction
• Creating Names for cells (named ranges) – how they work in formulas in MS Excel
• Best practices with named ranges
• Saving time & reducing errors with named ranges
Topic 3: Pivot Tables for Data Analysis
• Creating Pivot Tables for summary information & data analysis
• Modifying an existing Pivot Table
• Subtotals in an Excel Pivot Table
• Adding Interactive Pivot Charts
Topic 4: The Powerful IF Statement
• Creating an IF Statement (True or False output)
• Create multiple IFs when there are a range of outputs needed
• Add nesting IF statement with OR & AND functions included
Topic 5: VLOOKUP & HLOOKUP for Data Automation
• Creating a VLOOKUP Function to return a value
• VLOOKUPs Exact or Approximate match
• How the VLOOKUP can work across files
• Using the HLOOKUP Function
Topic 6: IS function for checking formulas
• The IS Functions to check formulas
• Auditing functions ISERROR to see errors
• IFERROR can be used to reduce clutter
Topic 7: Linking Multiple Worksheets
• Problems with working with many workbooks
• Open a second window on a workbook
• Linking Cells in Different Worksheets
Topic 8: Consolidating Data
• Consolidating Data from many worksheets
• Links to summarize
• Using CONSOLIDATE functions
• Create a 3D formula in Excel
Topic 9: Goal Seek & Solver for finding solutions
• Create a Goal Seek to give a solution
• Using Solver Utilities in MS Excel
• Using Scenario Manager to View a Worksheet with Different Input Values
Topic 10: Using the Scenario Manager
• Scenario Manager with different inputs
• Creating a summary report from Scenario manager
Topic 11: Introduction to power of Macros in Microsoft Excel
• Running existing Excel Macros
• Recording a new Macro for repetitive actions