Wiley 978-0-471-76321-5 Datasheet

Chapter 4109GET THE SCOOP ON...Simple calculations and quick answers Cell references Writing formulas Moving and copying formulas Linking workbook

118PART II GETTING THE DATA INcell because of the relative references. The advantages and disadvantagesof using relative references become clear when

119CHAPTER 4 WORKING DATA MAGIC WITH CALCULATIONSFigure 4.13. Moving and copying cells with relative referencesRelative references, on the other hand

120PART II GETTING THE DATA INdouble-click the Fill handle, the formula or cell entry is filled all the waydown the column until there’s no entry in

121CHAPTER 4 WORKING DATA MAGIC WITH CALCULATIONSTo write a formula that includes a cell on another worksheet, click thesheet tab and click the cell

122PART II GETTING THE DATA INThe workbook with the formula is called a dependent workbook,because it depends on input from other workbooks. The inpu

123CHAPTER 4 WORKING DATA MAGIC WITH CALCULATIONSNo matter what method you use to name cells, names must follow cer-tain rules: Names must start with

124PART II GETTING THE DATA INThe Create Names dialog boxIf the names you want to use are already headings in a table or labels forspecific cells (su

125CHAPTER 4 WORKING DATA MAGIC WITH CALCULATIONSFigure 4.19. The Define Name dialog boxWhy would you want to define a tax rate in a named constant v

126PART II GETTING THE DATA INTo use a named formula in a cell, type = and the formula name (asshown in Figure 4.21). If the formula is complex and y

127CHAPTER 4 WORKING DATA MAGIC WITH CALCULATIONSa workbook with lots of formulas and 85 named ranges, many of themduplicates! (I had to do that for

110PART II GETTING THE DATA INFigure 4.1. AutoCalculate is always at work. All you need to do is select two or more cells.To use AutoCalculate, selec

128PART II GETTING THE DATA INYou can type defined names in formulas as you write them, or in theFunction Arguments dialog box (covered in Chapter 5)

129CHAPTER 4 WORKING DATA MAGIC WITH CALCULATIONS(as many cells as you like, including cells that don’t contain formulas),and choose InsertNameAppl

130PART II GETTING THE DATA INEditing formulasYou can easily change a formula in any way (function, arithmetic operators,referenced cells, or constan

131CHAPTER 4 WORKING DATA MAGIC WITH CALCULATIONSTracing a formulaIn some worksheets, formulas reference other formulas that referencestill other for

132PART II GETTING THE DATA INFigure 4.27. Tracing a formulaLocating worksheet errorsErrors and invalid data seem to sneak into even the most scrupul

133CHAPTER 4 WORKING DATA MAGIC WITH CALCULATIONSTable 4.2. Error valuesThis error Usually means this To fix it, do this##### The column isn’t wide e

134PART II GETTING THE DATA INFigure 4.28. The Formula Auditing toolbarIf a perceived error is located, the Error Checking dialog box appearsand tell

135CHAPTER 4 WORKING DATA MAGIC WITH CALCULATIONSFigure 4.29. Circling invalid data in a data-validation rangeIf data validation was not set up befor

136PART II GETTING THE DATA IN Edit formulas by double-clicking to edit in the cell, or clicking toedit in the Formula bar. Locate errors and invalid

111CHAPTER 4 WORKING DATA MAGIC WITH CALCULATIONSAutoSumTo enter a formula in the worksheet that calculates a group of numberswithout actually writin

112PART II GETTING THE DATA INSum is the function most people want to use with AutoSum (and it’sthe function most people use in a workbook), so the d

113CHAPTER 4 WORKING DATA MAGIC WITH CALCULATIONSYou can open the calculator from an Excel toolbar button. Right-clickin the toolbar area, click Cust

114PART II GETTING THE DATA INWhen you write formulas that include cells, the cells in the formulaare identified by their references. For example, in

115CHAPTER 4 WORKING DATA MAGIC WITH CALCULATIONSFor any cell, there is only one reference but four reference types: rela-tive, absolute, and two mix

116PART II GETTING THE DATA INFigure 4.11. Open the cell, click in the reference (in the cell or in the Formula bar), press F4 to cycle, and press En

117CHAPTER 4 WORKING DATA MAGIC WITH CALCULATIONSAll formulas can calculate cells in the same worksheet, on differentworksheets, and even in differen