Excel Intermediate 2010 Course

MULTIPLE WORKSHEETS AND WORKBOOKS

Working With Multiple Worksheets

• Rename A Worksheet
• Group Adjacent Worksheets
• Group Non-Adjacent Worksheets
• Enter Data Into A Group Of Worksheets
• Setup A Summary Sheet

Using A 3-D Formula Across Worksheets

• Enter A 3-D Formula
• Enter A 3-D Formula Using A Function

Combining Worksheets And Data From Different Workbooks

• Consolidating Data
• Linking Information From Multiple Workbooks
• Managing The Formula Links
• Moving Or Coping Worksheets Into Another Workbook

NAMING RANGES

Creating And Using Named Ranges

• Quickly Define Named Range
• Defining Multiple Named Ranges
• Insert A Named Range Into A Formula
• Entering Named Ranges Into A Formula From A List
• Using ‘Paste Name’ To Enter Named Ranges

TABLES

General Table Setup Rules

Creating A Table

• Create A Table
• Change The Table Style
• Create Your Own Table Style
• Show / Hide Table Elements
• Add The Total Row
• Insert And Delete Table Rows / Columns
• Resize An Existing Table
• Perform A Simple Sort
• Sort By Multiple Columns
• Change The Sort Order
• Use AutoFilter To Show Specific Records
• Turning The AutoFilter On / Off
• Appling / Removing A Filter
• Custom Filters
• Use A Search To Create A Filter
• Convert A Table To A Normal Range

VLOOKUP Function

• Range Names For The Table Array
• Use Vlookup To Automate A Search
• Enter A VLOOKUP Using the Insert Function Wizard
• Enter Vlookup Functions To Assist with Data Entry
• Return The Closest Match Using A VLOOKUP

Using Simple Database Functions

• Sum Records Using DSUM
• Average Records Using DAVERAGE
• Counting Records Using DCOUNT / DCOUNTA
• Finding The Highest And Lowest Values Using DMAX And DMIN

OUTLINING DATA IN A WORKSHEET

Data Outlining

• Outlining Data Automatically
• Removing An Outline Automatically
• Auto Outlining Large Worksheets
• Creating An Outline Manually
• Copy The Outline Results With Visible Cells Only
• Subtotal A Table
• Showing And Hiding Content Using Outline Symbols
• Removing A Subtotal From A Table

USING ADVANCED FORMATTING, STYLES AND THEMES

Advanced Number And Date Formatting

• Built-In Number Formats
• Advanced Custom Number Formats
• Customising Number Formats
• Customising Date Formats

Conditional Formatting Data

• Highlight Cells Using A Conditional Format
• Implementing Data Bars Into A Range Of Cells
• Implementing Colour Scales
• Use Icon Sets To Rank Data
• Create New Conditional Format Rule
• Edit Conditional Format Rules
• Set The Precedence Of Conditional Format Rules
• Stop A Conditional Format Rule
• Clear Conditional Format Rules

Maintaining Format Consistency With Themes And Styles

• Built-in Cell Styles
• Custom Cell Styles
• Apply Built-in Or Custom Cell Styles
• Modifying And Deleting A Cell Style
• Appling A Theme

CHARTING

Create A Chart

• Insert A Chart Into A Worksheet
• Modify The Chart Type
• Setting The Chart Data Source
• Change Grouping By Rows Or Columns
• Setting The Data Series Order
• Move An Existing Chart

Modify An Existing Chart

• Change The Chart Layout
• Change The Chart Style
• Change The Chart Title
• Add / Remove The Axis Titles
• Change The Location Of The Legend
• Add Data Labels To A Chart
• Add A Data Table To A Chart
• Add Or Remove Gridlines
• Insert And Deleting New Chart Elements

Chart Formatting

• Select Elements In A Chart
• Chart Element Formatting
• Adding WordArt To A Chart
• Adding Pictures To A Chart
• Combination Charts
• Working With Pie Charts
• Set The Rotation For 3D Charts
• Explode A Wedge In A Pie Chart
• Adding Sparklines in Excel
• Modifying Sparklines

REVIEWING FORMULAS - Appendix

Using A Simple Formulas In Excel

• Using The Keyboard To Enter A Formula
• Using Manual Operators In A Formula
• Relative Cell References
• Absolute Cell References
• Applying An Absolute Cell References To A Formula

Working With Simple Functions

• Entering A Simple Function
• Use The AutoSum Button To Calculate Totals
• Using Insert Function To Enter A Formula