Chapter 1: Spreadsheet Basics
Chapter 1: Spreadsheet Basics CSE 2111 - 0070
Popular in Modeling and Problem Solving with Spreadsheets and Databases
Popular in Computer Science and Engineering
This 12 page Class Notes was uploaded by Ashley Faulkner on Thursday October 1, 2015. The Class Notes belongs to CSE 2111 - 0070 at Ohio State University taught by Mark Jackson in Summer 2015. Since its upload, it has received 202 views. For similar materials see Modeling and Problem Solving with Spreadsheets and Databases in Computer Science and Engineering at Ohio State University.
Reviews for Chapter 1: Spreadsheet Basics
Almost no time left on the clock and my grade on the line. Where else would I go? Ashley has the best notes period!
Report this Material
What is Karma?
Karma is the currency of StudySoup.
You can buy or earn more Karma at anytime and redeem it for class notes, study guides, flashcards, and more!
Date Created: 10/01/15
CHAPTER 1 SPREADSHEET BASICS 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 brie y 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 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 ABZ AAABAZ BABBBZ etc and the rows are named by numbers 12 1o48576 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 Quick Access Toolbar r M SizingBultons i A 33 3 39Lprcev39uecl l cuanzftlnzcl Heb V l 939 than F13 than Fztmu 2 01 Flea urw Teaquot 7 7quot El 1 km 13 v x 39 v Illflflll 39vII lF39y W7 J vaiy pa y J I 39 runv 139quot J B I u 39 w W A E i i 1 it quot139 1quot gt1 1 I39fvz 39 5 39 A39 lirl ler Name I m lt Fommla Bar 39 Box I o E a I v 7 L m N I l Cell V FuIlIPart fusequot N Tim HW1 W2 W3 MT comm Famga 5 lolalPosaible Headings Button Simona 5 mm 3 Edwatd Freeman R FA S DOVE Numbers 1 Vertical Scroll Bar I 1 15 1a L Honzonlal Scroll Bar 1amp2 19 1 p v gig911 339 ng 39jquotj J quot new a quotV quot Skeet labs 1585871 View Buttons Zoom worksheet lm orz Figure 1 The Parts of a Worksheet Page 1 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 Sheet3B3 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 eg 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 columnsrows 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 o Ctrll 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 0 Ctrl4 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 0 Ctrl 9 will move the cursor to the rightmost consecutive entry in a row or to the next entry in that row that contains data 0 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 0 Ctrl Home will move the cursor to cell A1 of the worksheet o CtrlEnd will move the cursor to the bottom of the used worksheet area ie the rightmost column and last used row 0 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 0 The Tab key will move the cursor to the right one column Page 2 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 dropdown 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 dropdown menus Additional buttons and tabs are displayed at the bottom of the window The functions of each of these tools are as follows 0 The File Ribbon This tab reveals a dropdown menu backstage view containing commands for creating a new Excel file opening and existing le 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 0 Quick Access Toolbar This toolbar is located just to the right of the Office Button and can be customized by the user H a T m T 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 Figure 2 o 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 speci c 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 0 The Help Button I This button launches the Excel help feature and is located in the top righthand side of the Excel window H W o 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 Page 3 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 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 Rlbbon j c t i r 39 i E quotquot3 E A t s v A Fl 5 39539 u Arial 12 r A 1n Eg 9 quot U Wrao Text General m l li u D um Copy v g v E Fill w quotJ Paste I B I U 2121 w A g E E aa Merge centerv 7 a 5 3939 393quot339 Conditional Format Cell Insert Delete Format Sorta Findal JFnrmat Pamtequot 39 39 a D 3900 Formatting as Talsle39r Styles r s Q Clea Filter39r Selectquot Clipboard llignment Number Styles Cells Editing Figure 3 The home ribbon Figure 3 contains many of the most commonly used Excel tools They are arranged into the following groupings o The Clipboard group contains the buttons for copying and pasting including the Cut button gHito delete selected text the Copy button Eto copy selected text and the E 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 A A I that can be used to increase or decrease font size one size at a time The Font group also contains buttons to apply borders to 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 Page 4 o The Number group contains tools for changing the format of cells containing numerical values Avalue such as 05 can be displayed as 50 currency 50 percent or even as 1 using the increase and decrease buttons 0 The Styles group contains additional tools for formatting groups of cells using conditional formatting table formatting and cell styles Cell styles are preset 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 o 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 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 andor replaced with a dropdown arrow note the Number Styles and Cells groupings in Figure 4 Also note the arrows located on the bottom righthand 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 if General quot E quotifquot Arial v 12 El Eing T a39liil 539 Paste B I U 1 1 3 1 v at l taE 4513 Clipboard Font a Number 4 Group Dialog Box launch arrow Page 5 The Insert Ribbon u 7 IE quotquot quotr a 7 39 a s i i 7 v V II I 239 5 NJ 154 bail jar 314 h o lM M1 1 J g Pi39u39otTabIe Table Picture Clip Shapes SmartArt Screenshot Column Line Pie Bar Area Scatter Other Art 7 v v v Chartsv Tables Illustrations Charts Line Column WinfLoss Slicer Hyperlink Text Header WordArt Signature Object Eguation Symbol Box39 8 Footer 7 Line39 7 Sparklines Filter Links Text 1 v v v 1 Symbols l Figure 5 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 headerfooter WordArt etc The Page Layout Ribbon E Colors 139 IrI El E Width Automatic 139 Gridlines Headings IE L El l r m l f l L Fonts t Height Automatic 139 View View 39 I li39 l Themes Margins Orientation Size Print reaks Background Print g I Bring Send Selection Align Group Rotate Themes Page Setup 71 Scale to Fit 7 Sheet Options 1 Arrange Figure 6 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 0 Themes includes preset color and font themes for customizing your worksheet displays 0 Page Setup includes access to dropdown 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 0 Scale to Fit includes features that allow the user to scale the selected print area to t within the page width and or page length or to just scale it to a speci c percent up or down 0 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 0 Arrange includes options specific to creating drawings on a worksheet These include alignment of the elements snap to grid View gridlines etc O The Formulas Rlbbon z 2 w mm El 1 39 39 39 39 39 5 39 39 1 Define Name 139 Trace Precedents Show Formulas ff 3 II a E E a m h 3 ifquot l Calculate Now 39 I quot is Use in Formula 2 Trace Dependents Error Checking 39 D Insert AutoSum Recently Financial Logical Text Date 31 Lookup 31 Math More Name I j 3 Watch Calculation Calculate Sheet Function 139 Llsedquot 139 139 139 Timer Referencequot SiTrig39 Functionsquot Manager EOE fmm SE39EEt39Dquot Eis RE39T39WE 39 39rmws 139 EualuatE F39Z39rmU39a Window Optionsquot Function Library Defined Names Formula Auditing Calculation Figure 7 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 2 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 Page 6 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 The Data ribbon Figure 8 contains tools for managing data within a spreadsheet using databasetype features such as sorting and ltering This ribbon also provides access to tools to help cleanup data by dividing text into columns or removing duplicates A useful feature is 3 H W 139 H Show Detail Data Analysis I 39E39 n Pr ii L e a 45 Hide Detail HE TE39I Tb1 Connections A i le jlaar 39 l lJ Lli LIP ll aim Y Zr Properties 2 Reappiy From From From From IDther Existing Refresh I Sort Filter Y Textto Remoye Data Consolidate WhatIf ISroup Ungroup Subtotal Access Web Text Sources39r Connections All PPM Mill quotEMVPMER Columns Duplicates Validation quot Pinalysis39r 39 quot Get External Data Connections Sort 339 Filter Data Tools Outline a Analysis Figure 8 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 Protect and Share Workbook Wllow Users to Edit Ranges S or 39 l g 2quot GE r my 1 ShowyllFilde Comment a 39 551 Show All Comments quot Spelling Research Thesaurus Translate New eiete Preyious talent Protect Protect Share Comment i J ShPWInk Sheet Workbook Workbook 1 3393 ChanQES quot39 Proofing Language Comments Changes Figure 9 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 O O The Vlew Rlbbon I j Rum meula Bar WiewSide by Side E f 39 39 39 I Synchronous Scrolling 5 Page Page Break Custom Full Gridlines H adingS Eoom 100 Eoom to New Arrange Freeze a H v a H Saye Switch Macros 7 Layout Preyiew quotu iews Screen Selection Window All Panes 139 J UDPHPJP Z RPSPP WHDRPW PPSHPHPP Workspace Windows 139 quot Workbook quotu iews Show Eoom Window Macros Figure 10 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 Page 7 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 Cells may contain textual labels numeric and A B c D E Boolean values or formulas that result in Pr39ce Quant39ty39 1 Item day days Total of total e1ther labels or values For example 1n F1gure 2 Food 25 10 250 56 11 cell A1 contains the label Item cell B2 3 Lodging 15 10 150 4 Travel 10 5 50 conta1ns the numer1c value 25 and cell D2 5 total 450 contains a formula which multiplies the values in cells B2 and C2 To type in a label or value Figure 11 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 A relational operators ltgtltgtltgt and operands constantscell 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 D2450 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 modi ed the value resulting from this formula would be automatically updated However if the total in cell D5 450 is updated the formula D245o 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 1nctions Functions are predefined 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 SUMD2D4 This formula contains the SUM function which adds all values contained in cells D2 D3 and D4 The syntax D2D4 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 o or an x as is used in algebraic expressions Page 8 If a cell contains a formula this formula can be viewed in one of several ways 0 Click on the cell and view modify the formula in the Formula Bar 0 Double click on the cell to activate Edit Mode and viewmodify the formula directly in the cell 0 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 speci ed 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 5 Relational operators lt gt lt gt ltgt FC39 quot Consider the formula B23C2 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 B23C2 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 speci c number formats ie 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 Page 9 Consider the spreadsheet in Figure 12 In A B c D E Price day cell B51s the formula B2B3B4 The displayed to Price lday value 1n cell B5 1s 4552 Column C Price zero decimal rounded to zero 2 Food 3 2510 s 25 s 25 55 but the cells 1n column C are displayed 3 Led in 5 1o42 5 1o 5 1o 23 with zero decimal places To modify 4 TraVel 55 1000 S 10 S 10 22 5 total 4552 46 45 the number of decimal places displayed use the increase and decrease buttons Flgure 12 i113 algal on the Number group of the Home ribbon Thus cell C2 contains the value 2510 but it is displayed as 25 and similarly cells C3 C4 and C5 contain the values 1042 1000 and 4552 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 C2C3C4 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 025 into cell A1 and cell A1 is formatted as percent the number 025 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 a ect 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 D2D3D4 results in the value 45 since it is actually adding the exact values 25 10 10 and not 251 1042 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 0 Multiplication of Decimals Consider the formula 13 displayed with two decimal places and placed in cell A1 What would the value of A1100 be The value displayed in cell A1 would be 033 One may expect that the result of A1100 would be 33 However the precise value stored would be 3333333333333333 Thus A1100 would not be 33 but instead would be 333333333333333 Page 10 o 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 all 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 055 and then multiplying 055 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 055 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 25235 by clicking on the comma button in the Number group of the Home tab If 25235 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 252332 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 exible 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 Page 11 A B C D E F G 1 data weekdays per week 4 2 weekend days per week 1 3 Price per Price per Total weekday weekend Weekly Cost Cost Total 4 Item day day Cost Weekday Weekend Cost 5 Food 25 50 150 50 59 53 6 Lodging 15 35 95 30 41 33 7 Transportation 10 40 20 0 14 8 total 50 85 285 Figure 13 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 B5F1C5F2 Cell D6 contains another formula B6F1C6F2 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 Page 12
Are you sure you want to buy this material for
You're already Subscribed!
Looks like you've already subscribed to StudySoup, you won't need to purchase another subscription to get this material. To access this material simply click 'View Full Document'