Estimating With Microsoft Excel
Table of Contents

Introduction | Preview A Chapter | Back Cover

ABOUT THE AUTHOR vii
ACKNOWLEDGMENTS ix
FOREWORD xi
INTRODUCTION 1

CHAPTER ONE Spreadsheets Basics and Not-So-Basics 4
     Getting Started 4
          What You Need to Have 5
          Conventions in This Book 6
          Finding Help 6
          Undoing Mistakes 6
     Using Workbooks 7
          Adding and Deleting Worksheets 8
          Naming a Worksheet 9
          Moving a Worksheet 9
          Moving the Cursor 9
          Selecting a Block of Cells 10
          Selecting Noncontiguous Cells 10
     Naming Cells and Cell Ranges 10
          Formatting Cell for Currency and for Percentage 11
          More on Names 11
          Finding Information in a Table 15
          Entering Series of Data and Text 16
          Formatting Cells 17
          Moving and Copying the Contents of Cells 18
          Changing the Width of Rows and Height of Columns 18
     What's Next 19

CHAPTER TWO Overview of a Computerized 
                         Spreadsheet Estimate 20
     Cost Breakdown Summary Sheets 20
          Typical Cost Breakdown Summary Sheets 20
          Using the Cost Breakdown Summary Sheet 
              for Cost Control 23
     Detail Sheets 24
          What-if Scenarios 24
          Repair Shop Phenomenon 25
          Detail Sheet Format 27
     Databases 27
          Typical Spreadsheet Estimating 27
          A Better Way to Use Spreadsheets to Estimate 28
          Start by Creating Your Own Cost Breakdown
               Summary Sheet 28

CHAPTER THREE Setting up a Cost Breakdown
                            Summary Sheet 18
          The AutoSum Function 31
          Writing Formulas 32
          Copying Formulas to Other Cells 34
          The Now Function 35
          Formatting the Date 36
          Formatting for Currency 36
          Increasing or Decreasing the Number of Zeros
               Behind the Decimal 37
          Paste Special 39
     The Paste Special Operators 39
          Paste Special/Transpose 39
          Sorting Data 42
     Summarizing the Chapter 43

CHAPTER FOUR Creating Detail Sheets 44
          Extending Item Costs 44
     VLOOKUP 45
          Data Validation 51

CHAPTER FIVE Linking 55
     The IF Function 56
          Viewing the Formulas in Cells 57
     Linking 58
          Method One 59
          Method Two 59
     Hyperlink 60

CHAPTER SIX More on Formulas and Functions 66
     Estimating Concrete 67
          Factoring Waste 68
          Rounding Numbers 69
          VLOOKUP Revisited 70
          Using the Match Function 72
          List Validation 74
     Calculating Rebar for Footings and Foundations 76
          Custom Formatting 77
     Estimating Permit Fees 78
          Protecting Cells from Accidental Erasure 81
          Working with Variables 81
     Connection and Impact Fees 82
          Adding Comments to Cells 84
     Roofing 85
          Starter Strip 88
          Roofing Labor 89
          Sub-Bid Methods 90
          What-if Scenarios 91

CHAPTER SEVEN Loan Interest and Builder's Margin 92
     Construction Loan Interest 92
          Origination Fees 93
          Construction Loan Interest 93
     Builder's Margin: Profit and Company Overhead 96
          Circular References 98

CHAPTER EIGHT Automating Spreadsheets with Macros 101
     Creating a Macro 102
          Running Macros 104
          The Shortcut Method of Running Macros 106
          Creating Macro Buttons 106
          Editing Macro Buttons 107
          Creating Your Own Macro Buttons 107
          Creating Custom Toolbar Icons 109
          Changing Icon Images 111
          Creating Custom Menu Items 113
     Editing Macros in Visual Basic for Applications 115
     Visual Basic for Application 117
     Looking Ahead 117

CHAPTER NINE Integration: Using Excel with
                        Other Programs 118
     Other Applications 118
     Exporting Data 120
          Copying Text and Objects 122
     Object Linking and Embedding 123
          Understanding Embedded Objects 124
          Understanding Linked Objects 126
     Conclusion 131

Introduction | Preview A Chapter | Back Cover