What you’ll learn

Excel Module

Understanding Excel's elements

• Workbooks, worksheets, cells, formula bar etc.

Editing techniques

• Cells selection
• Data entry
• To insert a row
• To delete a row
• Undo
• copying a range of cells
• Moving a range of cells
• Using paste special
• Clearing cell contents

Writing formulas

• Constructing formulas
• Using operators (+,-,x, ¸,etc...)
• Using references
• Tips on entering formulas

File operations

• Saving workbooks
• Save as...
• Save
• Closing workbooks
• Deleting workbooks

Printing worksheets

• Using print preview
• Page setup
• Header and footer settings
• Margin settings
• Turning the gridlines on or off
• Turn the row/column heading on or off
• Auto scaling printout
• Setting manual page breaks
• Setting row or column titles
• Setting print area

Useful functions

• SUM( )
• AVERAGE( )
• MAX( )
• MIN( )
• IF()
• Count()

Tips in editing a worksheets

• Paste insert row/column
• Drag and drop
• Autofill
• Fill down
• Fill right

Understanding address notations

• Relative address
• Absolute address

Polishing worksheets

• Adding border
• Adjusting row heights and column widths manually
• Adjusting row heights and column widths automatically
• Setting text alignments ( horizontal and vertical )
• Setting text orientations
• Shading cell background
• Font settings

Number format

• Adding thousand separator
• Adding currency sign number
• Adding decimal points
• Creating a master spreadsheet
• Creating document templates
• Using document templates

Exchange files with other applications

• Understanding various Excel file formats
• Saving workbook for backward compatibility
• Exporting excel workbook for other software

Styles

• Creating styles
• Using styles
• Editing styles
• Merging styles from other workbooks

Managing Records

• Sorting Records
• Filtering records

Drawing

• Inserting shapes
• Drawing lines, rectangles, ovals etc.

Charting

• Inserting various kinds of chart – column, line, bar and pie chart
• Adding and modifying chart’s elements
• Chart title, Legend, Axis, Grids, Data table, Data label
• Polishing charts
• Adding special effects to chart elements
• Filling charts with gradients or pictures

Using Layout tab

• 3D Chart
• Constructing 3D charts
• Formatting 3D charts
• Changing 3D view

Advanced Excel Module

Naming cells and ranges

• Creating and defining names
• Making a name list
• Advanced technique of using names in formulas
• Using Name Manager
• Navigating spreadsheet with names

Database

• The database components
• Using Excel Form feature
• Inputting data
• Deleting data
• Finding records
• Using menu commands to find records

Advanced data sorting and subtotal

• Multi-level sorting
• Restoring data to original order after performing sorting
• Sort by icons
• Sort by colours
• Multi-level subtotal

Using database functions

• DSum()
• DMax()
• DMin()
• DAverage()
• Dcount()

Managing multiple worksheets

• Arrange All
• New Window

Consolidation with several worksheets

• Consolidating and combining several spreadsheets using the operation addition, subtraction
• Synchronizing the consolidated tables with the source data

Data table

• One-Input table
• Two-Input table

Lookup table

• Lookup()
• Vlookup()
• Hlookup()
• Application of exact match and approximate match
• Creating an order form using vlookup function

Document protection

• Files protection
• Protecting cells/documents
• Unprotecting documents

File linking

• Paste link

Filter and advanced filter

• Defining single and multiple criteria
• Combining search criteria
• Deleting criteria
• Extracting records

Building A Pivottable

• Prepare Your Worksheet Data.
• Create a Table for a PivotTable Report.
• Build a PivotTable from an Excel Range.

Manipulating Your Pivottable

• Turn the PivotTable Field List On and Off.
• Customize the PivotTable Field List.
• Remove a PivotTable Field.
• Refresh PivotTable Data.
• Add Multiple Fields to the Row or Column Area.
• Add Multiple Fields to the Data Area.
• Add Multiple Fields to the Page Area.
• Delete a PivotTable.

Conditional format

• Highlighting data using cell colours, font colours
• Highlighting data using icons

Data validation

• Define the data input type
• Define the warning message
• Define the error message
• Circle invalid data
• Creating a pull down box to facilitate the data entry process

What-If Analysis

• Using Scenario Manager
• Defining your own scenario
• Preview the result of scenario
• Editing a scenario
• Using Goal Seek
• Using Goal Seek to solve problems

Inserting a hyperlink to a workbook

• Creating a hyperlink
• Editing a hyperlink
• Building a menu system with hyperlinks

Creating and using Macros