# 2.3 Multi-Cell Array Formulas

Video Activity

Join over 3 million cybersecurity professionals advancing their career

Sign up with

or

Already have an account? Sign In »

Video Transcription

00:00

Okay, Uh, this lesson.

00:04

We're going to talk about multi celled array formulas. So previously you have seen a lot of examples how a raise congee used in formulas and functions. But in all of these cases, we have bean,

00:20

um,

00:21

creating a formula with the ray in one single. So

00:27

now, sometimes it is needed to create a group of cells with formulas

00:33

that use race.

00:36

And to do that, it's

00:40

It's very simple. It's just slightly different than what you have seen before.

00:45

So let's go to this example here. So we have a group of cells

00:51

and we essentially want to

00:53

used them

00:55

as an array. So this is simple data entered cell by cell and excel,

01:02

and we select the group of cells we want to turn to array,

01:07

and then we just type equals. And then we referenced the cells were going to use. And of course, we press control shift Enter.

01:17

Now we have got

01:19

an array. You see, this is an array which references to these four cells.

01:26

It cannot be changed. So if I want to type eight here, I will get the usual error message, which is typical for

01:34

trying to meddle with the ray

01:38

and we have to press escape to buy that. But we made a mistake and we didn't select all the cells when it did.

01:47

So we're just going to select the

01:49

*** selection we need.

01:52

And we're going to just type B three instead of two

01:57

and then press again. Control, shift, enter.

02:00

And now we have the Arabia ones.

02:02

Now, having that done in the same worksheet is usually something you won't be doing.

02:08

But if you have RO dating one worksheet and you want to do something with it in another worksheet, you two are so burkas. Well, is this one? So you will just references to another worksheet.

02:22

So, um,

02:23

imagine now that we actually want to turn this into array constant. So this is now a formula. So this is references to this. So if you change, hear something by mistake,

02:36

it will be changed. Here is Well,

02:38

we don't want that.

02:39

So we're going to select our array and press F two, which will turn us into edit moat.

02:46

And then we will press F nine, which will recalculate

02:51

the formulas and put them in as Constance.

02:55

You see this? We now got an array.

02:59

And now when we press control shift, enter,

03:01

we got an array which again behaves like an array. So we cannot change

03:07

just one so

03:09

cannot do that by accident.

03:13

So

03:14

this is

03:15

very good thing to do.

03:19

And if you want

03:20

to leave your data unchangeable

03:23

or not accidentally changeable

03:27

Now, I also if you don't need the original data,

03:31

you can know simply

03:35

select the array.

03:36

His control X

03:38

go, Pete here

03:39

and now you have the selection, which is the road data, which is now

03:45

ah,

03:46

array

03:46

constant, which is very nice. If you want to preserve your

03:53

decorative data from accidental change.

03:57

Now let's move to the next example.

04:00

So this is very simple calculation. Some students are doing measurements there. They have a path. The object is moving there, measuring time in orderto determined the every speed.

04:13

So we know that speed is equal path over time.

04:17

So we're going to just do this calculation here

04:23

and say that speed is equal

04:27

slash time

04:30

and of course, we're going to get their message. Now. If you want this to expand everywhere,

04:35

we'll just drag this little

04:40

black box and get it here.

04:42

And now if I'm a teacher and I want to give this to my students, I don't want them to be able to mess with this

04:51

because they will do that on purpose. But because they will do that by accident, because maybe they don't know how to use Excel Well,

05:00

so in order to do it as a Raymond formula multi celery formula, it's just going to delete this.

05:09

And then we're going to leave these self selected because we want all the formulas in these cells

05:15

and then we're just going to say, OK, this divided by

05:20

this

05:23

and we're going to press control shift. Enter the usual thing, idiot of Henry, right formula.

05:30

And now when we enter here,

05:33

there were three in two,

05:36

and here we had

05:40

three

05:41

and 1.9,

05:45

and here again we have the same path and it's going to be 2.2

05:50

We're going to have different results, but these cells now cannot be changed by it accident like any other. Hurry.

06:00

So this shows you the example off multi cell array formula

06:05

and the main purpose of multi celebrate formula would be to have it

06:11

protected from accidental or intentional change. Of course, if you want to protect it against intentional change, you also have to protect these cells with password the things we're going to talk about much later.

06:25

But this is something that can help you create worksheet that is going to be used by somebody else, and you want to make sure that it stays

06:35

as it was, so so that the formulas remained with their integrity.

06:43

Uh,

06:44

also, the use off multi cell formulas can be

06:50

interesting for people who are creating something just for viewing,

06:59

also against accidental change. And they don't want to go into the whole process off protecting the worksheet, protecting cells with password. You just want to keep them protected from somebody accidentally pressing on the keyboard.

Up Next

Intermediate Excel

Being an intermediate level user of Excel can help save a user hours of work time and take full advantage of the Excel application. Taking an intermediate Excel training will help students advance their skills and pass an intermediate Excel exam.

Instructed By

Similar Content