VERBAL VIEW OF EXCEL



VERBAL VIEW OF EXCELbyPeter DuranCopyright ? 2011 American Printing House for the Blind, Inc., Louisville, KYTable of Contents TOC \o "1-3" \h \z \u PREFACE PAGEREF _Toc291673926 \h 7Excel Uses PAGEREF _Toc291673927 \h 7Excel History PAGEREF _Toc291673928 \h 8Excel in Education and in the Workplace PAGEREF _Toc291673929 \h 9Excel TOOLS AND SKILLS PAGEREF _Toc291673930 \h 9TUTORIAL OVERVIEW PAGEREF _Toc291673931 \h 11CHAPTER 1: EXCEL OVERVIEW PAGEREF _Toc291673932 \h 13Worksheet tradition PAGEREF _Toc291673933 \h 13Worksheet LAYOUT PAGEREF _Toc291673934 \h 13Worksheet Navigation PAGEREF _Toc291673935 \h 14Arrow Navigation Keys PAGEREF _Toc291673936 \h 14Other Navigation Keys PAGEREF _Toc291673937 \h 15About Columns and Rows PAGEREF _Toc291673938 \h 15Number of Columns and Rows PAGEREF _Toc291673939 \h 16Formula Bar PAGEREF _Toc291673940 \h 16Column Heading Bar PAGEREF _Toc291673941 \h 17Row HEADING BAR PAGEREF _Toc291673942 \h 17Cell references PAGEREF _Toc291673943 \h 18Worksheet Tabs PAGEREF _Toc291673944 \h 18Worksheet Glossary PAGEREF _Toc291673945 \h 19CHAPTER 2: EXCEL Window LAYOUT PAGEREF _Toc291673946 \h 21Program Window Layout PAGEREF _Toc291673947 \h 21Office Button PAGEREF _Toc291673948 \h 21Ribbon Bar PAGEREF _Toc291673949 \h 22The Two Bars & Four Buttons PAGEREF _Toc291673950 \h 22Work Area PAGEREF _Toc291673951 \h 23Customizable Status Bar PAGEREF _Toc291673952 \h 23Program Window Navigation PAGEREF _Toc291673953 \h 24Office Button Overview PAGEREF _Toc291673954 \h 24Left Pane Commands PAGEREF _Toc291673955 \h 24Right Pane Workbooks PAGEREF _Toc291673956 \h 25Ribbon Bar Overview PAGEREF _Toc291673957 \h 26Ribbon Bar Layout PAGEREF _Toc291673958 \h 26Ribbon Bar Navigation PAGEREF _Toc291673959 \h 28Command Group Overview PAGEREF _Toc291673960 \h 29Command Group Layout PAGEREF _Toc291673961 \h 29Command Group Navigation PAGEREF _Toc291673962 \h 30Command Group Arrangement PAGEREF _Toc291673963 \h 33View Command Overview PAGEREF _Toc291673964 \h 36CHAPTER 3: CELL AND WORKSHEET NAVIGATION PAGEREF _Toc291673965 \h 38Cell Navigation Keys PAGEREF _Toc291673966 \h 38Region Navigation Keys PAGEREF _Toc291673967 \h 40End Navigation Keys PAGEREF _Toc291673968 \h 41Worksheet and Workbook NAVIGATION Keys PAGEREF _Toc291673969 \h 42CHAPTER 4: EDIT CELL DATA PAGEREF _Toc291673970 \h 44The Erase keys: The Del Key and the BackSpace Key PAGEREF _Toc291673971 \h 44The Edit Keys: The F2 Key and the Erase Keys PAGEREF _Toc291673972 \h 44The undo Keys PAGEREF _Toc291673973 \h 45The Edit Line Keys PAGEREF _Toc291673974 \h 45CHAPTER 5: Adjust cell size PAGEREF _Toc291673975 \h 46Determine Cell Size PAGEREF _Toc291673976 \h 46Specify Cell Size PAGEREF _Toc291673977 \h 46Adjust Cell Size PAGEREF _Toc291673978 \h 47Adjust Row Height PAGEREF _Toc291673979 \h 47Manually Adjust Row Height PAGEREF _Toc291673980 \h 48automatically ADJUST Row Height PAGEREF _Toc291673981 \h 48Adjust Every ROW PAGEREF _Toc291673982 \h 49Adjust Column Width PAGEREF _Toc291673983 \h 49Manually Adjust Column Width PAGEREF _Toc291673984 \h 49automatically ADJUST Column WIDTH PAGEREF _Toc291673985 \h 50Adjust Every COLUMN PAGEREF _Toc291673986 \h 51Specify a Standard Column Width PAGEREF _Toc291673987 \h 51CHAPTER 6: SELECT CELL DATA PAGEREF _Toc291673988 \h 52Select a Cell or a Cell Range PAGEREF _Toc291673989 \h 52Shift Key + Navigation Keys PAGEREF _Toc291673990 \h 52Extend key + Navigation Keys PAGEREF _Toc291673991 \h 52Go To Dialog Box PAGEREF _Toc291673992 \h 53Select an Entire Column or Row PAGEREF _Toc291673993 \h 54Select Most of a Column or Row PAGEREF _Toc291673994 \h 54Select an Entire Worksheet PAGEREF _Toc291673995 \h 54Select Cells with Specified Content PAGEREF _Toc291673996 \h 54Selection Types and Their Options PAGEREF _Toc291673997 \h 55Read CELLS WITH Comments PAGEREF _Toc291673998 \h 57Format CELLS with Titles PAGEREF _Toc291673999 \h 57Clear CELLS with Data PAGEREF _Toc291674000 \h 58SELECTION TYPES AND THEIR Shortcut Keys PAGEREF _Toc291674001 \h 59CHAPTER 7: INSERT, ERASE, OR COPY CELL DATA PAGEREF _Toc291674002 \h 61Work with Single Cells PAGEREF _Toc291674003 \h 61Erase Multiple Cells PAGEREF _Toc291674004 \h 61Copy Multiple Cells PAGEREF _Toc291674005 \h 62Move All of the Data within the Active Cell PAGEREF _Toc291674006 \h 62MOVE Part OF THE DATA within the Active Cell PAGEREF _Toc291674007 \h 63Merge Data within ANOTHER CELL PAGEREF _Toc291674008 \h 63Move Data Range PAGEREF _Toc291674009 \h 64CHAPTER 8: PERFORM ARITHMETIC WITH CELL DATA PAGEREF _Toc291674010 \h 66Arithmetic Operations PAGEREF _Toc291674011 \h 66Perform Single Operations PAGEREF _Toc291674012 \h 67Perform Multiple Operations PAGEREF _Toc291674013 \h 68Perform calculations with Formulas PAGEREF _Toc291674014 \h 69Perform calculations with Cell ranges PAGEREF _Toc291674015 \h 72About Cell ranges PAGEREF _Toc291674016 \h 72Label Cell ranges PAGEREF _Toc291674017 \h 73Use Named Cell Ranges PAGEREF _Toc291674018 \h 74Manage Cell ranges PAGEREF _Toc291674019 \h 74Perform Calculations with Functions PAGEREF _Toc291674020 \h 75Use the other sum Functions PAGEREF _Toc291674021 \h 77AutoSum function PAGEREF _Toc291674022 \h 77SumIf Function PAGEREF _Toc291674023 \h 78AutoCalculate Sum PAGEREF _Toc291674024 \h 80Find and Correct Errors within formulas PAGEREF _Toc291674025 \h 80Show Formulas within Cells PAGEREF _Toc291674026 \h 80Correct Errors One by One PAGEREF _Toc291674027 \h 81Run the Error Checker PAGEREF _Toc291674028 \h 81Formula Glossary PAGEREF _Toc291674029 \h 82CHAPTER 9: PERFORM ARITHMETIC WITH CELL FORMULAS PAGEREF _Toc291674030 \h 83Relative Cell References PAGEREF _Toc291674031 \h 83Absolute Cell References PAGEREF _Toc291674032 \h 84Mixed Cell References PAGEREF _Toc291674033 \h 85References on Different Worksheets PAGEREF _Toc291674034 \h 86CHAPTER 10: ENTER CELL DATA AUTOMATICALLY PAGEREF _Toc291674035 \h 87AutoComplete Column Items PAGEREF _Toc291674036 \h 87AutoFill Cell Items PAGEREF _Toc291674037 \h 89Repeat an Item across a Row PAGEREF _Toc291674038 \h 89Repeat an Item down a Column PAGEREF _Toc291674039 \h 89AutoFill Cell Commands PAGEREF _Toc291674040 \h 90AutoFill Adjacent Cells with the Same Item PAGEREF _Toc291674041 \h 90AutoFill Adjacent Cells with Sequential Items PAGEREF _Toc291674042 \h 90AutoFill Tasks with Examples PAGEREF _Toc291674043 \h 91AutoFill with Personal Lists PAGEREF _Toc291674044 \h 96Create Custom AutoFill Lists PAGEREF _Toc291674045 \h 97Edit or delete Custom AutoFill Lists PAGEREF _Toc291674046 \h 97CHAPTER 11: FORMAT CELLS PAGEREF _Toc291674047 \h 99Number Format Overview PAGEREF _Toc291674048 \h 99Number Format Category PAGEREF _Toc291674049 \h 99Number Format Commands PAGEREF _Toc291674050 \h 102General Format PAGEREF _Toc291674051 \h 103Number Format PAGEREF _Toc291674052 \h 103Currency Format PAGEREF _Toc291674053 \h 104Accounting Format PAGEREF _Toc291674054 \h 104Date and Time Formats PAGEREF _Toc291674055 \h 104Percentage Format PAGEREF _Toc291674056 \h 106Fraction Format PAGEREF _Toc291674057 \h 107Scientific Format PAGEREF _Toc291674058 \h 107Text Format PAGEREF _Toc291674059 \h 108Special Format PAGEREF _Toc291674060 \h 108Number Format Shortcut Keys PAGEREF _Toc291674061 \h 108Format Shortcut Keys PAGEREF _Toc291674062 \h 108Related Shortcut Keys PAGEREF _Toc291674063 \h 109PREFACEWelcome 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 UsesThere 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 HistoryA 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 WorkplaceExcel 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 SKILLSExcel 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 OVERVIEWExcel 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.This computational power comes at a cost: complexity and lots of detailed planning. Consequently, most users rely on worksheets or workbooks created by Excel experts. There are literally tens of thousands of Excel applications available online, for free and for a charge. Microsoft at its Excel web site offers hundreds of Excel applications for free. More good news: There are hundreds of web sites that offer free tutorials on Excel, and hundreds of web sites that offer "canned" solutions to specialized problems. Read this tutorial to learn the basics about Excel and how to work with Excel from the keyboard, and then go online and explore. You will most certainly find what you need—additional training materials or ready-to-use worksheets and workbooks. This tutorial presents all the ways to access Excel using the keyboard which aren't covered by online resources.This tutorial includes eleven chapters. Read them in order!Chapter 1 introduces a few important terms and concepts used in Excel. Read and memorize them to facilitate your understanding of this tutorial.Chapter 2 describes the layout and content of the Excel program window. The main parts of the user interface, Office Button, Quick Access Toolbar, Ribbon Bar, and Status Bar are briefly reviewed for the sake of completeness. (Please consult the Verbal View of Office Ribbon Bar tutorial from the American Printing House for the Blind for all the details.)Chapter 3 presents the most commonly-used cell and worksheet navigation keys. There are many convenient ways to move through the cells on a worksheet. All navigation keys move the cell cursor directly onto a cell or from cell to cell. You use navigation keys to move through a worksheet and read its data. Please master all the keys discussed so you can browse worksheets quickly and comfortably.Chapter 4 presents the ways to enter and edit information within cells. Cells are where you get down to business and enter data in a worksheet. You move onto cells and type text into them. This brief chapter presents the two basic ways to edit cell content.Chapter 5 explains the ways to make cells wider or taller so they can hold the desired amount of text. You adjust cell sizes to meet your needs; cells can hold short numbers or long titles.Chapter 6 presents the ways to select text within cells. Think of cells as mini Word documents with three parts: data that you enter, formats that you apply, and comments that you may attach. You must select cells before you can change their contents, formats, or comments. So, you need to know the various ways to select cells. This chapter presents the three common ways to select cells and ranges of cells with the keyboard, and these techniques are used to edit or move cell contents. (Cell formats are discussed in Chapter 11.)Chapter 7 presents the ways to place data within cells, erase data from cells, or copy data to other cells. That is, you learn to edit and move data.Chapter 8 introduces Excel as a fancy calculator program. You can perform calculations on cell data. You can also work with formulas without much effort on your part—Excel does all the work for you.Chapter 9 details how to create and copy formulas and explains why they still work when moved. There are two types of cell references, relative and absolute, and they have different uses when you copy formulas. Relative and absolute cell references are used to specify cell ranges and to tell formulas where to find their data.Chapter 10 presents various ways to automate the entry of cell data. You can copy a formula into selected cells to perform the same calculation. You can tell Excel to fill selected cells with predefined labels (e.g., weekdays or months). You can even create your own list of predefined labels (e.g., student names, part numbers) and have Excel fill selected cells with them. That is, the two Excel features AutoComplete and AutoFill let you enter items without having to actually retype them.Chapter 11 reviews cell formats and how you can apply them to cells. Cell formats help you prevent entry errors and let you specify the visual appearance of cell data.CHAPTER 1: EXCEL OVERVIEWThis chapter introduces a few important terms and concepts used in Excel. Read and memorize them to facilitate your understanding of this tutorial.Excel uses the column and row layout of a traditional paper spreadsheet, but adds powerful tools for data calculation, analysis, and formatting. Excel saves you a great deal of time and effort when you perform different tasks in school and in business. This chapter introduces some of the essential concepts that will help you work with worksheets in Excel.Worksheet traditionIn middle school students often do their lessons in workbooks; these are usually over-sized, soft-covered books that have worksheets. Students open their workbooks and they fill out the assigned worksheets.Bookkeepers often prepare trial balances on worksheets. Trial balances confirm the correctness of the company's books.Students and professionals work in Excel the same way: They launch Excel to open a new workbook named Book 1. A new workbook in Excel starts off with three worksheets labeled Sheet 1, Sheet 2, and Sheet 3 with Sheet 1 on top. You may add more worksheets to a workbook when you need them.You may rename the three worksheets to make the information on them easier to identify. For instance, you might rename the three worksheets in a workbook January Expenses, February Expenses, and March Expenses to track your monthly expenses, and you may add more worksheets as the months pass by.Access note: Your screen reader should announce the name of a workbook when you open it and should also announce the name of a worksheet as you move onto it. I renamed my practice workbook Cats and its top worksheet Dogs for the fun of it.Excel displays a worksheet in the work area of its program window. (The next chapter describes the layout of the Excel program window in detail.)Worksheet LAYOUTLaunch the Excel 2007 program. Part of a worksheet appears onscreen and fills up the work area of the program window. It looks like a giant chessboard. There are letters over the grid (A, B, C, …) that label its columns; there are numbers (1, 2, 3, …) to the left of the grid that label its rows. Excel shows eleven columns and sixteen rows of the worksheet, but the worksheet has many more columns to the right and many more rows below. There are tabs below the worksheet named Sheet1, Sheet2, and so on.In summary, a worksheet in Excel looks like a table with its columns labeled by letters and with its rows labeled by numbers. Column labels, called column headings, are displayed in a horizontal strip located over the table. Row labels, called row headings, are displayed in a vertical strip located to the left of the table. As you move through the table, column headings and row headings change to show you where you are currently on the worksheet. An intuitive description of table layout and the details for a worksheet are described next.No doubt many blind readers of this tutorial have little experience with worksheets. So, here's an intuitive introduction to their layout and nomenclature.Take a sheet of paper and draw seven vertical lines uniformly along the length of the sheet. These vertical lines divide the sheet into eight vertical columns. Now, draw seven horizontal lines uniformly spaced along the width of the sheet. These horizontal lines divide the sheet into eight horizontal rows. The vertical and horizontal lines that you drew are called gridlines.The vertical columns and the horizontal rows overlap to form 64 little rectangles or small boxes called cells. This sheet of paper with its 64 little rectangles looks like a chessboard or a checkerboard or part of a tiled wall.Excel places keyboard focus on a single cell at a time, called the Active Cell. Excel also places a dark rectangular border around the active cell to visually distinguish it from neighboring cells. If you move onto a different cell with navigation keys it becomes the active cell—with keyboard focus and a dark border. (The headings for the column and the row of the active cell are also highlighted.)Label the eight columns left to right with the letters A through H; column letters are placed over their respective columns in an Excel worksheet. Label the eight rows top to bottom with the numbers 1 through 8; row numbers are placed left of their respective rows in an Excel worksheet. Now you have the standard chessboard layout; as in the standard worksheet layout in Excel the upper left cell resides in the northwest corner of the paper sheet.Worksheet NavigationYou are able to move among the cells within a worksheet using the keyboard. Here are the details.Arrow Navigation KeysLaunch the Excel program. An empty worksheet appears in the work area of the Excel program window. The cell in the northwest corner of the worksheet has keyboard focus by default. You can move left or right and up or down from cell to cell with the four Arrow keys; that is, across a row or through a column.When you move onto a cell it gains keyboard focus. The cell with keyboard focus has a dark border to visually distinguish it from neighboring cells.New users usually remain in the northwest corner of a worksheet and enter data (labels, values, formulas) in nearby cells. Experienced users place data and formulas in different parts of a worksheet known as Worksheet Regions. (Just because there are 17 billion cells available, you don't have to place stuff in them or even visit them.)Other Navigation KeysYou don't need to press an Arrow key a thousand times to reach cell A1000 in the leftmost column. Excel, just like Word, has a Go To command. Press the Ctrl + G key, type any cell reference, and tap the Enter key. Excel moves keyboard focus onto that cell.Access note: A mouse user may type a cell reference directly within the Name box at the left end of the Formula Bar to move to that cell.There are also handy shortcut keys that let you move keyboard focus onto specified locations on a worksheet. For instance, Ctrl + Home moves keyboard focus back onto the upper left cell on the worksheet—the cell labeled A1. (Cell and worksheet navigation are discussed in Chapter 3.)About Columns and RowsA worksheet has columns, rows, and cells. That's the grid that appears onscreen when you launch Excel. Columns go from top to bottom and rows go from left to right on the worksheet. A cell is the space where a column and a row overlap.A worksheet is just a rectangular table divided up into as many columns and rows as you like. The little rectangles that make up the worksheet are called cells.A single cell, called the Active Cell, has a black border. You can only enter data (labels, values, formulas) into the active cell. The black border (called the Cell Cursor) that surrounds the active cell serves as a visual cue within a worksheet so a user can readily locate the active cell. Every worksheet within a workbook has its own Active Cell. The default active cell is always the cell in the northwest corner of the top worksheet when you open a workbook.Cells are labeled so you can refer to them. A cell label (called a cell reference) has two parts: the column heading (letter or letter combination) and the row heading (number) are placed side by side.Cell references are important because you use them when you tell Excel to perform calculations. For instance, Excel needs to know where the data are located on a worksheet in order to add up a grocery Row ReferencesA1 labels the cell formed by the overlap of Column A and row 1; B1 labels the cell formed by the overlap of Column B and row 1; C1 labels the cell formed by the overlap of Column C and row 1; and so on. So, A1, B1…K1… are the labels for the cells within the top row of the worksheet.Left Column ReferencesA1 labels the cell formed by the overlap of Column A and row 1; A2 labels the cell formed by the overlap of Column A and row 2; A3 labels the cell formed by the overlap of Column A and row 3; and so on. So, A1, A2… A16… are the labels for the cells within the left column of the worksheet.Remark: The three dots … mean that columns continue to the right and the rows continue downward. That is, there are many more available columns and rows on an Excel worksheet. The cell reference of the active cell always appears in the Name Box in the upper-left corner of the worksheet. (Your screen reader should possess a hot key to read the active cell reference.)Number of Columns and RowsDifferent board games are played around the world. The checkerboard and the chessboard have eight columns and eight rows with a total of 64 (= 8×8) cells. The scrabble board has 15 columns and 15 rows with a total of 225 (= 15×15) cells; the board for the Go game has 19 columns and 19 rows with a total of 361 (= 19×19) cells. A worksheet in Excel surpasses them all in size and grandeur.The most important part about a worksheet is the data that you place within its cells. The kind of data you place in a worksheet usually determines the number of columns that you use; the amount of data you place in a worksheet usually determines the number of rows occupied. Excel includes plenty of columns and rows in a worksheet so you don't run out of room for your data.The Excel 2007 worksheet is spacious! A single worksheet has 16,384 columns available and 1,048,576 rows available; that means you have more than 17 billion cells available in which you can place data!Remark: The math nerds among you may wish to know that the number of available columns 16,384 = 2^14 (labeled A through XFD) and the number of available rows 1,048,576 = 2^20; so there are 2^14 × 2^20 = 2^34 = 17,179,869,184 available cells per worksheet. A worksheet has lots of room indeed.Formula BarA horizontal strip called the Formula Bar resides just above an Excel worksheet. It has three parts. The left part (the Name box) shows the name of the active cell or its reference label. The middle part shows Cancel, Enter, and Edit buttons. The right part (a text box) shows the contents of the active cell; you may type data (text or a formula) here and then it appears within the active cell.There's an auto expand/collapse button at the far right of the strip. This button increases the width of the formula bar when you need to view a long formula or lots of text within the active cell.Access note: Your screen reader may fail to read the contents of the Formula Bar or let you move onto that strip. There are ways to create and edit a formula directly within its cell so you can ignore the Formula Bar entirely.Column Heading BarBetween the Formula Bar and the worksheet proper resides another horizontal strip; it shows column headings.The number of columns visible at a time depends on both the screen size and the screen magnification in effect. The leftmost 15 columns (labeled A through O) are visible for a restore screen size and a zoom value of 100%. Whereas, the leftmost 21 columns (labeled A through U) are visible for a maximum screen size and a zoom value of 100%. You can specify a screen size with the Window menu (displayed with Alt + SpaceBar); you can specify a zoom value with the Zoom button on the Status bar located at the bottom of the Excel window. (Recall that you can reach that bar with repeated taps of the F6 key; move onto the Zoom button with Arrow keys, and then press the Enter key to display the menu of zoom options.)The column headings change as you move left to right by a screen width across a worksheet. For instance, press Alt + PgDn to display the next 15 columns; they are labeled P through AD. (And yes, Alt + PgUp moves a screen width to the left.)Row HEADING BARLeft of the worksheet resides a vertical strip with row headings.The number of rows visible at a time depends on both the screen size and the screen magnification in effect. The topmost 15 columns (labeled 1 through 15) are visible for a restore screen size and a zoom value of 100%. Whereas, the topmost 26 rows (labeled 1 through 26) are visible for a maximum screen size and a zoom value of 100%. You can specify a screen size with the Window menu (displayed with Alt + SpaceBar); you can specify a zoom value with the Zoom button on the Status bar located at the bottom of the Excel window. (Recall that you can reach that bar with repeated taps of the F6 key; move onto the Zoom button with Arrow keys, and then press the Enter key to display the menu of zoom options.)The row headings change as you move top to bottom by a screen height down a worksheet. For instance, press PgDn to display the next 15 rows; they are labeled 15 through 29. (And yes, PgUp moves a screen height upward.)Remark: The keys PgUp and PgDn don't actually move a full screen up and down. They move up and down 1 line short of a full screen; that way, the row at the top or bottom of the prior screen remains visible to offer continuity between screens.Cell referencesCells within a worksheet are labeled so you can refer to them. Labels uniquely specify their cells; that is, different cells have different labels. Cell labels (also called cell references) consist of two parts: column headings followed by row headings. Here are the details.Column HeadingsAs mentioned, columns are labeled A through H in the standard chessboard portion of a worksheet. Excel continues to label columns I, J, K, through Z. That is, the leftmost 26 columns are labeled A through Z.The next 26 columns are labeled with two letters: AA, AB, AC, through AZ. The next 26 columns are labeled with two letters BA, BB, BC, through BZ. Excel continues through the alphabet. Finally, you reach the 26 columns labeled ZA, ZB, ZC, through ZZ. That is, column 702 = 26 + 26×26 has the label ZZ. Next, Excel uses three letters for more column headings. Namely, the letter pair AA followed by the individual letters A through Z to yield the column labels: AAA, AAB,…, AAZ.Row HeadingsAs mentioned before, rows are labeled 1 through 8 in the standard chessboard portion of a worksheet. Excel continues to number rows 9, 10, 11, and so on. You can't run out of numbers, so rows are numbered in order.Cell referencesAs mentioned, cell references (alias cell labels) have two parts: their column headings and their row headings placed side by side. For example, the cells within the top row have cell references A1, B1, C1,…, Z1, AA1, AB1, AC1,…, AZ1,…. The cells within the leftmost column have cell references A1, A2, A3,…, A26, A27,….Worksheet TabsAs mentioned every workbook includes three worksheets. By default Excel shows the top worksheet of the workbook, and it is labeled Sheet 1. There are Worksheet Navigation Tabs on the left side of the Status bar of the Excel window that let a mouse user display different worksheets. A keyboard user must rely on Ctrl + PgUp to display the prior worksheet and on Ctrl + PgDn to display the next worksheet.Excel displays the name of the active worksheet in bold on the Status bar and shows its tab on top of the tabs for the other worksheets.There is an Insert Worksheet Button (located immediately to the right of the last sheet tab) that lets you include an additional worksheet when needed. (Alt + Shift + F1 will add a new worksheet to the current workbook.)A single workbook can contain as many as three thousand worksheets. The number of available worksheets within a workbook depends on the amount of memory in your computer.Access note: On the left side of the bottom of the Worksheet area, the Sheet Tab scroll buttons—First sheet, Previous sheet, Next sheet, and Last sheet—appear followed by the actual tabs for the worksheets in your workbook and the Insert Worksheet button. If your workbook contains too many sheets for all their tabs to be displayed at the bottom of the Worksheet area, use the Sheet Tab scroll buttons to bring new tabs into view (so that you can then click them to activate them).Worksheet GlossaryActive Cell or Current Cell – the cell that has keyboard focus; you can enter data only within an active cell. (A worksheet can have only one active cell at a time.)Cell – a rectangle, the overlap of a column and a row, in which you can enter data (titles, values, formulas)Cell Address or Cell Reference – the label that marks the location of a cell within a worksheet; it consists of its column heading and row heading placed side by side. For instance, A1 denotes the location of the cell at the northwest corner of a worksheet; that is, the cell within Column A and Row 1.Cell Cursor – a dark solid border that frames the active cell (A dark dotted border frames a group of selected cells to be acted upon—copied, deleted, or moved.)Cell Comment – an electronic version of a sticky note that you can attach to any cellCell Formula – a type of cell entry that instructs Excel to perform a calculationCell Range – a group of cells, usually part of a column, row, or rectangle of cellsColumn – a vertical strip of cellsFormula Bar – a horizontal strip located below the Ribbon Bar that displays the cell address and the contents of the current cellName Box – the leftmost part of the Formula bar that displays the address or name of the current cellRow – a horizontal strip of cellsSheet Tabs – small tabs located below worksheets that label them in a workbook; you can assign your own names to sheet tabs.Sheet Tab Scroll Buttons – icons located left of the sheet tabsWorkbook – the basic file type that you create when you use Excel (A new workbook has three worksheets by default.)Worksheet – a table with labeled rows and columns displayed within the work area of the Excel program window; a worksheet can contain data, charts, or both. (The terms worksheet and spreadsheet are often used interchangeably.)Worksheet Area – the part of a worksheet where you enter cell data and add charts and graphicsCHAPTER 2: EXCEL Window LAYOUTThe user interface in Excel 2007 replaced the File Menu and the Recent Documents list with the Office Button; the Menu Bar and the Common Toolbars with the Ribbon Bar; all the Customizable Toolbars with the Quick Access Toolbar; the rigid Status Bar with a customizable Status Bar; and the complicated shortcut keys with simple letter combinations. These interface elements appear on the program window in precise locations.This chapter describes the layout and content of the Excel program window. The main parts of the user interface, Office Button, Quick Access Toolbar, Ribbon Bar, and Status Bar are briefly reviewed for the sake of completeness. (Please consult the Verbal View of Office Ribbon Bar tutorial from the American Printing House for the Blind for all the details.)Program Window LayoutAs with the Excel program of old, the Excel 2007 program window is visually and conceptually divided into three horizontal parts. The top section shows all the gadgets and controls used to interact with the program; that is, it shows the user interface elements. The middle section serves as the work area where you fill out a worksheet. The bottom section shows the status of the program and handy commands; that is, you go here to check file information, change program views, etc. Here are the details.Office ButtonIn the upper left-hand corner of the Excel window resides the Office Button, a large round symbol with the Office Logo (a colorful squiggle) on it. Microsoft developers wanted a very big Office Button so it stood apart visually from the Ribbon Bar and, in particular, from the Home tab on the Ribbon Bar. Moreover, developers wanted the Office Button to look like the Start button in Windows Vista, which is another big, round button. This visual resemblance is intentional for many reasons. Both buttons let you access core functionality—exit the Excel 2007 program, shut down Windows Vista, and so on.This button displays two vertical panes when activated. The standard file commands—New, Open, Save, Save As, Print, Close—and three new file commands—Prepare, Send, Publish—are listed in the left pane. Menu options with an arrow or a split button offer additional options. Recently used workbooks are listed in the right pane.Two buttons, Excel Options and Exit Excel, are located at the bottom of the right pane. Rely on the Excel Options button to make adjustments to Excel such as color scheme, page display options, AutoCorrect options, spelling, and grammar. You exit Excel when you are finished with it. There are two ways to exit Excel with the keyboard: either press the Alt + F4 key or press Alt, F, X in succession. You are prompted to save your workbook before you exit if you haven't saved your current work. What a great safeguard against accidental loss of your work.Ribbon BarJust right and slightly below the Office Button lives the Ribbon Bar where you find commonly-used Excel commands. It replaces the menu bar and toolbars that are found in earlier releases of Excel. There are seven standard tabs on the Ribbon Bar by default. Commands that are most relevant for a task area are placed on the same Ribbon tab. The seven standard Ribbon tabs let you perform the common program tasks.You can't alter the layout or content of the Ribbon Bar. This means that commands are always located in the same place on the Ribbon Bar, no matter with which computer you work. This consistency saves you time and effort.Fear not, there are ways to customize the Excel 2007 program to accommodate your preferences and work habits. There are two locations within the Excel program window where you may add your favorite commands—on the Quick Access Toolbar and on the Status Bar—as discussed below.The Two Bars & Four ButtonsOver the Ribbon Bar are two bars placed side by side: the new Quick Access Toolbar (QAT) together with its Customize Menu button located on the left, and the standard Program Title Bar located to its right. These two bars are unrelated—just placed side by side. (They are visually distinctive in Windows Vista and in Windows 7. The Quick Access Toolbar is presented entirely on Aero Glass, a specialized visual effect.)By default, there are just three buttons on the Quick Access Toolbar: Save, Undo, and Redo. You may personalize the QAT whenever you wish; that is, add or remove a command. You are able to place a favorite command located on the Ribbon Bar on this toolbar for quicker access: Move onto the command on the ribbon Bar; display the shortcut menu with Shift + F10; pick the Add to Quick Access Toolbar item. For instance, follow these steps to add the New Comment command located on the ribbon Bar to the QAT:Move onto the Ribbon Bar with a tap of the Alt key.Move onto the Review tab with the Arrow keys.Press the Tab key to move onto the leftmost command group of that tab.Move onto the New Comment command with the Arrow keys.Display the shortcut menu with Shift + F10.Pick the Add to Quick Access Toolbar item; just press the Enter key.Excel adds the New Comment command to the QAT, and you return to the active worksheet.Now you have three ways to add a comment to the active cell: press Alt, R, C; press Shift + F2; or use the New Comment command on the QAT.This bar gets squeezed between the Office Button and the Title Bar when you add lots of commands; you may position this bar below the Ribbon Bar to give it more room to spread out.The Title Bar displays the workbook name on the left, the legend [Compatibility Mode] in the middle when the workbook has the older Office file format, and the title of the program on the right. To the right of the Title Bar the three usual buttons are displayed: the Help Button (a right-aligned "international help" icon), the Maximize/Restore Window Button, and the Close Window Button.The four buttons at the top of the Excel program window possess shortcut keys. Left of the QAT resides the Office Button—accessed with the Alt + F key because File commands live there. To the right of the Title Bar, three standard buttons are displayed as in prior Office releases: the Help Button—accessed with the F1 key; the Maximize/Restore Window Button; and the Exit Program Button—accessed with the Alt + F4 key.Remark: The shortcut keys for the Restore and Maximize commands are absent in the Excel 2007 program. However, you may rely on the Alt + SpaceBar key to pop up the Window menu where these options reside.Work AreaLaunch the Excel program. Excel shows worksheet 1 of workbook 1 in the middle section of the program window. (Worksheet layout and basic navigation are presented in the next chapter.)Customizable Status BarAt the bottom of the Excel window resides a Customizable Status Bar. This area shows information about the active workbook, and also lets you check the on/off status of many Excel options.You are able to specify its items—pick from a shortcut menu the items you want displayed there. Here are the steps to accomplish this:Press the F6 key repeatedly till you land on the Status bar.Press the Shift + F10 key to display its shortcut menu.Move onto an item.Press the Enter key (not the SpaceBar key) to check or uncheck that item.Press the Esc key to return to the Status bar.You have added or removed that item.Program Window NavigationThe good news is you may rely on repeated taps of the F6 key to move onto the three sections of the Excel program window. This key places you on the active tab on the Ribbon Bar, on the active cell on the active worksheet, and on the active item on the Status Bar. Also, F6 moves you onto task panes when they are present. Press the Shift + F6 key repeatedly to move in the reverse direction through window parts and task panes when present.Office Button OverviewYou may press Alt and F in succession or together to activate the Office Button; two vertical panes are displayed. The left pane shows a menu of file commands. The right pane shows a list of recently used workbooks. Below the right pane are two items: Excel Options and Exit Excel. All menu items in the left pane possess access letters; only the top nine workbooks listed in the right pane possess access numbers. You can pick an item with three successive key taps: Alt, F, Letter; or Alt, F, Number.Left Pane CommandsThe left pane in Excel 2007 shows these menu items, top to bottom:Alt, F, N NewAlt, F, O OpenAlt, F, V Convert (only available when in Compatibility mode)Alt, F, S SaveAlt, F, A Save AsAlt, F, P PrintAlt, F, E PrepareAlt, F, D SendAlt, F, U PublishAlt, F, C CloseAs mentioned, all menu items possess access letters; a few menu commands also possess shortcut keys, as before, and you may rely on them instead:Ctrl + N New workbookCtrl + O Open workbookCtrl + S Save workbookF12 Save workbook AsCtrl + P Print workbookCtrl + F4 Close workbook but not programAn item (such as Save As and Print) may have a split button that offers alternative options, or an item (such as Prepare, Send, and Publish) may have a pull-down menu with additional options.Navigate the pull-down menus as usual. Home and End keys place focus at the top and at the bottom; Up and Down keys move through the menu.Right Pane WorkbooksThis pane shows a list of the recently used workbooks. The list shows as many workbooks as can fit on the list—seventeen workbooks by default, but you may specify a different number.You can reach the top workbook in the right pane with three key taps: Alt, F, and Right Arrow. Up and Down keys move you through the vertical list of workbooks in the right pane, but Home and End keys don't work as expected.The top nine workbooks possess access numbers, 1 through 9. So Alt, F, 1 will open the workbook currently at the top of the list.The workbook you opened most recently becomes the top list item; that is, the list changes as you open different workbooks.You may have a workbook you rely on daily; here's how you keep it on the list:Open that workbook.It appears at the top of the list.Press Alt, F, and Right Arrow to move onto that workbook in the list.Press the Right Arrow again to move onto the Push Pin button, and then press the Enter key to activate it.That workbook will stay on the list forever—well, until you unpin it. Unfortunately, it doesn't stay in the same place—so you can't type Alt, F, 1 to open it. And, unfortunately, letter key navigation also doesn't work; that is, you can't type the initial letters of the workbook name to reach that workbook. (Taps of letters will activate items on the left pane instead.)The two items below the right pane possess access letters:Alt, F, I Excel OptionsAlt, F, X Exit ExcelOr, press the Up Arrow key while at the top of the left pane to move onto the Exit Excel item. Then press the Left Arrow key to move onto the Excel Options item.Ribbon Bar OverviewMenu and toolbars that reside at the top of the Excel program window in prior releases of Excel are replaced with a single bar that takes up about the same vertical space. This horizontal strip, called the Ribbon Bar, lets you pick all Excel commands and options with the keyboard. Replacement of top-level menus and toolbars makes possible new and easier ways to carry out tasks. (You can't remove, modify, or replace the Ribbon Bar. There are third party add-ons that can provide a menu system for the Excel 2007 program. I don't recommend that course of action because the Ribbon Bar offers keyboard users complete access to all features and commands in the Excel 2007 program.)Activate the Ribbon Bar with the Alt key just like the Menu Bar. A tab on the Ribbon Bar is activated with an Alt + Letter key just as you activated a menu on the Menu Bar. In other words, the Ribbon Bar replaces the Menu Bar, and tabs replace menus. (There's a lot more to say about this later.)Ribbon Bar LayoutThere are three parts to the horizontal Ribbon Bar. Tabs are listed along its top edge; commands and options are listed throughout the middle area; and names of command groups are listed along its bottom edge.Ribbon TabsThere are three kinds of Ribbon tabs. Most of the time, you only deal with the standard tabs listed along the top edge of the Ribbon Bar. The two other kinds of tabs automatically appear on the Ribbon Bar when you need them.Standard TabsAlong the top edge of the Ribbon Bar reside seven tabs. They represent commonly-performed tasks. The active Ribbon tab has a three-sided rectangle around it to visually mimic a real folder tab. This visual cue moves whenever you activate a different Ribbon tab. Every tab on the Ribbon Bar shows a different version of the Ribbon Bar.Here are the Excel 2007 Ribbon tabs:Alt, H HomeAlt, N InsertAlt, P Page LayoutAlt, M FormulasAlt, A DataAlt, R ReviewAlt, W ViewAlt, L Developer (Optional)You can just show the row of Ribbon tabs when you want more room for your worksheet; that is, you can hide all the commands usually displayed below the row of ribbon tabs. There are a few ways to temporarily hide the rest of the Ribbon Bar: Ctrl + F1 turns the Ribbon Bar on and off; a double click of the active tab turns the Ribbon Bar on and off; move onto a Ribbon tab or command and press Shift + F10 and then pick Minimize the Ribbon located at the bottom of the shortcut menu. Or, activate the Customize Button near the Quick Access Toolbar and then pick Minimize the Ribbon on that menu.You still have access to every Ribbon tab after you minimize the Ribbon Bar. Just press Alt + the tab letter to temporarily display that Ribbon tab and its commands. Pick a command as usual; the Ribbon Bar returns to its minimized form, and you return to the active document.Access note: Don't rely on the Minimize command on the shortcut menu because it has a bug: Your screen reader may fail to talk after you invoke that command. Use the Ctrl + F1 toggle instead; it works properly.Contextual TabsThere are hidden Ribbon tabs; they automatically appear on the Ribbon Bar when needed and go away when no longer needed. For example, Excel shows the Chart Tools tab with Design, Layout, and Format subtabs when you insert a chart. Ribbon tabs that appear only when their commands are needed are called Contextual tabs; they appear in a different color and are placed on the Ribbon Bar after all the standard tabs. Contextual tabs let you easily discover needed commands and options in atypical situations that you might otherwise overlook.You may wonder when contextual tabs appear on the Ribbon Bar, and how you would know they are there. There are three rules that determine when contextual tabs appear.Rule 1: Insert an item—table, chart, and so on; contextual tabs for that item appear.Rule 2: Select an inserted item—table, chart, and so on; then contextual tabs for that item appear.Rule 3: Double click an item—table, chart, and so on; contextual tabs for that item appear.In summary, check for contextual tabs on the ribbon Bar after you either select or insert an item—chart, equation, graph, etc.Access note: Highlight (select) text with the mouse, then the Mini Toolbar appears near the selected text; it shows commonly-used format options. This toolbar doesn't appear when you highlight (select) text with the keyboard, so rely on the format options located on the Home tab of the Ribbon Bar.Program TabsProgram tabs replace the standard Ribbon tabs only when you switch to atypical edit situations or unusual views in Excel.Ribbon CommandsA task may require that you perform multiple commands. Related commands for a task are grouped together on the Ribbon Bar which makes it easy to find them. For example, font commands are grouped together in the Font Group on the Home tab. Trigonometry functions and financial commands are grouped together in the Function Library Group on the Formula tab.Ribbon Bar NavigationExcel has a Home tab on the far left of the Ribbon Bar; that Ribbon tab shows the most frequently used commands in the program. This tab has focus when you launch Excel; that is, if you press the Alt key, then you are placed on the Home tab. More specialized commands are located on the other Ribbon mands are performed in three or four steps: (1) select an item (cell, row, table, and so on); (2) activate a Ribbon tab; (3) move onto a command group; (4) finally move onto the desired command and tap the Enter key. The next sections give all the details.Ribbon Bar TabsThere are two ways to activate a tab on the Ribbon Bar with the keyboard. Here are the details.Method 1Tap either Alt key.You land on either the Home tab or the currently active tab.Move left or right along the row of tabs with the Arrow keys.You land on the next tab in that direction and activate that tab and display its commands.Tap either Alt key again.You leave the Ribbon Bar and land on the active cell within the active worksheet.Remark: Move onto a Ribbon tab. Then, you may press either Alt key, the esc key, the SpaceBar key, or the Enter key to dismiss the Ribbon Bar and return to the active worksheet.Here's what happens when you fall off the row of tabs on the Ribbon Bar. Move left while on the leftmost tab, and you land on the Office Button. Move right while on the rightmost tab, and you land on the Help Button. In summary, rely on the Horizontal Arrow keys to move through Ribbon tabs and display their command groups.Method 2Tap the Alt key plus the Access letter for the desired tab. You leap onto the Ribbon Bar and immediately activate that tab and display its commands.Ribbon Bar BadgesBadges are groups of letters or numbers enclosed in little rectangles and displayed throughout the Ribbon Bar; they are placed next to buttons, icons, tabs, and so on. They serve as text labels for those items. You should memorize the badges for the commands you frequently rely on so you can quickly activate them and bypass Ribbon Bar navigation. Badge letters are case insensitive; that is, you may type lower- or uppercase letters.Badges may contain letters or numbers. For example, launch Excel and press the Alt key. The letter F appears near the Office Button; merely type F to activate that button. There are three items on the Quick Access Toolbar (QAT) by default. The number 1 appears near the Save Button; type 1 to activate that button. The number 2 appears near the Undo Button; type 2 to activate that button. The number 3 appears near the Redo Button; type 3 to activate that button. The letter H appears near the Home tab; type H to activate that Ribbon tab.Badges are visible onscreen only when they are needed, and are announced by screen readers only when they are moved onto. For example, press the alt key to activate the user interface and show its top-level badges. Activate any tab on the Ribbon Bar; its badges appear and the top-level badges disappear from view. In summary, only a single layer of badges are visible at a time!Command Group OverviewAs mentioned, a tab on the Ribbon Bar represents a task. Related task commands are grouped together, and names for these groups are displayed along the bottom edge of the Ribbon mand Group LayoutCommands within groups are placed on the Ribbon Bar between the top edge and the bottom edge, just over their group names. Commands include buttons, menus, text boxes, and dialog boxes. Commands within groups are arranged in different ways. For example, the Clipboard group on the Home tab shows four commands over the group name Clipboard, one command by itself and three in a column to its right. The Font group on the Home tab shows two rows of commands over the group name Font.Only the common and most useful commands reside on the Ribbon Bar. So, where are the rest of the commands and options?Groups with additional commands and options possess group buttons, small diagonal arrows called launchers, located to the right and slightly below their names. When activated these buttons display dialog boxes or task panes. For example, all the commonly-used font commands are listed in the Font group. More options, such as superscript, are available. Display the Format Cells dialog box with Alt, H, F, N to show superscript and other options related to cell format.Group buttons are often context-sensitive. Whether you can activate a group button depends on the current context. Group buttons are grayed out when they don't pertain to the current mand Group NavigationThere are two ways to reach commands within a group: with navigation keys and with access keys. Rely on navigation keys to explore and learn the layout of the command groups for a Ribbon tab. Memorize the access keys for your favorite commands, and then use them to quickly activate those commands. Here are the details.Navigation KeysThe two Horizontal Arrow keys move left and right through the Ribbon tabs located on the top edge of the Ribbon Bar. If you stop on a Ribbon tab, that tab is activated and its command groups are displayed on the bottom edge of the Ribbon Bar.By analogy, the two shifted Horizontal Arrow keys move you left and right through the command groups located on the bottom edge of the Ribbon Bar. If you stop on a command group, then its leftmost command has keyboard focus.Here's what happens when you fall off the row of command groups on the Ribbon Bar. Move left while on the leftmost command group, and you land on the active tab. Move right while on the rightmost command group, and you land on the Office Button. Move right again, and you land on the Quick Access Toolbar (QAT). Move right again, and you return to the active tab.There are three different ways to reach commands on the Ribbon Bar. Here are the details.Method 1Use the Tab keys to explore and learn the layout of the command groups and move through their commands. This method ensures that you will reach every command and not skip over commands by mistake.Press the Alt key.You land on either the Home tab or the currently active tab.Move left or right with the Arrow keys.You land on the next Ribbon tab in that direction and activate that Ribbon tab and display its command groups.Press the Tab key.You move keyboard focus onto the leftmost command group of that Ribbon tab.Move through the individual commands within a command group with the two Tab keys, Tab and Shift + Tab.You land on successive commands within that group. Eventually, you reach the next command group.Press the Alt key again.You leave the Ribbon Bar and land on the active cell within the active worksheet.Method 2You may skip from command group to command group with a single key tap. Here are the details.Press the Alt key.You land on either the Home tab or the currently active tab.Move left or right with the Arrow keys.You land on the next Ribbon tab in that direction and activate that Ribbon tab and display its command groups.Press the Tab key.You move keyboard focus onto the leftmost command group of that Ribbon tab.Move left or right through command groups with the Shift + Arrow keys.You land on the next command group in that direction. Its leftmost command has keyboard focus.Move through the individual commands within a command group with the two Tab keys, Tab and Shift + Tab. Eventually you reach the next command group.You land on successive commands within that group.Press the Alt key again.You leave the Ribbon Bar and land on the active cell within the active worksheet.In summary, rely on the Shift + Horizontal Arrow keys to move through the command groups for the active Ribbon tab. Rely on the two Tab keys to move through the individual commands within groups.This method has a minor glitch. Shift + Arrow keys work as just described—move group to group—except when the next group you land on starts with a command which is a text box. You are placed within the text box. You must press the Tab key to leap over the text box; then Shift + Arrow keys continue to move you through the command groups.Here's an example:Activate the Home tab with the alt, H keys.Press the tab key.You move onto the leftmost command group, the Clipboard group.Press Shift + Right Arrow.You move onto the Font Group and land in the Font Type text box.Press the tab key.You move out of the Font Type text box and land in the Font Size text box.Press the tab key again.You move out of the Font Size text box and onto the Grow Font command. You are back in business.Press Shift + Right Arrow.You move onto the Alignment Group.Remark: This problem arises because Shift + Arrow keys perform double duty: they move between command groups and select text within text boxes.Method 3The two Vertical Arrow keys move you above and below the row of Ribbon tabs and so do the Tab keys. Here are the details.Activate the Ribbon Bar with the Alt key. Tap the Up Arrow key while on the Home tab. Keyboard focus moves up and onto the Quick Access Toolbar (QAT). There are two problems: Up Arrow doesn't move up and onto the QAT when you are on most Ribbon tabs, and you don't necessarily land on the same QAT button. Here is the solution: rely on the Shift + Tab key instead. It works while you are on any Ribbon tab; that is, you always land on the Customize QAT button, which is located just right of the QAT. (The icon for this button is an inverted solid triangle with a dash on top.) Then use the Horizontal Arrow keys to move along the QAT. Press the Enter key when over a QAT command to activate that command.Activate the Ribbon Bar with the Alt key. Tap the Down Arrow key while on any tab. Keyboard focus moves down and onto the leftmost command group; and its leftmost command has keyboard focus. You may rely on the Tab key instead; then, repeated taps of the Tab key move you through all commands within that command group.The commands within a command group may occur in a row, in a column, or both. The two keys, Tab and Shift + Tab, will dependably move through all the commands within a group, no matter their arrangement within the ribbon Bar. On the other hand, the Arrow keys may skip over commands, and you will never realize they are there.In summary, rely on the Tab keys to read all the commands within a command group. Use the four Arrow keys thereafter to move around within a command group.Navigation BadgesEvery command in the user interface—on the Ribbon Bar, on the Quick Access Toolbar (QAT), and on the menu displayed by the Office Button—has a little label, called a badge, with access keys written on it. There are two ways to pick commands: type the access keys listed on their badges, or move onto them with Navigation keys and press the Enter key.Here's how you reach a command or option within a command group with navigation keys:Activate a Ribbon tab which displays its command groups.Press the Tab key to place focus on the leftmost command group of the active tab.Rely on Shift + Arrow keys to move through command groups.Rely on ordinary Arrow keys to move through commands within the current command group.Take the appropriate action when over the desired command: Press the Enter key to activate a command button, press the SpaceBar key to check or uncheck a checkbox, type text in a text box, and so on.As you browse through commands within a group, you will see/hear access keys for them. You can press the Alt key and then type those access keys to activate them directly. This is the fastest way to pick commands—once you know their access keys. For instance, press Alt, H, F, N to display the Format Cells property sheet, a dialog box with multiple tab mand Group ArrangementExcel makes the selection of commands more intuitive and often shows you how text will look after you apply them. The use of table style commands discussed below illustrates this process marvelously.Ribbon GroupsThe names of the command groups related to the active tab are listed along the bottom edge of the Ribbon Bar. By default, the Home tab has keyboard focus, and its command groups are listed along the bottom edge of the Ribbon Bar. If you activate a different Ribbon tab, then the names of its command groups are listed along the bottom edge of the strip instead.Here are the Excel 2007 tabs with their command groups listed below them:Alt, H HomeClipboard, Font, Alignment, Number, Styles, Cells, EditingAlt, N InsertTables, Illustrations, Charts, Links, TextAlt, P Page LayoutThemes, Page Setup, Scale to Fit, Sheet Options, ArrangeAlt, M FormulasFunction Library, Defined Names, Formula Auditing, CalculationAlt, A DataGet External Data, Connections, Sort and Filter, Data Tools, OutlineAlt, R ReviewProofing, Comments, ChangesAlt, W ViewWorkbook Views, Show/Hide, Zoom, Window, MacrosAlt, L Developer (Optional)Code, Controls, XMLAccess note: Keyboard navigation works wonderfully after you accustom yourself to the Fluent User Interface and its ways. The entire Fluent User Interface has keyboard accessibility. The long way requires that you activate a Ribbon tab, move onto a command group, and move onto a command within the group; the short way requires that you type five or fewer characters to reach the desired command. That is, you can reach every command and every option in an Office 2007 program with five or fewer key taps.Grid CommandsExcel introduces a new kind of command called a Grid Command. This type of command displays items in a checkerboard layout. For example, Alt, H, T displays a grid of styles only applicable to Excel tables. This particular checkerboard layout shows three kinds of Excel table styles: Light-shaded styles followed by Medium-shaded styles followed by Dark-shaded styles.You land on the default Excel table style (labeled Light 1) when you pick this command.The two Extreme keys, Home and End, place the focus at the upper left corner of the grid and at the lower right corner of the grid. In this example, on the item labeled Light 1 and on the item labeled Dark 11.Arrow keys move only through items within a row or within a column. For example, while on row 1 of this grid, the Arrow keys move across this row and stay on that row; that is, when you reach Light 7, a press of the Right Arrow key moves back onto Light 1, not onto row 2.On the other hand, the Tab and the Shift + Tab move through all grid items in order. For example, when on Light 7 at the end of row 1, a press of the Tab key moves onto Light 8 at the start of row 2. You land on the commands below the grid when you move off the grid.Ribbon ScreenTipsExcel has oodles of commands listed on its ribbon Bar. But what do they do? Until now, you had to guess, try to find relevant help topics in the Help viewer, or just try them.Excel offers a great solution. All commands listed on the Ribbon Bar possess ScreenTips with included help. ScreenTips appear as you navigate through commands on the Ribbon; they show detailed information about commands and may even illustrate them with pictures or diagrams.ScreenTips show command names, keyboard shortcuts, access keys, and text labels that describe the commands. They also link to the Help viewer. Press F1, and you are placed in the Help viewer at the proper topic. A command may lack an associated help topic; then you are placed in the Table of Contents in the Help viewer which is not very helpful!Access note: Your screen reader should possess a key which will read all the information on a ScreenTip to you. For example, move onto any command on the View tab to read about that view.ScreenTips possess another new and welcome feature. You may encounter commands that are grayed out; that is, disabled or don't work. You may not know why or what to do. ScreenTips usually will offer a reason why and recommend a course of action.These enhanced ScreenTips don't cover up the Ribbon Bar as you move around. They are displayed below the Ribbon Bar so they keep out of the way.Remark: There's no more Help menu! Activate the Help button or press F1 to launch the Help program. You land on a text box in which you may enter text; a press of the Tab key moves you onto a Search button. Press the Enter key to start the search, and a list of help topics appears. Even better, highlight a command on the ribbon Bar and press F1 to display help topics directly related to that command—no search required.ScreenTips are an excellent way for you to learn about commands in the Excel program. However, you may find them intrusive and even annoying. Turn them off if you don't want them:Press Alt, F, I to display the two panes of the Program Options item.You land on the Popular category on the left pane.Press the Tab key to move onto the right pane.Press the Tab key repeatedly until you reach the ScreenTip Style combo box.Highlight one of the three options: Show Feature Descriptions In ScreenTips; Don’t Show Feature Descriptions in ScreenTips; or Don't Show ScreenTips.Press the Enter key to accept the highlighted option.View Command OverviewYou can display your workbook in three different views: Normal, Page Layout, and Page Break Preview. Typically, users employ Normal view while they work because they can edit and rewrite text most efficiently in that view—cell format stays in the background for the interim. (Excel uses Normal view by default.) Page Layout view displays the workbook as it will look when printed. Page Break Preview shows where pages will break when the workbook is printed.Excel offers two convenient ways to switch workbook views: with commands on the View tab and with view buttons on the Status bar.View CommandsAlt, W, L—Normal ViewAlt, W, P—Page Layout ViewAlt, W, I—Page Break Preview ViewAlt, W, C—Custom ViewAlt, W, E—Full Screen ViewIn summary, you don't have to hunt for the view commands. Just remember they reside on the View tab of the Ribbon Bar and recall their access letters.View ButtonsPress F6 until you land on the Status bar.The active button has focus.Move left or right with the Arrow keys.You land on the next button in that direction.Press the Enter key when over the desired view button.The workbook appears in that view.Press Shift + F6 to return to the current worksheet.In summary, you don't have to hunt for the view commands. Just remember they reside on the Status bar, left of the three Zoom buttons.CHAPTER 3: CELL AND WORKSHEET NAVIGATIONThere are many convenient ways to move through the cells on a worksheet. All navigation keys move the cell cursor directly onto a cell or from cell to cell. You use navigation keys to move through a worksheet and read its data. This chapter presents the most commonly-used cell and worksheet navigation keys.Cell Navigation KeysLaunch the Excel program. An empty worksheet appears in the work area of the Excel program window. The empty cell in the northwest (upper left) corner of the worksheet has keyboard focus. On the other hand, open a nonempty workbook. The cell that had keyboard focus when you closed that workbook still has keyboard focus; it may be empty or occupied.Except for a cell located in the top row or in the leftmost column, a cell has four adjacent neighbors: a cell to its left and a cell to its right, and a cell over it and a cell beneath it. So it is natural to rely on the four Arrow keys to move onto those four cells.There are four other keys that also move onto adjacent cells. Press the Tab key to move onto the cell to the right of the current cell; press the Shift + Tab key to move onto the cell to the left of the current cell. Press the Enter key to move onto the cell beneath the current cell; press the Shift + Enter key to move onto the cell over the current cell.Remark: Usually, you would enter data, say monthly bills, within columns labeled January through December. So, it's natural to have the Enter key move onto the cell below the active cell within the current column when you are finished typing data in the active cell. That is, a tap of the Enter key completes the entry within the active cell, and then it moves keyboard focus onto the next cell down the column so you can add another column entry.When you move onto an empty cell with any of these eight keys, you are able to type a title, value, or formula within that cell. On the other hand, when you move onto an occupied cell with any of these eight keys, you are able to edit or delete its content. That is, when you move onto a cell, Excel selects that cell's content. (Merely begin to type to replace cell content, or press the F2 key to begin to edit cell content.)There are two handy keys that let you change your mind when you edit cell content. While on a cell, you may press the Esc key when you want to keep the current cell content and discard any changes that you made to that content. When you move off an edited cell, you may press the Undo key, the Ctrl + Z key, when you want to return to the current cell content.Access note: Your screen reader should possess handy read cell keys: one will read the cell's location on the worksheet and one will read the cell's content. Rely on them to verify cell position and reread cell content.As mentioned, Excel places a dark border around the active cell, the current cell with keyboard focus. The dark border, called the Cell Cursor, visually marks the Active Cell within the active worksheet. You can only enter data into one cell at a time, namely the active cell.Remark: Normally, you enter data into a single cell at a time; however, you are able to place the same formula into a group of selected cells. For instance, you may have many columns of numbers you want to add up. You can select the row of cells below those columns and place a formula within them that adds up the columns of numbers for you. Much more about this follows in a later chapter.You may use these basic navigation keys:Left Arrow or Shift + TabMove 1 cell to the left within the current row if possible.Right ARROW OR TabMove 1 cell to the right within the current row if possible.Up Arrow or Shift + EnterMove 1 cell up within the current column if possible.Down Arrow or EnterMove 1 cell down within the current column if possible.You may use these bonus navigation keys:HomeMove onto Column 1 within the current row.Ctrl + HomeMove onto Column 1 within the top row. That is, onto the cell at the northwest corner of the current worksheet.Now practice. Move around a bit with these ten keys. Type a word here and there; then, try to find them.Region Navigation KeysThe cells on a worksheet form a huge rectangle. Most columns on the right are empty, and most rows beneath are empty. That is, you populate cells with data in a much smaller rectangle, anchored at the northwest corner of the worksheet. This much smaller rectangle contains the active area of the worksheet.You may enter blocks of data here and there throughout the active area within a worksheet. Blocks of data are groups of occupied cells bounded by empty rows, empty columns, or worksheet borders.As mentioned earlier, the Arrow keys move 1 cell in the direction of the arrows. On the other hand, the Ctrl + Arrow keys move to the very edges of the current data region. Here are the details.Ctrl + HomeMove onto the northwest corner of the active area; that is, onto the initial column and initial row of the active area.Ctrl + EndMove onto the southeast corner of the active area; that is, onto the final column and final row of the active area.Access note: Ctrl + Home and Ctrl + End place you at opposite corners of the rectangle that makes up the active area. Use the Ctrl + End key to determine how many columns and how many rows comprise the active area. That's useful information especially when you deal with an unfamiliar worksheet. That information tells you how far you need to browse left and right and up and down to read all the worksheet data. (Press the Ctrl + End key, and then read the cell's reference via your screen reader's hot key to learn the final column and row headings within the active area.)Ctrl + Left ArrowMove onto the leftmost occupied cell within the current row of cells or onto the leftmost unoccupied cell within that row.CTRL + Right ARROWMove onto the rightmost occupied cell within the current row of cells or onto the rightmost unoccupied cell within that row.CTRL + Up ARROWMove onto the top occupied cell within the current column of cells or onto the top unoccupied cell within that column.CTRL + Down ARROWMove onto the bottom occupied cell within the current column of cells or onto the bottom unoccupied cell within that column.Ctrl + PgUpMove onto the active cell of the previous worksheet.CTRL + PGDnMove onto the active cell of the next worksheet.Remark: Blocks of cells on a worksheet with data, separated from other areas with data, are called Data Regions. Ctrl + Navigation keys let you browse them separately. Data regions are used to visually split up a complicated worksheet into more manageable parts.End Navigation KeysThe curious reader may have noticed the absence of the End key in the prior discussion of cell navigation keys. Excel hijacks the End key for its End Mode. That is, with the Ctrl key commands, you must press two keys concurrently. However, with the End key commands, you press two keys in succession—which may be easier to do in some cases.End (By Itself)Turns End Mode on or off. The word END appears on the Status bar till you tap the End key again or tap a navigation key.End, HomeMove onto the final row and final column; that is, onto the southeast corner of the active area.End, Left ArrowMove onto the leftmost occupied cell within the current row of cells or onto the leftmost unoccupied cell within that row.End, Right ArrowMove onto the rightmost occupied cell within the current row of cells or onto the rightmost unoccupied cell within that row.End, Up ArrowMove onto the top occupied cell within the current column of cells or onto the top unoccupied cell within that column.END, Down ARROWMove onto the bottom occupied cell within the current column of cells or onto the bottom unoccupied cell within that column.End, EnterMove onto the rightmost occupied cell within the current row of cells or onto the rightmost unoccupied cell within that row.Remark: Blocks of cells on a worksheet with data, separated from other areas with data, are called Data Regions. Ctrl + Navigation keys let you browse them separately. Data regions are used to visually split up a complicated worksheet into more manageable parts.Worksheet and Workbook NAVIGATION KeysAs mentioned, a worksheet consists of a huge rectangle filled with cells. Often, you may need to move left or right or up or down by many rows or columns. It would be a chore to tap Arrow keys multiple times to go from here to there.Rely on Page keys to move a screenful at a time:PgUpMove up 1 screenful; the cursor stays within the same column.Alt + PgUpMove left 1 screenful; the cursor stays within the same row.PgDnMove down 1 screenful; the cursor stays within the same column.Alt + PgDnMove right 1 screenful; the cursor stays within the same row.CTRL + PGUPMove onto the southeast corner of the previous worksheet of the current workbook.CTRL + PGDNMove onto the southeast corner of the next worksheet of the current workbook.Remark: Notice that the PgUp and the PgDn keys along with their modified forms (Alt, Ctrl) are all worksheet commands. They let you move by screenfuls within a worksheet or move among worksheets.CHAPTER 4: EDIT CELL DATACells are where you get down to business and enter data in a worksheet. You move onto cells and type text into them. This brief chapter presents the two basic ways to edit cell content.You may move onto a cell and immediately replace all of its data with new data, or you may edit the current data within the cell. Here are the details.The Erase keys: The Del Key and the BackSpace KeyRecall that in Word, if you select any text, a tap of the Del key or BackSpace key erases that text. Or, you can just begin to type new text to replace the selected text. These keys work the same way in Excel for a selected cell. Here are the details.Launch the Excel program and open any workbook. The cell in the northwest corner of the worksheet has keyboard focus, and its text is selected by default. A tap of the Del key or of the BackSpace key erases that text. Or, you can just begin to type new text to replace any text within that cell.Move onto any cell within a worksheet; that cell becomes the active cell with its text selected by default. So, you can rely on the Del key or the BackSpace key to erase its text.Practice is highly recommended. Type the word dog within any cell. Next, move off that cell so Excel accepts your text entry. Move back onto that cell, and try either the Del key or the BackSpace key. Repeat these steps but just begin to type text when over the cell; notice your typed text immediately replaces the text within the cell.In summary, the Del key and the BackSpace key (used individually) do four things: turn off cell selection; erase cell content; enter edit mode; and place the text cursor at the northwest corner of the cell. You then are able to navigate and write as if the cell were a mini Word document.The Edit Keys: The F2 Key and the Erase KeysAs just described, a press of the Del key or of the BackSpace key erases the content of the active cell. Often, however, you don't want to erase the cell's text but instead want to just edit that text.Move onto any cell; it becomes the active cell with its text (if any) selected. Tap the F2 key. Excel turns off cell selection and places the text cursor within the cell at the southeast corner of the cell after its text. That is, you are now in Edit mode instead of Navigation mode.Notice that the four Arrow keys and the two Extreme keys (Home and End) move through cell text instead of cells on the worksheet. That is, you are able to move through cell text with normal Word navigation keys.Notice that the Del key erases just the character at the text cursor, and the BackSpace key erases just the character before the text cursor. That is, these two keys work just as they do in Word when in cell Edit mode.Also, notice that you must rely on the two pairs of keys Tab and Shift + Tab and Enter and Shift + Enter to move off the active cell and onto an adjacent cell. You can't use the four Arrow keys because in edit mode they work as text navigation keys within the active cell.In summary, the F2 key does three things: turns off cell selection; enters cell edit mode; and places the text cursor after the cell's text. You are able to navigate cell text with ordinary Word navigation keys and edit cell text with the Del key and the BackSpace key as if the cell text were a mini Word document.The undo KeysPress the Esc key while on a cell to cancel any changes you made to cell content and keep the current cell content. Move off a cell with any cell navigation key to accept any changes you made to its content. Thereafter, you can press the Undo key, Ctrl + Z, to return the prior cell content.The Edit Line KeysYou may enter multiple lines of text within a single cell or delete parts of lines. Here are the details.Move onto a cell, and press the F2 key to enter edit mode. Type a line of text. Press the Alt + Enter key to start a new line of text within the cell.Remark: Shift + Enter starts a new line within a paragraph in Word, and Alt + Enter starts a new line within a cell in Excel. That is, these two keys let you break the flow of text within a paragraph and within a cell.The two Erase keys delete single characters within a cell. However, often you need to delete part of a line. You can press the Ctrl + Del key to delete text to the end of the current line within a cell.CHAPTER 5: Adjust cell sizeA standard worksheet resembles a sheet of address labels with every label just one line high and just eight characters wide. If you type an address fifteen characters wide, then eight of them are placed on one label and the other seven are placed on the next label to its right. Or, if you type an address two lines high, the address falls on two vertical labels. It would be nice if you could make cells wider and/or taller. This chapter explains the ways to make cells bigger so your text fits properly within them.Determine Cell SizeIt's important to know how big cells should be, so you should ask yourself and answer these two basic questions before you begin to develop a worksheet:?What's the purpose of the worksheet? Do you want to show a budget? If so, you will need twelve columns for the months—January through December and as many rows as there are budget items. Do you need to show the results of a lab experiment? If so, you will need as many columns as there are experimental measurements (for instance, temperature, pressure, and volume to summarize the ideal gas law) and as many rows as there are data points. Do you need to summarize test scores? If so, you need as many rows as there are students in the class and as many columns as there are tests.?What are the column and row titles? For instance, in a household budget, you could label columns January through December to designate the twelve months for which the budget period refers, and you could label rows as: rent, electric, trash pickup, entertainment, and so on.After you answer these two questions and decide upon the titles and data to be listed, you will have a good idea of how big (wide and tall) your cells should be. At this point, selecting cell widths for your columns and cell heights for your rows follows.Specify Cell SizeAdjust cell size (column width and row height) with the commands and options found within the Cells group on the Home tab of the Ribbon Bar. That group has three items: Insert Cells, Delete Cells, and Format Cells. You may rely on Alt, H, O to reach the commands on the Format Cells list, or you may reach individual items directly:Alt, H, O H—Manually Pick Row Height (Dialog Box)Alt, H, O, A —Let Excel Pick Row Height (AutoFit Command)Alt, H, O, W—Manually Pick Column Width (Dialog Box)Alt, H, O, I—Let Excel Pick Column Width (AutoFit Command)Alt, H, O, D—Specify Standard Column Width (Dialog Box)Alt, H, O, U—Hide and unhide Rows, Columns, Worksheets (Pull-Down Menu)Alt, H, O, R —rename Worksheet (Command)Alt, H, O, M—Move or Copy Worksheet (Dialog Box)Alt, H, O, T—Change Worksheet Tab Color (Pull-Down Menu)Alt, H, O, P—Protect Worksheet (Dialog Box)Alt, H, O, L—Lock (preserve) Cell Format (Toggle)Alt, H, O, E—Format Cell Content (Dialog Box)In summary: The top two items let you and Excel specify row height; the next two items let you and Excel specify column width; the fifth item lets you specify a standard column width for all cells on all worksheets in the current workbook.The item Hide and Unhide lets you show or conceal rows, columns, and entire worksheets. (Rely on this option when you wish to conceal important information from unauthorized users.)The next four items let you Rename, Move/Copy, Color, and Protect worksheets.The final two items let you lock and format cells.Adjust Cell SizeYou may enter as much text as you like within a cell. You may enter multiple lines as well as long lines. However, Excel doesn't display or print the text that doesn't fit within the cell. You need to increase cell height to accommodate multiple lines, and you need to increase cell width to accommodate long lines. That is, you must increase row height and increase column width so that all of your text fits on a worksheet.You are able to alter cell size in two different ways: alter row height and alter column width. You can make either adjustment manually or let Excel make the adjustment for you. Here are the details.Adjust Row HeightRecall that the standard column headings (A, B, C…) and the standard row headings (1, 2, 3…) are generic labels; they don't tell you anything about the contents of the columns or of the rows on your worksheet. Add your own titles to all columns and rows so their purpose on the worksheet is clear to you and others.Your titles may consist of single lines of text or contain multiple lines of text. For instance, you may label twelve columns January through December for a worksheet that shows an annual budget. You may want to place the year below the month so readers know the year to which the budget data refers. In this instance, column titles contain two lines of text, and you or Excel must increase row height to fit both lines.Remark: You may place column titles within any row; it doesn't need to be the top row, Row 1. You may place row titles within any column; it doesn't need to be the leftmost column, Column A.Manually Adjust Row HeightTraditionally, a worksheet shows columns of numbers to be added, averaged, etc. By default, a cell can only show a single line of text. That may be insufficient room; for instance, a row title or a column title may require two or more lines of text.You may place as many lines of text as you wish within a cell. (Press the Alt + Enter key to finish a line and start a new line.)You may specify a cell height between 0 and 409 points. These extreme values are impractical: A zero value means that the row disappears from view; a large value means that a row is too tall. Pick values that make sense for your worksheet titles. For instance, a cell height of thirty points will accommodate titles as big as two lines high.You may pick any row near the top of a worksheet where you want to place column titles. You need to adjust the row height if there are titles with multiple lines of text.You may specify the height of that row independently of other rows:Move onto the row.It doesn't matter where you are within the row.Display the Row Height dialog box with Alt, H, O, H.Immediately type the row height that you want.It overwrites the current value—which is selected by default.Tap the Enter key to activate the OK button.You return to the active worksheet, and Excel adjusts the current row height as you specified.Notice that Excel only adjusted a single row! You may adjust as many rows as you wish, one by one. (Press the Ctrl + S key to save your workbook and preserve your row adjustments.)automatically ADJUST Row HeightSometimes it is a nuisance to determine the proper height for a row because items within a long row may vary widely in height. It would be nice if Excel could scan a row and determine the appropriate height for you.Excel can determine the appropriate height of any row for you:Move onto the row to be adjusted.It doesn't matter where you are within the row.Select the entire row with Shift + SpaceBar.Activate the AutoFit Row Height command with Alt, H, O, A.You return to the active worksheet, and Excel adjusts the current row appropriately.Notice that Excel only adjusted a single row! You may have Excel adjust as many rows as you wish, one by one. (Press the Ctrl + S key to save your workbook and preserve your row adjustments.)Adjust Every ROWExcel can determine the appropriate height of every row for you:Select the entire worksheet (all rows) with a double press of Ctrl + A.A single press of Ctrl + A selects only the current region on the worksheet; there may be multiple regions.Activate the AutoFit Row Height command with Alt, H, O, A.You return to the active worksheet, and Excel adjusts every row appropriately.Notice that Excel adjusted every row separately! Different rows are likely to have different heights. (Press the Ctrl + S key to save your workbook and preserve your row adjustments.)Adjust Column WidthAs mentioned, a cell only holds about eight characters left to right. Clearly, that is insufficient room for a typical company name or customer name. You need to widen cells to fit long names; otherwise Excel doesn't display or print the data properly.Manually Adjust Column WidthIf you enter an item wider than eight characters, then Excel adjusts that item. A text item such as "Dogs like" fits, but "dogs like cats" fills up the current cell and flows onto the cell to its right. A number such as 12341234 fits, but a number wider than eight digits such as 1234123400 doesn't fit, and Excel shows it in scientific notation—not very useful in most situations.You may specify a cell width between 0 and 255 characters. These extreme values are impractical: A zero value means that the column disappears from view; a large value means that a column is too wide. Pick values that make sense for your data. For instance, a cell width of fifteen characters will accommodate numbers as big as one hundred trillion.Remark: Actually, you ought to pick column widths two more than you believe you need. The item abcdefgh (eight lower-case letters) and the item 12345678 (eight digits) fit within the default cell width, but the item ABCDEFGH (eight upper-case letters) doesn't fit because upper-case letters are wider characters and thus take up more room left to right.You may specify the width of any column independently of other columns:Move onto the column.It doesn't matter where you are within the column.Display the Column Width dialog box with Alt, H, O, W.Immediately type the column width that you want.It overwrites the current value—which is selected by default.Tap the Enter key to activate the OK button.You return to the active worksheet, and Excel adjusts the current column as you specified.Notice that Excel only adjusted a single column! You may adjust as many columns as you wish, one by one. (Press the Ctrl + S key to save your workbook and preserve your column adjustments.)automatically ADJUST Column WIDTHIt is sometimes a nuisance to determine the proper width for a column because items within a long column may vary widely in length. It would be nice if Excel could scan a column and determine the appropriate width for you.Excel can determine the appropriate width of any column for you:Move onto the column to be adjusted.It doesn't matter where you are within the column.Select the entire column with Ctrl + SpaceBar.Activate the AutoFit Column Width command with Alt, H, O, I.You return to the active worksheet, and Excel adjusts the current column appropriately.Notice that Excel only adjusted a single column! You may adjust as many columns as you wish, one by one. (Press the Ctrl + S key to save your workbook and preserve your column adjustments.)Adjust Every COLUMNExcel can determine the appropriate width of every column for you:Select the entire worksheet (all columns) with a double press of Ctrl + A.A single press of Ctrl + A selects only the current region on the worksheet; there may be multiple regions.Activate the AutoFit Column Width command with Alt, H, O, I.You return to the active worksheet, and Excel adjusts every column appropriately.Notice that Excel adjusted every column separately! Different columns are likely to have different widths. (Press the Ctrl + S key to save your workbook and preserve your column adjustments.)Specify a Standard Column WidthA user may always work with data of a specified width. For instance, a teacher may specify a column width of 20 to accommodate student names. It would be nice if all worksheets in all workbooks start out with a column width of 20 characters.You may specify a default width for every column on every worksheet:Move onto any column.It doesn't matter where you are within the column.Display the Default Width dialog box with Alt, H, O, D.Immediately type the column width that you want.It overwrites the current value—which is selected by default.Tap the Enter key to activate the OK button.You return to the active worksheet, and Excel adjusts the current column as you specified.Notice that Excel adjusted all the columns, and they are all the same width! (Press the Ctrl + S key to save your workbook and preserve your column adjustments.)CHAPTER 6: SELECT CELL DATAThink of cells as mini Word documents with three parts: data that you enter, formats that you apply, and comments that you may attach. You must select cells before you can change their contents, formats, or comments. So, you need to know the various ways to select cells. This chapter presents the three common ways to select cells and ranges of cells with the keyboard, and then these techniques are used to edit or move cell contents. (Cell formats and cell comments are discussed in their own chapter.)Select a Cell or a Cell RangeThere are three ways to select cells: use Shift + Navigation keys; use the Extend key with Navigation keys; and use the Go To command. By far, the Go To command works best with the keyboard. Here are the details.When you move onto a cell with any Navigation key, that cell and its content are selected. You may erase cell content—just begin to type, or you may edit cell content—just press the F2 key.Often, you may want to erase cell content from multiple cells at the same time. You must select the cells to be cleared of data before you can perform that task. The three basic ways to select ranges of cells are described below.Shift Key + Navigation KeysMove onto any cell; Excel selects that cell. Hold down the Shift key; move onto the final cell within the range with the four Arrow keys or the two Extreme keys, Home and End; and finally release the Shift key to halt the selection. All cells crossed over are selected. You can select part of a column with the vertical Arrow keys; you can select part of a row with the horizontal Arrow keys. You can even select a block of cells with the Arrow keys: move down a column and then across a row to select all the cells within that rectangle.Extend key + Navigation KeysMove onto any cell; Excel selects that cell. Then tap the Extend key F8; move onto the final cell within the range with the four Arrow keys or the two Extreme keys, Home and End; and finally, tap the F8 key again to turn off the Extend mode. All cells crossed over are selected. You can select part of a column with the vertical Arrow keys; you can select part of a row with the horizontal Arrow keys. You can even select a block of cells with the Arrow keys: move down a column and then across a row to select all the cells within that rectangle.You can also select the first cell or range of cells, and then press SHIFT + F8 to add another nonadjacent cell or range to the selection. To stop adding cells or ranges to the selection, press SHIFT + F8 again. Note you cannot cancel the selection of a cell or range of cells in a nonadjacent selection without canceling the entire selection.Go To Dialog BoxThis method offers the quickest and most general way to select a cell range or multiple cell ranges.As mentioned earlier, you may rely on the Go To dialog box to move onto (and select) any cell. You can do much more with the Go To dialog box. Here's a tour of that dialog box.Reference Text BoxDisplay the Go To dialog box with Ctrl + G. You land on the Reference text box. It may, or may not, contain highlighted text. Just begin to type text to write over any text or tap the BackSpace key to erase any text.You may type a single cell reference or multiple cell references within the Reference text box. Cell references separated by commas are selected individually; two cell references separated by a colon denote a cell range and all the cells within that range are selected. Tap the Enter key to close the dialog box and select all the listed cells. Here are a few examples that illustrate the use of commas and colons within the Reference text box.Type A1, C3, E5 within the Go To dialog box and press the Enter key. Excel selects these three non-adjacent cells along the main diagonal of the worksheet. E5 becomes the active cell.Type A1:Z1 within the Go To dialog box and press the Enter key. Excel selects the twenty six cells from A1 through Z1; that is, the first twenty six cells across the top row of the worksheet. A1 becomes the active cell.Type B10:B20 within the Go To dialog box and press the Enter key. Excel selects the eleven cells from B10 through B20; that is, eleven cells down column B. B10 becomes the active cell.Type A1:D9 within the Go To dialog box and press the Enter key. Excel selects all the cells within the rectangle with northwest corner A1 and southeast corner D9; that is, Excel selects 4 x 9 = 36 cells. A1 becomes the active cell.Type A1:C3, E1:G3 within the Go To dialog box and press the Enter key. Excel selects all the cells within the two rectangles with northwest corners A1 and E1; that is, Excel selects 3 x 3 + 3 X 3 = 18 cells. E1 becomes the active cell.In summary, you may select (highlight) individual cells with the Go To dialog box; just separate them with commas. You may select part of a row, part of a column, or a rectangular range of cells with the Go To dialog box; you need to specify the start reference, the stop reference, and separate them with a colon. The initial cell of the final cell range becomes the active cell.Go To List BoxDisplay the Go To dialog box with Ctrl + G. You land on the Reference text box. Tap the Tab key, and you land on the Go To list box. That list shows the four most recent referenced locations. If you move onto any one of them, it appears within the Reference text box. Press the Enter key to go back to that location on the worksheet.Remark: Notice the Dollar signs ($) that flank cell references in this list. They designate Absolute Cell References which are discussed elsewhere in this tutorial.Select an Entire Column or RowExcel has handy keys that let you select a complete column or row. Move onto a column and press Ctrl + SpaceBar to select the entire column. Move onto a row and press Shift + SpaceBar to select the entire row.You can also select adjacent columns or rows with these two keys. Select a column; hold the Shift key and move left or right with the Arrow keys to select adjacent columns. Select a row; hold the Shift key and move up or down with Arrow keys to select adjacent rows. Release the Shift key when all columns or rows are selected.Select Most of a Column or RowAs mentioned, you can select adjacent cells within a column or row with Shift + Arrow keys. You can also select from the current cell to the end of a column or row. Move onto any cell within a worksheet. Press Shift + Ctrl + Arrow key to select all the cells in that direction. You need to press Shift + Ctrl + Arrow key when a data region intervenes so you reach the end of the column or row.Select an Entire WorksheetAs mentioned earlier, tap the Ctrl + A key twice to select all the cells within a worksheet. Only do that when you want to remove all data from the worksheet or when you want to format the worksheet uniformly.Select Cells with Specified ContentThe methods described so far let you select individual cells or cell ranges based on their locations within a worksheet. However, there are many occasions when you don't know the locations of cells that hold specified content—for instance, those cells that hold just numbers, those cells that hold just formulas, those cells that hold just titles, etc. You may want all numbers to have two decimal points; you may want all titles in bold; or you may want to delete all items of a specified type. Of course, you could browse the entire active area of a worksheet and make format adjustments or deletions as you go, but that would take a lot of time and work, and you may miss cells here and there.You would like to be able to select all the cells that hold specified data, and you would like a quick way to do that. Luckily, you are able to accomplish that with the Go To Special dialog box. There are two ways to access that dialog box. You can either display the Go To dialog box and display its Special dialog box, or you can reach that dialog box located in the Editing group on the Ribbon Bar with Alt, H, F, D, S. Follow these three steps to display the Go To Special dialog box:Display the Go To dialog box with Ctrl + G.Tap the Tab key twice.You land on the Special Button.Press the Enter key.Up pops the Go To Special dialog box.This dialog box lets you select cells based on the type of information they hold. You land on a vertical list with fifteen radio buttons, most of which you can ignore. The useful items are illustrated below.Selection Types and Their OptionsWhen you pick a selection type from the list you are able to refine the selection via four check boxes or two radio mentsCells that possess attached commentsConstantsCells that contain values that are not calculated and, therefore, don't change; numbers and labels are constants. (Formulas or values resulting from formulas aren't constants.)FormulasCells that contain formulas; formulas are sequences of values, cell references, names, functions, or operators that produce new values.BlanksCells that are empty.Current regionThe block of nonempty cells that includes the currently selected cell or cells. The region extends in all four directions to the first empty row or column.Current arrayAn array is used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns.ObjectsCharts and graphs on the worksheetRow differencesAll cells that differ from the active cell in a selected rowColumn differencesAll cells that differ from the active cell in a selected columnPrecedentsCells that are referenced by the formula in the active cellDependentsCells with formulas that refer to the active cellLast cellThe last cell on the worksheet that contains data or formattingVisible cells onlyOnly cells visible in a range that crosses hidden rows or columnsConditional formatsCells that have conditional formats applied to themData validationCells that have data validation rules applied to themThese items may, or may not, have associated check boxes or radio buttons. Check boxes are listed horizontally and are checked by default. Radio buttons are listed vertically. Press the Tab key to move onto these controls when present. Rely on Left and Right arrows to move through the four check boxes; press the SpaceBar to check or uncheck a box. Rely on Up and Down arrows to move between the two radio buttons. Press the Tab key repeatedly to reach the OK and Cancel buttons.The nine items Comments, Blanks, Current Region, Current Array, Row Differences, Column Differences, Last Cell, Visible Cells Only, and Objects lack both. The two items Constants and Formulas have the same four check boxes. The two items Precedents and Dependents have the same two radio buttons. The two items Conditional Formats and Data Validation have the same two radio buttons.You may use a radio button within this list to highlight specified items on a worksheet so you can read them, format them, or delete them. Next follow a few common tasks.Read CELLS WITH CommentsThe creator of a worksheet may attach comments to cells to explain their purposes or to serve as reminders. How do you know which cells possess attached comments? You could move through a worksheet till you find all the cells with attached comments, but that's impractical for a worksheet with many cells.Access note: A cell with a comment has a little red triangle located at the upper right-hand corner of the cell. Your screen reader should announce the presence of a comment when you move onto the cell.Excel can highlight all the commented cells for you.Display the Go To Special dialog box.Rely on Ctrl + G, or press Alt, H, F, D, S.Move onto the Comments item with Arrow keys.Tap the Enter key to activate the OK button.Excel tells you that there are no cells with comments, or Excel highlights the cells that possess comments.Repeatedly tap the Tab key to move through the highlighted cells and read them.Move onto any other cell with a navigation key to deselect the cells.Access note: Your screen reader should possess a hot key to read the attached comment.Format CELLS with TitlesItems that you place within cells and that don't change unless you change them are called constants. For instance, column and row titles are constants; they are textual items.You may decide to make all the text on a worksheet bold, green, and 12 points high. It would be quite a chore to browse a worksheet and change the format of text within cells one by one. Excel can highlight all the cells that hold text, and let you format all of them at the same time.Display the Go To Special dialog box.Rely on Ctrl + G, or press Alt, H, F, D, S.Move onto the Constants item with Arrow keys.Press the Tab key to move onto the row of check boxes.Uncheck all but the Text check box.Tap the Enter key to activate the OK button.Excel tells you that there are no cells with text, or Excel highlights all the cells that hold text.Apply the desired formats. (E.g., press Ctrl + B to bold all the text within all the highlighted cells.)Move onto any other cell with a navigation key to deselect the cells.Now, all the text has the desired format.Clear CELLS with DataItems that you place within cells and that don't change unless you change them are called constants. Plain numbers, currency amounts, and percentages are examples of constants; they are numeric items.You may have a worksheet that calculates items of interest for you, such as your monthly car payment, monthly mortgage payment, money market funds, etc. The formulas that do the calculations for you can be complicated and can take a lot of work to get right. It would be nice if you could erase all the data on the worksheet but keep the formulas. That way, you can reuse the worksheet with different data.Excel can highlight all the cells that hold numbers, and let you clear all of them at the same time.Display the Go To Special dialog box.Rely on Ctrl + G, or press Alt, H, F, D, S.Move onto the Formulas item with Arrow keys.Press the Tab key to move onto the row of check boxes.Uncheck all but the Numbers check box.Tap the Enter key to activate the OK button.Excel tells you that there are no cells with numbers, or Excel highlights all the cells that hold numbers.Tap the Del key.Move onto any other cell with a navigation key to deselect the cells.Now, you can enter new data, and the formulas will compute the required amounts.SELECTION TYPES AND THEIR Shortcut KeysSometimes you don't need to customize a selection. In those situations, you can rely on shortcut keys to make selections and bypass the Go To Special dialog box entirely. Here's the list of available shortcut keys and what they do.CTRL + SHIFT + O (Letter O)Select all cells that contain comments.CTRL + [ (Left Bracket)Select all cells directly referenced by formulae in the selection.CTRL + SHIFT + { (Left Brace)Select all cells directly or indirectly referenced by formulae in the selection.CTRL + ] (Right Bracket)Select cells that contain formulae that directly reference the active cell.CTRL + SHIFT + } (Right Brace)Select cells that contain formulae that directly or indirectly reference the active cell.CTRL + SHIFT + * (Shift 8)Select the current region around the active cell (the data area enclosed by blank rows and blank columns).CTRL + / (Forward Slash)Select the array containing the active cell.CTRL + \ (BackSlash)In a selected row, select the cells that don't match the value in the active cell.CTRL + SHIFT + | (Shift BackSlash)In a selected column, select the cells that don't match the value in the active cell.ALT + ; (Semicolon)Select the visible cells in the current selection.CHAPTER 7: INSERT, ERASE, OR COPY CELL DATAYou can place data within cells, erase data from cells, or copy data to other cells. This chapter presents the various ways to accomplish these three tasks.Work with Single CellsInsert Cell DataYou can place data within a single cell in three steps:Move onto any cell with Navigation keys.Excel selects its content if it has any.Type the data (title, value, formula) that you want to enter.Excel writes over any data already within the cell.Move off the cell to accept your change, or press the Esc key to cancel your change.Erase Cell DataYou can erase data within a single cell in three steps:Move onto any cell that holds data with Navigation keys.Excel selects its content.Press either the Del key or the BackSpace key.Excel erases any data within the cell.Move off that cell to accept that change, or press the Esc key.Del followed by the Esc key clears cell content; BackSpace followed by the Esc key retains cell content.Erase Multiple CellsThis topic shows you the easiest way to clear multiple cells all at once.Select the cells, rows, or columns that you want to clear.Press the Del key.Excel erases the content of every selected cell.Tap any navigation key to accept the change and deselect the cells.Remark: You can't deselect the cells with the Esc key! A tap of any navigation key deselects the specified cells, and then moves onto a cell which becomes selected.Copy Multiple CellsOccasionally, you may need to move data within a group of cells to a different group of cells. For instance, you may type titles for columns in row 4 of a worksheet, and then decide that they would fit or look better in row 3. You could, of course, clear those cells in row 4 and retype the titles in row 3. That could be a lot of work, a big waste of time, and fraught with potential typos. Or, you may need to duplicate all the data within a row or column. In this case the Copy, Cut, and Paste commands come to your rescue.These commands let you move data (titles, values, formulas) from anywhere on a worksheet and place those data within other cells on the same worksheet, on a different worksheet, or on another workbook. These move commands are located in the Clipboard group on the Home tab of the ribbon Bar but you can ignore them and rely on their standard shortcut keys.Follow the procedures below to move data in various ways from cells to other cells on the same worksheet.Move All of the Data within the Active CellIt's easy to move all the data within a cell onto another cell. Follow these steps:Move onto any cell that contains data.Rely on standard navigation keys to reach that cell. Its data are selected.Move the cell data onto the Clipboard with either the copy command Ctrl + C or the Cut command Ctrl + X.Excel replaces the dark solid black border that surrounds the active cell with a dotted dark border to indicate that you are about to move its data.Move onto a different cell on the worksheet with navigation keys.Paste the data into this cell with Ctrl + V.The data will reside within both cells if you used the Copy command; the data will reside only within the destination cell if you used the Cut command.If you used the Cut command , Excel removes the dotted border around the active cell as soon as you move off the destination cell. However, if you used the Copy command instead, you must move off the destination cell and then press the Esc key to remove the dotted border around the active cell.Remark: Any data within the destination cell are replaced by the moved data! You can tap the Esc key to cancel the move operation.MOVE Part OF THE DATA within the Active CellIt's also easy to move part of the data within a cell onto another cell. Follow these steps to accomplish this:Move onto any cell that contains data.Rely on standard navigation keys to reach that cell. Its data are selected.Enter cell Edit mode with a tap of the F2 key.Now, the text cursor lies within the active cell and normal edit keys and selection keys work.Rely on Shift + Arrow keys to select part of the data within the cell.Move the selected cell data to the Clipboard with either the copy command Ctrl + C or the Cut command Ctrl + X.Tap the Esc key to exit Edit mode.Move onto a different cell on the worksheet with navigation keys.Paste the partial data into this cell with Ctrl + V.The data will reside within both cells if you used the Copy command; the data will reside only within the destination cell if you used the Cut command.Remark: Data within the destination cell are replaced by the moved data! You can tap the Esc key to cancel the move operation.Merge Data within ANOTHER CELLSo far, you replace all the data within the destination cell with all or part of the data within the active cell. There are times when you need to add the data within the active cell to the data within another cell. You can do that easily as well; you must use the F2 key within both cells to accomplish that. Here are the steps to combine data within two cells:Move onto any cell that contains data.Rely on standard navigation keys to reach that cell; its data are selected.Enter the Edit mode with the F2 key.Now, the text cursor lies within the active cell and normal edit and selection keys work.Rely on Shift + Arrow keys to select all or part of the data within the active cell.Move the selected cell data to the Clipboard with either the copy command Ctrl + C or the Cut command Ctrl + X.Tap the Esc key to exit Edit mode.Move onto a different cell on the worksheet with navigation keys.Enter the Edit mode with the F2 key.Now, the text cursor lies within the destination cell and normal edit and navigation keys work.Place the text cursor where you want to insert the extra data.Paste the data into this cell with Ctrl + V.The moved data will reside within both cells if you used the Copy command; the data will reside only within the destination cell if you used the Cut command.Remark: The destination cell now holds its original data as well as the moved data! You can tap the Esc key to cancel the move operation.Move Data RangeSo far, you have moved data between two cells. However, you may need to move or duplicate the data within a range of cells. For instance, you may wish to move a row of titles to a different row, or you may want to duplicate a column of figures so you can perform other calculations on them. These tasks are doable with the selection commands and the move commands already discussed.Select a partial or complete row of cells, a partial or complete column of cells, or a rectangle of cells.Rely on the Shift + Arrow keys, any other Shift + Navigation keys, or the Extend key F8 to make your selection. Better yet, use the Go To Box to make the selection.Invoke the desired move command, Copy or Cut.Excel places a dotted dark border around all the selected cells to indicate that you are about to move their data.Move onto the cell on the worksheet where the new cell range should start.Paste the data into the destination range with Ctrl + V.The data will reside within both cell ranges if you used the Copy command; the data will reside only within the destination cell range if you used the Cut command.If you used the Cut command, Excel removes the dotted border around the selected cells as soon as you move off the destination cells. However, if you used the Copy command instead, you must move off the destination cells and then press the Esc key to remove the dotted border around the selected cells.For instance: Select the cells from A1 through A9; place their contents on the Clipboard with the Copy command; move onto cell D4; and invoke the Paste command. The data in cells A1 through A9 appear in cells D4 through D12.Remark: Data within the destination cell range are replaced by the moved data! You can tap the Esc key followed by any navigation key to cancel the move operation.CHAPTER 8: PERFORM ARITHMETIC WITH CELL DATASo far, you can move through the cells on a worksheet and enter and edit data in cells. You can also select, delete, copy, or move data. However, most importantly, you can perform calculations on cell data.This chapter introduces Excel as a fancy calculator program. You can also work with formulas without much effort on your part—Excel does all the work for you.Arithmetic OperationsThere are four basic operations in arithmetic: addition, subtraction, multiplication and division. These four operations are Binary Operations because you perform them on two numbers at a time. In the Excel program, the symbol + denotes the addition operation; the symbol - denotes the subtraction operation; the symbol * denotes the multiplication operation; and the symbol / denotes the division operation.As just mentioned, you can only add, subtract, multiply, and divide two numbers at a time. So, for instance, how do you add or multiply the three numbers 2, 3, and 6 together?You must add two of them and then add the third to that sum. For example, 2 + 3 + 6 = (2 + 3) + 6 = 5 + 6 = 11, or 2 + 3 + 6 = 2 + (3 + 6) = 2 + 9 = 11. Luckily, these two sums are the same; thus, the triple sum 2 + 3 + 6 makes sense computed either way.To find the product of these three numbers, you must multiply two of them and then multiply the third by that product. For example, 2*3*6 = (2*3)*6 = 6*6 = 36, or 2*3*6 = 2*(3*6) = 2*18 = 36. Luckily, these two products are the same; thus, the triple product 2*3*6 makes sense computed either way.Traditionally, pairs of parentheses are used to group a binary operation together with its two operands—the two numbers it works on; that is, the two numbers it adds, subtracts, etc. It turns out that any sequence of numbers grouped by pairs of parentheses always gives the same sum and product. For instance, no matter how you group pairs of numbers within 2 + 3 + 6 + 11 and 2*3*6*11, you get the same sum (22) and the same product (396).Notice that the binary operations subtraction and division don’t behave so nicely. For example, (6-3)-2 = 3-2 = 1, but 6-(3-2) = 6-1 = 5; the results are different depending on the placement of the parentheses. The same is true for division: (6/3)/2 = 2/2 = 1, but 6/ (3/2) = 6/1.5 = 4; the results are different depending on the placement of the parentheses.Here is another example of a commonly-used binary operation. The binary operation of exponentiation lets you perform repeated multiplication. For instance, 2 to the power of 3 is displayed as 2^3 = 2*2*2, and 3 to the power of 2 is displayed as 3^2 = 3*3. Excel uses the symbol ^ to indicate exponentiation. (The number on the left is the number to be multiplied, and the number on the right indicates how many times.) You will meet other useful operations as you work with Excel.Remarks: Excel knows about positive and negative numbers as well as about decimals and fractions. Excel can handle simple tasks—add up your grocery bill as well as advanced tasks—help design a bridge. With normal numbers, you can’t divide by zero! General binary operations are studied in the branch of mathematics called abstract algebra or modern algebra. Binary operations like addition and multiplication are called associative operations—they give equal results no matter how you group their operands, and operations like subtraction and division are called non-associative—they give different results when you group their operands.Perform Single OperationsLaunch your Excel program. Now you are ready to perform basic arithmetic calculations.Type 2 + 3 in cell A1 (or in any other cell). Notice that 2 + 3 looks like and acts like ordinary text. Nothing happens when you move off that cell.You must tell Excel to perform the addition 2 + 3 to get the sum 5. Type = before 2 + 3 (or any other arithmetic operation) to make Excel carry out the calculation. Follow these steps to make the text 2 + 3 become an Excel formula:Move back onto cell A1.Press F2 to enter Edit mode.Press the Home key to place the text cursor in front of 2 + 3.Then type = before 2 + 3.Move off cell A1 to accept the content change.Move back onto cell A1.Notice that now this cell shows the value 5 instead of the expression =2 + 3.In summary, when you type = before an arithmetic expression within a cell such as 2 + 3, Excel performs the operation for you and replaces the expression with the result of the calculation. That is, the cell holds the formula =2 + 3 but displays the result 5. You can move back onto the cell and press F2 to reveal the formula again. You can then replace the two numbers (2 and 3) with different numbers to perform a different calculation.Normally you type = followed by an arithmetic expression at the same time within a cell to enter a formula, and Excel automatically performs the calculation and displays the result as soon as you move off that cell. Here are two practice computations:Move onto any cell; type =1 + 2 + 3 + 4; move off that cell. What does that cell now show?Move onto any cell; type =1*2*3*4; move off that cell. What does that cell now show?Here are two trick questions:Move onto any cell; type =4-3-2; move off that cell. What does that cell now show?Move onto any cell; type =6/3/2; move off that cell. What does that cell now show?Recall that subtraction and division are non-associative operations; that is, you get different results when you group the operands differently. By convention, Excel assumes you group operands left to right when there is ambiguity and when there are no pairs of parentheses to clarify the situation. The expression =4-3-2 is evaluated as = (4-3)-2 which gives 1-2 = -1. The expression =6/3/2 is evaluated as = (6/3)/2 which gives 2/2 = 1.Remark: If you accidentally omit the equal sign before 4-3-2 or 6/3/2, then Excel treats those expressions as dates instead of arithmetic expressions to be evaluated. They appear as 4/3/2002 and as 6/3/2002 and Excel formats the cell with the Date format.Every cell has its own content as well as its own format. Remove cell content with the Del key, and remove cell format with the Clear command located in the Edit group on the Home tab of the ribbon Bar.Perform Multiple OperationsSo far, you used a single operation within a calculation, but you can use multiple operations within a calculation. Here are a few examples.You may enter = (2*3) + (4*5). Excel performs the calculations enclosed within pairs of parentheses first: = (2*3) + (4*5) becomes = 6 + 20 which yields 26.What about = 2*(3 + 4)? Well, =2*(3 + 4) becomes =2*7 which yields 14.You may always enclose an operation together with its two operands within a pair of parentheses to specify what you want Excel to do. But, long expressions may include many operations and hence lots of pairs of parentheses. They are a nuisance to type, and it’s easy to miss a parenthesis which frustrates the calculation. For example, if you type = 2*(3 + 4 by mistake (with no closing parenthesis), Excel notices the error and prompts with a suggested correction.Luckily, you may often omit pairs of parentheses entirely. Excel performs calculations in a specified order when you omit pairs of parentheses. Here’s how it works.Excel scans the entire formula left to right for operation signs. It performs all exponentiation operations first. For example, =2^3 + 4 becomes =8 + 4 which yields 12. The expression =2*3^4 becomes =2*81 which yields 162. The expression =2^3/4 becomes =8/4 which yields 2.Next, Excel performs all multiplications and divisions. For example, =2*3 + 3*4 becomes = 6 + 12 which yields 18. The expression =2*3 + 8/2 becomes =6 + 4 which yields 10.Finally, Excel performs all additions and subtractions. For example, =2*3 + 4 becomes =6 + 4 which yields 10. The expression =2*3-4 becomes =6-4 which becomes 2. The expression =2 + 3*4 + 5 becomes =2 + 12 + 5 which yields 19.In summary, Excel performs calculations within pairs of parentheses first. Then Excel performs all operations of exponentiation followed by all operations of multiplication and of division. Finally, Excel performs all operations of addition and of subtraction.Excel has a list of formulas properly constructed you can choose from, so you don’t have to make up your own to carry out common computational tasks. You only need to pick the formula you need. (Or, you can make up your own formula and use it over and over again).Perform calculations with FormulasSo far, you perform calculations on particular numbers and have Excel carry them out. For instance, you may enter = (3 + 17)/2 to have Excel average the two numbers 3 and 17.Here are the steps used to carry out a calculation on particular numbers:Move onto the cell to hold the calculation to be performed by Excel.Type the equal sign to tell Excel that the following text should be treated as a formula rather than as ordinary text.Type the formula to be carried out by Excel.The formula includes the desired operations and the numbers to be used by them.Either press the Enter key or move off the cell to have Excel perform the calculation using the formula within the cell.The result of the calculation appears within the cell.You may move back onto the cell and press F2 to show the actual formula again—instead of the result of the calculation.It would be convenient if you could type the formula by itself and only need to type the numbers to be used within the calculation. You can do just that with cell references, and here is how.As an example, you may often need to average different pairs of numbers. Follow these steps to automate that task:Type any number in cell A1.Type any number in cell A2.Type the formula =A1 + A2 in cell A3.Press Enter or Tab.Behold! Cell A3 shows the sum of the numbers in cells A1 and A2. If you change the numbers in cells A1 and/or A2 and press Tab or Enter, Excel shows their sum in cell A3.Or, you can place the formula = (A1 + A2)/2 in cell A3 to average the two numbers placed in cells A1 and A2. (Excel calculates the sum and then divides by 2.)Here is a summary so far: Numbers typed into cells are called constants because they don’t change unless you change them. Enter a formula in a different cell that refers to the cells that hold the numbers to be operated on. Excel will place those numbers into the formula at the proper places and perform the entire calculation for you. Always type the formula in the cell where you want the result of the calculation to appear. (That cell shows the result of the calculation, and the Formula Bar displays the formula.) There’s no need to change the formula; you only need to change the constants used within the formula to perform different calculations.Here is another example; you will calculate the average of any three numbers:Type any number in cell A1.Type any number in cell A2.Type any number in cell A3.Type the formula = (A1 + A2 + A3)/3 in cell A4, the cell just below the three numbers to be averaged.Now experiment. Type different triples of numbers in cells A1, A2, and A3 and check that their averages appear in cell A4. Play with the triples (1, 2, and 3), (4, 5, and 6), and (11, 12, 13).Remark: it is fun to notice that the average of every triple of three consecutive numbers always equals the middle number. Can you explain why?The cell references within a formula act as variables, and the numbers placed in the cells act as constants you substitute for the variables. (Yes, Excel lets you perform most tasks you may have encountered in algebra class.)Formulas that contain cell references are useful because you only need to change the constants substituted for the cell references to have Excel carry out a different calculation for you. Formulas that rely on cell references can repeatedly perform different computations without any further help from you. Try this: Type any three numbers in column A and use cells A1, A2, and A3. Type any three numbers in column B, use cells B1, B2, and B3. Type any three numbers in column C, use cells C1, C2, and C3. Include as many columns of numbers as you like.Now you have a mini table of numbers listed down columns. Column A could hold your January, February, and March utility expenses. Column B could hold your January, February, and March food expenses. Column C could hold your January, February, and March entertainment expenses. You can add additional columns as the months go by, and, of course, you could add more rows to hold other expenses.You can place below each column of figures a formula to add up that month’s expenses. For instance place =A1 + A2 + A3 in cell A4, and place like formulas in the other columns to add up the listed expenses.It’s not too hard to add up a few cells by explicitly listing the cell references within the formula, but it becomes unwieldy when there are many cell references to specify. Luckily, you may specify a cell range instead.You can replace the explicit formula =A1 + A2 + A3 with =Sum (A1:A3). Try it! You get the total in cell A4 as before. That is, you can use a cell range to shorten a lengthy formula.Notice the form of the Sum formula: As usual, the = sign starts the formula; next comes the name of the computation to be performed; after it comes the cell range placed within a pair of parentheses. The start cell and the end cell are separated by a colon.As another example, you can average the numbers within a cell range as well. Try =Average (A1:A3). Of course, when you use these formulas in other columns, you must change the column letter. For instance, use the formulas =Sum (C1:C3) or the formula =Average (C1:C3) in cell C4 in column C to sum or average the numbers within column C.In summary, Excel has many useful ways to perform computations. You can specify a single computation such as =2 + 3 or =2*3 + 4. You can specify a general computation such as =A1 + A2 or =A1*A2 + A3. Or, you can specify a formula via an Excel function and a cell range such as =Sum (A1:A3) or =Average (B1:B3).You always place a cell range within a pair of parentheses after a function name— Sum, Average—to tell the function which constants to place within the formula. Excel has over a hundred functions that you can employ as parts of formulas whenever you need them.The functions Sum and Average (as well as many other Excel functions) are applied to a bunch of numbers. As just described, you can enter the individual numbers within a cell range and have the functions Sum and Average perform their calculations over that cell range. Here’s another handy shortcut: Don’t bother to place the numbers within cells, and don’t bother with a cell range when you want to carry out a quickie calculation. Instead, just place the numbers within the formulas. Try =Sum(1,3,5,7) and =Average(1,3,5,7). This method is handy when you want to perform a quick calculation and don’t need to show your work. (You can sum up to twenty-nine numbers this way; always separate them with commas.)Here’s another trick with cells. Try =Sum(A1,A2,A3,B1,B2,B3) or =Average(A1,A2,A3,B1,B2,B3). Excel adds up the six numbers in the six cells. (Cells don’t need to belong to the same column or row.) Try this formula: =Sum (A1,B2,C3). It adds up the first three numbers along the main diagonal of the spreadsheet.Warning: I could not get the prior Sum and Average formulas to work right at first. I referenced empty cells within the formulas and always got zeros as the results. (You can rely on the conditional sum function SumIf discussed later instead of the normal Sum function when you have empty cells and need to skip over them during calculations.)Perform calculations with Cell rangesOften it’s a nuisance to enter a lengthy formula and a challenge to make sure that its cell references are correct. Excel eases the effort with many handy shortcuts. A few useful ways to enter and copy formulas are next discussed.About Cell rangesIn the prior topic, you computed the sum or average of numbers located within a column. You can also sum or average numbers within a row, within a block of cells, and within separate cell ranges.Numbers within a RowPlace any number in cell A1.Place any number in cell B1.Place any number in cell C1.Place any number in cell D1.Place the formula =Sum(A1:D1) in cell E1.Place the formula =Average(A1:D1) in cell F1.Excel takes the numbers in cells A1, B1, C1, and D1 and sums and averages them. Their sum appears in cell E1, and their average appears in cell F1.Numbers within a BlockPlace any number in cell A1.Place any number in cell B1.That is, place numbers within columns A and B of row 1.Place any number in cell A2.Place any number in cell B2.That is, place numbers within columns A and B of row 2.Place the formula =Sum(A1:B2) in cell C1.Place the formula =Average(A1:B2) in cell C2.That is, place the sum and average of the four numbers in column C.Remark: Notice that in formulas spaces aren't allowed; you must type the entire formula as if it were a single word.Excel takes the numbers in the block A1, B1, A2, B2 and sums and averages them. Their sum appears in cell C1, and their average appears in cell C2.Numbers within Separate Cell RangesPlace numbers in cells A1, A2, and A3.Place numbers in cells A5, A6, and A7.Place numbers in cells C1, C2, and C3.You have three different cell ranges, two of them in column A and one of them in column C.Move onto any empty cell.Enter the formula =Sum(A1:A3,A5:A7,C1:C3).Excel adds up the numbers located in the three separate ranges and displays the total sum in the active cell.Remark: Make sure that the individual ranges don’t include empty cells within them. Excel may complain when a cell range includes a cell with no data.In summary, you can fill up as many cells within a row, column, or block as you like and refer to all of them with a cell range, and you can use that cell range within a formula. A cell range in a row or column typically starts with the first used cell within that row or column and ends with the last cell used within that row or column. A cell range as a block of cells typically starts with the cell located at the upper-left corner of the block and ends with the cell located at the lower-right corner of the block.Label Cell rangesYou may want to reuse a particular cell range over and over again within different formulas. It’s a chore to retype that cell range every time. Excel allows you to name a cell range so you only need to enter its label within a formula. Here are the steps to label a cell range:Select the cells to belong to the range of cells.Pick the Name Manager command in the Define Names group on the Formula tab of the Ribbon Bar with either Alt, M, N or Ctrl + F3.Up pops the Name Manager dialog box.Pick the Define Names option.Type a descriptive label in the Name box.A name cannot begin with a number or include spaces.Pick Workbook or a particular worksheet on the Scope drop-down list.Pick a numbered worksheet when you intend to use the labeled range only on that worksheet; otherwise, pick the workbook option when you intend to use the labeled range on different worksheets within the workbook.Optionally, add a comment to describe the labeled range.The comment should either specify the cell locations or their contents.Finally, activate the OK button to accept the cell range name.Label a cell range when you intend to use it over and over or when it’s difficult to enter because it has so many cells or consists of multiple cell regions.Here’s a concrete example of how to name a cell range:Launch your Excel program.The cell A1 in the northwest corner gets selected by default.Hold down a Shift key, and press Down Arrow.Cell A2 gets selected also.Press Right arrow to also select cells B1 and B2.Now you have selected a block of four cells; the block has cell A1 in the northwest corner and cell B2 in the southeast corner.Access the New Name dialog box on the Formula tab with Alt, M, N.Type the label Block4 within the Name box, and then activate the OK button.Now, enter numbers in the 4 cells A1, A2, B1, and B2.Move onto cell A3, enter the formula =Sum(Block4), and finally move off of cell A3.Notice that cell A3 now holds the sum of the four numbers you entered into cells A1, A2, B1, and B2. That is, Excel added up the numbers located within the labeled block of cells.Remark: Of course, you could have used the formula =Sum(A1:B2) instead and not bothered to assign a label to that range. Labels become really useful when the cell range is much bigger or consists of multiple cell regions.Use Named Cell RangesYou may create many labeled cell ranges, and you may not remember their names and what they do. You can pick a labeled cell range from a list as you enter a formula. Here are the details:Enter the data into the desired cells.Move onto the cell to hold the formula.Begin the formula with =Sum(, =Average(, or whatever function you wish to carry out.Display the list of labeled ranges with Alt, M, S.Move onto the desired label and press the Enter key.Excel inserts the specified name.Type the close parenthesis, and then press the Enter key or move off the cell.Excel performs the specified calculation and places the result within the active cell.Manage Cell rangesYou may rename cell ranges or delete them from the list of labeled ranges. Display the Name Manager on the Formula tab with Ctrl + F3 or with Alt, M, N as before. Then progress as follows:Rename Cell range: Activate the Edit button and enter a new name in the Edit Name dialog box.Delete Cell range: Activate the Delete button, and then activate OK in the confirmation box.Perform Calculations with FunctionsYou rely on Excel to carry out calculations for you because Excel knows all the necessary math. The developers of Excel at Microsoft have gathered together all the commonly-used formulas and many advanced formulas as well. Canned formulas in Excel are called Excel Functions; you can use them as parts of your formulas and to carry out standard calculations. You don’t need to know the math behind Excel Functions; the experts at Microsoft have checked the math for you.A function has three parts: the equal sign followed by the name of the function followed by a cell range enclosed within a pair of parentheses. The good news is that you may pick a function from a list, let Excel determine the appropriate cell range and enter the formula in the active cell for you.You can rely on the Insert Function dialog box to simplify the selection and application of a function. This dialog box helps you locate the proper function for the task at hand and also provides information about the appropriate cell range. Use this dialog box to automate the selection and insertion of a function into the active cell. This dialog box provides a step-by-step process. A typical example of its use follows.Move to the cell just below a column of numbers, or move to the cell just right of a row of numbers. Then you can pick a function and have Excel perform the calculation using the numbers in that column or row. Here are the detailed steps:Launch your Excel program.Type any numbers in cells A1, A2, A3, A4, and A5; that is, place data within column A. Or, type any numbers in cells A1, B1, C1, D1, and E1; that is, place data within row 1.Move onto either cell A6 or onto cell F1; that is, make the next cell within the column or row the active cell.Display the Insert Function dialog box with Alt, M, F.Move onto the Select a Category combo box with the Tab key, and move through this combo box with the Arrow keys.Move onto the desired category in this combo box.If you don't know which category to pick, use the All category to display all the available functions.Next, move onto the list of functions in the category with the Tab key.Move onto the desired function in this list with Arrow keys.Excel describes the function and tells you what it does when you move onto its name.Press the Enter key to accept your selection. (Or, press the Esc key to cancel the selected function.)The Function Arguments dialog box appears. This is where you enter or select the arguments for the function.Press the Enter key to accept the default cell range.Excel places the function in the active cell and uses the cell range in the column or in the row to perform the calculation.You must press the Enter key to accept the function and perform the calculation. (You can’t press navigation keys to move off the active cell and accept the function.) Excel performs the calculation and moves the cell cursor onto the cell below the cell that holds the formula.Try this process with the Sum function or the Average function. Check what happens. Are the five numbers added or averaged?Remark: You may manually enter a formula that includes a function name. Do that when you are familiar with the function to save a few steps. For instance, just type =Average(C1:C9) to average the numbers in cells C1 through C9 in column C.Here’s a sample list of commonly-used functions and what they do:Basic FunctionsAverage: Takes the average of the numbers listed in the cell range as in the example =Average(A1:A9).Max: Displays the largest number listed in the cell range as in the example =Max(A1:A9).Min: Displays the smallest number listed in the cell range as in the example =Min(A1:A9)Product: Multiplies together the numbers listed in the cell range as in the example =Product(A1:A9)Sum: Adds up the numbers listed in the cell range as in the example =Sum(A1:A9)Descriptive StatisticsAverage: Calculates the mean of the numbers listed in the cell range as in the example =Average(A1:A9)Median: Calculates the median of the numbers listed in the cell range as in the example =Median(A1:A9)Mode: Calculates the mode of the numbers listed in the cell range as in the example =Mode(A1:A9) (If a data set has more than one mode, the Mode function in Excel calculates only the lower modal value.)Varp: Calculates the variance of the numbers listed in the cell range as in the example =Varp(A1:A9)Stdevp: Calculates the standard deviation of the numbers listed in the cell range as in the example =Stdevp(A1:A9)Remark: In most psychology and social science courses, Varp (not the Excel functions Var, Vara, or Varpa) and Stdevp are used to calculate variance and standard deviation, respectively.If you enter a function manually, then you must type its arguments correctly. On the other hand, if you pick a function from Excel’s list of functions, then you will get help with entering the cell references for the arguments.You may enter function names (and cell references) in lowercase. Excel converts function names (and cell references) to uppercase after you press the Enter key to complete the formula. Excel doesn’t convert a name to uppercase that it doesn’t recognize as a legitimate function. Use this behavior to check whether you entered a function name correctly.Use the other sum FunctionsYou frequently need to add up columns or rows of numbers; you accomplish that with the standard Sum function as illustrated in the prior topic. This function has two handy variants that can simplify the process.AutoSum functionYou add up numbers so often within a column or within a row, Excel automates the task further for you. You may bypass the Sum function and instead rely on the AutoSum function to quickly add up a column or a row of numbers for you. Here’s how it works.Make the cell below a column of cells with data the active cell, or make the cell to the right of a row of cells with data the active cell.This tells Excel which cell values you want to total up—the cells within the column above the active cell or the cells within the row left of the active cell.Invoke the AutoSum function with Alt + =; that is, hold the Alt key down and tap the equal sign in the top row of your keyboard.Excel places the Sum function within the active cell, and then scans the column or row and figures out the cell range to be used in the formula. (You can read the inserted formula to be sure that Excel picked the proper cell range.) Excel also places a dotted border around the cell range.Press the Enter key or the Tab key to accept the AutoSum function, or press the Esc key to cancel the calculation.Excel now shows the total for the column or for the row instead of the AutoSum formula.You may prefer to tell Excel which cells to add up. Here’s how you accomplish that:Select the cells you wish to total.Rely on whatever selection technique you prefer—Shift + Arrow keys to select contiguous cells, Ctrl + G to select non-adjacent cell ranges, or selection shortcut keys.Press Alt + = followed by Enter to invoke the AutoSum function.Excel automatically places the AutoSum formula within the next available cell below a column or to the right of a row and shows the total within that cell.Good news. Select a block of cells and invoke the AutoSum command. Excel totals the numbers within every separate column of the block and places the results within the cells below the respective columns. This means you can add up many columns at a time!Remark: You can only add up numbers along a column or across a row with the AutoSum function. Rely on the Sum function when you need to add up all the numbers within an entire block of values. For instance, rely on =Sum(A1:B4) when you need to add up the eight values within cells A1 through A4 and cells B1 through B4.The function AutoSum scans a column upward and scans a row leftward. The scan stops when AutoSum encounters a cell with a text label instead of a number; only the cells up to that cell are summed.SumIf FunctionConsider this situation. You list all the days of the week throughout a month or throughout an entire year in column A, and you list the money you spent on any particular day in column B. For instance, you could start 2011 with:Column A, Saturday Jan 01, 2011; Column B, $25.50Column A, Sunday Jan 02, 2011; Column B, $16.40Column A, Monday Jan 03, 2011; Column B, $10.60Column A, Tuesday Jan 04, 2011; Column B, $10.60Column A, Wednesday Jan 05, 2011; Column B, $10.60Column A, Thursday Jan 06, 2011; Column B, $10.60Column A, Friday Jan 07, 2011; Column B, $10.60Column A, Saturday Jan 08, 2011; Column B, $25.50You may continue this daily listing of expenses throughout January 2011, for a total of thirty one weekdays in column A and thirty one dollar amounts in column B.You already know how to add up all the expenses for January 2011; namely, place the formula: =Sum(B1:B31) in cell C1. But, how would you add up just the expenses that occurred on a specified weekday? For instance, you may want to add up the expenses only for Saturdays throughout January 2011.You can rely on the SumIf function to specify a condition on which cells in column A to consider when you add up the dollar amounts in column B. Place the formula =SumIf(A1:A31, Saturday, B1:B31) in cell C2. This formula has three parts separated by commas: the range of weekdays A1:A31; a specified criterion Saturday; the range of dollar amounts B1:B31. Here's what the SumIf function does in this situation: It browses through cells A1 through A31 in search of Saturday dates; it skips over cells with the other weekdays. It marks down (behind the scenes) the cells in column A with Saturday dates; finally, it adds up all the dollar amounts in column B with Saturday dates.Here's another example of a conditional sum. Use the SumIf function to determine the number of babies born at local hospitals in a specified year. Type years ranging from 2000 to 2008 in random order in cells A2 through A20 in column A; column A holds the CellRange. Place hospital names in cells B2 through B20 in Column B. Type the number of births in random order in cells C2 through C20 in column C; column C holds the SumRange. Every row of your table has three parts: birth year in column A, birth hospital in column B, and number of births in column C.Now pick a year, say 2006, for which you want the total of all births at the listed hospitals; the specified year value becomes your cell selection criterion. Place the year value 2006 in cell D2. Put the SumIf formula =SumIf(A2:A20, D2, C2:C20) in any cell you like on the worksheet, perhaps in cell E2, and then hit Enter. Excel adds up the number of births listed in every row with the year value 2006. That is, Excel totals all the births in 2006 at the listed hospitals.You may, of course, place a different year value in cell D3 and its SumIf formula in E3 to total the number of births at the listed hospitals in year 2007. Experiment with a few year values until you understand how the SumIf function works.In summary, you list values (weekdays) in the Cell Range column; pick a value to match (Saturday) and either place it in its own cell or directly in the SumIf formula; and place the numbers to be summed (dollar amounts) in the SumRange column. You may place the SumIf formula in any convenient cell on the worksheet. Excel adds up only the numbers in the SumRange Colum that matches your specified value in the CellRange column.Here’s another example of the SumIf function in action. In this example you find the cells in the cell range E1 to E6 that hold the number 165. For those cells, you sum up the corresponding numbers in cells F1 to F6. Here are the steps:Enter the following data into cells E1 to E6: 114, 165, 178, 143, 130, 165.Enter the following data into cells F1 to F6: 10, 20, 30, 10, 20, 30.Place your formula =SumIf(E1:E6, 165,F1:F6) in cell F7.If the criterion 165 matches any item in the CellRange E1:E6, the SumIf function adds up the numbers in the corresponding cells in the SumRange F1:F6. The criterion 165 is met in only the two cells E2 and E6; consequently, only the numbers in cells F2 and F6 are summed. The sum of 20 and 30 is 50; thus, the sum 50 appears in cell F7.Remark: Notice that you placed the criterion (the year value 2006) in cell F7 in the first example. However, you placed the criterion (the number 165) within the SumIf formula directly in the second example. Both methods have their advantages.AutoCalculate SumYou may wish to check the sum of a bunch of cells quickly and don’t need to save the result. Just select those cells; rely on whatever selection technique you prefer—Shift + Arrow keys to select contiguous cells, Ctrl + G to select non-adjacent cell ranges, or selection shortcut keys. Excel places their total on the Status Bar. Read the Status Bar to get the sum. Deselect the cells, and then the total vanishes from the Status Bar.Access note: Your screen reader has a hot key that will read the Status Bar for you.Find and Correct Errors within formulasIt’s easy to make an error when you enter a formula. You may mistype a function name or a cell range name; you may specify the wrong cell range to work on; and you may mistype cell references. An error within a formula can cause errors within other formulas that refer to the mangled formula. Luckily, Excel offers many ways to detect and correct common problems. You can correct errors one by one; you can run the Error checker; and you can follow cell references to make sure errors aren’t included within other formulas on your worksheet. The next few topics present the options to check your formulas.Show Formulas within CellsEnter a formula within a cell, and then move off that cell. Excel performs the calculation and displays the result of the calculation within the cell instead of the actual formula. As mentioned earlier, you can move back onto the cell and press F2 to enter Edit mode and display the formula. However, it’s a nuisance to enter Edit mode just to reveal the formula. It would be very convenient if you could toggle (turn on) the display of formulas, and then browse through a worksheet with navigation keys in search of formulas.Activate the Show Formulas option in the Formula Auditing group on the Formula tab with Alt, M, H. Then, as you browse through a worksheet, the formulas are displayed instead of the computed values. Check for missing parentheses, mistyped function names, etc., and make appropriate corrections.You can at any time press Alt, M, H again to turn off this option; Excel will once again display values instead of formulas.Correct Errors One by OneExcel checks the format of a formula as you enter that formula. Excel adds a visual cue to a cell when it notices an error. For example, Excel puts a small green triangle in the upper-left corner of a cell that holds a mangled formula. Excel also places three or four cryptic letters preceded by a number sign within a cell when the error may cause bad problems. Here’s a list of common error messages:Remark: Notice that an error message within a cell always begins with a number sign; that way, you can distinguish an error message from your text.#div/0: You tried to divide a number by zero or by an empty cell.#name: You used an undefined cell range name within the formula. (You may have just mistyped the name; that’s why you should pick a name from the list of defined cell ranges.)#n/a: The formula refers to an empty cell; thus, there’s no data. (Revise the formula or enter a number or formula in the empty cell.)#null: The formula refers to an ambiguous cell range. (Make sure that you enter the cell range correctly.)#num: An argument in a formula is invalid.#ref: The cell or cell range that the formula refers to isn't located there.#value: You used a function incorrectly within the formula. (Make sure that the function uses the correct argument and is typed correctly.)Move onto a cell with a green triangle; that is, move onto a cell that holds a formula with an error. An Error button appears beside that cell. There’s a dropdown list on the Error button which suggests ways to correct the formula.Access note: The Error button is unusable with the keyboard.Run the Error CheckerExcel includes an error checker that can find errors within formulas and suggest ways to correct them. Excel displays the Error Checking dialog box when it encounters a cell that may hold a formula with an error. That dialog box describes the potential error.Move onto the Formula Auditing group on the Formulas tab. Move onto the Error checking split button and then press the Enter key. A dialog box appears.Access note: This dialog box is unusable with current screen readers.Formula GlossaryYou should remember these terms because they are used throughout this tutorial.FormulaA type of cell entry that instructs Excel to perform a calculationFunctionA formula already defined by Excel (Excel includes over a hundred useful functions.)ReferenceA single cell or cell range used to perform a calculationOperatorA symbol ( + , -, *, /) which denotes a basic binary calculationConstantA number or label that doesn’t changeCHAPTER 9: PERFORM ARITHMETIC WITH CELL FORMULASThe prior chapter discussed various ways to perform calculations in Excel. You learned to perform particular computations such as =2*3 + 4 and general computations such as =A1*A2 + A3 where cells A1, A2, and A3 hold the data to be used within the formula.As mentioned in that chapter, the usefulness of Excel derives from its built-in formulas (called functions) and your personal formulas that you create to perform uncommon computations. Formulas may be lengthy, complicated, and difficult to retype; all of that can cause problems when you want to use the same formulas repeatedly throughout a workbook or on a worksheet. This chapter details how to copy formulas and explains why they still work when moved.There are two types of cell references, relative and absolute, and they have different uses when you copy formulas as discussed in the next two sections. Relative and absolute cell references are used to specify cell ranges and to tell formulas where to find their data.Relative Cell ReferencesCell references that change when you copy a formula are called Relative References. Do the following experiment to investigate how and why Excel uses relative references by default within formulas.Launch your Excel program. Enter data within cells A1, B1, and C1 in row 1; cells A2, B2, and C2 in row 2; and cells A3, B3, and C3 in row 3. Move onto cell A5, and enter the formula =Sum(A1:A3). Move onto cell E1, and enter the formula =Average(A1:C1). Now you have a square array with nine numbers: a Sum formula in column A located two cells below the column data and an Average formula in row 1 located two cells right of the row data.Move onto cell A5 and use the Copy command Ctrl + C to place its formula onto the clipboard. Next, move onto cell B5, and use the Paste command Ctrl + V to put the formula into cell B5, and finally move onto cell C5, and use the Paste command Ctrl + V to put the formula into cell C5. Now you have formulas within columns A, B, and C.Move back onto cell A5, and press F2 to read its formula. Notice the cell range A1:A3 within the formula; it is the cell range you originally typed within your Sum formula in column A. Move onto cell B5 and cell C5 in turn, and press F2 to read the formula within each cell. What do you notice?Move onto cell E1, and place its formula onto the clipboard. Then paste that formula into cells E2 and E3. What do you notice about the pasted formulas?When you copy a formula into another cell, Excel automatically updates the cell references within the formula. For instance, the formula below the data in column C is =Sum(C1:C3); the formula in row 3 right of the row data is =Average(A3:C3). How does Excel know what cell references to use within a moved formula?You may place a formula in any empty cell; it needn’t be near the data it works on. In the prior example, I placed the Sum formula two cells below the data it uses, and placed the Average formula two cells right of the data it uses. I could have placed either formula in any empty cell as far away as I like from its data.Excel does two things when you use a formula: It gets the data from a referenced cell, and it remembers the distance to that cell. Here’s how it works.The cell that holds the formula is home base. Excel keeps track of how many cells it needs to move horizontally and vertically to reach a referenced cell. These two distances from the formula's cell location are the coordinates of that referenced cell. Notice that the coordinates of a referenced cell are measured relative to the cell that holds the formula. Consider the prior example. Cell B5 holds the formula =Sum(B1:B3). To reach cell B3, Excel moves 0 cells horizontally and moves 2 cells up; thus, its relative position to cell B5 is the ordered pair (0, 2).Copy a formula into another cell. Excel assumes that the cell references within the formula are relative to the new location of the formula and updates them using the location data it figured out for the original formula location. In the prior example, the formula =Sum(A1:A3) in cell A5 has (0, 2) as the relative location of cell A3. The formula =Sum(C1:C3) in cell C5 also has (0, 2) as the relative location of cell C3.Try this: Copy the formula in cell A5 into cell B4 instead of cell B5. Excel complains, and cell B4 contains a mangled formula because it can’t locate the data it needs. (Excel shows #ref! instead of a computed sum; Excel looks for data above cell B1 and there’s no data there because there's no cell above B1.)In summary, copy a formula you need to use again; you don’t need to retype it. Place it in a cell with the same relative position to its data as the original formula. Excel adjusts cell references within the copied formula to match the current active cell location. If you place a formula in the wrong place, then Excel will either complain or produce incorrect results.Place a formula below a column of data. Place that formula on the clipboard. Select the cells below the other columns and paste the formula into them. Excel updates their cell references for you. This process is a convenient way to use the same formula over and over, no retyping required. You can, as in the prior example, also duplicate a formula next to a column so it will apply to the adjacent rows of data.Absolute Cell ReferencesBy default, Excel uses relative cell references when you copy a formula into a different cell. However, there are occasions when you need to use a formula with its original cell references intact after you copy it to a new location on a worksheet. For instance, you may want to use its value in another formula.Cell references that don’t change when you copy a formula are called Absolute References. Here’s a typical example in business when you need to use both relative and absolute references within the same formula. A company wants to give each employee a bonus this month. The bonus for each employee is calculated by multiplying their salary for the month times the bonus percentage.Place the bonus percentage in cell A1, and place salary amounts in Column B. Place the formula =B1*$A$1 in cell C1. This formula uses one relative reference B1 and one absolute reference $A$1. Type this formula down column C, adjusting for the cell number in Column B. What happens?Excel changes the row number but leaves the bonus rate fixed. For instance, cell C2 holds the formula =B2*$A$1, cell C3 holds the formula B3*$A$1, and so on. This is what you want: The salary values change, but the bonus rate remains the same.Notice the dollar sign before the column letter and before the row number. The dollar sign tells Excel to keep the same column letter and keep the same row number. A column letter or a row number with a dollar sign before it indicates an absolute column or row reference; that is, the column or row (or both) doesn’t change when you copy a formula that contains that cell reference.Mixed Cell ReferencesA cell reference, cell range, or a formula may include both relative and absolute parts. Place a dollar sign $ before a column letter to keep that column fixed, and place a dollar sign character $ before a row number to keep that row fixed. For instance, a formula with the cell reference $A1, when copied to a new cell location, will always refer to column A, but the row number will move the same distance the formula moved.You can type the necessary dollar signs yourself, or you can pick the required combination from a list of cell reference types. Begin to type a formula within a cell. Type a cell reference. Press the F4 key once after the cell reference to switch from relative to absolute. Repeatedly press F4 to pick absolute, mixed with fixed row, mixed with fixed column, and relative.In summary, place a dollar sign $ before a column letter or a row number (or both) to make these reference locations absolute. Only the part of a cell reference directly after a dollar sign becomes an absolute (or fixed) location. Place a dollar sign before its column letter and before its row number to keep the entire cell reference fixed. Press F4 after a cell reference while you enter a formula to change its reference type.References on Different WorksheetsYou may have a workbook with multiple worksheets, and you may need to refer to cells on different worksheets. For instance, you may have a workbook with twelve worksheets which show business activity from January through December. You may wish to summarize the year's business results on another worksheet: annual revenue, annual expenses, and so on. Formulas that gather data from cells on the other worksheets belong on a summary worksheet. It’s easy to refer to a cell on a different worksheet: just place the name of the worksheet and an exclamation mark before the cell reference. For instance, Sheet2!B4 refers to cell B4 on worksheet 2.Here’s an example of multiple cell ranges on different worksheets to calculate a grand total. The formula =SUM(Sheet1!B3:B10,Sheet2!B3:B10,Sheet3!B3:B10) adds up the values in the cell range B3:B10 on Sheet1, Sheet2, and Sheet3 of a workbook, and gives you the grand total of all these values. Place the formula on any unoccupied cell on any worksheet .A user would likely rename the twelve worksheets in an annual business summary workbook as January through December. June!B7 would refer to cell B7 on the June worksheet; October!C10 would refer to cell C10 on the October worksheet; and so on.Recall that a worksheet name and a cell reference on that worksheet are separated by the exclamation mark. Also, a cell reference on any worksheet can be relative, absolute, or mixed. It can be a chore to type a cell reference on another worksheet within a formula on the current worksheet. Alas, there are no keyboard shortcuts available.A mouse user can more readily enter a cell reference on a different worksheet.Begin to type the formula in the active cell on the current worksheet.Click the desired worksheet tab at the bottom of the current worksheet.That worksheet appears onscreen.Click the desired cell.The cell reference and sheet name are placed within the formula.CHAPTER 10: ENTER CELL DATA AUTOMATICALLYThis tutorial has used very small examples of worksheets—worksheets with three columns and three rows of data. I took this approach to minimize extraneous complications so you could focus on the concepts being presented. But, in reality, a worksheet usually has hundreds, and even thousands, of populated cells. For instance, a timesheet for a year would include the 52 weeks of the year and list the seven days Monday through Sunday for every week. Thus the labels for Monday through Sunday must be entered fifty-two times—a lot of repetitious typing! Or, you may need to develop a scope of work schedule for a multi-year project and need to enter the months January through December for multiple years. Again, lots of repetitious typing required! As another example, a teacher might use a worksheet as a grade book for a class and need to enter the names of all the students for every test and quiz; a class may have twenty-five or more students. Again, this means lots of repetitious typing.This chapter presents various ways to automate the entry of cell data. You can copy a formula into selected cells to perform the same calculation. You can tell Excel to fill selected cells with predefined labels (e.g., weekdays or months). You can even create your own list of predefined labels (e.g., student names, part numbers) and have Excel fill selected cells with them. That is, the two Excel features AutoComplete and AutoFill let you enter items without having to actually retype them.AutoComplete Column ItemsTypically, you enter items within a column. You type an item and then press the Enter key to move down to the next cell. Then you begin to type the next item.There are many situations when you must enter the same items multiple times throughout the current column. Begin to type an item. Excel scans the items you already typed within the column, before and after the active cell. If the letters you just typed uniquely match the start of a prior item, then Excel fills the active cell with that item. Then you have two options: either press the Enter key to accept Excel's suggested entry or continue to type your entry and ignore Excel.Try this:Launch your Excel program.Cell A1 has keyboard focus as usual.Enter the name Jack Jones and press the Enter key.You move onto cell A2.Enter the name Jane Jones and press the Enter key.You move onto cell A3.Just type the two letters J and a in cell A3 and notice what happens.Excel does nothing because there are two items that begin with the letters J and a.Next type the single letter c or the single letter n and notice what happens.Excel populates cell A3 with Jack Jones or Jane Jones. Excel has enough letters to decide between them. Notice that the completed entry has the same capitalization as the copied item.Or, continue to type James Smith if you want that entry instead.Here are a few caveats to keep in mind. Excel scans upward as well as downward from the active cell; it displays an entry in the active cell as soon as it encounters a unique match. Excel halts its upward or downward scan when it encounters an empty cell in that direction; thus, items before or after an empty cell aren't checked for matches. In addition, Excel skips over those cells that contain just numbers, dates, or times; that is, Excel checks only those cells that contain either text or a combination of text and numbers. Excel completes an entry only when the text cursor is at the end of the cell contents. By the way, items that are repeated within a row aren't automatically completed.Remark: You may, as you enter items within a column, press the SpaceBar to enter a space character within an otherwise blank cell so Excel doesn't interrupt its scan when it reaches that cell. (You may enter any nonprinting character within an otherwise empty cell to accomplish the same thing. Don't enter a character that prints because you would need to find and delete it before you print your worksheet.)This feature, suggest cell items, helps a lot when there are many repetitive text items within columns. It can be an annoyance in other situations—cells are populated with no rhyme or reason. You can turn the AutoComplete feature off when not needed:Start up your Excel program.Press Alt, F, I to display the two panes of Excel Options.Move onto the advanced category on the left pane with vertical Arrow keys.Press the Tab key to move onto the right pane.Press the Tab key repeatedly to move onto the check box labeled Enable AutoComplete for Cell Values, and then tap the SpaceBar key to clear this check box.Press the Tab key repeatedly to move onto the OK button, and then tap the Enter key to accept your change.You return to the active workbook.Hence forth, Excel will not prompt you with suggested cell items. Mark the Enable AutoComplete for Cell Values check box again when you wish to use the AutoComplete feature on a different worksheet.In summary, AutoComplete anticipates what you might want to enter within the active cell based upon text you previously entered within its column. AutoComplete reduces errors and speeds up your work. AutoComplete comes into play only when you're entering text within a column. You are on your own when you enter numbers, dates, or times.AutoComplete displays an entry within the active cell only when you start a new entry in the same column that begins the same way. If you want to enter a different item that begins the same way as another item in the column, just continue to type the new item (and ignore Excel's suggestion); finish the entry with a press of the Enter key.AutoFill Cell ItemsSometimes you need to repeat items within a column or across a row. For instance, you may want to sum multiple columns of data or multiple rows of data. You must place an AutoSum formula below every column or next to every row.The repetition of an item across a row or down a column occurs so often that Excel has two handy hot keys to perform these tasks.Repeat an Item across a RowFollow these steps to duplicate an item throughout a row of selected cells:Type an item (number, label, or formula) within a cell.Then hold down the Shift key and press the Right Arrow key to select the active cell as well as cells to the right.AutoFill the selected row of cells with a press of Ctrl + R.Press any navigation key to deselect the cells and accept the pending AutoFill action. (Or, press the Del key and then any navigation key to cancel the operation.)Repeat an Item down a ColumnFollow these steps to duplicate an item throughout a column of selected cells:Type an item (number, label, or formula) within a cell.Then hold down the Shift key and press the Down Arrow key to select the active cell as well as cells downward.AutoFill the selected column of cells with a press of Ctrl + D.Press any navigation key to deselect the cells and accept the pending AutoFill action. (Or, press the Del key and then any navigation key to cancel the operation.)Remark: You can only AutoFill cells to the right within a row using Ctrl + R or downward within a column using Ctrl + D. Rely on the AutoFill commands to fill cells to the left within a row or upward within a column. Also, notice that AutoFill replaces the contents of selected cells; so be careful not to overwrite wanted data.AutoFill Cell CommandsExcel has a list of AutoFill options located in the Edit group of the Home tab on the Ribbon Bar. You can reach that list with Alt, H, F, I.Remark: AutoFill commands, which are keyboard commands, produce the same results as the fill handle employed by a mouse user. The Fill handle is a small black square located in the lower-right corner of a selection of cells. When a user points at the fill handle, the mouse pointer changes to a black cross. A user selects the cells to use as a basis for filling out additional cells, and then drags the fill handle across or down the additional cells to be filled in.AutoFill Adjacent Cells with the Same ItemSometimes you need to copy a cell entry into an adjacent cell. Rely on the next procedure for complicated or long cell content to avoid typos.Make an adjacent cell the active cell; that is, move one cell left or right or up or down.Display the AutoFill list with Alt, H, F, I.Pick the matching fill option: left or right or up or down.Excel fills the active cell with the content (number, label, or formula) of the adjacent cell.You may bypass the AutoFill list when you want to fill an adjacent cell to the right or below.Make an adjacent cell the active cell; that is, move one cell right or down.Use the matching hot key, Ctrl + R or Ctrl + D.Excel fills the active cell with the content (number, label, or formula) of the adjacent cell. This method, move right or down and press a fill hot key (Ctrl + R or Ctrl + D), lets you quickly copy a cell's content into an adjacent cell to the right or below.You may also select multiple cells left or right or up or down and fill them:Left repeats the contents of the first cell throughout the selected cells to the left of it.Right repeats the contents of the first cell throughout the selected cells to the right of it.Up repeats the contents of the first cell throughout the selected cells above it.Down repeats the contents of the first cell throughout the selected cells below it.AutoFill Adjacent Cells with Sequential ItemsSo far, you have AutoFill copy previously entered items into other cells. AutoFill can do more: enter sequential names of days or names of months; enter sequential dates or times; and enter sequential numbers that have a simple pattern.Display the list of AutoFill options with Alt, H, F, I. Move onto the option labeled Series and press the Enter key. A dialog box appears. Here are the details about that box.This dialog box at the top has three columns. The left column, labeled Series In, has two radio buttons named Rows and Columns. The middle column, labeled Type, has four radio buttons named Linear, Growth, Date, and AutoFill. The right column, labeled Date Unit, has four radio buttons named Day, Weekday, Month, And Year.At the bottom of this dialog box reside the OK and Cancel buttons. Over them are the two text boxes named Step Value and Stop value. Over them resides the single item named Trend.Notice that the text box Step Value has keyboard focus by default, and it shows the value 1 by default. Also notice that Shift + tab places keyboard focus on the middle column and then on the left column; you skip over the right column. The right column remains unusable (grayed out) unless you first select the Date option in the middle column.You can quickly enter items that form a sequential pattern with the AutoFill option located at the bottom of the middle column. Here are the details.AutoFill Tasks with ExamplesYou can carry out many different tasks with the AutoFill dialog box. The common tasks are simple to perform, but other tasks are more sophisticated. For the sake of clarity, tasks are presented below in separate examples.The AutoFill OptionSometimes you need to list the days of the week or the months of the year across a row or along a column. Follow these general steps to accomplish that:Start your Excel program.Move onto the cell where you want to begin the series of items.Type the initial item–for instance, Monday for days, January for months—in the active cell.Hold down the Shift key and select the other cells you wish to fill.You can select either part of a row or part of a column.Display the list of AutoFill options with Alt, H, F, I.Move onto the Series item with Arrow keys, and then press the Enter key.As mentioned, a dialog box with three columns of options appears.Move onto Column 1, labeled Series In, with the Shift + Tab key, and notice that Excel checked the proper option (row or column) to be filled.Next, move onto Column 2, labeled Type, with the Tab key, move onto its fourth item AutoFill with the Down Arrow key, and press the Enter key to accept that choice.You return to the active worksheetCheck out your handiwork. The specified items, days or months, are listed within the portion of the row or column that you selected. Also, notice that Excel followed your capitalization of the item that you initially entered. That is, capitalize only the initial letter of the item or enter the entire item in lowercase or uppercase, then Excel does the same for the items that it enters. There's more: You could have selected as many cells as you like, and Excel would have filled all of them sequentially. For instance, start with Sunday and select fourteen cells total; Excel enters Sunday through Monday and enters Sunday through Monday again. Experiment and enjoy!You just entered sequential days of the week; you only needed to enter a single weekday because Excel knows the pattern for the day names–Sunday followed by Monday followed by Tuesday and so on. Likewise, Excel knows about dates and times and only a single entry establishes a recognizable pattern. If you enter a single number to begin a sequence of numbers, Excel will just repeat that number. You must enter two sequential numbers so Excel can guess the pattern you want. Study the table below to find out the best ways to establish recognizable sequential patterns for Excel to AutoFill for you. In this table, items that are separated by commas are contained in separate adjacent cells on the worksheet. The items you must enter are followed by the items that Excel enters for you.1, 2; 1, 2, 3, 4, 5, 6, …8:00 AM ; 8:00 AM, 9:00 AM, 10:00 AM, 11:00 AM, 12:00 PM, 1:00 PM, 2:00 PM, 3:00 PM, …8:15 AM ; 8:15 AM, 9:15 AM, 10:15 AM, 11:15 AM, 12:15 PM, 1:15 PM, 2:15 PM, 3:15 PM, …Mon; Mon, Tue, Wed, Thu, Fri, Sat, Sun, …Monday ; Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday, …Jan; Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec, …25-Jan ; 25-Jan, 26-Jan, 27-Jan, 28-Jan, 29-Jan, 30-Jan, 31-Jan, 1-Feb, 2-Feb, 3-FebRemark: I actually entered Jan 25 as the start date; Excel changed the date format.January ; January, February, March, April, May, June, July, August, September, October, November, December, …15-Jan, 15-Mar; 15-Jan, 15-Mar, 15-May, 15-Jul, 15-Sep, 15-Nov, 15-Jan, 15-Mar, 15-May, 15-Jul, …1-Aug, 2-Aug; 1-Aug, 2-Aug, 3-Aug, 4-Aug, 5-Aug, 6-Aug, 7-Aug, 8-Aug, 9-Aug, 10-Aug, …Remark: You must tell Excel whether you want to change the month abbreviation or the date; that's why you must specify two initial items.Quarter 1; Quarter 1, Quarter 2, Quarter 3, Quarter 4, Quarter 1, Quarter 2, Quarter 3, Quarter 4, …Remark: You may use either Q or Qrt instead of Quarter.1st; 1st, 2nd, 3rd, 4th, 5th, 6th, 7th, 8th, 9th, 10th, …This table illustrates many ways to employ the AutoFill option. However, it can't generate every date sequence. You must rely on the Date option instead of the AutoFill option to generate other commonly-used date sequences. For instance, you often need to list just the weekdays Monday through Friday; that's when you use the Date option and its Date units, as discussed below.The Linear and Growth OptionsYou may have noticed that the two text boxes Step Value and Stop value were skipped over (disabled, greyed out) when you used the AutoFill option. Now you get to use them. A little basic math comes first. There are two basic types of number progressions: arithmetic and geometric.Here is a typical arithmetic progression: 1, 3, 5, 7, 9, and so on. You start with the initial number 1 and repeatedly add 2 to it to get the subsequent numbers. You can use any number as the initial number and any number as the common difference between consecutive terms within an arithmetic progression.Here is a typical geometric progression: 1, 2, 4, 8, 16, and so on. You start with the initial number 1 and repeatedly multiply it by 2 to get the subsequent numbers. You can use any number as the initial number and any number as the common ratio between consecutive terms within a geometric progression.The number you enter within the active cell serves as the initial term of your progression. The number you specify for the Step Value when Linear or Growth is picked as the Type determines how Excel will calculate successive cell values. Linear creates a series that is calculated by adding the value in the Step value box to each cell value in turn. Growth creates a series that is calculated by multiplying the value in the Step value box by each cell value in turn. (In contrast, Date generates date values incrementally by the value in the Step value box and determined by the unit specified under Date unit.)You may want a progression of numbers to not exceed a maximum value; that is, not get too big. You can enter a number within the Stop value text box that Excel should not exceed. For instance the geometric progression 1, 2, 4, 8, … has 33554432 as its 26th term; that is, 2 raised to the power 25 = 33554432. However, you may want to halt this progression before it reaches a value of 1,000,000. With this restriction the final value becomes 524288; that is, 2 raised to the power 19.Remark: This example shows how growth of bacteria can get out of hand very quickly. The population grows from about half a million to about 33 million in just 6 steps; 6 = 25 – 19.You just learned that 2 raised to the 25 power = 33554432; that is 2 multiplied by itself 25 times = 33554432. How big do you think 3 raised to the 25 power could be? Let's find out:Start your Excel program.Move onto the cell where you want to begin the geometric progression.Enter the initial value 1 in the active cell.Hold down the Shift key and select the other 25 cells you wish to fill.You can select either part of a row or part of a column.Display the list of AutoFill options with Alt, H, F, I.Move onto the Series item with Arrow keys, and then press the Enter key.A dialog box with three columns of options appears.Move onto Column 1, labeled Series In, with the Shift + Tab key, and notice that Excel checked the proper option row or column to be filled.Next, move onto Column 2, labeled Type, with the Tab key, move onto its second item Growth with the Down Arrow key.Finally, move onto the Step Value text box with the Tab key, press the Del key to erase its default value, enter 3 as the value, and press the Enter key to activate the OK button.You return to the active worksheet.Again, check out your handiwork. The number 3 raised to the 25th power = 282429536481 which is a lot bigger than 2 raised to the 25th power = 33554432. How much bigger you ask. Enter the formula =282429536481/33554432 into Excel to find out. This ratio is equal to about 8417; that's a huge increase in size. The power grows over eight thousand times bigger when you switch from base 2 to base 3 with the same exponent (25)!The Date OptionRecall that Column 1 shows whether you selected a portion of a row or of a column where you intend to place your series of items. So far, you tried out three items—Linear, Growth, and AutoFill—from Column 2; Column 3 remained greyed out for these options. When you use the Date option from Column 2 the date units from Column 3 become available to you.Recall that the four date units are day, weekday, month, and year. The next four examples illustrate how date units are used to specify sequential dates.To begin with, launch Excel. As usual, cell A1 has keyboard focus. Please enter the date 6-Dec-10 within that cell and press the Enter key. Notice the date format: day number followed by month abbreviation followed by year number. Use this date format for now; other date formats are presented in another chapter.Move back onto cell A1 and use Shift + Right Arrow to select cells A1 through J1; that is, select the first ten cells in Row 1. Repeat all of these steps for the four examples below.Try the Day OptionDisplay the AutoFill list with Alt, H, F, I.Display the Series dialog box.Change the Type to Date, change the Date Unit to Day, and then press the Enter key.Admire your work. You may have a mess as I did. The dates 6-Dec-10, 7-Dec-10, 8-Dec-10, and 9-Dec-10 show up properly, but the dates from 10-Dec-10 through 15-Dec-10 may show up as number signs rather than dates. This happens when columns are too narrow to hold the entire date. If this happens to you, select all the columns with dates and widen them:Display the Format list with Alt, H, O.Display the Column Width dialog box, and specify a column width value of 10 or more.Now start over. Go back and select 30 cells instead of 10 cells. Repeat the rest of the above steps.Again admire your work. Notice that Excel continues the date sequence properly into January 2011.Try the Weekday OptionDisplay the AutoFill list with Alt, H, F, I.Display the Series dialog box.Change the Type to Date, change the Date Unit to Weekday, and then press the Enter key.I used the date 6-Dec-10 because it occurred on a Monday. The dates 6-Dec-10, 7-Dec-10, 8-Dec-10, 9-Dec-10, and 10-Dec-10 show up as expected but the dates 11-Dec-10 and 12-Dec-10 are skipped. This date unit only shows the weekdays; that is, it skips over Saturdays and Sundays.Which date unit—Day or Weekday—you use depends on the Excel task at hand. You would likely use the Weekday option for class schedules because most classes occur on weekdays. On the other hand, you would likely rely on the Day option for personnel timesheets for businesses that stay open all week long.Try the Month OptionDisplay the AutoFill list with Alt, H, F, I.Display the Series dialog box.Change the Type to Date, change the Date Unit to Month, and then press the Enter key.Notice that Excel keeps the same day number but steps through the month abbreviations and through the year numbers. You get 6-Dec-10, 6-Jan-11, 6-Feb-11, 6-Mar-11, 6-Apr-11 as the initial five dates.Try the year OptionDisplay the AutoFill list with Alt, H, F, I.Display the Series dialog box.Change the Type to Date, change the Date Unit to year, and then press the Enter key.Notice that Excel keeps the same day number and the same month abbreviation but steps through the year values. You get 6-Dec-10, 6-Dec-11, 6-Dec-12, 6-Dec-13, and 6-Dec-14 as the five initial dates.Try the Step Value OptionDisplay the AutoFill list with Alt, H, F, I.Display the Series dialog box.Change the Type to Date, and change the Date Unit to Day.Change the Step Value to 2, and then press the Enter key.As expected you get 6-Dec-10, 8-Dec-10, 10-Dec-10, 12-Dec-10, and 14-Dec-10 as the initial five dates. Now try the following steps:Display the AutoFill list with Alt, H, F, I.Display the Series dialog box.Change the Type to Date, and change the Date Unit to Weekday.Change the Step Value to 2, and then press the Enter key.You get 6-Dec-10, 8-Dec-10, 10-Dec-10, 14-Dec-10, 16-Dec-10, and 20-Dec-10. Notice the initial three dates fall on Monday, Wednesday, and Friday as expected, but then Excel gets confused. Excel skips the next Monday, and instead gives Tuesday's date; the dates are incorrect from then on. (I don't know a simple workaround for this Excel bug.)You must rely on the Weekday option with a Step value of 1. Then you can delete those columns that show Tuesday and Thursday dates. You can also place an empty column between adjacent weeks to make them easier to read.AutoFill with Personal ListsYou can harness the power of Excel to meet your needs with personal lists of items that Excel can use to AutoFill adjacent cells. For instance, a teacher can create a list of students within a class; a marketing manager can create a list of sales regions; a retailer can create a list of products in inventory. The possible uses of custom lists of AutoFill items are only limited by the imagination of Excel users.There are two ways to create a custom list of AutoFill items: You can either copy them off an extant worksheet, or you can type them manually. You can't edit or delete a standard AutoFill list used by Excel, but you can edit or delete a personal list. Note that a custom list can only contain text or text mixed with numbers as its items.Numbers, used as actual numbers (not as phone numbers, house numbers, zip codes), aren't allowed as list items, but numbers formatted as text are permitted. (Number formats are presented in the next chapter.)Create Custom AutoFill ListsAs mentioned, there are two ways to create a personal AutoFill list. One method is more keyboard-friendly than the other. Follow these steps to create a custom AutoFill list with the keyboard:Start your Excel program.Display the Excel Options with Alt, F, I.On the left pane, the Popular category has keyboard focus.Press the Tab key to move onto the right pane.Press the Tab key repeatedly until you reach the Edit Custom Lists option, and then press the Enter key.In the Custom lists box, pick NEW LIST, and then type the items in the List entries box, starting with the first entry.Remember to press ENTER when you finish typing an item.Activate the Add button when you finish the list, and then activate the OK button twice.You can insert your personal AutoFill list on any worksheet. As usual, type the start item in the desired cell, select the cells to be filled, and use the AutoFill option to fill in the selected cells.Edit or delete Custom AutoFill ListsYou may notice an error within a list item when you use its list. You can go back to the list and edit items at any time. Also, you may no longer need an old list; you can delete it.Start your Excel program.Display the Excel Options with Alt, F, I.On the left pane, the Popular category has keyboard focus.Press the Tab key to move onto the right pane.Press the Tab key repeatedly till you reach the Edit Custom Lists option, and then press the Enter key.In the Custom lists box, move onto the list that you want to edit or delete and then pick the appropriate command.CHAPTER 11: FORMAT CELLSSo far you may type any numbers within any cells; by default, there are no restrictions on the numbers you may place within cells. Often this freedom can cause you problems: You may place currency amounts (numbers that stand for money amounts) where dates ought to go; you may enter percentages where decimals should go; and you may enter text where only numbers belong. It would be handy if you could specify the content type and other attributes for cells. This chapter discusses cell formats and how you can apply them to cells. Cell formats help you prevent entry errors and let you specify the visual appearance of cell data.Number Format OverviewExcel distinguishes between the format of a cell and the format of the content of a cell. You may apply a format to a cell whether empty or occupied! For instance, you may apply the Date format to a cell to indicate that only a date—not a percentage, fraction, or label—should go in that cell. You can also have Excel format the date text for you. For instance, apply a font size, font type, and a font color to the date text you enter. Excel can even apply a date style to the date text that you enter—for example, Saturday, January 01, 2011 or 1/1/11.Excel can also automatically apply a cell format. You may have noticed in Chapter 10 that Excel applied the Date format to a cell when you entered a date within that cell via the AutoFill command.Every cell on a worksheet may have a different cell format. Usually, however, you format only those cells that you plan to use, and you apply to them the formats appropriate for the data you intend to place within them. The unused cells on a worksheet continue to possess the General format so you can enter any kinds of numbers within them. Excel provides many different number formats that you can apply to cells. They let you standardize their contents and how they are displayed on a worksheet.Number Format CategoryYou adjust cell size (column width and row height) with the commands found on the Format list located within the Cells group. Also, you specify cell format with a command found on the Format list. You find the Cells group on the Home tab of the Ribbon Bar.You may rely on Alt, H, O to reach the commands on the Format Cells list, or you may reach individual items directly. In particular, the command sequence Alt, H, O, E displays the Format Cells property sheet. It has six tab pages: Number, Alignment, Font, Border, Fill, and Protection. Good news: There's a shortcut key to reach the Format Cells property sheet; just press Ctrl + 1.I recommend that you display the Format Cells property sheet with Ctrl + 1. Typically, you land on the Number tab, or on the previous tab you accessed. Move onto the desired tab with the Ctrl + Tab key, and then move onto the actual tab page with a press of the Tab key by itself.Number TabThis tab page shows a Category list and two command buttons, Cancel and OK. You move onto a number format in the Category list with Arrow keys, and Excel displays options associated with that number format on a panel located to its right. Press the Tab key to move onto this pane after you move onto the desired number format. Use the cancel button or the OK button to reject or accept your format choice. Number formats are the main topics discussed in this chapter; the other tab pages are briefly discussed for the sake of completeness.Alignment TabA cell is a rectangle in which you can place numbers, labels, or formulas. You may specify where Excel places your text. You can specify horizontal alignment within a cell (left, center, right), and you can also specify vertical alignment within a cell (top, center, bottom). You may wrap text or shrink text so it fits better within a cell. Important: you can Merge Cells; that is, combine them into a single long rectangle; you should rely on this option when you want to place a long title over a group of cells. Select the cells to be combined; then, mark the Merge Cells check box and activate the OK button; finally, type the title within the expanded rectangle. Practice this with the following example:Launch your Excel program.Select cells A1 through A4.Display the Format Cells property sheet with Ctrl + 1 or Alt, H, O, E.Move onto the Alignment tab.Mark the Merge Cells check box with the space bar, and then activate the OK button.You return to your worksheet; observe that cells A1 through A4 are now a single rectangle! (Repeat the prior steps and unmark the Merge Cells check box to separate the cells.)Font TabAs usual, you may specify font type, font style, font size, and much more. Rely on this tab page when you want to make text within cells stand out from other worksheet text. For instance, pick a different font type and font style for a title that you place over a cell region, or pick a different font type for cells that hold formulas.Border TabEvery cell has a frame that surrounds the cell content. You may specify a border type—thin, medium, thick—to set off particular cells. For instance, you may apply a thick border to those cells that hold formulas; that way, those cells are visually distinguishable from other worksheet cells.Fill tabThe options on this tab page have nothing to do with filling cells with stuff! Fill refers to Fill Effects; they are visual effects that you can apply to cells. (Ignore this tab unless you have sighted assistance to help pick appropriate visual options.)Protection TabThis tab page has two check boxes, Locked and Hidden, that let you lock or hide particular cells.Usually, an Excel expert develops a workbook for a specific purpose to be accessed by many different users. A developer can protect parts of a workbook from accidental alteration. For instance, the formulas within a workbook should remain unchangeable; users should only be able to change the data used by the formulas.By default, when a developer protects a worksheet with the Protect Worksheet item on the Format Cells list, all the cells on the worksheet are locked; that is, users can't make any changes to them. (Users can't insert, modify, delete, or format data within locked cells.)A developer can specify which cells users can change on protected worksheets. But, hiding, locking, and protecting worksheet data from accidental modification doesn't secure any confidential information that appears on a worksheet! However, you can add a password to prevent unauthorized access to confidential worksheet data; a password allows access to worksheet data only by specified users. (Excel does not encrypt data that is hidden or locked in a workbook.) You can unlock the ranges that you want users to be able to change or enter data in. You can unlock cell ranges for all users or for specific users before you protect a worksheet.Unlock Cells and Cell RangesFollow these steps to unlock an individual cell or cell range that you want users to modify:Select the cell or cell range that you want to unlock.Unmark the Locked check box on the Protection tab, and then activate the OK button.Hide FormulasHide formulas you don't want users to view or to alter; follow these steps:Select those cells on a worksheet that hold formulas you wish to conceal.Mark the Hidden check box on the Protection tab, and then activate the OK button.Quick Worksheet ProtectionSo far, you have unlocked cells and cell ranges, and you have hidden formulas from view. Now you need to protect the worksheet so those adjustments can't be undone.Select the worksheet that you want to protect.Display the Format Cells List with Alt, H, O.Move onto the Protect Sheet item, and then press the Enter key.Add a password if desired, and mark or clear the available check boxes.More Worksheet ProtectionThere are many ways to refine worksheet protection and change. All those options are located on the ribbon Bar.Display the Review tab with Alt, R.Press the Tab key until you reach the Changes group.Browse through the available options at your leisure.The dialog boxes listed there — Protect Sheet, Protect Workbook, Share Workbook, and Protect and Share Workbook, Allow Users to Edit Ranges, and Track Changes—hold all the available protection options.Number Format CommandsExcel performs calculations via formulas. The raw materials that go into calculations are numbers of various kinds: Dates and Times, Currency Amounts, Percentages and Decimals, and so on. Now you get to tell Excel what kinds of numbers go where on your worksheet.You can specify what type of number belongs within a cell or cell range. Follow these steps to accomplish that:Launch your Excel program.Select a cell or a cell range to be formatted.Display the Format Cells property sheet with Ctrl + 1 or Alt, H, O, E.Move onto the Number tab if necessary.Move onto the Category list with the Tab key.That list shows these items: General, Number, Currency, Accounting, Date, Time, Percentage, Fraction, Scientific, Text, Special, and Custom.Move onto any format in the Category list. Its available options appear on a panel to the right.Press the Tab key to move onto that panel.Pick the needed options, and then activate the OK button to apply them to the selected cell or selected cell range.There's another way to apply cell formats:Launch your Excel program.Select a cell or a cell range to be formatted.Display the Home tab on the Ribbon Bar with Alt, H.Move onto the Number group.Pick the desired number format.Only basic options are listed here; that's why I recommend the use of the Format Cells property sheet instead. Also, it's easier to press Ctrl + 1 or press Alt, H, O, E than to find the Number group on the Home tab with the keyboard.Next follow descriptions of the available number formats along with their options.General FormatBy default, every cell on a worksheet starts off with the General format for numbers. Numbers with twelve or more digits are displayed within cells in scientific notation, but they appear on the Formula bar as normal numbers. (Scientific notation shows the letter E followed by a number, the exponent of the power of ten used to represent the number.)Experiment with this option. Type these numbers into successive cells and notice what happens when the numbers have ten or more digits: 1234, 12341234, 1234123412, 123412341234. You know that the numbers are too long when they show up with the letter E followed by a number. Widen the cell (or its column) and use the Number format instead of the default General Format to display longer numbers normally with fifteen or fewer digits. (Excel can't handle numbers beyond fifteen digits with precision, and Excel reverts to scientific notation even if you widen the column.)Notice that the General format for numbers has no options. That is, you can't alter its format. Only the Cancel and OK buttons are available for this number format.Remark: The number format in effect does not alter the value that Excel uses to perform calculations. Excel always displays the actual value on the formula bar. The maximum limit for number precision is fifteen digits; that is, Excel "rounds" numbers and uses scientific notation when they get too big.Number FormatDon't like the use of scientific notation by Excel for long numbers? If so, then replace the General format with the Number format which shows only numeric values. Also, this format for numbers gives you more control over the appearance of your numbers. Notice that this format for numbers has three options. You may specify the number of decimal places to be displayed; you may specify whether numbers appear with or without comma separators—12354 or 1,234; and you may specify the appearance of negative numbers—use a minus sign as in mathematics or use an asterisk as in accounting.Currency FormatExcel can track your money. You enter money amounts, and Excel can total them, display them as a profit and loss statement, and Excel can even compute your taxes.Individual users may need to enter hundreds of money amounts, and businesses may need to enter thousands of them. In either case, it's a big hassle to type all the needed dollar signs and needed zeros—for instance, $123.00.Apply the Currency format to those cells that will hold money amounts. Excel will type the needed dollar signs and add trailing zeros for you. Experiment with the following: Apply the Currency format to cell A1, and then enter different numbers—25, 25.04, etc. Excel inserts the required dollar signs and adds two zeros when appropriate. For instance, just enter the number 250 into a cell with the Currency format, and Excel will show $250.00—you don't need to type $ before the number or .00 after the number.Notice that this format for money amounts has three options. You may specify the number of decimal places to be displayed; you may specify the monetary symbol (US Dollar by default, Pound, Euro) to be used; and you may specify the appearance of negative numbers—use a minus sign as in mathematics or use an asterisk as in accounting. (Pick the desired format from the list of two options.)Accounting FormatExcel displays money amounts as an accountant would. Excel lines up the currency symbols and lines up the decimal points.Notice that this format for monetary amounts has only two options. You may specify the number of decimal places to be displayed, and you may specify the monetary symbol (US Dollar by default, Pound, Euro) to be used.Date and Time FormatsThese two formats work the same way so they are discussed together. A date or a time appears in a default format. The default format is based on the regional date and time settings that are specified in Control Panel. You can display numbers in various date and time formats, most of which are not affected by Control Panel settings.Notice that the formats for dates and times have only two options. Move onto the right panel; pick the desired date or time format in the Type list. You can ignore the Local combo box option, for the local region was specified when you set up your Windows computer.Note that a date or time format within the list that begins with an asterisk (*) respond to changes in regional date and time settings that are specified in Control Panel. A date or time format within the list without an asterisk is independent of Control Panel settings!Enter Current DateYou may rely on a handy shortcut key to enter the current date within a cell.Move onto any cell.Press Ctrl + Semicolon to enter the current date.Move off the cell to accept your entry.The current date appears as 1/9/2011 with the default date format: mm/dd/yy which stands for month/day/year. Use this date format to enter any other date. For instance, 02/28/12 would display the date Tuesday, February 28, 2012. Excel will apply the date format you pick when you enter a date in the form mm/dd/yyy. You may type 2 instead of 02 for the month; Excel replaces 12 with 2012 for you.Remark: you may use either 02/28/12 or 02-28-12 when you enter this date manually.Enter Current TimeYou may rely on a handy shortcut key to enter the current time within a cell.Move onto any cell.Press Ctrl + Shift + Semicolon to enter the current time.Move off the cell to accept your entry.The current time appears as 10:10 PM with the default time format: hh:mm which stands for hours:minutes. Excel will apply the time format you pick when you enter the current time and will add AM or PM as appropriate. A time format may display the time as military time and can include minutes and seconds. Pick the time format you prefer and rely on Ctrl + Shift + ; to enter the current time with that time format.You may use the time format hh:mm A or hh:mm P to enter any other time. For instance, enter 10:45 A, and Excel will display 10:45 AM.Enter Dynamic DateThe shortcut key Ctrl + Semicolon places the date displayed by your computer's clock on the worksheet. That date doesn't get updated the next time you turn on your computer and access the worksheet —it remains the same. You rely on a static date like this when you need to specify a fixed date — for instance, the day you signed a contract.There are occasions, however, when you may want to reuse a worksheet and have it show the current date instead of the date you originally used. For instance, you would want the current date always displayed or printed on a To-Do list, timesheet, and so on. It would be handy if Excel would update the date on the worksheet to match that displayed by your computer's clock. You can have the current date appear every time you access a worksheet if you rely on a formula. Type =Today() within a cell to hold the current date as determined by your computer's clock. Access a worksheet with the formula =Today(), then Excel shows the current date as determined by your computer.Notice that the today function has no arguments; that is, you place nothing between the parentheses. As mentioned, the today function updates its value whenever you access a worksheet that includes that function.In summary: The formula =Today() always displays the current Date on a worksheet —a date that changes as days pass. On the other hand, the shortcut key Ctrl + Semicolon inserts and displays a Static Date —a date that remains the same as days pass. Place a dynamic date on a worksheet using =Today() if you want the date to remain the current date whenever you access the worksheet. Place a static date on a worksheet using Ctrl + Semicolon if you want the date to remain the same date whenever you access the worksheet.Enter Dynamic Date and TimeThe shortcut key Ctrl + Shift + Semicolon enters the current time for you. That time value doesn't change the next time you access the worksheet—it's no longer the current time.You may want to display the current date along with the current time whenever you access a worksheet and have Excel update both values for you. You can have the current date along with the current time appear whenever you access a worksheet if you rely on a formula. Type =Now() within a cell to hold the current date and time as determined by your computer's clock. Access a worksheet that includes the formula =Now() later on, Excel shows the current date as well as the current time—not the original displayed. For instance, I just entered =Now() within cell A1, and Excel displayed: 1/21/2011 10:51. I exited Excel and a few minutes later opened that workbook again. Excel displayed 1/21/2011 10:56; that is, it updated the time value.Note: This function has no arguments; that is, you place nothing between the parentheses. This function places the date before the time. As mentioned, this function updates its date and time values whenever you access a worksheet that includes this function.Percentage FormatApply the Percentage format to those cells that will hold percentage values. Excel will type the needed percent signs and add trailing zeros for you. Experiment with the following; Apply the Percentage format to cell A1, and then enter different numbers—25, 25.04, etc. Excel inserts the required percent signs after the numbers and adds two zeros when appropriate. For instance, just enter the number 25 into a cell with the Currency format, and Excel will show 25.00%—you don't need to type percent after the number or .00 after the number.Fraction FormatExcel can convert decimals to fractions and can reduce fractions to their lowest terms. These kinds of manipulations are handy if you wish to develop a lesson plan about decimals and fractions.Fractions correspond to decimals. For instance: 1/2 = 0.500; 3/4 = 0.750; 5/8 = 0.625; 4/3 = 1.333; 8/5 = 1.600.You can make Excel convert decimals to fractions or reduce fractions to their lowest terms in a few steps. First, apply the Fraction format to those cells that will hold decimals or fractions. Specify the Type value as three digits for maximum accuracy. Enter a decimal to have Excel convert it to a fraction, or enter a fraction to have Excel reduce it to lowest terms. For instance: 8/24 becomes 1/3; 37/8 becomes 4 and 5/8; and 3.1416 becomes 3 and 16/113.You can perform calculations on cells that hold fractions. Apply the Fraction format to the relevant cells. Next, type the fractions within separate cells. Type a formula in a different cell that uses the relevant cell references. For instance:Apply the Fraction format to cells A1, B1, and C1.Place the fraction 3/8 in cell A1, and Place the fraction 2/5 in cell B1.Place the formula =A1*B1 in cell C1.Excel displays the fraction 3/20in cell C11—the product of the fractions 3/8 and 2/5.Remark: The addition and multiplication of fractions may seem to be kid stuff. However, there are many other applications where the Fraction format shows up. For instance, stock quotes are represented as whole numbers and fractions. Scientific FormatUnlike the General format for numbers Scientific format displays all numbers, whether small and large, in exponential notation; that is, it replaces part of the number with E+n, where E (which stands for Exponent) multiplies the preceding number by 10 to the nth power. For instance, a 2-decimal Scientific format displays 12345678901 as 1.23E+10, which is 1.23 times 10 to the 10th power.The default prefix for scientific notation displays two decimal places. You can employ the Decimal Places option to specify more decimal places.Text FormatExcel will attempt to interpret a number that you enter. For instance, Excel will interpret a number of the form 02/28/12 to be a date. But, it could be a part number, serial number, etc. Apply the Text format to those cells that hold numbers that serve as labels.Special FormatExcel offers four commonly-used number formats: Zip Code, Zip Code + 4, Phone Number, and Social Security Number. These number formats, described below, preserve these number layouts. Just type entire numbers, then Excel formats them appropriately—adds the necessary spaces, hyphens, or parentheses.ZIP CODEThis format retains any leading zeros within the number. For example, enter 00123 to get 00123 (instead of 123).ZIP CODE + 4This format separates the last four digits from the first five digits with a hyphen and retains any leading zeros. Example: Enter 001235555 to get 00123-5555.PHONE NUMBERThis format encloses the first three digits of the number in parentheses and separates the last four digits from the previous three with a hyphen. Example: Enter 9995551111 to get (999) 555-1111.SOCIAL SECURITY NUMBERThis format places hyphens within the number to separate its digits into groups of three, two, and four. Example: Enter 666009999 to get 666-00-9999.Number Format Shortcut KeysIndeed, it's quick to press Ctrl + 1 to display the Format Cells property sheet and pick a number format. But, experienced users want an even faster way to do that. Excel offers shortcut keys that let you apply a desired number format directly to a cell or cell range. The available shortcut keys related to number formats are listed below with brief descriptions.Format Shortcut KeysCtrl + Shift + ~ (Grave Accent on Top Row)Apply General Number FormatCtrl + Shift + ! (Digit 1 on Top Row)Apply Number Format (Two Decimal Places, Thousands Separator)Ctrl + Shift + @ (Digit 2 on Top Row)Apply Time Format (Hours, Minutes)Ctrl + Shift + # (Digit 3 on Top Row) Apply Date Format (Day, Month, Year.Ctrl + Shift + $ (Digit 4 on Top Row) Apply Currency Format (Two Decimal Places)Ctrl + Shift + % (Digit 5 on Top Row)Apply Percentage Format (No Decimal Places)Ctrl + Shift + ^ (Digit 6 on Top Row)Apply Scientific Format (Two Decimal Places)Related Shortcut KeysCtrl + ` (Grave Accent on Top Row)Toggle Between Displayed Values and Formulas Within CellsCtrl +; (Semicolon on Middle Row)Enter Current DateCtrl + Shift + : (Semicolon on Middle Row)Enter Current TimeCtrl + 1 (Digit 1 on top Row)Display Format Cells Property SheetCtrl + 2 (Digit 2 on top Row)Apply/Remove Bold FormatCtrl + 3 (Digit 3 on top Row)Apply/Remove Italic FormatCtrl + 4 (Digit 4 on top Row)Apply/Remove UnderliningCtrl + 5 (Digit 5 on top Row)Apply/Remove Strikethrough ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download