VERBAL VIEW OF EXCEL
by
Peter Duran
Copyright © 2011 American Printing House for the Blind, Inc., Louisville, KY
Table of Contents
PREFACE 7
Excel Uses 7
Excel History 8
Excel in Education and in the Workplace 9
Excel TOOLS AND SKILLS 9
TUTORIAL OVERVIEW 11
CHAPTER 1: EXCEL OVERVIEW 13
Worksheet tradition 13
Worksheet LAYOUT 13
Worksheet Navigation 14
Arrow Navigation Keys 14
Other Navigation Keys 15
About Columns and Rows 15
Number of Columns and Rows 16
Formula Bar 16
Column Heading Bar 17
Row HEADING BAR 17
Cell references 18
Worksheet Tabs 18
Worksheet Glossary 19
CHAPTER 2: EXCEL Window LAYOUT 21
Program Window Layout 21
Office Button 21
Ribbon Bar 22
The Two Bars & Four Buttons 22
Work Area 23
Customizable Status Bar 23
Program Window Navigation 24
Office Button Overview 24
Left Pane Commands 24
Right Pane Workbooks 25
Ribbon Bar Overview 26
Ribbon Bar Layout 26
Ribbon Bar Navigation 28
Command Group Overview 29
Command Group Layout 29
Command Group Navigation 30
Command Group Arrangement 33
View Command Overview 36
CHAPTER 3: CELL AND WORKSHEET NAVIGATION 38
Cell Navigation Keys 38
Region Navigation Keys 40
End Navigation Keys 41
Worksheet and Workbook NAVIGATION Keys 42
CHAPTER 4: EDIT CELL DATA 44
The Erase keys: The Del Key and the BackSpace Key 44
The Edit Keys: The F2 Key and the Erase Keys 44
The undo Keys 45
The Edit Line Keys 45
CHAPTER 5: Adjust cell size 46
Determine Cell Size 46
Specify Cell Size 46
Adjust Cell Size 47
Adjust Row Height 47
Manually Adjust Row Height 48
automatically ADJUST Row Height 48
Adjust Every ROW 49
Adjust Column Width 49
Manually Adjust Column Width 49
automatically ADJUST Column WIDTH 50
Adjust Every COLUMN 51
Specify a Standard Column Width 51
CHAPTER 6: SELECT CELL DATA 52
Select a Cell or a Cell Range 52
Shift Key + Navigation Keys 52
Extend key + Navigation Keys 52
Go To Dialog Box 53
Select an Entire Column or Row 54
Select Most of a Column or Row 54
Select an Entire Worksheet 54
Select Cells with Specified Content 54
Selection Types and Their Options 55
Read CELLS WITH Comments 57
Format CELLS with Titles 57
Clear CELLS with Data 58
SELECTION TYPES AND THEIR Shortcut Keys 59
CHAPTER 7: INSERT, ERASE, OR COPY CELL DATA 61
Work with Single Cells 61
Erase Multiple Cells 61
Copy Multiple Cells 62
Move All of the Data within the Active Cell 62
MOVE Part OF THE DATA within the Active Cell 63
Merge Data within ANOTHER CELL 63
Move Data Range 64
CHAPTER 8: PERFORM ARITHMETIC WITH CELL DATA 66
Arithmetic Operations 66
Perform Single Operations 67
Perform Multiple Operations 68
Perform calculations with Formulas 69
Perform calculations with Cell ranges 72
About Cell ranges 72
Label Cell ranges 73
Use Named Cell Ranges 74
Manage Cell ranges 74
Perform Calculations with Functions 75
Use the other sum Functions 77
AutoSum function 77
SumIf Function 78
AutoCalculate Sum 80
Find and Correct Errors within formulas 80
Show Formulas within Cells 80
Correct Errors One by One 81
Run the Error Checker 81
Formula Glossary 82
CHAPTER 9: PERFORM ARITHMETIC WITH CELL FORMULAS 83
Relative Cell References 83
Absolute Cell References 84
Mixed Cell References 85
References on Different Worksheets 86
CHAPTER 10: ENTER CELL DATA AUTOMATICALLY 87
AutoComplete Column Items 87
AutoFill Cell Items 89
Repeat an Item across a Row 89
Repeat an Item down a Column 89
AutoFill Cell Commands 90
AutoFill Adjacent Cells with the Same Item 90
AutoFill Adjacent Cells with Sequential Items 90
AutoFill Tasks with Examples 91
AutoFill with Personal Lists 96
Create Custom AutoFill Lists 97
Edit or delete Custom AutoFill Lists 97
CHAPTER 11: FORMAT CELLS 99
Number Format Overview 99
Number Format Category 99
Number Format Commands 102
General Format 103
Number Format 103
Currency Format 104
Accounting Format 104
Date and Time Formats 104
Percentage Format 106
Fraction Format 107
Scientific Format 107
Text Format 108
Special Format 108
Number Format Shortcut Keys 108
Format Shortcut Keys 108
Related Shortcut Keys 109
PREFACE
Welcome to Excel, your math whiz. Excel can handle your simple math chores—add up the items on your grocery bill, calculate your mortgage interest or loan payment, schedule your time or budget your money, and much, much more. Excel does basic math—add, subtract, multiply, and divide numbers as well as deal with many of those formulas that gave you fits in algebra class.
Excel does for numeracy what Word does for literacy: it handle the routine, tedious chores for you. For example, just as Word spell checks and sorts a list of words, Excel sums and computes the average of a typed list of numbers.
You can lay out a table of data in both Excel and Word. However, there's a big difference between these two programs. Excel performs calculations on a table of data and displays the results in various forms, such as charts or graphs. Word can't do most of that stuff. Nevertheless, both programs work together. You can create a table, chart, or graph in Excel and move it into a Word document as part of a financial report for the boss; as part of a term paper that summarizes data from scientific experiments; or as part of a dissertation that requires tables of statistics to impress professors.
Excel Uses
There are many reasons to learn and use Excel. You may use Excel as a simple calculator which also keeps track of any transactions you have completed. For instance, you can list all of your personal expenses for January in Column A, list all of your personal expenses for February in Column B, and so on. You can then tell Excel to add up the columns of numbers for you so you can review and compare your monthly expenses throughout the year.
Unlike an ordinary handheld calculator or the Calculator program in Windows, Excel saves all your work so you can review it at any later time. You are able to make changes (correct entries or add more entries) and have Excel recalculate the values for you. You may use Excel as a graphing calculator: just type all the data, and then tell Excel to display those data as a chart or graph for you.
Access note: I find Excel much easier to use than the dedicated graphing calculators or programs developed for blind and visually-impaired users. I only need to type up the data and Excel does all the rest. It performs the calculations and creates the charts or graphs I need. This way I use the same software as my business partners and science colleagues. As an additional benefit, any tables, charts, or graphs I produce in Excel can be dropped into any Word document.
Excel knows about many math topics. In algebra or trigonometry class, you may need to plot the graph of a function such as y = sin x; let Excel compute a few values for you and then plot them as a pretty sine curve. Or, in business math, you may need to figure out the profit of a sale based on the cost of goods and the profit margin; Excel can do that for you.
Excel has many professional uses. For instance, a teacher can list student names across columns, list student grades down columns, and let Excel sum, average and compare student grades. A salesperson can list client names across columns, list client purchases down columns, and let Excel figure out total sales and profit margins.
Excel History
A spreadsheet is a computer program that displays a worksheet used for financial and business calculations—budgets, profit and loss statements, etc. It displays a grid (chessboard layout) of rows and columns that overlap to form little rectangles called cells. Cells may hold labels (column headings, row headings, table titles, chart titles), values (ordinary numbers, dates, currency amounts), or formulas that define how the contents of cells are to be calculated from the contents of other cells. For instance, place the sum formula within a cell; that cell will show the sum of the numbers from other cells that you specify.
A spreadsheet program can recalculate all the values on a worksheet automatically whenever you alter a single cell value or multiple cell values. This means that you can try out various scenarios to a financial calculation. For instance, you may change the interest rate for a mortgage or a car loan from 6.5 percent to 5.25 percent to see how much money you would save if you refinanced your mortgage or car loan. The ability to try out "what if" calculations makes a spreadsheet program invaluable to institutions that deal with money such as banks, groups that lend money, and to customers who want to save money.
The word "spreadsheet" came from "spread" in its sense of a newspaper or magazine item that covers two facing pages that spread across the center fold; the two pages are treated as a single large sheet of paper. The compound word "spreadsheet" came to mean the format used to present bookkeeping ledgers—with columns for categories of expenditures across the top, invoices listed down the left margin, and the amount of each payment in the cell where its row and column overlap.
Microsoft released the first version of its spreadsheet program, Excel for Windows, in November 1987, and has released updated versions of Excel for Windows every two years or so. The current version for Windows is called Excel 12, but it is also called Microsoft Office Excel 2007. Microsoft encouraged the use of the letters XL for the program. That usage has been abandoned, but the Excel icon on Windows still consists of a stylized combination of the letters XL, and the default Excel format is xls (where s stands for spreadsheet).
Excel in Education and in the Workplace
Excel and Word are used in many middle and high school courses so students can gain basic skills with these programs and learn how to manipulate quantitative data and how to write with a word processor. The modern office depends on both programs to carry out daily tasks. These two programs are also essential for college work and many vocational education courses.
Like Word, Excel includes the Office Ribbon Bar. You must understand this user interface before you can navigate and use either program efficiently. Verbal View of Office Ribbon Bar from the American Printing House for the Blind describes in detail the Ribbon Bar and the other user interface elements introduced with Office 2007. Also, Excel and Word share common format styles and themes. Refer to the Verbal View of Word 2007 tutorial from the American Printing House for the Blind for information on styles and themes. This tutorial only covers the basic uses of Excel 2007; consult the other tutorials for related information.
Excel and Word run on computers that have Microsoft Windows as the operating system. Again, you are referred to Verbal View tutorials on Microsoft Windows from the American Printing House for the Blind for any necessary information about Windows.
Excel TOOLS AND SKILLS
Excel displays a table (called a worksheet) many columns wide and many rows high. Many blind readers, no doubt, will have no or limited familiarity with worksheet layout and navigation. Consequently, I begin this tutorial with a gentle introduction to worksheet layout and navigation. Those of you who play chess and are familiar with the chessboard layout, with letters for column headings and numbers for row headings, already know what an Excel worksheet looks like. I recommend that you purchase or borrow a tactile chessboard or ScrabbleÒ board to play with, as there is nothing like "hands-on" experience to help you understand what a worksheet grid looks like and how you move around on it.
There are two ways to use Excel. You can create a worksheet to perform a specified task such as tracking income and expenses to form a budget, tracking student performance to form a grade book, tracking daily tasks to form a calendar of events, and so on. You can use a worksheet (already created) to carry out educational pursuits or to perform job-related tasks. Most readers of this tutorial will rely on worksheets created by others. Therefore, only a modest acquaintance with Excel features is necessary.
You may apply Excel to any problem or task where calculations are required. Throughout this tutorial, I will discuss a topic in Excel and then show you various ways to apply it. Applications of Excel are taken from coursework at the middle school level; that way you aren't required to have highly specialized knowledge to practice your Excel skills.
This tutorial offers only a brief glimpse at the possible uses of Excel. After reading this tutorial, go onto the Internet for more information from Microsoft and from hundreds of online college level handouts and tutorials.
Excel does algebra; that is, it evaluates formulas you pick out of a list or create yourself. I present the basics of algebra so you can understand why Excel uses formulas. Formulas are essential for any meaningful use of Excel!
TUTORIAL OVERVIEW
Excel performs calculations for you, and it has a vast array of options to meet virtually every need. You can create order forms, purchase order forms, timesheets, and countless other forms used in business. You can create math worksheets, charts and graphs, data sheets for experiments, and many other tools used in the classroom. Excel can carry out mundane computations: add fractions, sum columns of numbers, figure out percentages, and the like. Excel can also carry out advanced math tasks: generate arithmetic and geometric progressions, find prime numbers via the Sieve of Eratosthenes, graph the Ulam spiral for the primes, and much more.