- Basics
- Database Management
- Formulas, Functions, and Charts
- Intermediate Formulas, Functions, and Charts
Excel 2010 Basics
Audience
This course is appropriate for individuals who have a basic knowledge of Windows and the 2010 Microsoft Office Suite who want to learn the essentials of Microsoft Excel.
Structure
This course is a combination of lecture and hands-on guided instruction. Course manual and quick reference guide provided.
Course Length
Half Day
Topics
The Ribbon
- Group scrolling and more Buttons
- Two part buttons
- Contextual ribbons
- Accessing dialog boxes
- Minimize/restore the Ribbon
- Keytips
Office Backstage
- Info
- Recent
- New
- Save and send options
- Help
Quick Access Toolbar
Explore the environment
- Column/row headings
- Status Bar
- Zoom
- Views
Spreadsheet Basics
- Excel terms
- Excel specifications
- Common uses of Excel
Manipulate data
- Select data
- Move and copy data
- Use drag and drop
- Spell check
Manage page setup
- Modify orientation, paper size, and margins
- Scale to print on specific number of pages
- Center on the printed page
- Headers and footers
- Define repeated columns and rows on print
Enter and save data
- Data entry basics
- Edit data
- Data entry shortcuts
- Delete data
- Undo mistakes
- Save workbooks
Modify columns and rows
- Insert and delete rows and columns
- Change row height and column width
- Shortcuts
Format cells
- Modify fonts and point size
- Align data
- Format numbers and dates
- Apply borders and fill
- Customize cell formats
Manage worksheets
- Name worksheets
- Insert, copy, and delete worksheets
- Group worksheets
- Color code worksheets
Excel 2010 Database Management
Audience
This course is appropriate for individuals who have a basic knowledge of Excel formulas who want to learn to use Excel to manage databases.
Structure
This course is a combination of lecture and hands-on guided instruction. Course manual and quick reference guide provided.
Course Length
Half Day
Topics
Understand database requirements
- Use database terminology
- Understand Excel requirements
Sort records
- Perform single key sorts
- Perform multiple key sorts
- Perform special sorts
Filter data
- Autofilter
- Multiple and custom filters
- Wildcards
Define a data table
- Apply table styles
- Filter data based on a criteria
- Use wildcards in criteria
- Sort data
- Use summary calculations
Subtotal data
- Apply simple subtotals
- Nest subtotals
- Remove subtotals
Use database functions
- Apply sumif
- Apply averageif
Separate and combine data
- Use a concatenate formula
- Split text to columns
Data slicers
Database setup
- Validate data
- Specify requirements for data entry
- Add data entry notes
- Set error messages
- Specify fields on two or more lines
- Best practices for databases
- Split rows/columns
- Freeze panes
- Hide/unhide columns
Outline a database
- Apply manual outlines
- Collapse and expand outlines
- Clear outlines
- Apply automatic outlines
Work with PivotTables and PivotCharts
- Create the PivotTable
- Create PivotCharts
- Add and move data
- Work with totals
Special tools
- Add cell comments
- Use track changes
- Apply custom views
Import data Use a data form (optional)
- Display the data form command
- View records
- Add records
- Delete records
- Filter records
Excel 2010 Formulas, Functions, and Charts
Audience
This course is appropriate for individuals who have a basic knowledge of Excel who want to perform calculations and display their data in charts.
Structure
This course is a combination of lecture and hands-on guided instruction. Course manual and quick reference guide provided.
Course Length
Half Day
Topics
Formula basics
- Spreadsheets vs calculators
- Importance of cell references
Enter a formula
- Use the keyboard
- Use the mouse
- Use the arrow keys
Audit formulas
- Enter edit mode
- Use formula mode
Work with functions
- Understand function syntax
- Use basic functions (sum, average, min, max, count)
Use Autofill
- Autofill basic data
- Autofill formulas
- Understand Autofill assumptions
Work with absolute references
- Define relative references
- Define absolute references
- Use an absolute references
Create a chart
- Choose the right chart
- Determine the chart placement
- Format chart objects
Apply conditional formats
- Use the conditional format gallery
- Define custom conditional format
Change the screen behavior
- Split the screen
- Freeze columns and rows
Use special tools
- Use goal seek
- Create scenarios
Excel 2010 Intermediate Formulas, Functions, and Charts
Audience
This course is designed for individuals with a basic understanding of Excel formulas, functions and charts, who want to use more advanced tools.
Structure
This course is a combination of lecture and hands-on guided instruction. Course manual and quick reference guide provided.
Course Length
Half Day
Topics
Review formulas and functions
- Steps to create a formula or function
- Apply functions using non-adjacent cell references
- Use an absolute reference
Apply 3d formulas and functions
- Perform math between worksheets
- Use summary functions between workbooks
Link workbooks
- Understand best practices
- Perform simple workbook links
- Redirect a workbook link
Use intermediate functions
- Work with the function palette
- Apply the pmt, if, vlookup functions
Use cell and range names
- Benefits of cell and range names
- Define a cell name
- Use a cell name in a formula
- Use range names in a formula
- Remove cell names
Apply conditional format
- Use the conditional format gallery
- Define custom conditional format
Apply advanced chart techniques
- Apply chart styles
- Modify chart objects
- Save a chart as a template
Sparklines
Protect data
- Understand cell, worksheet, and workbook protection
- Define cell protection options
- Protect the worksheet
- Unprotect the worksheet
Create a custom number format
- Add text
- Add special characters
Excel tricks
- Navigate worksheets quickly
- Cell selection tricks
PDF