logo
 
Educational Publishers
 
 
 

 

Excel 2010 Basics
Author:
William B. Bergquist
Rowe List No.:
450
ISBN:
0-88294-893-8
Pages:
252
Completion Time:
50-60 hours
Copyright:
2010

 


Excel Basics 2010 is designed to teach students all of the elements necessary to obtain an entry-level position as an Office Assistant, Information Processing Assistant, or Data and Financial Assistant in a business office. 

Beginning with basic worksheet label and value entry, editing, and formatting the instruction continues through using multiple worksheets, calculating with formulas and functions, chart building, database, macros, scenarios, filters, pivot tables, and statistical analysis. The book is peppered with helpful worksheet, icon, and dialog box illustrations.

Excel Basics 2010 includes over 150 hands-on applications. Each of the Exercises have complete and uniform step-by-step instructions, with specific keystrokes, to complete the lesson. All applications are given in increasingly difficult order.  At the end of the chapter, students are asked to demonstrate their knowledge in a series of Projects.  

An Instructor's Resource CD is free upon request with a class-sized order. Unit tests are available in the Instructor's Resource CD.

 


CONTENTS

CHAPTER 1: INTRODUCTION TO EXCEL
INTRODUCTION
EXCEL BASICS
LAUNCH EXCEL
THE EXCEL APPLICATION WINDOW
Workbooks and Worksheets
Title bar
Menu bar
Standard Toolbar
Formatting Toolbar
Formula Bar
Worksheet Frame
Task Pane
Worksheet Area and Cells
Status Bar
Scroll Bars
Dialog Boxes
EXERCISE 1-1: LAUNCH EXCEL AND EXPLORE THE APPLICATION WINDOW
WORKSHEET NAVIGATION AND DATA ENTRY
Enter Data into a Worksheet
Edit Cell Data
Insert Symbols and Special Characters
Navigate the Worksheet
Undo and Redo an Action
WORK WITH WORKBOOKS
Save a Workbook
Print a Worksheet
Close a Workbook
Create a New Workbook
EXERCISE 1-2: CREATE, SAVE, NAVIGATE, PRINT, AND CLOSE A WORKBOOK
Open a Workbook
Zoom
EXERCISE 1-3: OPEN A WORKBOOK AND CHANGE THE ZOOM SETTING
Smart Tags
EXERCISE 1-4: USE SMART TAGS
HELP
Office Assistant
Table of Contents
EXERCISE 1-5: USING HELP FEATURES
Research
EXERCISE 1-6: USE THE RESEARCH TOOL
CHAPTER SUMMARY
CHAPTER 1 PROJECTS
PROJECT 1-1: IDENTIFY WORKSHEET COMPONENTS
PROJECT 1-2: BUILD, SAVE, AND PRINT A WORKSHEET
PROJECT 1-3: OPEN, EDIT, SAVE, AND PRINT A WORKBOOK
PROJECT 1-4: USE HELP
PROJECT 1-5: BUILD, SAVE, AND PRINT A WORKSHEET
PROJECT 1-6: USE SMART TAGS
PROJECT 1-7: BUILD, SAVE, AND PRINT A WORKSHEET FROM RAW DATA
PROJECT 1-8: USE THE RESEARCH FEATURE

