Time
8 hours 33 minutes
Difficulty
Intermediate
CEU/CPE
9

Video Transcription

00:00
So let's start with how to work with the Rays in Excel.
00:06
So in this lesson, I'm going to talk about what? Our dear raising excel and what are the array? Constance.
00:14
So my definition in Excel
00:17
arrays a set or collection of cells that can be operated on collectively.
00:23
Ah,
00:25
so a raise can be one dimensional, like a single row or single called,
00:30
and they could be to them and show consisting of more rows and columns.
00:34
But they have to Group B group, so you cannot have, for example, cold A and see when the rate has to be a B or A B, C or B C. But A C is not possible.
00:47
Uh,
00:48
and you can create a rate Constance by typing in a single cell. But this array constant is going to spread across multiple cells.
01:00
So, um,
01:00
in excel, there is no need to specially define array.
01:07
So if you're using a rainha function, it is just important that use correct sells for you. But there were you doing and to enter it in a special, and we're going to talk about it
01:17
very soon,
01:19
so formulas with the rays can be single cell formulas or multi cell for once, and the vehicles owned. Going to see examples of that
01:26
and the typical use of a raise is combining into functions.
01:32
So let's start with the basics.
01:36
So let's say we want to make horizontal array. We're going to select a couple of cells, for example, is safe four of them,
01:45
and then
01:47
we're just going to type
01:49
equals. And then we're going to open curvy brackets.
01:53
And then we're gonna just type one comma to come a three. Come on, four.
02:00
It is not necessary that you enter
02:02
as many
02:05
numbers or whatever
02:07
constant separated by commas
02:09
in this field. And I'm going to explain to you how it looks a little bit later if you do it in another way. But anyway, you close the curry brackets and then you press, and this is the most important thing. We're working with the race. You don't press, enter you press control shift, enter
02:29
and then you have a ray.
02:30
And if you look at the cells, they all contain the same content that you have entered in first cell,
02:40
where you type the array for vertical array. It can be
02:46
it is the same way off, Anthony. Pretty much
02:51
so. You're select cells
02:53
and then you type equals you open career bracket and for example, we're now entering text and we're going to say
03:00
Monday
03:04
to use the
03:06
sorry
03:07
used a
03:12
Then stay
03:15
There is the
03:19
and
03:22
Friday
03:23
But I have intentionally made the mistake in this just to show you that you shouldn't use comma,
03:31
you should use a semicolon because in order to separate
03:36
Rose in excel, you need to use semi colon.
03:40
So this is a typical mistake that everybody is making when they start working with the race
03:46
and they by default goto separating elements in array with comma. And this is yes, the case with
03:54
horizontal rays. But when you doing vertical raise, the separator is Sammy Cohn. So I have entered this array constant, and then I can press control shift into her again.
04:09
And this is what we get to the end.
04:12
Two dimensional re
04:14
is entered in the same way. So let's do it very simply,
04:18
Two by two
04:20
b type
04:21
equals career. Break it
04:25
and then one comma two and then semicolon. This way we're telling Excel, go into next row and then three
04:33
four Close the curry brackets and we type control shift, Enter.
04:41
And we have a mistake because we have entered,
04:46
uh,
04:46
one common to many.
04:49
And this is what I want to tell you. So excel will warn you if you're typing something that doesn't fit
04:57
the XL. Ah, logic off designing a raise. So you have to go and correct It is not going to offer you out to correct like with some functions.
05:10
So you correct in the new place. Control, shift, enter.
05:15
And here it is.
05:15
And the other thing about the race and why are they so interesting is if I want to change this cell and I just want to type five here.
05:26
What I'm going to get is also an error which says you cannot change part often array.
05:32
This is very, very important thing, and we're going to talk about the importance of it
05:39
later during this course. But they suggest for you to understand what I'm talking about.
05:45
So you have to press cape, go back to original, and if you want to change this cell, you have to select again. Entire array constant, and it's time of two to type five and again to press control shift enter.
06:00
And now you have your worry corrected.
06:02
Also, if you select four cells
06:05
here
06:06
and you type equals career, break it. 123
06:12
and then you close
06:14
and press control shift enter.
06:15
So you have entered three,
06:17
uh, fields in the four field horizontal ary.
06:23
What you get is this.
06:26
So it's there to remind you that you haven't entered all the numbers you need to fill
06:31
the ray you have selected.
06:33
It can be corrected later, off course by again selecting this
06:39
and then
06:42
pressing
06:44
comma leading to another number.
06:46
I have to before
06:48
control shift, enter. And now you get it right.
06:55
So imagine you, You already have
06:59
data entered in worksheet and you want to make
07:03
ah
07:04
array constant out of it.
07:06
So what you do, you select the equivalent
07:12
selection of cells somewhere else,
07:15
and then you go here and then you type equals,
07:19
and then you just select these cells you already have entered in your data sheet
07:25
in your press control shift, enter.
07:28
And now this is an array
07:30
which is linked to this data. So if you put here number nine instead of F
07:38
you will get it automatically changed here.
07:42
What is also very important about this is that here you cannot change anything,
07:48
so you'll get an error and you'll say, Cancel you revert back to the old thing. So if this is summer in another worksheet and you keep it kibun
08:01
and you're giving this to somebody else to just do calculation on the date that they cannot change them, they cannot easily manipulate them. And what is most important, they cannot change his data by mistake or by
08:15
just not paying enough attention to what they're doing.
08:18
So if you were giving some Excel sheet for your students to do some practice with it, you can protect the road data that you're using,
08:30
uh, from accidental manipulation.
08:33
And now we're going to close this. Listen,
08:39
So you have learned in this lesson how to use it. Sell Constance

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