This chapter contains general information about spreadsheets and the spreadsheet software package Microsoft Excel 2010. Spreadsheet applications are used extensively in industry to analyze data, create balance sheets, prepare budgets, and keep track of other information such as project costs, inventory, etc. This chapter will explore the basic concepts of using a spreadsheet and briefly introduce some of the features found in an Excel workbook. Subsequent chapters will cover additional tools and develop an understanding of how Excel can be used as a problem solving tool.
Basic Features of an Excel 2010 Workbook
The Worksheet:
Figure The Parts of a Worksheet
As seen in Figure 1 spreadsheet is a work area composed of columns and rows. The intersection of a column and row is referred to as a cell. Each cell is identified by an address which indicates the column and row in which it resides. Columns are named by letters (A,B..Z, AA,AB..AZ, BA,BB..BZ etc), and the rows are named by numbers (1,2 ….1048576). The cell address first lists the column name and then the row name. So the cell in the first column and first row has the cell address A1 and the cell in the third column second row has the cell address C2. To enter a value or formula, click on the cell where you wish to place your entry and type. This selected cell is referred to as the active cell. The contents of the active cell will also be displayed on the formula bar which is located directly above the column headings. 
A single Excel file may contain many spreadsheets, which are referred to as worksheets. Together, these worksheets comprise an Excel workbook file. The tabs at the bottom of the Excel window list the worksheets in the workbook. The worksheet you are currently using is referred to as the active worksheet. The active worksheet tab appears highlighted. To reference cells on different worksheets in a workbook place the worksheet name in front of the cell address. So a cell that is on sheet 3, in the second column third row would be referred to as Sheet3!B3. Note the use of ! to indicate a sheet name. When referring to a cell on the active worksheet the sheet name is not required. A worksheet can be renamed by right-clicking on the tab and selecting Rename. A worksheet’s name can be almost any text string (e.g. data, Financial Summary, January, etc.). In addition, worksheet tabs can be colored or reordered, and worksheets may be copied or deleted.
Navigating the Worksheet:
The easiest way to move from one cell to the next is to use the mouse pointer and click on the desired cell. The up, down, left, right arrow keys on the keyboard can also be used to move around the worksheet one cell at a time. However not all worksheets are as simple as the one seen in
Figure 1. The window displays columns A through N and rows 1 through 19. What if your worksheet required more than 15 columns or 19 rows? To view rows 19 and above on the screen use the
Vertical Scroll bar on the right side of the worksheet window or the down arrows on your keyboard. Similarly, to view additional columns on your screen use the
Horizontal scrollbar. Using the scroll bars change the columns/rows displayed in the window but does change the active cell. Other methods of navigating the worksheet that change not only the view but also the cursor/active cell are the following:
-
Ctrl+ Simultaneously holding down both the Ctrl key and down arrow key will move the cursor to bottom of a list of consecutive cells in a column or to the next entry (non-empty cell) in that column. This new cell will then become the active cell.
-
Ctrl+ will move the cursor to the top of a consecutive list in a column or to the next entry above the active cell in that column.
-
Ctrl+ will move the cursor to the rightmost consecutive entry in a row or to the next entry in that row that contains data.
-
Ctrl+ will move the cursor to the leftmost consecutive entry in a row or to the next entry located to the left of the current active cell.
-
Ctrl+Home will move the cursor to cell A1 of the worksheet
-
Ctrl+End will move the cursor to the bottom of the used worksheet area. i.e. the rightmost column and last used row.
-
The Page Down and Page Up buttons will move the cursor up or down a distance equal to the number of rows displayed in the window. If the spreadsheet window shows 10 rows then the cursor will be moved 10 rows, if the spreadsheet window shows 20 rows the cursor will be moved 20 rows.
-
The Tab key will move the cursor to the right one column.
Additional tools for managing large spreadsheets are available using the Excel ribbon buttons and will be covered later.
Accessing Excel’s tools:
The Excel Window itself contains much more than just the worksheet cell area and the scroll bars. The default format, as seen in
Figure 1, displays several sets of drop-down buttons, tabs, and icons located on both the top and bottom of the worksheet. Many of the basic features of Excel 2010 require the use of the various objects found in Excel’s
ribbons and
toolbars. At the very top of the window is the Quick Access Toolbar, and the file title in the title bar. Just below this are the Ribbon Tabs, Help Button, and Sizing buttons. The area just below this is the selected ribbon and its associated icons and drop-down menus. Additional buttons and tabs are displayed at the bottom of the window. The functions of each of these tools are as follows:
-
The File Ribbon: This tab reveals a drop-down menu “backstage” view containing commands for creating a new Excel file, opening and existing file, saving, printing, emailing and closing the file. In addition to the menu are two buttons – Excel Options launches a dialog box allowing the user to customize their Excel features, and Exit Excel which closes the application.
Figure
Quick Access Toolbar: This toolbar is located just to the right of the Office Button and can be customized by the user. This is an excellent location to place icons of features the user wishes to have most easily accessible. By default the toolbar contains the file save icon and the undo and redo icons. To customize the toolbar click on the down arrow located just to the right of the icons (Figure 2).
-
The Ribbon Tabs: Many of the features available in Excel can be accessed from one or more of the Excel ribbons. To use a tool on a specific ribbon, click on the ribbon tab. Excel contains the following ribbons: Home, Insert, Page Layout, Formulas, Data, Review and View. The ribbon displayed in Figure 1 is the Home ribbon.
Ribbons are further organized into groups of icons/buttons of related tasks. For example the Font group on the Home tab contains buttons to change the font, modify the font size, apply a font style, etc. A summary of the contents of each ribbon is presented later. Additional context sensitive ribbons are also available depending on the tool being used. For example, if a workbook contains a chart, when the chart is selected several chart tool ribbon tabs will appear that allow the user to edit the chart.
-
The Help Button: 
This button launches the Excel help feature and is located in the top right-hand side of the Excel window.
-
Sizing Buttons: 
As with all Microsoft Windows applications, these buttons can be used to size the Excel application window. Clicking on these buttons allows the user to close, maximize, minimize, or allow custom sizing of the window. A second set of these icons appear in each worksheet window within the Excel window. One or more worksheet windows can be opened simultaneously and sized independently using these buttons.
-
View Buttons: 
These buttons are located in the bottom right hand side of the Excel window and allow the user to change the view of the active window between Normal, Page Layout, and Page Break View.
-
Zoom: 
The Zoom Bar allows the user to customize the screen magnification in the active window by sliding the zoom selector left and right. The Zoom Bar is located in the bottom right corner of the Excel window.
The ribbons:
Figure
Accessing many of the Excel tools requires the use of ribbons. An illustration and description of each ribbon is listed below. Additional content sensitive ribbons are also available – these will be presented as the features associated with the ribbons are introduced. Please note that some of the tools described here may have no meaning to you as you have not yet begun to create your spreadsheets or write formulas. You need not memorize the contents of the ribbons – only be aware of the general categories and use this section as a reference as you begin to use these tools. 
The Home Ribbon:
The home ribbon (Figure 3) contains many of the most commonly used Excel tools. They are arranged into the following groupings:
-
The Clipboard group contains the buttons for copying and pasting, including the Cut button 
to delete selected text, the Copy button 
to copy selected text and the Paste button
to paste selected text. Excel allows the user to cut/copy and paste parts of cells, entire cells, ranges of cells, and even entire worksheets with these tools. Also included in this group is the Format Painter tool 
that allows the user to copy the format of a cell or range of cells without copying the actual content.
-
The Font group contains buttons and drop down menus for changing the font, font size, and font styles (bold, italics, underline). There are also two buttons 
that can be used to increase or decrease font size one size at a time. The Font group also contains buttons to apply borders t0 a cell or to apply a color to the fill and/or text of the cell.
-
The Paragraph group allows the user to adjust the alignment of text within the cell (left, right, centered, justified to top of cell, justified to bottom of cell). There are even tools to rotate text within the cell, automatically wrap text within in the cell, and merge several cells and center the center the text over those cells.
-
The Number group contains tools for changing the format of cells containing numerical values. A value such as 0.5 can be displayed as $.50 (currency), 50% (percent) or even as 1 using the increase and decrease buttons.
-
The Styles group contains additional tools for formatting groups of cells using conditional formatting, table formatting and cell styles. Cell styles are pre-set formats that allow the user to quickly and easily setup professional and easy to read spreadsheets.
-
The Cells group contains several important buttons that open drop down lists. The Insert button allows the user to insert a row, column, or worksheet. Correspondingly the Delete button allows the user to delete a cell, set of cells, columns, or rows from the worksheet and adjust the location of adjacent cells. From the Format button menu one can adjust the height of rows, width of columns, hide or unhide rows or columns, and rename/move worksheets.
-
The Editing group located on the far right of the ribbon contains miscellaneous buttons that are also found on some of the other ribbons. They include the AutoSum button, a Continue Pattern button (for copying patterns of cells), a Clear button, a Filtering/Sorting button, and the Find/Select button.
Figure
Please note that the view of the ribbons depends on the size of the Excel window. If you size the Excel window into a long narrow box, it is likely that the icons will be compressed and/or replaced with a drop-down arrow (note the Number, Styles, and Cells groupings in Figure 4. Also note the arrows located on the bottom right-hand corner of some ribbon groupings. These arrows can be used to launch dialog boxes which allow the user access to an even greater array of features for that tool. 
Group Dialog Box launch arrow
Figure
The Insert Ribbon: 
Figure
Figure 5 is an illustration of the Insert ribbon. This ribbon contains groupings for inserting tables, illustrations, charts, links, and specific textual elements such as a header/footer, WordArt, etc. 
The Page Layout Ribbon:
Figure 6 is an illustration of the Page Layout ribbon. From this ribbon the general layout and print features can be modified. The groupings include the following:
-
Themes – includes preset color and font themes for customizing your worksheet displays
-
Page Setup – includes access to drop-down menus for customizing print layouts including page margins, orientation, paper size, print area, page breaks, backgrounds and setting print titles to appear on each page.
-
Scale to Fit – includes features that allow the user to scale the selected print area to fit within the page width and/or page length or to just scale it to a specific percent up or down.
-
Sheet Options – includes options to modify the printout area, add sheet titles, repeat column and/or row headings, insert gridlines, print in draft, or order pages.
-
Arrange – includes options specific to creating drawings on a worksheet. These include alignment of the elements, snap to grid, view gridlines, etc.
The Formulas Ribbon:
Figure
Figure
Figure 7 is an illustration of the Formulas ribbon. This ribbon is useful when entering calculations into the workbook. It contains the insert function button (Fx- also available on the formula bar), some of the AutoSum commands (Σ – also available on the home ribbon editing group), and drop down menus to access many of the most commonly used functions such as Logical functions, Text functions, Lookup and Reference functions, etc. This ribbon also contains a Defined Names group with features for specifying and using named ranges. The formula auditing features such as Trace Precedence and Show Formulas are located in the Formula Auditing grouping. There is a button to access the calculation options within Excel. 
The Data Ribbon:
Figure
The Data ribbon (Figure 8) contains tools for managing data within a spreadsheet using database-type features such as sorting and filtering. This ribbon also provides access to tools to help “clean-up” data by dividing text into columns or removing duplicates. A useful feature is the ability to group rows and columns and display subtotals for groupings. The Data ribbon also contains the What-If Analysis button for access to the Goal Seek tool. 
The Review Ribbon:
Figure
Excel provides a set of tools to support collaboration. These tools allow users to share documents, block a workbook or worksheet from being edited, insert comments into a workbook, and trace changes to a spreadsheet. These tools are located in the Review ribbon (Figure 9). The Review ribbon also contains links to the spelling and thesaurus features. 
The View Ribbon:
Figure 10 is an illustration of the View ribbon. The View ribbon contains tools for changing the way Excel displays content. The View ribbon contains features for changing screen views and zoom level and displaying gridlines, headings, formula bars, etc. The most useful features of this ribbon are the commands that allow the user to open multiple worksheet windows within the Excel window (New Window) and arrange these windows. Window views can also be customized to freeze or split certain portions of the worksheet to that titles or a specific set of data can be viewable no matter where you scroll to in the worksheet. This ribbon also contains a toolbar button to launch and create Macros. Macros are “little programs” that tell Excel to execute a specific set of instructions.
Writing Simple Formulas
Now that you have seen the general structure of a workbook and the interface for launching Excel’s tools you can begin to create your own spreadsheets. The most important element of the worksheet is the cell. We use cells to store data and formulas that perform calculations on data contained within other cells.
Entering a simple formula:
Figure
Cells may contain textual labels, numeric and Boolean values, or formulas that result in either labels or values. For example, in Error: Reference source not found cell A1 contains the label “Item,” cell B2 contains the numeric value 25, and cell D2 contains a formula which multiplies the values in cells B2 and C2. To type in a label or value, click on the cell to make it active and begin typing. To enter a formula into a cell, begin by typing an equal sign (=). 
Formulas may consist of a combination of arithmetic operators (+,-,*, , ^), relational operators (<,>,<=,>=,=,<>), and operands (constants/cell references). In the context of Figure 11, suppose we want to write a formula in cell E2 to calculate food costs (D2) as a percentage of total costs (D5). An example of such a formula might be =D2/450. This formula contains the cell reference D2, the arithmetic operator for division (/), and the numerical constant 450. The result of this formula will be the numerical value resulting from dividing the contents of cell D2 by 450. If the value in cell D2 is modified, the value resulting from this formula would be automatically updated. However, if the total in cell D5 (450) is updated, the formula =D2/450 will not be affected as the value 450 has been placed directly in the formula as a constant and not as a cell reference. Experienced spreadsheet users know that explicitly listing all input data and using cell references in formulas to refer to the input data greatly improves the readability, maintenance, and even reliability of the spreadsheet.
Formulas may also include functions. Functions are pre-defined worksheet formulas – “shortcuts” available in Excel. In Figure 11, cell D5 displays the value 450. However, if you click on cell D5, in the formula bar you will see the cell actually contains the formula =SUM(D2:D4). This formula contains the SUM function, which adds all values contained in cells D2, D3, and D4. The syntax D2:D4 represents a range of values in Excel. If any of the values in this range were to be modified, the value in cell D5 would be automatically updated.
Formulas in Excel must be written in a very specific format, referred to as syntax. Syntax is not the language itself; it can be compared to rules of spelling or the grammar of a language. For example, the syntax for the multiplication operator in Excel is an asterisk (*) not a dot (•) or an (x) as is used in algebraic expressions.
If a cell contains a formula, this formula can be viewed in one of several ways:
-
Click on the cell and view/modify the formula in the Formula Bar.
-
Double click on the cell to activate Edit Mode and view/modify the formula directly in the cell.
-
Display formulas for all cells by modifying the worksheet properties. Click on the Formulas ribbon and then select the Show Formulas button
. To revert back to the original view, again click the Show Formulas button. Alternatively, the keyboard shortcut keys Ctrl+` (grave accent) will activate this feature.
Before you begin writing meaningful formulas, there are several things that you will need to know in order to obtain the desired results.
Order of Precedence
As in mathematics, spreadsheets analyze formulas based on a specified order of operations. When writing formulas for spreadsheets, you must pay careful attention to this order of precedence or run the risk of obtaining incorrect values. The order of precedence in which a formula is evaluated is as follows:
-
Operations enclosed in parentheses including functions
-
Exponentiation
-
Multiplication and division from left to right
-
Addition and subtraction from left to right
-
Relational operators (<, >, =, <=, >=, <>)
Consider the formula
=B2+3*C2. Excel would first multiply 3 by the value in cell C2 and then take the result of that calculation and add it to the value in B2. If cell C2 contains the value 10 and cell B2 the value 25, the resulting value would be 55. If, however, you meant to add 3 to cell B2 and then multiply it by the value in cell C2 you would not obtain the desired result (280). To indicate that B2 should be first added to 3, parentheses must be used around the operation. Since operations in parentheses are always performed first, the calculations will now be completed in the desired order.
The Excel formula =(B2+3)*C2 results in the value 280.
Data Precision
Excel provides the user a variety of methods for formatting text and data within a cell. In addition to standard text formatting tools available in word processors (font, font style, color, etc.), users can also specify specific number formats (
i.e., commas and decimal places) and styles (negative number display, currency, etc.). These tools can be accessed from the home tab groups or by clicking on the group launch arrows and opening the Format Cells dialog box. These features all focus on the
display of a value in the cell.
Figure
Consider the spreadsheet in Figure 12. In cell B5 is the formula =B2+B3+B4. The value in cell B5 is $45.52. Column C contains the same numbers as column B, but the cells in column C are displayed with zero decimal places. To modify the number of decimal places displayed use the increase and decrease buttons 
on the Number group of the Home ribbon. Thus, cell C2 contains the value 25.10 but it is displayed as 25 and similarly cells C3, C4 and C5 contain the values 10.42, 10.00 and 45.52 but they are displayed as 10, 10 and 46. Looking only at the display of column C and adding up these values (25 + 10 + 10), the result is 45. However if you enter the formula =C2+C3+C4 and display it with zero decimal places, the computer comes up with a result of 46. Has Excel made an addition error? No.
In order to understand why the value displayed in cell C5 is correct, it is important to differentiate between the value that Excel stores in a cell and the value Excel displays for a cell. The value that Excel stores for a cell is the precise value you enter in the spreadsheet or the precise value resulting from a formula in that cell. The value Excel displays to you depends on how the cell is formatted. For example, if you type 0.25 into cell A1 and cell A1 is formatted as percent, the number 0.25 is stored in the cell and Excel will display 25%. When Excel evaluates a formula that contains cell reference, it uses the precise values stored in the cells to calculate the result.
What is important to understand is that when you change the format of the cell display it does not affect the actual value of the number stored by the computer. So you may enter a value that has 5 decimal places but for clarity only show the value to one decimal place. When you do this, Excel still uses all 5 decimal places in calculations involving that cell. As a result of this, what can sometimes occur is that the result of arithmetic operations appears to be incorrect.
In column D, the Round function (which will be covered later) has been used to change the actual precision of the value. Hence, in column D the formula =D2+D3+D4 results in the value 45 since it is actually adding the exact values 25 + 10 +10 and not 25.1 + 10.42 + 10 as is the case in columns B and C.
Consider these of examples where calculations using the precise value in a cell may not be consistent with the cell display:
-
Multiplication of Decimals: Consider the formula =1/3 displayed with two decimal places and placed in cell A1. What would the value of A1*100 be? The value displayed in cell A1 would be 0.33. One may expect that the result of A1*100 would be 33. However, the precise value stored would be .3333333333333333…. Thus A1*100 would not be 33 but instead would be 33.3333333333333….
-
Percentages: In cell E2 on the spreadsheet in Figure 12 the user has entered the formula =B2/B5 to represent the cost of food as a percentage of total cost and formatted the cell using the percent style button 
located in the Numbers group on the Home tab. The result displayed is 55%. But what is the actual precise value stored in this cell? When computing percent on a pencil and paper exercise this would be done by dividing 25 by 45 to get 0.55 and then multiplying 0.55 by 100 to arrive at 55 percent. In Excel after writing the formula =B2/B5 the % toolbar button was used. Did the % toolbar button modify the precise value from .55 to 55? The answer is no. The percent toolbar button displayed the value as a percentage by dividing it by 100 and inserting the percent symbol, but the precise value was not changed from the original .55 calculated in the formula =B2/B5. Thus, when using cell E2 in subsequent calculations, the computer will use the value 0.55 and not the value 55.
Another display element to be aware of is the use of commas in a workbook. The value 25235 can be displayed as 25,235 by clicking on the comma button in the Number group of the Home tab. If
25,235 is typed directly into a cell including the comma, Excel will interpret it correctly and setup the cell with a comma format. However, Excel will not infer that a number typed with commas is a numerical constant when it is typed in a formula. As you will see later, the comma has a special use in Excel formulas. As an example, consider the formula
=25,233+2. If this formula, including the comma, is typed directly into a cell an error message window would appear; Excel is unable to interpret the formula as written. When using functions in formulas, typing commas can present even more of a problem. A best practice recommendation is that numbers
never be typed with commas; instead, use the formatting options instead to modify the display.
Using Formulas in Spreadsheets
Now that the importance of order of precedence and the difference between precision and format display are understood, the next step is to begin writing simple formulas. As you have already seen, formulas can not only perform simple mathematical calculations using constants, but can also reference data contained in other cells. As a result of this, the values required for a calculation can reside in other parts of a workbook. A cell reference can be used again and again in many formulas as a “shortcut” to the value it contains. Later when that value changes, all calculations referencing this value will be automatically updated. This feature makes our spreadsheets extremely flexible and easily modifiable.
Consider the spreadsheet display in Figure 13 which lists a company’s costs for employee travel. The costs for travel vary based on whether the travel day is during the week (Monday to Friday) or a weekend day (Saturday and Sunday). Currently it is assumed that an employee travels 4 weekday days and 1 weekend day. What if the number of weekdays they travel changes from 4 to 3? How many of the values on this spreadsheet would change? Almost all of the values in columns D and G would be affected. On a large spreadsheet it becomes difficult, if not impossible, to keep track of all the calculations that would be affected by a single change to one or more value. If spreadsheets are designed properly, values need only to change in one place and all subsequent calculations will be automatically updated.
Figure
On the spreadsheet in Figure 13, the number of weekdays and weekend travel days are placed in cells F1 and F2, respectively. Any formula that requires the use of these values would reference these cells and not the actual values 4 and 1. For example, cell D5 contains the formula for total cost per week for food: =B5*F1+C5*F2. Cell D6 contains another formula, =B6*F1+C6*F2, which also references cells F1 and F2. Later, if a change is required to the number of weekday days, only cell F1 would need to be updated. Excel would automatically recalculate the values in cell D5 and D6. 
Proper spreadsheet design becomes increasingly important as spreadsheets become larger and more complicated. Using formulas and setting up the spreadsheet so that no actual value appears more than once for a given variable will ensure that your numbers can be reliably and quickly updated.
Chapter 1