CHAPTER 2: FORMULAS AND FUNCTIONS
INTRODUCTION
RANGES
Name Box
EXERCISE 2-1: SELECT RANGES
FORMAT DATA
Fonts, Font Sizes, and Font Styles
Methods of Formatting Data
NUMBER FORMATS
General Format
Number Format
Currency Style
Comma Style
Percent Style
Fraction
Decimal Places
Date and Time
Rounding Values
Automatic Formatting
Data Alignment
Merge and Center
Split Cells
EXERCISE 2-2: FORMAT DATA USING THE FORMATTING TOOLBAR
Rotate Data
EXERCISE 2-3: FORMAT DATA USING THE FORMAT CELLS DIALOG BOX
Format Painter
Clear Cell Contents and Formatting
EXERCISE 2-4: USE THE FORMAT PAINTER
WORKSHEET FORMATTING
Column Width
Row Height
EXERCISE 2-5: FORMAT COLUMNS AND ROWS
Inserting and Deleting Columns and Rows
Hiding and Unhiding Columns and Rows
EXERCISE 2-6: INSERT, DELETE, HIDE, AND UNHIDE COLUMNS AND ROWS
Insert and Delete Cells
EXERCISE 2-7: INSERT AND DELETE CELLS
Change Cell Background Color and Cell Borders
EXERCISE 2-8: BACKGROUND COLOR AND BORDERS – PART 1
EXERCISE 2-9: BACKGROUND COLOR AND BORDERS – PART 2
DATA ENTRY SHORTCUTS
AutoFill
EXERCISE 2-15: USE AUTOFILL
AutoComplete
Pick From List
EXERCISE 2-16: USE AUTOCOMPLETE AND PICK FROM LIST
AutoCorrect
Find and Replace
EXERCISE 2-17: USE FIND AND REPLACE
Find and Replace Formatting
EXERCISE 2-18: USE FIND AND REPLACE TO REPLACE FORMATTING
CHAPTER SUMMARY
CHAPTER 2 PROJECTS
PROJECT 2-1: BUILD AND FORMAT A WORKSHEET
PROJECT 2-2: BUILD AND FORMAT A WORKSHEET
PROJECT 2-3: SHADE AND APPLY BORDERS TO A RANGE
PROJECT 2-4: INSERT, DELETE, HIDE, AND UNHIDE COLUMNS AND ROWS
PROJECT 2-5: CREATE A DATA ENTRY FORM
PROJECT 2-6: USE FIND AND REPLACE

CHAPTER 3: ADVANCED FUNCTIONS AND TEMPLATES
INTRODUCTION
FORMULAS
Formulas
Copying Formulas
Insert and Paste Options Buttons
EXERCISE 3-1: CREATE AND ENTER FORMULAS
EXERCISE 3-2: ENTER AND COPY FORMULAS
Order of Operations
Display Cell Formulas
EXERCISE 3-3: ORDER OF OPERATIONS AND DISPLAY CELL FORMULAS
Relative Cell References
Absolute Cell References
EXERCISE 3-4: RELATIVE AND ABSOLUTE CELL REFERENCES
FUNCTIONS
Function Structure
SUM
AutoSum
Exercise 3-5: Use the AutoSum Feature
AVERAGE
MAX
MIN
EXERCISE 3-6: USE THE SUM, AVERAGE, MAX, AND MIN FUNCTIONS
TODAY
NOW
COUNT
COUNTA
COUNTBLANK
Insert Function Dialog Box
Enter an Argument by Dragging
EXERCISE 3-7: TODAY, NOW, COUNT, AND COUNTA FUNCTIONS
CHAPTER SUMMARY
CHAPTER 3 PROJECTS
PROJECT 3-1: BUILD A WORKSHEET WITH FORMULAS AND FUNCTIONS
PROJECT 3-2: BUILD A WORKSHEET WITH FORMULAS AND FUNCTIONS
PROJECT 3-3: INSERT, DELETE, AND HIDE COLUMNS AND ROWS
PROJECT 3-4: USE FORMULAS TO DETERMINE BREAK-EVEN ANALYSIS
PROJECT 3-5: CREATE AND PRINT FORMULAS
PROJECT 3-6: USE THE AVERAGE, MAX, AND MIN FUNCTIONS
PROJECT 3-7: USE THE COUNT, COUNTA, AND COUNTBLANK FUNCTIONS

