MICROSOFT EXCEL FOR INTERMEDIATE COURSE

What is Excel?

Microsoft Excel is one of the most simplest and powerful software applications. It lets users do quantitative analysis, statistical analysis with an intuitive interface for data manipulation, so much so that its usage spans across different domains and professional requirements. It is quite different from Basic Excel, the focus for the user is more on DSUM, DCOUNT, Pivot Table, Pivot Chart, Formulas, Functions, and Macros.

Course Overview?

This short course will extend your knowledge of Excel beyond Microsoft Excel: Level 1 and provide you with the skills and knowledge to produce more effective and productive workbooks. Learn essential functions as well as how to manage list data, cover formulas and function techniques, PivotTables, intricate formatting, setting complex printing options and using intricate charting features. Completion of this course provides sufficient knowledge to progress onto Microsoft Excel: Level 3.

At the intermediate level, users begin to use more complex formulas. You're considered intermediate when you're familiar with how to use pivot tables, VLookup, and date functions among other more. Advanced Excel users need to know how to manage and organize large amounts of data. You must be able to make comprehensive graphs, tables, and record a macro to automate common tasks.

Course objectives

This course aims to provide more experienced users with proficient skills in Excel’s three major strands: formulas, list management and charts, as well as providing tips to assist efficiency

Outcomes

By the end of this course, you should be able to:

• use the fill operations available to fill a data series
• use a range of techniques to work with worksheets
• protect data in worksheets and workbooks
• use common worksheet functions
• understand and create simple PivotTables
• create more complex formulas and functions
• 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
• create and work with tables
• use a range of elements and features to enhance charts
• select and change the format of objects in a chart.

Course contents

• Creating and Modifying a Custom Fill List
• Deleting a Custom Fill List
• Extracting with Flash Fill
• More Complex Flash Fill Extractions
• Extracting Dates and Numbers
• Hiding a Worksheet
• Unhiding a Worksheet
• Moving or Copying a Sheet to Another Workbook
• Changing Worksheet Tab Colors
• Grouping Worksheets
• Hiding Rows and Columns
• Unhiding Rows and Columns
• Freezing Rows and Columns
• Splitting Windows
• Understanding Data Protection
• Providing Total Access to Cells
• Protecting a Worksheet
• Working with a Protected Worksheet
• Disabling Worksheet Protection
• Providing Restricted Access to Cells
• Password Protecting a Workbook
• Opening a Password Protected Workbook
• Removing a Password from a Workbook
• Setting up print area
• Designing the structure of a template
• Print titles: repeat Row/Columns
• Custom header/footer
• Key Worksheet Functions
• Using SUMIF, SUMIFS, COUNTIF and COUNTIFS Functions
• Using IF with Text and Numbers
• Nesting IF Functions
• Using logical functions (AND & OR)
• Using IF with AND & OR
• Using text Functions (Right, Left, Len, Proper, Lower and Upper)
• Using Concatenate Function
• The Round, Round up and Round down Functions
• The MOD Function
• The DATE and TEXT Functions
• Using LOOKUP Functions (VLOOKUP and HLOOKUP)
• 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
• Understanding PivotTables
• Recommended PivotTables
• Creating Your Own PivotTable
• Defining the PivotTable Structure
• Filtering a PivotTable
• Clearing a Report Filter
• Switching PivotTable Fields
• Formatting a PivotTable
• 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
• Customizing the Display of Negative Values
• 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 Color Scales
• Working with Icon Sets
• Understanding Sparklines
• Creating Sparklines
• Editing Sparklines
• Understanding Goal Seeking
• Using Goal Seek
• Understanding Quick Analysis
• Quick Formatting
• Quick Charting
• Quick Totals
• Quick Sparklines
• Quick Tables
• 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
• Understanding Chart Formatting
• Selecting Chart Objects
• Using Shape Styles
• Changing Column Color Schemes
• Changing the Color of a Series
• Changing Line Chart Colors
• Using Shape Effects
• Coloring the Chart Background
• Understanding the Format Pane
• Using the Format Pane
• Exploding Pie Slices
• Changing Individual Bar Colors
• Formatting Text