Engineering Notation in Excel

In this post, you’ll learn how to display numbers in Excel using engineering notation, how engineering notation differs from scientific notation, and how custom formats work in Excel

Contents

[toc]

Video

Engineering Notation in Excel

Elevate Your Engineering With Excel

Advance in Excel with engineering-focused training that equips you with the skills to streamline projects and accelerate your career.

Find Out More

Numbers can be displayed in Excel using engineering notation by creating the following custom format:

##0.0E+0

For more information about displaying numbers in engineering notation in Excel, continue reading:

Engineering Notation Background

Engineering notation is a way of expressing either very large or very small numbers in a readable way using exponential notation.

Engineering notation allows us to quickly determine whether the prefix on a unit is milli-, micro-, nano-, kilo-, mega-, giga-, or so on.

Scientific notation also provides a way of displaying very small and very large numbers, but it’s not as convenient because the exponent can be any integer.

By default, Excel can display values using scientific notation, but it does not have an engineering notation format built in. However, we can use a custom number format to display numbers this way.

Worksheet Inputs

To start, I’ll enter a number into the spreadsheet in cell B2. Just for fun, let’s just give it the unit of wiffles (believe it or not, this is a legitimate unit of measurement).

Worksheet Outputs

In cell B4, we’ll display the number in engineering notation. So, I’ll set cell B4 equal to cell B2.

How to Display Engineering Notation in Excel

Next, let’s create the custom format for cell B4 to show the value in engineering notation.

To do that, select B4 and click the icon at the lower right of the Number group in the Home tab of Excel ribbon.

Custom Format for Engineering Notation

In the number tab, select Custom.

And then in the “Type” field, enter the symbols to create the custom format:

##0.0E+0

We can see a preview of how the number in the cell will look in the Sample field at the top of the window.

Click OK.

The value in cell B4 is in engineering notation because 10 (represented by the letter E) is raised to the power of a number that is a multiple of three. In this case, it’s exactly three. So, there are 654.3 kilowiffles.

Engineering Notation vs. Scientific Notation in Excel

Let’s compare that to how the number would be displayed in scientific notation.

Set cell B6 equal the cell B2 and select cell B6.

Then, from the number group in the home ribbon, select the scientific format.

Scientific notation is very similar, but there’s one key distinction. The exponent is not a multiple of three, which makes it difficult to understand the prefix on the units.

How Engineering Notation Responds to Changes in the Magnitude of a Cell Value

When we increase the order of magnitude of the input value in cell B2, the value in cell B4 changes to 6.5E+6 or 6.5 times 10 raised to the power of six. Six is a multiple of three, so this is still engineering notation. And because we’re using engineering notation, it’s easy to tell that now we are talking about 6.5 mega-wiffles.

This engineering notation works, even when we’re talking about numbers that are less than zero. If a decimal point is added to the beginning of the number in cell B2, now we have 654.3 milli-wiffles.

How the Excel Custom Format for Engineering Notation Works

Let’s take a closer look at the custom number format we created to understand how engineering notation works in Excel.

The “#”and “0” placeholders in the custom format each do specific things.

Whenever there is a zero in a custom format, a digit will be displayed there, even if it is an insignificant zero. With this format, there will always be a digit directly to the right and left of the decimal point because there are zeroes on either side of the decimal point.

The “#” sign is essentially an optional placeholder. It will not display insignificant zeros and will only display a digit when there is one to be displayed.

The combination of two #’s and a 0 on the left side of the decimal point forces Excel to display between one and three digits to the left of the decimal point. This will yield a number between zero and 999 on the left side. If the number is 1000, it will be displayed as 1.0E+3.

In this format, there will always be one digit displayed to the right of the decimal point. We can increase that by adding more zeros to the right of the decimal point.