#Excel 2007 tutorial with exercises how to#

Advantages to using a Data Table include: The ability to use an unlimited number of values as inputs to one or more key formulas in your model. The layout of your Data Tables is important and must follow Excel s rules for Data Tables. The Two-Variable Data Table allows identification of two input variables but only a single formula. The One-Variable Data Table allows identification of a single input variable but an unlimited number of formulas. When you execute the Data Table command, Excel then iterates through a process of executing each formula you ve identified, substituting in each formula each one of the values you ve identified for the key input variables, and recording how the value changes change the results of the formulas. Then you identify one or more formulas in your model that are dependent on those inputs. You identify one or two key input variables in your model and describe the range of values you want those inputs to take on. Excel has two varieties of Data Table: The One-Variable Data Table The Two-Variable Data Table Both varieties work in a similar fashion. An analysis of this sort is often termed a sensitivity analysis. For example, you might be interested in knowing how changes in the price your firm charges for an item affect the firm s net income. 17Д¤ Introduction Data Tables are a tool used frequently in Excel models to track how small changes in inputs affect the results of formulas in your model that are dependent on those inputs.

9 Making the One-Variable Data Table More Powerful by Adding Additional Formulas Data Table Formatting Note An Extension: Create Scenarios for Key Values The Two-Variable Data Table Graphing the Results of a Data Table Analysis. 4 The One-Variable Data Table: Modifications and Refinements Modifying the One-Variable Data Table. 3 Practice Creating a One-Variable Data Table. 2 A Simple One-Variable Data Table Example.

#Excel 2007 tutorial with exercises how to#

1 The One-Variable Data Table: Basics How to Create a One-Variable Data Table.

These notes are written using Microsoft Excel Version To get a copy of the XerTech Exercise.xls workbook mentioned on page 3 of this handout go to this URL: Paula Ecklund Duke University Fuqua School of Business Spring 2009ДЈ Contents Page Preface Introduction. The XerTech example used here is drawn from a text used frequently in Fuqua s Decision Models course: Introductory Management Science: Decision Modeling with Spreadsheets, by Epson, Gould, Schmidt, Moore, and Weatherford, 5th edition. In fact, Data Tables are simple to construct and use and are well worth the effort to learn. Some spreadsheet users shy away from using Data Tables because they don t understand how Data Tables work or how to create them. 1 Tools for Excel Modeling Introduction to Excel2007 Data Tables and Data Table Exercises EXCEL REVIEWДў Preface Data Tables are among the most useful of Excel s tools for analyzing data in spreadsheet models.