Methods for using a mock table in an Excel worksheet

methods for using a mock table in an Excel worksheet

1. Create a new Excel worksheet and enter data on the worksheet. Enter the calculated formula for the commission amount in Cell B9 = $B $2* $B $, as shown in Figure 1.

Figure 1 Creating a worksheet and entering a formula

2, in the B10:b23 cell area to enter the percentage of royalty, then select the cell b9:i23 that is used to create the table for the simulation, and on the Data tab, in the Data Tools group, click the Simulate Analysis button and select the Simulate table option in the Open Drop-down list, as shown in Figure 2.

Figure 2 Selecting the "Simulate Table of operations" option

3. Open the Analog Table dialog box and enter the cell address "$B $" for the value of the sales amount in the cells in the Enter Reference row text box. Enter the address "$B $" for the cell that contains the commission ratio value in the cells in the Enter Reference column text box, as shown in Figure 3, and then click OK button to close the dialog box.

Figure 3 Specifying a reference cell

4, the worksheet is inserted in the datasheet, through the data table will be able to view the different sales amount and different percentage of the percentage of royalty, as shown in Figure 4.

Figure 4 Completing the creation of a mock operation table

Attention

The data in the analog table is stored in an array, and individual or partial data in the table cannot be deleted. To delete data in a datasheet, you can only select all the data and then press the DELETE key.