Intermediate Excel Course

Duration: 1 day

Description

This 1 day intermediate Excel course is designed to improve your productivity within Excel. You'll discover the most useful and most commonly used parts of Excel - the features that can be used by anybody in any type of business.

You'll master techniques to achieve quick results and produce slicker, more efficient workbooks that are better laid out, more consistent and easier to maintain.

Topics

WhatÂ’s new in the latest version(s) of Excel?

      • An overview of Excel's new features

Working with Excel databases

      • Entering data using AutoComplete
      • Entering data using Pick From List
      • Creating Spark Lines
      • Flash Fill
      • Filtering data using AutoFilter
      • Filtering data using Advanced Filter
      • Sorting rows or columns into order
      • Creating and using custom lists
      • Creating automatic subtotals

35 of Excel's Most Useful Formula Functions

      • Brief recap of the five basic functions
        - SUM, AVERAGE, MAX, MIN, COUNT
      • Using the Function Wizard
      • Smart ways to propagate formulas
      • COUNT functions - COUNT, COUNTA, COUNTBLANK
      • COUNTIF & COUNTIFS
      • SUMIF & SUMIFS
      • AVERAGEIF & AVERAGEIFS
      • MAXIF and MINIF
      • Logical functions - AND, OR, IF
      • Date and Time functions
        - TODAY, NOW, DAY, MONTH, YEAR, DATE, HOUR, MINUTE
      • ROUNDING functions - ROUND, ROUNDUP, ROUNDDOWN, CEILING, FLOOR, ABS, TRUNC
      • PI (for engineers & mathematicians)

Advanced Formatting Techniques

      • Establishing a theme
      • Conditional formatting
      • Using data bars, colour scales and icon sets
      • Two ways to use the Format Painter tool
      • Using styles and style sets to improve consistency and maintainability

Absolute vs Relative Cell Addressing

      • Using AutoFill and Smart tags
      • Absolute cell addressing?
      • The importance of dollar signs
      • F4 - The magic key
      • Partially-absolute addressing

Naming Cells & Cell Ranges

      • Naming a cell using the Name box
      • Naming cells using the Name Manager
      • Creating a constant
      • Using named cells for navigation
      • Using named cells within a formula
      • Modifying and deleting named cells
      • Creating names from existing headings
      • Applying names to existing formulas
      • Displaying a list of named cells

Tools To Validate Data

      • Setting the rules
      • Giving instructions to the user
      • Alerting the user of an error
      • Conveying the severity of invalid data
      • Highlighting invalid data

Charts

      • Creating a chart using the new Chart Ribbons
      • Modifying a chart
      • Changing the chart type
      • Changing the source of the data
      • Setting the chart options
      • Showing or hiding different chart components
      • Changing the chart location
      • Selecting and formatting a chart component
      • Applying a gradient, texture, pattern or picture fill to a chart component
      • Manipulating 3D charts
      • Using the Chart toolbar
      • Creating and saving custom chart types
      • Previewing and saving a chart as a web page

Grouping Data The Sophisticated Way

      • Creating an outline manually or automatically
      • Collapsing and expanding an outline
      • Removing one or all outlines
      • Expanding the range of an outline
      • Using outlines with charts
      • Outline styles
x