Nearly 4000 years ago, the ancient Babylonians were the first to use multiplication tables. However, they used a base of 60; it wasn’t until 2300 years ago that multiplication tables with a base of 10 were used. And just four years ago, scientists discovered the fastest way to multiply large numbers.

Today, we’ll look at multiplication and two other techniques to “stoke the flames” of Excel by starting with the basics of multiplication and adding two bonus multiplication topics: Data Tables and Goal Seek.

First, we’ll consider the simplest Excel multiplication process: Numbers alone.

 

A Day at the Gas Station

Let’s say you need to calculate the cost of 11.3 gallons of gas at $5.99 per gallon. You could write =11.3*5.99 (in Cell D3) and get the results ($67.69).

But that would be short-sighted, especially if the price of gas changes. Rather than write the formula with static numbers, you would rewrite your formula using only cell address references.

For example, if cell B3 contains the value 11.3, and cell C3 contains the value 5.99, you could write a formula in cell D3 that says =B3*C3.

Yes, the result would be the same as before ($67.69), but the benefit is flexibility.

Without editing the formula cell (D3) directly, you could easily experiment by changing either the number of gallons in cell B3 or the price per gallon in cell C3, and observe the impact in the calculation in cell D3.

Formulas work best when they contain cell addresses rather than static values — especially when the numbers are apt to be changed.

 

---

Learn more about Excel by registering for one of our live, online courses: 

• Getting the Most From Microsoft® Excel®

• Two-Day Workshop on Advanced Microsoft® Excel® Techniques

• Accelerated Microsoft® Excel® Certificate Program

• Generative AI for Excel® Power Users

---

 

Moving further, when numbers are apt to change, consider a feature which enables you to build a table that multiplies a range of gallon amounts and a range of prices.

Think of this as “Multiplication Squared!”

 

Data Table

In Excel’s Data tab is a button titled, “Data Table.” This is a powerful feature when you’re experimenting with cell addresses in a formula.

If you list the various gallon measurements in rows and the various prices per gallon in columns, you can generate an automatic listing of each combination.

Here’s what it would look like:

The key to building this type of Data Table is placing the formula at the intersection of the rows and columns of ingredients.

In the example above:

• Cell D3 contains the formula (=B3*C3).
• Cell B3 contains the row input cell.
• Cell C3 is the column input cell.

To create the Data Table:

1. Highlight the range of cells containing the row values, column values and the formula. (In the case above, D3:J13)
2. Open the Data Table dialog from the Data tab.
3. Provide the row input address (B3).
4. Provide the column input address (C3).
5. Press OK.

The result is a super multiplication table that displays the results of all the combinations of gallons and prices.

Notice at 11.3 gallons and $5.99, the total matches the original single cell calculation in cell D3 (namely, $67.69).

Amazing!

But what is also amazing is the usage of “Goal Seek.”

Goal Seek, our final example, is multiplication in reverse. Goal Seek enables you to define a result ($40) and determine the amount of gallons to pump to get to that number.

Goal Seek in Excel

Goal Seek is the “next-door neighbor” to Data Table in the Data tab of the Excel ribbon.

Let’s say you want to spend only $40. How much gas will that buy?

By using Goal Seek, you can calculate backwards.

To use Goal Seek:

Select the cell with the formula (D3) and use the Goal Seek dialog to input:

1. The cCell containing the formula (D3).
2. The result you want, $40.
3. The cell containing the value to “change” to get the total to $40.

So, whether you’re building a fire or filling a tank of gas, multiplication using a formula will enable you calculate the total cost.

• Using a Data Table enables you to figure out many combinations of gallons and prices and their costs.
• Using Goal Seek enables to figure out what “filling a tank” with $40 will get you these days.

And, mastering all three of these techniques will undoubtedly multiply your skillfulness and add value to your career.

---

Phil Wiest is an expert software trainer, computer analyst and database consultant who knows both the intricacies of computer systems and the ways today’s professionals need to use these essential business tools. Using his experience in Microsoft Excel and Microsoft Office, as well as Windows® and the Internet, Phil uses a special mix of tactful guidance and distilled observation so his audiences learn and retain the critical keystrokes, application combinations and creative processes that save time and simplify computer use.

Ready to learn more? Check out some of SkillPath’s live virtual training programs, on-demand video training or get it all with our unlimited eLearning platform.