Excel 2007 Formula method to build 99 multiplication table

The 99 multiplication table is the content that the elementary school students must study when they study mathematics. Copying a 99 multiplication table for primary school students is also one of the homework of many parents. In fact, using Excel as a multiplication table is also a good choice. IT168 has published an article using VBA programming to realize "99 multiplication table", it has guided us a very good way to make multiplication table, so we are very inspired.

In Excel, in addition to using VBA programming to make a multiplication table, we can also directly use the formula to write the multiplication table, the effect is good. Here's an example of Excel 2007.

First, create a multiplication table

First we create an empty table in Excel, fill in the numbers 1 to 9 in the B1:j1 range, and fill in the numbers 1 to 9 in the A2:a10 cell, and get the table shown in Figure 1.

Figure 1 Excel 2007 fill in the basic numbers

Position the mouse over the B2 cell, and in the formula bar, enter the following equation: =if (b$1<= $A 2,b$1& "x" & $A 2& "=" &b$1* $A 2, "") "(excluding the outer double quotes). Then select B2 Cell, position the mouse over the autofill handle, and drag to the J2 cell to the right. At this point, you won't see any changes in the C2:j2 cell range. However, it doesn't matter, just select the B2:j2 range, drag the fill handle down to copy the formula to the J10 cell, and release the mouse, so you can get the result as shown in Figure 2. That's a good multiplication table, isn't it?

Figure 2 Excel 2007 padding cells

In fact, only one if function is used in this formula. The multiplier in the written multiplication table is the data in the b1:j1, and the multiplier is the data in the A2:a10 cell. The meaning of our formula can be understood as follows: first, determine whether the multiplier is less than or equal to the multiplier, and if so, output the result, and if not, the null value in the cell.

Add a table line to the multiplication table

Feel the multiplication table is a little shabby? It doesn't matter, we'll just add the form line to the table. Of course, just add a table line for those cells that have content. Way? First hide unnecessary secondary data, and then add table lines to the multiplication table using conditional formatting.

First click on Column A column to select all cells in column A, right click on the pop-up menu, click the "Hide" command, and then click on the first row of the line number, select all the first row of cells, and then click the Right button, in the pop-up menu, click "Hide" command, so that the secondary data is gone.