Functions and Formulas III

• 3d formulas

• creating 3d formulas

• creating 3d range references

• creating 3d named references

Reference functions

• match

• index

• vlookup

• offset

Summarizing Data Using Lists

• Creating data lists

• Creating a cross table table

• Creating a subtotal formula

• Filtering a data list

• grouping and outlining

Scenarios

• the scenario dropdown tool

• adding scenarios

• what if analysis

• creating a scenario report

• using an error mask

• create a bar graph in cells

• using goal seek

• use solver

Data

• sorting data

• the data form

• subtotal

• auto filter

• advanced filters

• Create a data list
• Filter data in lists
• Add totals to rows and columns
• Summarize table data using subtotals and totals

• Showing error bars on a chart

• Sharing data in worksheets
• Link cells between worksheets
• Using auditing tools
• Setting revision tracking
• Merging workbooks
• Tracking changes

Import and Export

• Import data from the web
• Importing data
• Exporting data
• Save worksheet as a web page
• Publish a workbook to the web

Customization

• Customizing toolbars
• Customizing menus

• Create a workbook from a template
• Creating a workbook templates

• Creating an input form fro data input
• Edit records in your database
  • Add
  • Delete
  • Edit
• Find records in your database
• Freezing column titles
• Filtering data to display a subset
• Setting up data validations criteria

• Exploring pivot tables
• Different pivot table views
• Creating a pivot table
• Pivoting a pivot table
• Adding fields from a pivot table
• Removing field form a pivot table
• auto formatting pivot tables
• using the pivot table toolbar

• Filtering data in pivot tables
• Filtering pivot tables using page fields
• Formatting pivot tables
• Creating pivot charts
• Adding subtotals to pivot tables

• What is a macro
• How to use macros
• Recording a basic macro
• Running a macro
• Viewing a macro
• Editing a macro
• Deleting a macro
• Absolute and relative recording modes
• Assign macros to shortcut skills
• Creating a macro button
• Making macros available to all workbooks