• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer
EngineerExcel

EngineerExcel

FREE EBOOK
  • About
  • Course
  • Free Training
  • Resources
  • Login

How to Use Array Constants in Excel

Array constants can be used to create arrays, or to modify existing arrays in a worksheet.

The worksheet is set up for a vertical array and a horizontal array. First, we’ll use array constants to fill in the x column of the vertical array. Select the cells in this column. An array is designated with a curly bracket { after the equals sign. In a vertical array, the constants are separated by semicolons.

Type into the formula bar:

[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]

={1;2;3;4}

Type Ctrl-Shift-Enter to fill in the vertical array with those constants:

If you forget and use Enter instead of Ctrl-Shift-Enter, the formula won’t be applied to the whole array.

To fill in the x2 column, we can perform an operation as in the previous section. To do this, select the empty cells in the x2 column. In the formula bar, enter an equals sign, then select the x values in column B. Add ^2 to raise it to the second power:

=B5:B8^2

Then type Ctrl-Shift-Enter. No curly brackets are needed for this method.

However, it’s possible to use array constants to fill in the x2 column and the x3 column together, with a single array formula.

First, select all the cells to populate. Go to the formula bar and enter an equals sign. Select the x values, and add a carat (^). So far you should have:

[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]

=B5:B8^

Next, we’ll add the exponents as a horizontal array. In a horizontal array, the values are separated with commas.

=B5:B8^{2,3}

Type Ctrl-Shift-Enter, and the formula will populate the other two columns.

There is a similar table in a horizontal orientation. To fill in the x values, select the empty cells, then enter into the formula bar:

={1,2,3,4}

Then type Ctrl-Shift-Enter. Remember to separate the values with commas to obtain a horizontal array.

To fill in the remainder of the horizontal array, select the remaining empty cells. In the formula bar, type:

    =G4:J4^{2;3}

Remember to use a semicolon to create the vertical component of this array. Again, type Ctrl-Shift-Enter to enter it as an array formula.

[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]

Primary Sidebar

Featured Posts

How to Look Up Data in 3D Excel Tables

Dynamic Chart Titles in Excel

LN, LOG, LOG10, EXP, SQRT, and FACT Functions in Excel

Using Excel Solver for Linear Regression

AND and OR Functions in Excel

About Me: Charlie Young, P.E.

I’m a licensed professional engineer with a degree in Mechanical Engineering and over a decade of practical experience building engineering applications in Excel. My goal is to help you learn how to turn Excel into a powerful engineering tool.

If you’re interested in learning more, click the button below to receive an update whenever I have a new tip to share. I’ll also send you a copy of my free eBook “10 Smarter Ways to Use Excel for Engineering”.


Subscribe Now

Footer

SOCIAL

Keep up with EngineerExcel:
  • Email
  • RSS
  • YouTube
EngineerExcel

Free Course

Navigation

  • Home
  • About
  • Free Course
  • Excel Tips
  • Resources
  • Coupon
  • Login

Support

  • Support
  • Terms
  • Privacy

Copyright © 2021 EngineerExcel.com · Log in