Time
8 hours 33 minutes
Difficulty
Intermediate
CEU/CPE
9

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

Instructor Profile Image
Milan Cetic
IT Security Consultant
Instructor