Excel Intermediate 2013 Course

MANAGING MULTIPLE WORKSHEETS AND WORKBOOKS

Managing Data Across Multiple Worksheets

• Renaming Worksheets In A Workbook
• Selecting Adjacent Worksheets
• Selecting Non-Adjacent Worksheets
• Simultaneously Enter Data Into Multiple Worksheets
• Creating And Preparing A Summary Sheet

Creating 3-D Formulas

• Entering A 3-D Formula Across Worksheets
• Enter A 3-D Formula Using A Sum Function

Combining Data From Different Worksheets Or Workbooks

• Consolidating Data In a Worksheet
• Consolidating Data Across Multiple Workbooks
• Manage Linked Workbooks
• Move / Copy Worksheets To Another Workbook

NAMED RANGES

Using Range Names

• Define A Named Range Quickly
• Define Multiple Named Ranges Using The Ribbon
• Pasting A Named Range In A Formula
• Selecting a Named Range From A List
• Using The ‘Paste Name’ Dialogue Box

WORKING WITH EXCEL TABLES

Introducing General Table Rules

Applying The Table Tool

• Creating A Table
• Changing The Style Applied To A Table
• Create Your Own Table Style
• Showing Or Hiding Table Features
• Adding The Total Row To A Table
• Inserting Table Rows
• Deleting Table Rows
• Resizing The Table
• Performing A Simple Sort
• Sorting By More Than One Column
• Customising The Default Sort Order
• Enable And Disable The AutoFilter In A Table
• Applying A Filter
• Clearing A Filter
• Custom Number And Date Filters
• Use A Search To Filter A Table
• Removing The Table Tool

THE VLOOKUP FUNCTION

• Setting Up Named Ranges
• Entering A VLOOKUP Function
• Using the Insert Function Wizard To Enter A VLOOKUP
• Use A VLOOKUP Function To Automate Data Entry
• Use A VLOOKUP Function To Find The Closest Match

OUTLINING DATA

• Creating An Outline Automatically
• Removing An Outline
• Outlining Large Worksheets
• Manually Creating An Outline
• Copying Outlined Results Using Visible Cells Only
• Subtotalling A Table And Outlining The Results
• Managing The Outline Using Outline Levels
• Removing A Subtotal

ADVANCED FORMATTING, STYLES AND THEMES

Customise The Appearance Of Dates And Numbers Using Advanced Formatting

• Formatting Numbers Overview
• Custom Number Format Syntax And Examples
• Customise The Number Format Using Format Cells
• Customise The Date Format Using Format Cells

Conditional Formatting

• Using Conditional Formatting To Highlight Cells
• Adding Data Bars To A Range
• Using Colour Scales
• Rank Data Using Icon Sets
• Creating A New Rule
• Edit An Existing Rule
• Setting The Precedence Of The Rules
• Stopping A Rule
• Clearing A Rule

Maintaining Consistency With Styles Or Themes

• Apply An In-Built Cell Style
• Create Your Own Cell Style
• Applying A Custom Cell Style
• Modifying A Cell Style
• Deleting A Cell Style
• Applying A Theme To A Workbook

CHARTS

Creating A Chart

• Inserting A Chart
• Changing The Existing Chart Type
• Manipulating The Chart Data Source
• Swap Rows/Columns
• Change The Data Series Order
• Moving A Chart

Modify A Chart

• Change A Chart Layout
• Change The Existing Chart Style
• Adding And Editing The Chart Title
• Adding Axis Titles
• Moving The Legend
• Adding Data Labels
• Showing A Data Table
• Add / Remove Gridlines
• Inserting A New Chart Element
• Delete An Existing Chart

Format The Chart

• Selecting Chart Elements
• Formatting Chart Elements
• Using WordArt In A Chart
• Using Pictures In A Chart
• Create A Combination Chart
• Creating A Pie Chart
• Rotating A 3D Chart Type
• Exploding A Wedge In A Pie Chart
• Sparklines in Excel
• Changing Sparkline Options

REVIEWING FORMULAS - Appendix

Simple Formulas In Excel

• Entering Formulas Using The Keyboard
• Manually Entering Formulas
• Working With Relative References
• Working With Absolute References
• Applying Absolute Cell Reference

Using Simple Functions In Excel

• Entering A Function To Perform A Calculation
• Using AutoSum To Calculate Row/Column Totals
• Using the Insert Function Wizard