Excel Create Table From Data3/20/2021
Click the Data tab. Choose Data Table from the What-If Analysis option in the Data Tools group ( Figure E ).When you buy through our links, TechRepublic may earn a commission.Since then, the author has updated the tip and images and provided demo files, and we have added a video tutorial and updated the related resources.Sometimes one answer isnt enough--occasionally, you need to see multiple possibilities.
When this happens, consider adding a data table to your sheet. A data table is a range that evaluates changing variables in a single formula. In other words, its a simple what-if analysis: How does changing an input value change the results Instead of viewing multiple sheets, you can examine the possibilities with a quick glance at one data table. Im using Office 365 Excel (desktop) on a Windows 10 64-bit system, but this feature is available in older versions. Excel Create Table From Data Download The DemonstrationYou can work with your own data or download the demonstration.xlsx and.xls files. You can view a data table in the browser, but you cant create one. LEARN MORE: Office 365 Consumer pricing and features How to create a mortgage calculator in Excel To illustrate their use, well add a data table to the simple mortgage calculator shown in Figure A. As is, you can change any of the input values to change the results. You can download the demo workbook or refer to Table A for the formulas. The IFERROR() function isnt necessary but consider using it to handle errors if you plan to distribute the workbook to others. Table A Cell Formula Format B4:B5 Currency B6 General B7 Percentage with two decimal places E4 B4-B5 Currency E5 IFERROR(PMT(B712, B612,-B4B5),0) Currency E6 IFERROR(E5B612,0) Currency E7 IFERROR(E6-E4,0) Currency Now, lets suppose you want to compare results when the interest rate changes, not once, but for several rates--thats where a data table comes in. In addition, the data tables first column should contain your variable. I recommend using AutoFill to create a list of interest rates as shown in Figure B. If you must use formulas, be sure to replace the formulas with values because data tables dont evaluate formulas in the first column. It doesnt matter what values you begin and end the list with. Figure B Add labels and then create the list of changing variables in the data tables first column. The next step is to enter the data tables references as follows: Select B11 and enter E5 (the cell that contains the monthly payment formula in the result cell section of your original sheet). ![]() The variable input cell--in this case, thats the interest rate value--must equal the first value in your data table. The first interest rate in the data table is 2.5. You must change the input value in B7 to 2.5 before you generate the data table, as shown in Figure D. Figure D Now, youre ready to generate the data table as follows: Select the data table range. In this case, thats A11:D29 (dont include the labels in row 10). Click the Data tab. Choose Data Table from the What-If Analysis option in the Data Tools group ( Figure E ).
0 Comments
Leave a Reply.AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |