Microsoft Excel 2013 Level 2 (with Challenge Exercises)

LoanNew

The skills and knowledge acquired in Microsoft Excel 2013 - Level 2 (with Challenge Exercises) enable users to expand their knowledge and learn how to create more productive workbooks. It covers formulas and function techniques, advanced formatting and charting features and data manipulation tools like PivotTables and goal seeking.

Anyone who wants to know more about the functionality of Excel 2013.

Microsoft Excel 2013 - Level 2 (with Challenge Exercises) assumes the learner has the knowledge required to create, edit, print and chart simple worksheets. Understanding ranges and copying is also important.

Fill Techniques

• Creating A Custom Fill List
• Modifying A Custom Fill List
• Deleting A Custom Fill List
• Extracting With Flash Fill
• More Complex Flash Fill Extractions
• Extracting Dates And Numbers
• Practice Exercise
• Practice Exercise Data

Worksheet Techniques

• Inserting And Deleting Worksheets
• Copying A Worksheet
• Renaming A Worksheet
• Moving A Worksheet
• Hiding A Worksheet
• Unhiding A Worksheet
• Copying A Sheet To Another Workbook
• Moving A Sheet To Another Workbook
• Changing Worksheet Tab Colours
• Grouping Worksheets
• Hiding Rows And Columns
• Unhiding Rows And Columns
• Freezing Rows And Columns
• Splitting Windows
• Practice Exercise
• Practice Exercise Sample

Page Setup

• 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 A Background
• Clearing The Background
• Settings Rows As Repeating Print Titles
• Clearing Print Titles
• Printing Gridlines
• Printing Headings
• Scaling To A Percentage
• Fit To A Specific Number Of Pages
• Practice Exercise
• Practice Exercise Sample

Applying Borders

• Understanding Borders
• Applying A Border To A Cell
• Applying A Border To A Range
• Applying A Bottom Border
• Applying Top And Bottom Borders
• Removing Borders
• The More Borders Command
• Using The More Borders Command
• Drawing Borders
• Drawing A Border Grid
• Erasing Borders
• Formatting The Drawing Pencil
• Practice Exercise
• Practice Exercise Sample

Essential Functions

• Key Worksheet Functions
• Using IF With Text
• Using IF With Numbers
• Nesting IF Functions
• The CHOOSE Function
• The LOOKUP Function
• Using Counting Functions
• The Round Function
• Rounding Up And Rounding Down
• Manipulative Functions
• The MOD Function
• The TODAY Function
• The NOW Function
• The DATE Function
• The PMT Function
• Practice Exercise
• Practice Exercise Sample

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
• Documenting Formulas
• Practice Exercise
• Practice Exercise Sample

Financial Functions

• Understanding Financial Functions
• Using PMT
• Using FV
• Using NPV
• Using PV
• Using RATE
• Using EFFECT
• Using NOMINAL
• Practice Exercise
• Practice Exercise Sample

Date and Time Functions

• Understanding Date And Time Functions
• Using NOW
• Using HOUR And MINUTE
• Using TODAY
• Calculating Future Dates
• Using DATE
• Using Calendar Functions
• Using WEEKDAY
• Using WEEKNUM
• Using WORKDAY
• Using EOMONTH
• Practice Exercise
• Practice Exercise Sample

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
• Practice Exercise
• Practice Exercise Sample

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
• Practice Exercise
• Practice Exercise Sample

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
• Practice Exercise
• Practice Exercise Sample

Goal Seeking

• Understanding Goal Seeking
• Using Goal Seek
• Practice Exercise
• Practice Exercise Sample

The Quick Analysis Tools

• Understanding Quick Analysis
• Quick Formatting
• Quick Charting
• Quick Totals
• Quick Sparklines
• Quick Tables
• Practice Exercise
• Practice Exercise Sample

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
• Practice Exercise
• Practice Exercise Workspace

PivotTables

• Understanding PivotTables
• Recommended Pivot Tables
• Creating Your Own PivotTable
• Defining The PivotTable Structure
• Filtering A PivotTable
• Clearing A Report Filter
• Switching PivotTable Fields
• Formatting A PivotTable
• Understanding Slicers
• Creating Slicers
• Inserting A Timeline Filter
• Practice Exercise
• Practice Exercise Sample

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
• Adding A Data Table
• Practice Exercise
• Practice Exercise Sample

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 The Format Pane
• Using The Format Pane
• Exploding Pie Slices
• Changing Individual Bar Colours
• Formatting Text
• Formatting With WordArt
• Changing WordArt Fill
• Changing WordArt Effects
• Practice Exercise
• Practice Exercise Sample

At the completion of this course you should be able to:

• use filling techniques
• use a range of techniques to work with worksheets
• apply a variety of page setup techniques
• apply borders to cells and ranges in a worksheet
• use the dozen or so most popular and common worksheet functions
• create more complex formulas and functions
• use a variety of financial functions
• use the date and time functions to perform calculations
• create and use defined names in a workbook
• apply a range of number formatting techniques to worksheet cells
• apply conditional formatting to ranges in a worksheet
• use goal seeking to determine the values required to reach a desired result
• understand and use Excel's Quick Analysis tools
• work with tables in Microsoft Excel
• understand and create simple PivotTables
• use a range of elements and features to enhance charts
• select and change the format of objects in a chart

Watsonia workbook with 227 topics. Files for the exercises are provided on a CD or can be downloaded here

Windows with Excel 2013, this software is available on MCS computers around the university, for further information email: training@ucs.cam.ac.uk or

Approximately 20 hours

• Excel 2016: Analysing and Summarising Data
• Excel 2016: Functions
• Excel 2016: Introduction
• Excel 2016: Introduction (Self-paced)
• Excel 2016: Managing Data & Lists
• Excel 2016: Recorded Macros
• Office 2016: Excel, Word and PowerPoint Top Ten Tips
• Office 365, Office Online and OneDrive: An introduction