Microsoft Office and Accounts

Quick Overview of Excel Shortcuts

• Copying Sheets using CTRL
• Grouping Sheets and Setting up Page Layout

Customising Toolbars to incorporate

• Show All Filter Button
• Quick Filter Button
• Select Visible Cells
• Select Active Cells

Useful Formulae

• Trim

• Left, Right

• Concatenate (Concatenate text and nums)

• Text to Columns

• Removing Duplicates (via IF statements, or Advanced Filters) 

Conditional Formatting

• Calculations in Conditional Formatting

• Formatting whole Rows to Colour
• vLookups
•  Locking the Column to quickly produce lookups by copying
• Working with dates
• Date Calculations
• Formatting Dates to show Days

Pivot Tables

• Creating Pivot Tables using the Wizard
• Creating Pivot Tables without the Wizard
• Formatting Pivot Tables
• Understanding the Group by Page Option
• Creating Multiple Page Pivots based on Page
• Filtering inside Pivots
• Displaying results from a Total
• Grouping Data inside a Pivot Table

• Calculated Fields inside a Pivot Table

Macros

• Introduction to Macros
• Personal Workbook Macro vs Existing Workbook
• Absolute and Relative Macros
• Recording a Macro
• Viewing and Editing a Macro
• Editing the VBA Code
• Copying and Adding to Macros
• Understanding Modules
• Creating Simple Macro Code
• Creating a button to run a Macro
• Creating a toolbar button
• Running Macros
• Macro Security

About SquareOne

Any SquareOne course can be tailored to meet the particular requirements of your organisation.  We will be happy to use any of your examples for delegates to ensure that the course is as interesting and relevant as possible.

Course Objectives

This course is suitable for advanced users of Excel who wish to improve their understanding of Macros and produce powerful well presented spreadsheet models. 

Course Details

Duration: 2 Days
Timings: 9:30 - 16:30

Course Contents

The subjects listed on the left are an outline. If there are any additional subjects you wish to cover, please feel free to call us prior to the course.