Microsoft Advance Excel
Course Description
The purpose of this course is to 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.
Course Duration: 1 Months
Classes: 5 Days In a Week
Timing: Morning / Evening
Course Outline
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 an 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
Round down
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
Unhiding 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 worksheet
Linking cells between worksheets within a workbook
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 a delimited text file
Importing a delimited text file
23.Sorting and filtering data
Sorting data by multiple columns at the same time
Applying a pre-installed custom sort
Creating a customized list and performing a custom sort
Removing a customized list
Using auto filter
Using AUTOFILTER to perform multiple queries
Top 10 auto filter
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 a 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 a ‘modify’ password from a workbook
Password protecting cells and worksheets
Hiding formulas
Un-hiding formulas
