Microsoft Advance Excel


1 Month 1-2 Hours / Day Morning / Evening

Course Description

The purpose of this course is provide Georgia Tech students the necessary skills to use MS Excel in the workplace as an analysis and presentation tool, above and beyond the average skill level of most users, thus making the student a more attractive candidate in the job market.

1.Setup and printing issues

  • Worksheet margins
  • Worksheet orientation
  • Worksheet page size
  • Header and footers
  • Header and footers fields
  • Scaling your worksheet to fit a page(s)
  • Visually checking your calculations
  • Displaying gridlines when printing
  • Printing titles on every page when printing
  • Printing the excel row and columns headings
  • Spell checking
  • Previewing a worksheet
  • Viewing workbook side by side
  • Zooming the view
  • Printing options
  • Setting the number of copies to print
  • Selecting a printer
  • Selecting individual worksheet or the entire workbook
  • Selecting which page to print
  • Single or double sided printing
  • Collation options
  • Page orientation
  • Paper size
  • Margins
  • Scaling
  • Printing

2.Functions and formulas

  • Getting help with functions
  • Nested functions
  • Consolidating data using A 3D reference sum function

3.Time And date Functions

  • Inserting the current date and time
  • Today
  • Now
  • Day
  • Month
  • Year

4.Mathematical functions

  • Round
  • Rounddown
  • Roundup

5.Logical functions

  • IF
  • AND
  • OR

6.Mathematical functions

  • SUMIF

7.Statistical functions

  • COUNT
  • COUNTA
  • COUNTIF
  • COUNTBLANK
  • rank

8.text functions

  • left
  • Right
  • Mid
  • Trim
  • Concatenate

9.Financial functions

  • FV
  • PV
  • NPV
  • RATE
  • PMT

10.Lookup functions

  • VLOOKUP
  • HLOOKUP

11.Database Functions

  • DSUM
  • DMIN
  • DMAX
  • DCOUNT
  • DAVERAGE

12.Named ranges

  • Naming cell ranges
  • Removing a named range
  • Named cell ranges and functions

13.Cell formatting

  • Applying styles to a range
  • Conditional formatting
  • Custom number format

14.Manipulating worksheets

  •  Copying or moving worksheet between workbooks
  • Splitting a window
  • Hiding rows
  • Hiding columns
  • Hiding worksheets
  • Un-hiding rows
  • Un- hiding columns
  • Un-hiding worksheets

15.Templates

  • Using templates
  • Creating excel templates
  • Displaying hidden templates folders on a Window 7 computer
  • Editing excel templates in a window 7 computer
  • Displaying hidden templates folders on a window vista computer
  • Displaying hidden templates folders on a window XP computer
  • Editing excel templates on a window XP computer

16.Paste special options

  • Using paste special to Add, subtract, multiply and divide
  • Using paste special values
  • Using paste special transpose options

17.Pivot tables

  • Creating and using a pivot table
  • Filtering and sorting data within a pivot table
  • Automatically grouping data in a pivot table and renaming groups
  • Manually grouping data in a pivot table and renaming groups

18.Input tables

  • One-input data table
  • Two-input data table

19.Charts

  • Creating a combined line and column chart
  • Adding a secondary axis to chart
  • Changing the chart type for a particular data series
  • Adding a data series to a chart
  • Removing a data series from a chart
  • Repositioning chart title
  • Repositioning the chart legend
  • Moving and formatting chart data labels
  • Modifying chart axis scales
  • Formatting an axis to display using comma
  • Inserting images into chart column
  • Inserting images to chart bars
  • Formatting the chart plot area using a picture
  • Formatting the chart area using a picture

20.Hyperlinks

  • Inserting a hyperlink
  • Editing a hyperlink
  • Removing a hyperlink

21.Linking and embedding

  • What is embedding and linking
  • Linking data within a work sheet
  • Linking cells between worksheets within a work book
  • Linking data between workbook
  • Linking data from excel to word document
  • Linking an excel chart to a word document
  • Updating, locking and breaking links

22.Important text files

  • What is delimited text file
  • Importing a delimited text file

23.Sorting and filtering data

  • Sorting data by multiple column at the same time
  • Applying a pre-installed custom sort
  • Creating a customized list and performing a custom sort
  • Removing a customized list
  • Using  AUTOFILTER
  • Using AUTOFILTER to perform multiple queries
  • Top 10 AUTOFILTER
  • Removing all AUTOFILTER from a worksheet
  • Advance filter criteria
  • SUB-Totaling
  • Removing sub-totals
  • Expanding and collapsing outline detail levels

24.Tracking and reviewing changes

  • Enabling or disabling the ‘track changes’ feature
  • Sharing, comparing and merging worksheets

25.Scenarios

  • Scenario manager
  • Scenarios summary report

26.Validating

  • Data validation – Whole number
  • Data validation – decimal number
  • Data validation – list
  • Data validation – Date
  • Data validation – time
  • Data validation – text length
  • Customizing a validation input message and error alert
  • Removing data validation

27.Auditing

  • Tracing precedent cells
  • Tracing dependent cells
  • Identifying cells with missing dependents
  • Showing all formulas in a worksheet, rather than the resulting values
  • Inserting and viewing comments
  • Editing and deleting comments
  • Showing and hiding comments

28.Macros

  •  Macro to change the page set up
  • Macro to apply a custom number format
  • Macro to format a cell range
  • Macro to insert field into header or footer
  • Assigning a macro to a button on the quick access toolbar
  • Deleting macros

29.Passwords and security issues

  • Adding ‘open’ password protection to a workbook
  • Adding ‘modify’ password protection to a workbook
  • Removing an ‘Open’ password from a workbook
  • Removing an ‘modify’ password from a workbook
  • Password protecting cells and work sheets
  • Hiding formulas
  • Un-hiding formulas

30.Final Project

Top