Excel’s POWER function is a surprisingly simple and easy to use function. On its own, it can easily perform basic mathematical exponential calculations. When nested inside a complex mathematical equation, however, the POWER function can reduce the size of otherwise very complex formulas.
The POWER function is highly versatile, considering its simplicity. The function’s shorthand operator allows users to condense a variety of really complicated mathematical formulas.
It can be used to display numbers in scientific notation, create intricate graphs and charts, and perform geographic calculations. For those working with complex mathematical data, knowledge of the POWER function is critical.
What Is Excel’s POWER Function?
POWER is a simple function that raises a given number to an exponent. The exponent can be a fixed number, or it can be a reference to any cell in your workbook.
The exponent argument of the POWER function can be either a positive number, or a negative number. When used for expressing scientific notation, positive exponents allow you to work with large numbers, while negative exponents are used for working with small values.
In addition to the standard POWER keyword, the function also has a shorthand, the circumflex operator, ^. When used in a formula, the circumflex operator will perform the same calculation as POWER. The POWER function takes two arguments, the number you are raising, and the exponent you are raising it to.
The POWER function can also be expressed as a series of multiplications. The following two formulas will produce the same results:
=POWER(5, 3)
=5*5*5
Multiplying any number by itself repeatedly will produce the same result as the POWER function or its shorthand operator, but in most cases using the function is far cleaner to read.
The first argument can be any real number, and can either be entered as a fixed number in the function, or referenced from anywhere in the current workbook. The second argument, the exponent, can be any real whole number, positive or negative.
How to Use Excel’s POWER Function
The POWER function can be used on its own in either form. It can also be nested within other functions to create more complex mathematical formulas.
The syntax for the POWER function is as follows:
=POWER(number, power)
Where "number" is the base number and "power" is the exponent. For example, if we want to raise the number 2 to the power of 3, we would write:
=POWER(2,3)
The result of this calculation would be 8.
=POWER(A2, 2)
Here, any number placed into cell A2 will be squared, and the result will be returned by the function. The same function could be written out using the shorthand version of the POWER function:
=A2^2
When using the POWER operator instead of the function, the base is placed on the left-hand side of the circumflex while the exponent is on the right.
Using Excel’s POWER Function With Fixed and Arbitrary Powers
Not only is the number being raised able to be a reference to a cell within your current workbook, but the exponent can also be passed as a cell. Passing the exponent as a cell creates a function like the following:
=POWER(15, A2)
The results of the above function will be 15 to whichever power is placed in A2. We can use one of Excel’s array formulas, formulas using a range of cell references rather than a single value as demonstrated below, to create a simple power chart showing us a complete list of exponents.
The chart was created with a single function:
=POWER(A2:A11, B1:K1)
This is an array formula. It uses each cell from A2 to A11 as the first argument of the POWER function, and uses each cell from B1 to K1 as the second argument. If the function is placed in cell B2, then the results will automatically be placed in each cell from B2 to K11 to create the above table.
Using Excel’s POWER Function in Calculations
While Excel’s POWER function has limited use cases as a standalone function, interestingly, it works well as a simple square calculator:
=POWER(A2, 2)
One notable feature in the above example is the value of B11 and of C11. If the number referenced to be raised to any power is an empty cell, it will be treated as 0. As a result, the product of running the POWER function on an empty cell will always be 0.
Using the POWER Function to Calculate the Area of a Circle in Excel
A far better use case, however, can be seen with a little simple geometry. By combining the POWER function into a mathematical formula, we can calculate the area and circumference of a circle of varying radius.
The area of the circle is equal to PI times the radius squared. The formula can quickly be turned into an area calculator in Excel:
- Place the radii of the circles you would like to calculate into column A
- The POWER function can be used to square the radius by using a cell reference as the first argument and 2 as the second.
- PI can be estimated using 3.14, or for a more precise measurement, Excel’s PI function can be used. Multiply the PI function by the squared radius.
Once the first cell, C2 has been created, use the drag handle on the bottom right of the cell to populate the column.
=PI() * POWER(A2, 2)
Finally, for readability, it is best to avoid trailing decimal places. There are a few different ways to modify decimal places in Excel. For this example, the ROUND function is the simplest way.
Excel’s ROUND function can truncate the results to a specific number of decimal places. Use the formula from above as the first argument for the ROUND function, and the number of decimal places you want to round to as the second.
=ROUND(PI() * POWER(A2, 2), 2)
Calculating the Volume of a Cylinder in Excel
Adding on an extra variable to the equation, you can determine what the volume would be for a cylinder. The equation for the volume of a cylinder is simply the equation for the area of a circle multiplied by the height of the cylinder, h.
In the new spreadsheet, B16 holds the height of the cylinder. With a slight alteration, we can now multiply the original formula we used to find the area by the height.
=ROUND(PI() * POWER(A2, 2) * B$16, 2)
Using the POWER Function to Determine Arbitrary Roots in Excel
The POWER function is often used when composing complex mathematical formulas for graphing purposes. It is easy to visualize such formulas with Excel’s built-in graph and chart tools.
Another way to use the POWER function is to create an arbitrary root calculator. Excel has a SQRT function built in for finding square roots, but for cubic roots, or any root larger than 2, you’ll need to do some extra math.
Any root can be found by raising the number to one divided by the root number. A cubic root calculator can be made by raising any number to 1/3. Excel’s POWER function allows you to quickly and easily find arbitrary roots.
Using one divided by any number as the exponent will convert the POWER function into a root finder for that number. As an example, the below function will find the cubic root of the number in A2:
=POWER(A2, 1/3)
Divide one by any real number to create an arbitrary root finding function by referencing a cell in your workbook as the denominator in the second argument:
=ROUND(POWER(A2, 1/$B$12), 2)
The POWER function can also be used as an easy way to display scientific notation. Displaying your numbers in scientific notation will allow you to work more easily with massive values or minimal ones. Scientific notation can be easily created by using the POWER function on 10, and either a positive number or a negative number:
=A2 * POWER(10, B2)
You can even use the POWER function as part of a calculator that uses scientific notation. You can perform basic arithmetic on complex numbers while maintaining them in an easy-to-read format:
=SUM(A2 * POWER(10, B2), C2 * POWER(10, D2))
=(A2 * POWER(10, B2)) - (C2 * POWER(10, D2))
What Makes the Excel POWER Function So Useful
By itself, the POWER function can act as a quick way to find squares and cubes, or arbitrary powers. It can quickly calculate ranges of roots by passing in an array formula to create tables of exponents.
Where Excel’s POWER function becomes most useful, however, is when blended into complex mathematical formulas. The POWER function can be used to solve a variety of basic geometry problems, calculate arbitrary roots, and create graphs of complex mathematical formulas.
It can even be used to create a simple scientific notation calculator. All of these features make the POWER function a must-know for anyone working with complex mathematical data.