CHAPTER 4: ADVANCED FUNCTIONS AND TEMPLATES
INTRODUCTION
ADVANCED FUNCTIONS
PMT
EXERCISE 4-1: USE THE PMT FUNCTION
FV
EXERCISE 4-2: USE THE FV FUNCTION
IF
EXERCISE 4-3: USE THE IF FUNCTION – PART 1
EXERCISE 4-4: USE THE IF FUNCTION – PART 2
COUNTIF
EXERCISE 4-5: USE THE COUNTIF FUNCTION
VLOOKUP
EXERCISE 4-6: USE THE VLOOKUP FUNCTION TO FIND AN APPROXIMATE MATCH
EXERCISE 4-7: USE THE VLOOKUP FUNCTION TO FIND AN EXACT MATCH
Nested Functions
EXERCISE 4-8: CREATE A NESTED FUNCTION
TEMPLATES
Spreadsheet Solutions
EXERCISE 4-9: USE SPREADSHEET SOLUTIONS
Custom Templates
EXERCISE 4-10: CREATE AND SAVE A TEMPLATE
CHAPTER SUMMARY
CHAPTER 4 PROJECTS
PROJECT 4-1: USE THE PMT FUNCTION
PROJECT 4-2: USE THE IF FUNCTION
PROJECT 4-3: USE THE VLOOKUP FUNCTION
PROJECT 4-4: USE THE COUNTIF FUNCTION
PROJECT 4-5: CREATE A TEMPLATE
PROJECT 4-6: USE SPREADSHEET SOLUTIONS
PROJECT 4-7: CREATE A NESTED IF STATEMENT
PROJECT 4-8: USE THE FV FUNCTION

CHAPTER 5: ADVANCED PRINTING AND FORMATTING
INTRODUCTION
ADVANCED PRINTING
Set and Clear a Print Area
Print a Selection
Print Preview
EXERCISE 5-1: PRINT PREVIEW, SET A PRINT AREA, AND PRINT A SELECTION
Insert and Remove Page Breaks
Print Orientation and Scaling
EXERCISE 5-2: INSERT/REMOVE PAGE BREAKS AND SET PRINT ORIENTATION/SCALING
Margins and Worksheet Centering
Headers and Footers 
EXERCISE 5-3: MARGINS, WORKSHEET CENTERING, AND HEADERS/FOOTERS
Print Titles
Preview and Print Noncontiguous Ranges
EXERCISE 5-4: SET PRINT TITLES AND PRINT NONCONTIGUOUS RANGES
ADVANCED FORMATTING
Cut
Copy
Paste
EXERCISE 5-5: CUT, COPY, AND PASTE DATA
Paste Special
EXERCISE 5-6: USE PASTE SPECIAL
Conditional Formatting
EXERCISE 5-7: CONDITIONAL FORMATTING
Data Validation
EXERCISE 5-8: DATA VALIDATION
AutoFormat
EXERCISE 5-9: APPLY AUTOFORMATS
Styles
EXERCISE 5-10: CREATE, APPLY, MODIFY, AND DELETE A STYLE
Cell Comments
EXERCISE 5-11: CREATE, EDIT, FORMAT, AND REMOVE CELL COMMENTS
CHAPTER SUMMARY
CHAPTER 5 PROJECTS
PROJECT 5-1: ADD A HEADER AND FOOTER, AND PRINT A SELECTION
PROJECT 5-2: SET PRINT OPTIONS, AND DATA VALIDATION
PROJECT 5-3: USE STYLES, HEADERS AND FOOTERS, COPY AND PASTE DATA
PROJECT 5-4: APPLY CONDITIONAL FORMATTING, VALIDATION RULES, AND PRINT NONCONTIGUOUS RANGES
PROJECT 5-5: SET PRINT TITLES AND HEADERS AND FOOTERS

CHAPTER 6: LARGE WORKSHEETS, MULTIPLE WORKSHEETS, AND WORKBOOKS
INTRODUCTION
LARGE WORKSHEETS
Navigating Large Worksheets
Sort Data
Freeze and Unfreeze Panes
Split Workbook Window
EXERCISE 6-1: SORT, FREEZE/UNFREEZE PANES, AND SPLIT WINDOW
AutoFilter
EXERCISE 6-2: USE AUTOFILTER
Advanced Filter
EXERCISE 6-3: USE ADVANCED FILTER
Subtotals
EXERCISE 6-4: USE SUBTOTALS
MULTIPLE WORKSHEETS
Navigate Worksheets
Format Worksheet Tabs
Rename Worksheets
Insert Worksheets
Rearrange Worksheets
Copy Worksheets
Delete Worksheets
EXERCISE 6-5: USE MULTIPLE WORKSHEETS AND FORMAT WORKSHEET TABS
Group Worksheets
EXERCISE 6-6: RENAME AND GROUP WORKSHEETS
3-D Reference Formulas
EXERCISE 6-7: USE A 3-D REFERENCE FORMULA
Print Workbook
EXERCISE 6-8: PRINT WORKBOOK
CHAPTER SUMMARY
CHAPTER 6 PROJECTS
PROJECT 6-1: SORTING, FILTERING, AND SUBTOTALS
PROJECT 6-2: NAME, INSERT, DELETE, AND FORMAT WORKSHEETS AND TABS
PROJECT 6-3: LINK WORKSHEETS WITH 3-D REFERENCES
PROJECT 6-4: USE ADVANCED FILTER
PROJECT 6-5: CREATE A YEARLY REVENUE TEMPLATE

