This one-day course is designed to help a delegate develop spreadsheets using more advanced functions and creating more complex formulas. A delegate will learn to use LOOKUP functions to extract data from a table, distribute data in different formats and publish a spreadsheet on the web.
| This course consists of 6 modules |
| Module One |
PivotTable Terms
Create PivotTables
Modify PivotTables
Adding Data Fields to a PivotTable
Grouping Items in a Field
Hiding, Showing & Removing Data Fields
Create Page Fields
Create Separate Pages
Format a PivotTable (Numbers / Tables)
Changing the Data Source Range
Refreshing Data
Showing Underlying Details
Chart a PivotTable
Formatting Pivot Charts |
| Module Two |
Advanced Formula Construction
Using VLOOKUP Function
Using HLOOKUP Function
Using VLOOKUP with an IF Function
The LOOKUP Wizard
Using Logical Functions
IF Function
Nesting If Functions
AND/OR Function
Financial Function — PMT Function
Financial Function — FV Function |
| Module Three |
Create a One-Input Table
Create a Two-Input Table
Goal Seek
Solver
Scenario Manager |
| Module Four |
Automating with Visual Basic (Macros)
Recording a Macro
Running a Macro by Command
Running a Macro by Shortcut Key
Examining the Procedure
Editing VBA Code
Assign a Procedure to a Button
Assign a Macro to a Toolbar
Changing the Button Image
Name the Toolbar Marco Button
Edit the Macro Button |
| Module Five |
MicroSoft Query
Using the Query Wizard
Working with Columns
Filtering Data
Sorting Records
Finishing the Query
Editing & Updating Query Results
Importing Text Data
Exporting Data
Saving a Worksheet as a Web Page
Publish a Worksheet to the Web
Auditing Workbooks & Worksheets
Using Excel’s Auditing Tools
Tracing Arrows
The Auditing Toolbar
Sharing Workbooks |
| Module Six |
Templates
Conditional Formatting
Customizing Microsoft Excel
Creating & Using Styles |
