Microsoft Excel Training - Level 2
Learning Outcomes: When you have completed this course, you will be able to create more productive workbooks covering comprehensive formulas and function techniques, advanced formatting and charting features and data manipulation tools like goal seeking.
Course Pre-Requisites: This level assumes the learner has the knowledge required to create, edit, print and chart simple worksheets. Understanding ranges and copying is also important.
Duration: 3 hours
Filling Data
Understanding Filling
Filling A Series & A Growth Series
Filling A Series Backwards
Filling Using Options
Creating & Modifying A Custom Fill List
Deleting A Custom Fill List
Extracting With Flash Fill
Extracting Dates & Numbers
Worksheet Techniques
Inserting & Deleting Worksheets
Copying & Renaming A Worksheet
Moving A Worksheet
Hiding & Unhiding A Worksheet
Copying A Sheet to Another Workbook
Changing Worksheet Tab Colours
Grouping Worksheets
Hiding & Unhiding Rows & Columns
Freezing Rows & Columns
Splitting Windows
Page Set-up
Strategies for Printing Worksheets
Understanding Page Layout
Using Built in Margins
Setting Custom Margins
Changing Margins by Dragging
Centring on a Page
Changing Orientation
Specifying the Paper Size
Setting the Print Area
Clearing the Print Area
Inserting Page Breaks
Using Page Break Preview
Removing Page Breaks
Setting & Clearing the Background
Settings Rows as Repeating Print Titles
Clearing Print Titles
Printing Gridlines & Headings
Scaling to a Percentage
Fit to a Specific Number of Pages
Applying Borders Understanding Borders Applying a Border to a Cell Applying a Border to a Range Applying a Bottom & Top Border Removing Borders Using the More Borders Command Drawing Borders & a Border Grid Erasing Borders Formatting the Drawing Pencil
Absolute Referencing Absolute Versus Relative Referencing Relative Formulas Creating Absolute Referencing Creating Mixed References
Essential Functions Key Worksheet Functions Using IF With Text Using IF With Numbers Nesting IF Functions
Complex Formulas Scoping A Formula Long Hand Formulas Preparing for Complex Formulas Creating the Base Formula Adding More Operations Editing a Complex Formula Adding More Complexity Copying Nested Functions Switching to Manual Recalculation Pasting Values From Formulas |
Defined Names
Understanding Defined Names
Defining Names from Worksheet Labels
Using Names in Typed Formulas
Applying Names to Existing Formulas
Creating Names Using the Name Box
Using Names to Select Ranges
Pasting Defined Names Into Formulas
Defining Names for Constant Values
Creating Names From a Selection
Scoping Names to a Worksheet
Using the Name Manager
Documenting Defined Names
Documenting Formulas
Number Formatting Techniques
Applying Alternate Currencies
Applying Alternate Date Formats
Formatting Clock Time
Formatting Calculated Time
Understanding Number Formatting
Understanding Format Codes
Creating Descriptive Custom Formats
Custom Formatting Large Numbers
Custom Formatting for Fractions
Padding Numbers Using Custom Formatting
Aligning Numbers Using Custom Formats
Customising the Display of Negative Values
Conditional Formatting
Understanding Conditional Formatting
Formatting Cells Containing Values
Clearing Conditional Formatting
More Cell Formatting Options
Top Ten Items
More Top and Bottom Formatting Options
Working With Data Bars
Working With Colour Scales
Working With Icon Sets
Understanding Sparklines
Creating Sparklines
Editing Sparklines
The Quick Analysis Tools
Understanding Quick Analysis
Quick Formatting
Quick Charting
Quick Totals
Quick Sparklines
Quick Tables
Worksheet Tables Understanding Tables Creating A Table From Scratch Working with Table Styles Inserting Table Columns Removing Table Columns Converting A Table to a Range Creating A Table from Data Inserting or Deleting Table Records Removing Duplicates Sorting Tables Filtering Tables Renaming a Table Splitting a Table Deleting A Table
Chart Elements Understanding Chart Elements Adding A Chart Title Adding Axes Titles Repositioning the Legend Showing Data Labels Showing Gridlines Formatting The Chart Area Adding A Trendline Adding Error Bars Pasting Values From Formulas Adding A Data Table
Chart Object Formatting Understanding Chart Formatting Selecting Chart Objects Using Shape Styles Changing Column Colour Schemes Changing the Colour of a Series Changing Line Chart Colours Using Shape Effects Colouring the Chart Background Understanding & Using the Format Pane Exploding Pie Slices Changing Individual Bar Colours Formatting Text Formatting With Word/Art Changing Word Art Fill Changing WordArt Effects
|