CHAPTER 7: CHARTS
INTRODUCTION
CHART BASICS
Chart Title
Chart Area
Walls and Plot
Legend
Axes
Embedded Charts and Chart Sheets
Instant Chart
Chart Wizard
Chart Types
Column Charts
Bar Charts
Line Charts
Pie Charts
Chart Options
EXERCISE 7-1: CREATE AN INSTANT CHART BASED ON SELECTED DATA
EXERCISE 7-2: CREATE A CHART USING THE CHART WIZARD
Resize, Move, and Copy a Chart
EXERCISE 7-3: RESIZE, MOVE, COPY, AND DELETE A CHART
Noncontiguous Ranges and Charts
EXERCISE 7-4: USING NONCONTIGUOUS RANGES AND CHART OPTIONS
Chart Formatting
EXERCISE 7-5: MODIFY AND FORMAT CHART ELEMENTS
Print Charts
EXERCISE 7-6: PRINT CHARTS
CHAPTER SUMMARY
CHAPTER 7 PROJECTS
PROJECT 7-1: FILL-IN-THE-BLANKS
PROJECT 7-2: CREATE AND PRINT A CHART
PROJECT 7-3: CREATE, MODIFY, PRINT, AND DELETE A CHART
PROJECT 7-4: CREATE, MODIFY, COPY, AND PRINT A CHART

CHAPTER 8: GRAPHICS
INTRODUCTION
GRAPHICS
Clip Art
Insert Clip Art Image
Search for Clip Art Image
Resize Clip Art Image
Move and Copy an Image
EXERCISE 8-1: INSERT, SIZE, MOVE, AND COPY AN IMAGE
Format Clip Art Image
EXERCISE 8-2: FORMAT MULTIPLE CLIP ART IMAGES
Graphic Files
EXERCISE 8-3: INSERT GRAPHIC FILES INTO A WORKSHEET
WordArt
Text Boxes
EXERCISE 8-4: INSERT WORDART AND TEXT BOXES
AutoShapes
EXERCISE 8-5: INSERT AND FORMAT AUTOSHAPES
Rotate and Flip AutoShapes
EXERCISE 8-6: FORMAT, ROTATE, AND FLIP AUTOSHAPES
DIAGRAMS AND ORGANIZATION CHARTS
Insert Diagram or Organization Chart
Resize and Move a Diagram or Organization Chart
Modify a Diagram or Organization Chart
EXERCISE 8-7: CREATE A DIAGRAM
EXERCISE 8-8: CREATE AN ORGANIZATION CHART
CHAPTER SUMMARY
CHAPTER 8 PROJECTS
PROJECT 8-1: INSERT AND FORMAT CLIP ART
PROJECT 8-2: GRAPHIC OBJECTS AND PICTURES FROM FILES
PROJECT 8-3: FORMAT AUTOSHAPES
PROJECT 8-4: INSERT DRAWING OBJECTS, CLIP ART, AND WORD ART
PROJECT 8-5: Create and Modify Diagrams
PROJECT 8-6: Insert an Organization Chart

CHAPTER 9: DATA ANALYSIS
INTRODUCTION
PIVOT TABLES
Prerequisites for Creating a Pivot Table
Create a Pivot Table Report
EXERCISE 9-1: CREATE A PIVOT TABLE REPORT
Modify a Pivot Table Report
EXERCISE 9-2: MODIFY AND FILTER A PIVOT TABLE REPORT
AutoFormat a Pivot Table Report
EXERCISE 9-3: APPLY AUTOFORMATS TO A PIVOT TABLE REPORT
Refresh a Pivot Table Report
EXERCISE 9-4: REFRESH AND PRINT A PIVOT TABLE REPORT
Pivot Table Options
EXERCISE 9-5: SET PIVOT TABLE OPTIONS
SPELLING
Spelling
Watch Window
EXERCISE 9-6: USE THE WATCH WINDOW, SPELLING CHECK, AND FIX FORMULAS
AUDITING FEATURES
Trace Precedents
Trace Dependents
Trace Errors
EXERCISE 9-7: TRACE PRECEDENTS AND DEPENDENTS AND SPELL CHECK
CHAPTER SUMMARY
CHAPTER 9 PROJECTS
PROJECT 9-1: FILL-IN-THE-BLANKS
PROJECT 9-2: CREATE AND PRINT A PIVOT TABLE REPORT
PROJECT 9-3: MODIFY A PIVOT TABLE REPORT
PROJECT 9-4 USE THE WATCH WINDOW, ANALYZE, AND REPAIR A WORKSHEET

CHAPTER 10: ADVANCED TOPICS
INTRODUCTION
TOOLBAR AND WORKSHEET CUSTOMIZATION
Customize Toolbars
EXERCISE 10-1: CUSTOMIZE TOOLBARS
Create a Custom Toolbar
EXERCISE 10-2: CREATE A CUSTOM TOOLBAR
Default Settings
EXERCISE 10-3: CHANGE DEFAULT SETTINGS
PROTECTION
Password Protect a Workbook
EXERCISE 10-4: PASSWORD PROTECT A WORKBOOK
Read Only Files
EXERCISE 10-5: SET A WORKBOOK AS A READ-ONLY FILE
Protect a Worksheet
EXERCISE 10-6: UNLOCK CELLS AND PROTECT A WORKSHEET
WORKBOOK MANAGEMENT
Rename a Workbook
EXERCISE 10-7: RENAME A WORKBOOK
File System
Create a Folder
Back Up a Workbook
Move a Workbook
Save a Workbook to a Folder
EXERCISE 10-8: BACK UP AND MOVE WORKBOOKS, CREATE A FOLDER, AND SAVE A WORKBOOK TO A FOLDER
Save a Workbook in a Different File Format
EXERCISE 10-9: SAVE A WORKBOOK IN A DIFFERENT FILE FORMAT
Hide/Unhide Worksheets and Workbooks
EXERCISE 10-10: HIDE/UNHIDE WORKSHEETS AND WORKBOOKS
THE WORLD WIDE WEB
Save a Workbook as a Web Page
Save a Worksheet as a Web Page
Save Selection as Web Page (With Interactivity)
Web Page Preview
EXERCISE 10-11: SAVE A WORKBOOK, WORKSHEET, AND SELECTION AS A WEB PAGE
Insert a Hyperlink
Edit a Hyperlink
Remove a Hyperlink
EXERCISE 10-12: INSERT, EDIT, AND REMOVE HYPERLINKS
Send a Worksheet as E-Mail
EXERCISE 10-13: SEND A WORKSHEET AS AN E-MAIL MESSAGE
CHAPTER SUMMARY
CHAPTER 10 PROJECTS
PROJECT 10-1: CREATE A FOLDER, PROTECT A WORKSHEET, AND HIDE A WORKSHEET
PROJECT 10-2: CREATE A FOLDER AND MOVE WORKBOOKS
PROJECT 10-3: INSERT HYPERLINKS AND SAVE IN A DIFFERENT FILE FORMAT
PROJECT 10-4: PROTECT A WORKSHEET AND SAVE A SELECTION OF CELLS AS A WEB PAGE WITH INTERACTIVITY
PROJECT 10-5: INSERT, EDIT, AND REMOVE HYPERLINKS
PROJECT 10-6: SAVE A WORKBOOK AND WORKSHEET AS A WEB PAGE

INDEX 



 

 
     

© 2013 The H. M. Rowe Company
Any comments? Please contact us at service@hmrowe.com.