1. Defining Named RangesCreating a named range| 00:00 | When you create dynamic charts
you'll find that assigning names to cells
| | 00:04 | rather than using the cell addresses and
formulas make your formulas easier to understand.
| | 00:08 | In this lesson I'll show you how to
create static named ranges, which don't
| | 00:12 | change unless you edit the references,
and dynamic named ranges, which expand to
| | 00:16 | include data that you add to the list.
| | 00:19 | The most straightforward way to
create a named range is to select the cell
| | 00:22 | range and then type a name for the range in the
name box at the top left corner of the worksheet.
| | 00:27 | For example, if I wanted to make
cells C2 through C6 into a range called Sales,
| | 00:33 | I could click in the Name box
here after selecting the cells,
| | 00:37 | type Sales and press Enter.
| | 00:41 | Now, if I wanted to select those cells,
I could click the down arrow in the Name box,
| | 00:45 | click Sales and they are selected.
| | 00:49 | In a similar way, if I want to
find the sum of the cells in the Sales
| | 00:53 | named range, I can type Sales or
use the formula AutoComplete, press a
| | 01:00 | right parenthesis to close the
formula, hit Enter, and there you see the
| | 01:04 | total of these five cells.
| | 01:07 | One limitation of static named ranges is
that if you add data below the existing range,
| | 01:11 | Excel does not include
those new values in its formula.
| | 01:16 | So for example, if I were to type in
for the year 2010, Quarter number 2, Sales
| | 01:22 | of let's call it $500,000 and press
Enter, Excel would not include those values
| | 01:28 | in the Sales named range because the
definition is still only these cells.
| | 01:35 | To include cell C7 in this formula,
I would need to create what's called a
| | 01:39 | dynamic named range.
| | 01:41 | Creating a dynamic named range uses a
formula to define the cells included
| | 01:45 | in the range but the good news is that you
don't need to know exactly what the formula does.
| | 01:49 | You just need to know enough to know how to
modify it to reflect the contents of your worksheet.
| | 01:53 | In this case the cells that currently
contain the data you want to summarize
| | 01:56 | using the named range
appear in cells C2 through C7.
| | 02:01 | To create a dynamic named range that
reflects those cells, on the Formula tab of
| | 02:05 | the ribbon in the Define
Names group, click Define Name.
| | 02:09 | Now, you can type in the name. I'll call
it DynamicSales, and then in the Refers to: box,
| | 02:17 | I can type the definition.
| | 02:21 | In this case instead of using a cell
range, I'll be typing in a formula.
| | 02:27 | That formula is =offset Sheet1, which is
the name of the worksheet I'm currently on,
| | 02:34 | the name of the worksheet is
followed by an exclamation point, then C2 which
| | 02:43 | is the first cell in my data list, 0,0,
which indicates the number of rows and
| | 02:50 | columns that I want to offset.
| | 02:52 | It's a part of the offset function
and you don't need to worry about it but
| | 02:55 | those values do need to be there.
| | 02:57 | And now the next argument gives me the
height of the range that I am defining
| | 03:02 | using the offset formula.
| | 03:03 | What I want to do is count the number
of cells in column C that contain values.
| | 03:08 | However, I'm only interested in the
numbers, so I want to exclude cell C1.
| | 03:14 | In other words, I want to
count 1-2-3-4-5-6, 6 cells with values.
| | 03:18 | To do that I use the counta formula,
which counts the number of numerical
| | 03:22 | values, Sheet 1 which is where we are
working, and then I want to look at all of
| | 03:29 | column C, the reference for that is
$C:$C. I can close with right parenthesis.
| | 03:39 | Now, I subtract one again because I
don't want to count cell C1, which contains
| | 03:44 | the Sales label, and then 1, which
indicates the number of columns that I want
| | 03:51 | to be included in the result.
| | 03:52 | In other words, I want the
result to be one column wide.
| | 03:54 | I will close the formula with a
right parenthesis and press OK.
| | 04:01 | Now if I change this formula to =sum,
Dynamic Sales, you'll see how Excel
| | 04:10 | highlighted the entire column here and
press Return. Excel gives me my result,
| | 04:15 | which includes the $500,000 here.
| | 04:17 | I will change formatting, so
it's a little bit easier to read.
| | 04:20 | Now, let's see what happens
if I add another row of data.
| | 04:25 | So, let's say that I go to Quarter number
3 and for that we have sales of $750,000.
| | 04:32 | When I press Enter, Excel updates
the formula because we are using a
| | 04:35 | dynamic named range.
| | 04:37 | Dynamic named ranges help you
summarize changing lists of data, while still
| | 04:40 | creating human readable formulas.
| | 04:42 | What's more, unlike Excel tables which
I'll cover later, dynamic named ranges
| | 04:46 | are compatible with Excel 2003
and earlier versions of the program.
| | Collapse this transcript |
| Creating an Excel table| 00:00 | One very useful new feature in
Excel 2007 is the Excel table.
| | 00:05 | In Excel 2003, you had lists, which
provided some of the same functionality as
| | 00:09 | Excel tables, but Excel tables take it and run.
| | 00:12 | You can create an Excel table by
setting up your data as a list.
| | 00:16 | In this case, we have data from
A1 all the way down to be D25.
| | 00:21 | There are no breaks. In other words
there are no empty rows, so Excel knows its
| | 00:25 | all part of the same data range.
| | 00:26 | And also there is no extraneous
data to the right or below the table.
| | 00:32 | That's important, because if you
created a table and there was data to the side
| | 00:36 | or data below it, then Excel will try
to include that data in the table even
| | 00:40 | though you wouldn't want it there.
| | 00:41 | To create the table, on the Home tab,
go to the Styles group, click Format as a
| | 00:46 | Table and click the table format you want.
| | 00:50 | Verify that Excel identified the table
data, A1 through D25, that your table has
| | 00:55 | headers, it does, those are
here in A1 through D1, and click OK.
| | 01:01 | And there is your Excel table.
| | 01:03 | The first thing you'll notice is that
you have filter arrows, which allow you to
| | 01:06 | limit the data that appears in the table.
| | 01:08 | So let's say for example that I only
wanted to see data from the month of January.
| | 01:13 | I can click the Month columns filter
arrow, clear Select All, click January,
| | 01:19 | click OK and Excel limits my data.
| | 01:25 | In the context of dynamic charts,
Excel tables are very useful because they
| | 01:29 | allow you to create expandable ranges.
| | 01:31 | In other words, an Excel table is a
collection of dynamic named ranges.
| | 01:35 | So for example, if I wanted to refer
to this Excel table in a formula, I would
| | 01:39 | type =sum and then the name of the table.
| | 01:43 | When Excel creates a table,
it uses names such as Table1, Table2.
| | 01:48 | This one will be named Table1 because
it's the first one that has been created.
| | 01:51 | I'll enter that and I want to
find the sum of the Sales values.
| | 01:56 | So I will type a left square
bracket and then type S for Sales.
| | 02:01 | Press Tab to accept it from the Formula
AutoComplete List, right square bracket,
| | 02:06 | right parenthesis closed, hit Return and
there is my value, which I'll format to
| | 02:13 | make it easier to read.
| | 02:14 | If I add data to an Excel
table, that formula will update.
| | 02:18 | So for example, let's say that in
2011 first-quarter month of January,
| | 02:27 | we had Sales of $500,000.
| | 02:32 | As soon as I hit Enter to include the
data in the table, this formula updates.
| | 02:36 | Referring to Excel tables in your
formulas make your formulas easier for you
| | 02:39 | and your colleagues to understand.
| | 02:40 | But remember that Excel tables can
only be used in workbooks created in
| | 02:44 | Excel 2007 and later.
| | 02:46 | That said, if you work with
colleagues who run Excel 2003, they will see any
| | 02:50 | Excel tables as lists, which were the
predecessor to Excel tables included in 2003.
| | 02:55 | If you collaborate with users who
still have Excel 2002 or earlier however,
| | 03:00 | you should use dynamic named ranges to refer
to data columns that you could expand or contract.
| | Collapse this transcript |
|
|
2. Creating Dynamic Charts Using FormulasCreating an expanding chart| 00:00 | One of the more difficult aspects of
managing a business is keeping your charts
| | 00:03 | and graphs current with the data you collect.
| | 00:06 | You can use Excel tables to create
expanding charts in Excel 2007 and create
| | 00:10 | dynamic named ranges to create the
same charts in any version of the program.
| | 00:15 | In Excel 2007, you can create a chart
from a table simply by clicking any cell
| | 00:20 | within the chart and then on the Insert tab,
click the type of chart you want to great.
| | 00:26 | And when you do, Excel creates it.
| | 00:28 | Now there is something interesting about
Excel charting. This is little bit of a
| | 00:32 | digression, but I think
you'll find it worth your time.
| | 00:35 | Notice here in the body of the chart
where I have the months and then the year
| | 00:40 | occurring exactly once here in the bottom.
| | 00:42 | The same thing happens in 2010.
| | 00:44 | The reason that happens is because here
on my table, I have the year 2009 here
| | 00:50 | at the top of the list and it doesn't repeat
until you get to 2010 where you get the next year.
| | 00:57 | If I were to put the value 2009 in
every one of these cells, you can see that
| | 01:04 | Excel writes the value 2009
for each of these data markers.
| | 01:08 | In other words for each month, it
writes the value 2009, the associated year,
| | 01:13 | exactly once, which makes the
chart completely unreadable.
| | 01:17 | Rather than do that, I take away these
values, hit Delete, and we get a chart
| | 01:24 | that's much more visually attractive.
| | 01:26 | Now also notice that if I were
to add another row to the table.
| | 01:31 | In this case, it'll be the year 2011,
January, and say we had sales of
| | 01:36 | $500,000, hit Return and Excel adds
that new data to the chart with the year
| | 01:42 | 2011 and the month of January.
| | 01:45 | In Excel 2003 and earlier, or if you
are creating a workbook you plan to share
| | 01:49 | with users who use Excel 2003 or earlier,
you can create a dynamic named range.
| | 01:55 | So let's say that we're here on this
worksheet and I have this dynamic named range here.
| | 02:03 | On Formulas, I can go to the Name Manager
and I will see the named range that I've created.
| | 02:08 | If you need more information about how
to create a dynamic named range, you can
| | 02:11 | get to the first movie in this course.
| | 02:14 | I have DynamicSales and here
is the definition down here.
| | 02:17 | Everything looks good so I'll click Close.
| | 02:20 | When I do, I can click any cell within
the dynamic named range and then on the
| | 02:25 | Insert tab, click Line because I want to
create a Line Chart this time and click Enter.
| | 02:32 | Now when Excel created
this chart it had two choices.
| | 02:35 | It could use the dynamic named range or
it could just use a cell range and say
| | 02:39 | well, the value for the header is in
cell A1 and the data values that will be
| | 02:45 | plotted in the body of the
chart are in A2 through A25.
| | 02:48 | So let's see what you did.
| | 02:50 | To do that, you click the Chart and then on
the Design contextual tab, click Select Data.
| | 02:55 | And you'll see that
Excel pulled the Sales value.
| | 02:58 | Now the dynamic named range is called
DynamicSales, so we should change that.
| | 03:04 | I'll select the Series name here. Click Edit.
| | 03:08 | The Series name is correct.
| | 03:10 | I want to use Sales, and then for
the Series values, I will type in
| | 03:17 | DynamicRange, which is the name of the
worksheet, !DynamicSales, which is the
| | 03:28 | name of the dynamic named range.
| | 03:30 | I'm done, so I'll click OK.
| | 03:31 | I see them here in the chart.
Click OK and there you have it.
| | 03:38 | Now if I were to add another value, say
500,000 for our next Sales figure, hit Return,
| | 03:45 | Excel adds the data to the chart.
| | 03:48 | Visualizing your data using an
expanding cart helps you make decisions based on
| | 03:51 | the most recent data at your disposal.
| | 03:54 | The easiest way to create these
charts is to use an Excel table as your
| | 03:57 | data-source, but dynamic named
ranges fit the bill just as well.
| | Collapse this transcript |
| Charting data from the beginning of a range to a specified end point| 00:00 | When you plot a chart series in a
dynamic chart, you can select how many
| | 00:03 | elements from the start of
the chart you want to visualize.
| | 00:07 | One example where this type of
analysis would be helpful is if you wanted
| | 00:10 | to examine the first 14 days sales
for a new product to see how momentum
| | 00:14 | did or didn't build.
| | 00:16 | To create a chart that displays a
specified number of data points from the
| | 00:19 | beginning of the chart series,
you need to know two things:
| | 00:22 | the address of the first cell in the
range, and the cell that contains the last
| | 00:25 | value to be plotted.
| | 00:27 | You also need to find the cells that
contain the data labels, but once you know
| | 00:30 | where the data is, it's easy to create
a rule that tells Excel to find a range
| | 00:33 | of the same size, one column to the left.
| | 00:36 | I created two named ranges and I will
show you those here in the Name Manager.
| | 00:42 | One range is named Days;
| | 00:43 | the other is named Sales.
| | 00:46 | The Days range uses a dynamic named
range definition which looks on Sheet 1,
| | 00:51 | which is where we are, starting in
cell A2, which is here, and then it counts
| | 00:58 | down the number of values
found in cell E1, which is here.
| | 01:04 | Currently it contains 3.
| | 01:05 | One thing that I do when I am
creating a dynamic chart is that I always put
| | 01:09 | meaningful data in the cell or cells
that I am using to control the chart.
| | 01:13 | That way when my chart appears for the
first time, I know immediately whether
| | 01:16 | I've got my formulas right.
| | 01:18 | The Sales named range also dynamic
just looks one column to the right.
| | 01:24 | We started the cell B2 and go down by
the number of values found in cell E1.
| | 01:30 | With those named ranges in place
I can start creating the chart.
| | 01:34 | So I'll click Close, select my
data, Insert, a Column Chart.
| | 01:42 | You'll notice that I have two data series here.
| | 01:45 | One in red, one in blue.
| | 01:47 | One of Excel's idiosyncrasies is that
if a column in your data source contains
| | 01:51 | numeric values, Excel plots
it in the body of the chart.
| | 01:55 | That's okay, because you can change it,
but it does make for some interesting
| | 01:58 | looking charts where you have your
days 1 through 14 plotted along with the
| | 02:03 | sales for those days.
| | 02:04 | So how do you change it?
| | 02:05 | Well, first you click the chart to
select it and then on the Design contextual tab,
| | 02:10 | click Select Data.
| | 02:13 | From here you can edit where
the values occur in your chart.
| | 02:16 | So for example, we do not want Day to
be a data series it's plotted in the chart,
| | 02:21 | so click Day and click the Remove button.
| | 02:24 | So we only have Sales, but Sales isn't
exactly right either, because Sales only
| | 02:31 | covers these values.
| | 02:33 | It doesn't allow us to change the
definition, which we can do with the
| | 02:35 | dynamic named range.
| | 02:36 | So I'll click Remove as well.
| | 02:39 | Now I'll click Add, our series name
appears in the Sheet 1, cell B1, and again,
| | 02:47 | this is the data that's going to
appear in the body of the chart, not the axis
| | 02:51 | values yet. Those come next.
| | 02:53 | Also it's vital that you must put
Sheet 1, the worksheet name, in front of the
| | 02:58 | cell and in front of the named
range. Always remember to do that.
| | 03:03 | Now to indicate the series, type in
=sheet1!Sales, which is the name of named
| | 03:13 | range that I want to use.
| | 03:14 | Everything looks good.
| | 03:16 | I'll click OK, and the values
appear in my chart and also in the Select
| | 03:20 | Data Source dialog box.
| | 03:22 | When I click OK, you'll see that my
chart now contains values for days 1, 2 and 3,
| | 03:26 | and that corresponds the number
of days that I have here in cell E1.
| | 03:31 | If I were to type in 4 and press Return,
Excel would go to four days and if I go
| | 03:38 | all the way up to 14,
we see all the data in the chart.
| | 03:40 | Now let's see what happens if I go up to 15,
which is one more data point that we have.
| | 03:45 | When I press Enter, we don't get an
error. Instead Excel says, okay, you want 15 values?
| | 03:53 | Well, I can't find a value for
the 15th part of the chart, so I'll just
| | 03:57 | display 0 or in this case no value at all.
| | 04:01 | If you type negative 1, Excel goes to
the lowest possible value, which is 1,
| | 04:06 | and shows it there.
| | 04:08 | Switching back to 3, we get
our normal look at the data.
| | 04:11 | This dynamic chart enables you to discover the
data transit at the beginning of your series.
| | 04:15 | The early days of your products are
often the most important, so this technique
| | 04:19 | can provide valuable information.
| | Collapse this transcript |
| Charting a specific data range| 00:00 | When you analyze your business,
there'll be times when you want to find data
| | 00:03 | within a specific range.
| | 00:05 | That range may not be defined by days
or years or months, but instead they
| | 00:09 | might be data points within a cell range.
| | 00:13 | When that's the case, you can
create a dynamic chart that allows you to
| | 00:16 | specify which data point you want to start
with and which data point you want to end with.
| | 00:21 | In this chart, I happened to have sales
for days 1 through 14 and I have days 3
| | 00:27 | through 5 inclusive
summarized in the chart right now.
| | 00:31 | If I wanted to change my starting point,
say by going all the way back to day 1,
| | 00:34 | I could edit the value in that
cell and Excel updates the chart.
| | 00:39 | If I want to extend the ending point
all the way out to day number 9, type it in,
| | 00:43 | press Return and the chart changes as well.
| | 00:45 | It's a great way to analyze your data.
| | 00:47 | Now let's go back to the beginning
so I can show you how I got here.
| | 00:51 | Business analysts often need to know
how a product, department or a division
| | 00:54 | performed during a given period.
| | 00:56 | When your data includes states, it's
easy to create a PivotChart and filter the
| | 01:00 | data using a Between rule.
| | 01:02 | If you don't want to use a PivotChart,
perhaps because you colleagues have a
| | 01:05 | version of Excel that doesn't support
them, you can create a dynamic chart that
| | 01:08 | offers the same functionality.
| | 01:10 | To create a dynamic chart that displays a
specific data range, you need to know two things.
| | 01:15 | The address of the first cell to plot
and the address of the last cell to plot.
| | 01:20 | Once you know how to determine the
addresses of those two cells, you can create
| | 01:23 | a dynamic named range based on those cells.
| | 01:26 | In this case, I've defined two dynamic
named ranges based on the values here in
| | 01:32 | cells A1 through A15.
| | 01:35 | I'll show you how the named range for
the Sales dynamic named range works and
| | 01:39 | then you can apply that knowledge
to figure out how the days works.
| | 01:42 | So in this case, I have the Sales named range.
| | 01:45 | This is a complicated formula so what
I'd like to do is copy it and paste it
| | 01:53 | into an Excel worksheet cell, and then
use the Evaluate Formula dialog box to
| | 01:58 | walk you through it step-by-step, and
don't worry about the formula result.
| | 02:03 | It doesn't matter in this case and
I'll type in a couple of starting values.
| | 02:07 | So our Starting Point will be 2 and
our Ending Point will be 5, and
| | 02:12 | you'll notice this value here changed.
| | 02:15 | So I'll click the cell that contains
the formula and on the Formulas tab, go up
| | 02:20 | to Evaluate Formula and
step through it piece-by-piece.
| | 02:23 | The formula appears in the
Evaluate Formula dialog box.
| | 02:27 | Excel underlines the first
item that it is going to evaluate.
| | 02:31 | So here we have Sheet1, which is the
current worksheet, cell H2 and that is here.
| | 02:38 | It contains a value 2.
| | 02:40 | So when I click Evaluate, my next
operation will be 2-1 which equals 1 and now
| | 02:47 | Excel has enough information
to evaluate this OFFSET formula.
| | 02:51 | What this OFFSET formula does is it
says from cell B2 offset by one row,
| | 02:57 | offset by 0 columns, and with one row
high and one column high find the cell
| | 03:03 | that meets that description.
| | 03:04 | I'll click Evaluate and I get B3, and
that is the second cell in the Sales data range,
| | 03:12 | which corresponds to the value
in cell H2, so that part of the formula
| | 03:16 | is working properly.
| | 03:18 | Next, it's going to find the value in cell H3.
| | 03:20 | That's here, so it should be 5, it is,
and now it's going to subtract the value
| | 03:26 | in cell H2 to figure out how far it
should go, in other words how big the data
| | 03:31 | range is going to be.
| | 03:32 | So we have Sheet1 H2, which is 2. Evaluate
and now that would result in a value of 3.
| | 03:41 | But because Excel would count the
first cell, we need to add one to make sure
| | 03:45 | that we get a total of four cells.
| | 03:47 | As if we start to cell 2 and end
at cell 5, we need 2, 3, 4, and 5.
| | 03:54 | That is 4 cells and 5 minus 2 only
leaves us with 3, so we need to add the 1.
| | 04:00 | Click Evaluate, we get 3+1, 4, and
now Excel can evaluate the entire OFFSET function.
| | 04:05 | Click Evaluate and you'll get 27.
| | 04:09 | Now 27 is the numeric value of the last
cell in the range that it's going to use
| | 04:14 | in the formula that we use to create the chart.
| | 04:18 | So that is an overview of
how the OFFSET function works.
| | 04:21 | Now we can create the chart.
| | 04:23 | I'll click any cell in the data over here,
click Insert and we'll do a Column Chart.
| | 04:28 | As before in previous movies, you'll
see that Excel tries to plot both the
| | 04:32 | Day and the Sales together in the chart, but
we can fix that using the Select Data dialog box.
| | 04:38 | So I will remove the two data series
that are currently there. Click Add.
| | 04:42 | The series name is in Sheet1,
cell B1, which is sales.
| | 04:48 | Again these are the data values, not the
axis values, which we'll get to in a moment.
| | 04:52 | Then =sheet1!sales, press OK,
and the values appear in the chart.
| | 05:01 | For the Horizontal (Category) Axis
Labels, I'll click Edit and the range is
| | 05:07 | =sheet1!days press Enter, there
it appears, 2, 3, 4, and 5, click OK, and
| | 05:16 | the values appear in the chart.
| | 05:18 | Now if I were to change the Ending Point to
say 7, Excel would display days 2 through 7.
| | 05:25 | If I change the Starting Point to 1,
we get the first week, days 1 through 7.
| | 05:30 | Even though the Starting Point and
Ending Point values happen to correspond to
| | 05:33 | the values in the Day column of the data
list, you're not using the named ranges
| | 05:36 | to find a match. Instead you're
asking Excel to plot the data starting at a
| | 05:40 | certain list row and ending at another row.
| | Collapse this transcript |
| Specifying the first point and number of points for a series| 00:00 | When you analyze your business
operations, you'll often be asked how well a
| | 00:03 | product performs starting at a
certain point in time and continuing on for
| | 00:07 | another set period of time.
| | 00:09 | If you work for a huge retailer, you
might be asked to measure product sales
| | 00:12 | every few hours and if the product
sales velocity rises above a certain
| | 00:16 | threshold, reorder the product in
the larger quantities than planned.
| | 00:20 | If you want to specify the first point
to plot and then indicate how many total
| | 00:23 | points to plot, you can do so
by creating dynamic named ranges.
| | 00:27 | In this worksheet, I have a small sales
data set and two cells that contain the
| | 00:31 | starting point and the number of points to plot.
| | 00:34 | From that data I can determine the
addresses of the first and last cells to plot
| | 00:37 | in my chart, based on user input.
| | 00:40 | I defined two dynamic named ranges for
the Sales and the Day and I'll just give
| | 00:45 | you a quick look at those.
| | 00:46 | I won't go through them step-by-step
because they are fairly complicated.
| | 00:51 | The Sales range looks in column B and
uses the value in cell H3 that is here to
| | 00:58 | determine the Number of Points to plot.
| | 01:01 | The Day is dynamic name range looks
in column A and uses the value here in
| | 01:06 | cell H3 to determine how many points to plot
based on the Starting Point given in cell H2.
| | 01:13 | Now I can create the chart.
| | 01:15 | I'll click any cell over here in the
table, insert a Column Chart. As we've seen
| | 01:24 | before Excel plots all of the numerical
values in the body of the chart but we
| | 01:28 | can change it. Go to Select Data,
delete the two existing data series, click
| | 01:35 | Add and this is the data that's going to
the body of the chart, so it would be =sheet1.
| | 01:41 | You always need to type the name of the
worksheet before you type in the name of a named range.
| | 01:46 | So we have sheet1!
| | 01:50 | and this is cell B1.
| | 01:55 | The series values are =sheet1!Sales.
| | 02:01 | When I press OK, the Sales data
appears in the body of the chart.
| | 02:06 | Now for the Horizontal or Category
Axis Labels, I'll click Edit and give the
| | 02:12 | Axis label range, which is =sheet1!Days.
| | 02:15 | So now I have values 2, 3, 4 and 5,
which means that it currently starts at 2,
| | 02:24 | and the Number of Points is 4.
| | 02:26 | Click OK and there you see the chart.
| | 02:29 | If I were to start from 4 and have the
Number of Points before, then we see 4 through 4.
| | 02:34 | If I were to type in a number of
points that's greater than the number of
| | 02:37 | remaining points, so let's say that I
start on 4 and I wanted 15 remaining
| | 02:42 | points, when I press Return, we get
all the remaining data in the chart, and
| | 02:46 | it blanks out here on the side.
| | 02:50 | If I were to put the Starting Point
below 1, such as say negative 1, we get an
| | 02:55 | error because that's an invalid reference.
| | 02:57 | I will switch that back to start at
point number 1 which gives us 1 through 9.
| | 03:04 | This dynamic chart provides useful
insights into how your enterprise performs
| | 03:07 | over a specific period of time,
offering you valuable insights to help you
| | 03:11 | make better decisions.
| | Collapse this transcript |
| Plotting the last number of data points in a series| 00:00 | When you a plot a chart series in a
dynamic chart, you can select how many
| | 00:03 | elements from the end of the
chart you want to visualize.
| | 00:07 | One example for this type of analysis
would be helpful is if a product's sales
| | 00:11 | decreased suddenly and you wanted to
examine the last five days of sales to see
| | 00:15 | if there was any indication that
a change is going to take place.
| | 00:18 | To create a chart that displays a
specified number of data points for the end of
| | 00:21 | the data series you need to know two
things: the address of the last sale on the
| | 00:25 | range and the cell that
contains the first value to be plotted.
| | 00:29 | You also need to find the cells that
contain the data labels but once you know
| | 00:33 | where the data is, it's easy to create a
rule that tells Excel to find the range
| | 00:36 | of the same size one column to the left.
| | 00:39 | In this case I created named ranges to
calculate the address of the last sale in
| | 00:43 | the Sales dynamic named range
and I'll show you that right now.
| | 00:47 | Under Formulas, go to the Name Manager and
you'll see the four named ranges that I created.
| | 00:54 | Looking for the LastSaleCell, it looks
in column B and works down from cell B1,
| | 01:01 | counts the number of values and says
very well, the cell that is the number of
| | 01:06 | values below the first cell
minus one is where the n must be.
| | 01:10 | So that's all that formula does.
| | 01:12 | LastDateCell does exactly the
same thing except of column A.
| | 01:17 | The sales from End and
Days from End named ranges
| | 01:20 | calculate the number of cells that it
needs to go up from the last cell in that
| | 01:25 | particular column for example days or
sales and then creates a range going from
| | 01:31 | that cell to the last cell on the list.
| | 01:33 | With those named ranges in place,
I can create my dynamic chart.
| | 01:36 | So, if I click any cell in the data
table and insert a column chart, once again
| | 01:44 | the data shows up incorrectly but I can
select the data, remove the two existing
| | 01:51 | series and add my own.
| | 01:52 | The series name and this is for the
data in the body of the chart as opposed
| | 01:56 | to the Axis values, so it is sales =
sheet1!b1 and you do need to have the
| | 02:03 | sheet 1 indicator there to tell Excel
where to look on which worksheet and
| | 02:09 | then for the series values it's =sheet1
!salesfromend and my values appear in
| | 02:18 | the chart and they look correct.
| | 02:20 | Now, also do the same thing for the Axis Labels.
| | 02:23 | =sheet1!daysfromend.
| | 02:28 | Got my Sales, days 13 and 14 which are
the last two in my data series, click OK,
| | 02:35 | and my chart looks to be correct and
changing the value here in Points to Plot,
| | 02:40 | say I wanted it to be 4, I get 4 days of
values. If I wanted to do 10, I get 10.
| | 02:45 | Now, there are only 14 values in the series.
| | 02:48 | If I were to type 15, Excel would fill
in days 1 through 14 and it would go back
| | 02:54 | and have a blank here for day 0.
| | 02:55 | If I were to do 16, I get an error
because it tries to put a negative number
| | 03:00 | into a formula that isn't
capable of handling them.
| | 03:03 | So click OK and I will type 12 and
we got the last 12 days worth of data.
| | 03:11 | This dynamic chart enables you to discover
the data trends at the end of your series.
| | 03:16 | These values can provide terrific insights
into what has just happened with your business.
| | Collapse this transcript |
| Plotting points at regular intervals in a series| 00:00 | If you work with large data sets,
you might find it useful to get a
| | 00:03 | coarser grained version of your data by
plotting every third, fifth or even tenth point.
| | 00:09 | To do so, you need to combine an Excel
table with a bit of math and the filter.
| | 00:13 | It's a multi step process but it's worth it.
| | 00:15 | In this case your goal is to plot
every few rows in your Excel table and you
| | 00:19 | need to have a way to identify which
rows of the third, fifth, tenth or at
| | 00:22 | whatever increment you desire.
| | 00:24 | That data doesn't appear in this table,
so I'll have to add a column and use a
| | 00:28 | formula to determine which
rows fall in the increment I want.
| | 00:31 | To do that I'll use arithmetic
operation called modular division, which divides
| | 00:35 | one number by another number like
regular division but returns the remainder.
| | 00:40 | In other words 10 mod 4 would have
an answer of 2, which is the remainder
| | 00:44 | when you divide 10 by 4.
| | 00:46 | The formula are used to calculate which
rows to plot uses the value in cell G1
| | 00:50 | as a divisor and the row number of
the cells in column C as the dividend.
| | 00:55 | First I will add a new column to the table.
| | 00:58 | I'll just call it Modular for
modular division and hit Enter.
| | 01:02 | When I do Excel adds the row to the
table and double-clicking this line here
| | 01:08 | expands the column to fit its contents.
| | 01:11 | Now that I have the column in place,
I can type in my formula and that is =mod
| | 01:15 | again for modular division, row, the
row number of C3 and I will leave the
| | 01:21 | reference to C3 relative so that it
will change when Excel copies the formula down.
| | 01:26 | Subtract two, so that I get the
proper row number, comma, and cell G1 and
| | 01:33 | I will make this reference absolute
because the value that I want to divide by is
| | 01:37 | always going to be a in cell G1.
| | 01:39 | It will never change.
| | 01:40 | Therefore, I use an absolute reference.
| | 01:44 | Close with parenthesis, hit Return
and every third cell has the value 0 and
| | 01:52 | that's the result I need but also notice that
Excel added the modular values to the chart.
| | 01:58 | I don't want this to be there, so I
click the chart, go to the Design tab,
| | 02:04 | select Data and remove the Modular series.
| | 02:09 | Click OK and my chart is back to normal.
| | 02:12 | Now I can filter the Excel table by
the values in the Modular column, which
| | 02:16 | leads Excel to update the chart.
| | 02:18 | I would do that by clicking Modular and
then I want to display every third value.
| | 02:25 | Those are the ones that contain the
value 0, so I'll click Select All, click 0,
| | 02:32 | click OK and Excel changes my chart.
| | 02:37 | If I want to change the value in the
interval cell, which is cell G1, say I want
| | 02:42 | to go to 5, press Enter and you'll see
that the values over here in the Excel
| | 02:48 | table changed but the chart did not update.
| | 02:50 | To make the chart update,
I need to reapply the filter.
| | 02:53 | So I go to the Modular, clear gilter,
Select All, 0, click OK and I get my data.
| | 03:04 | Plotting points at regular intervals
provide a useful summary of your data
| | 03:07 | particularly when you work with huge data sets.
| | Collapse this transcript |
|
|
3. Sorting and Filtering PivotTable DataCreating a PivotChart| 00:00 | PivotTables help you summarize large
dataset efficiently but it can be difficult
| | 00:04 | to interpret data when all you
have to go on are the raw numbers.
| | 00:07 | Charts on the other hand summarize data
visually making it easier to distinguish
| | 00:11 | groupings and trends of your data.
| | 00:13 | Just as you can create charts based
on regular worksheet datasets, you can
| | 00:16 | create dynamic charts called PivotCharts
from the data contained in PivotTables.
| | 00:20 | There are two ways to create a PivotChart.
| | 00:23 | You can either create a PivotTable and
the PivotChart at the same time or you
| | 00:26 | can create a PivotChart
from an existing PivotTable.
| | 00:29 | I should point out that because a
PivotChart summarizes the data in a
| | 00:33 | PivotTable, it's not possible
to create a PivotChart by itself.
| | 00:36 | Because your PivotChart is based on a
PivotTable, your data source must be
| | 00:40 | formatted as a data list, preferably
an Excel table, so the program can create
| | 00:44 | the PivotTable that powers a PivotChart.
| | 00:47 | To create a PivotChart at the same time
you create a PivotTable, click any cell
| | 00:51 | in the data source and then click Insert.
| | 00:55 | Click the PivotTable button's
down arrow and click PivotChart.
| | 00:59 | When you do, Excel displays that Create
PivotTable with PivotChart dialog box.
| | 01:03 | You can verify that the
proper data source is selected.
| | 01:06 | You can see the marquee here, and indicate you
want you want to create it on a new worksheet.
| | 01:11 | That's fine, click OK and
Excel creates your PivotChart.
| | 01:15 | I personally prefer to filter my
PivotTable using the controls in the
| | 01:19 | PivotTable Field List.
| | 01:20 | So I will just close the PivotChart Filter pane.
| | 01:22 | After you create your PivotChart, you
can arrange the fields using the controls
| | 01:26 | in the PivotTable Field List task pane
just like you would for a PivotTable.
| | 01:30 | So here, I will add Year to the Axis Fields,
which provide the row headers, and Month.
| | 01:37 | I will put Company for Legend Fields,
which provide the Axis values, and then I
| | 01:44 | will click Revenue, put that in the Values area.
| | 01:48 | Now notice when you created dynamic
charts earlier in this course, if your data
| | 01:52 | source had values in every cell for the
year, then Excel would try to put 2008,
| | 01:56 | 2008, 2008, 2008 in these values here.
| | 02:01 | But instead because you created the
PivotChart, you can have all of those
| | 02:05 | years in your data source as you see
here but Excel knows to put 2008 and 2009
| | 02:11 | by themselves at the bottom of the
PivotChart. Just move this little bit here.
| | 02:16 | There are some differences between
regular Excel charts and PivotCharts.
| | 02:20 | The most important ones are that you
can't switch the row and column orientation
| | 02:24 | of the PivotChart by using the
Select Data Source dialog box.
| | 02:27 | Of course, you can rearrange your data
by pivoting the PivotChart, so that's
| | 02:31 | not much of a loss.
| | 02:32 | There are three chart types you can't
use when you are creating a PivotChart.
| | 02:36 | Those are an XY or Scatter chart,
a Stock chart or a Bubble chart.
| | 02:40 | Refreshing a PivotChart removes any
trend lines, data labels, error bars and a
| | 02:44 | few other less common
settings you might have applied.
| | 02:47 | Finally, if you'd rather have your
PivotChart reside on the separate worksheet
| | 02:50 | from the PivotTable, click the
PivotChart, and then on the Design tab, click
| | 02:56 | Move Chart, select the chart where you
want to move it, in this case, I will put
| | 03:01 | it on the new chart sheet called
Chart1, click OK and there it goes.
| | 03:06 | Putting a PivotChart on its own chart sheet
makes a chart larger and easier to understand.
| | 03:10 | PivotCharts enable you to summarize
your data visually, providing an overview
| | 03:14 | of your data and opening the door to
insights you might not discover from
| | 03:17 | looking at the raw numbers.
| | 03:18 | You will find that PivotCharts are
powerful tools that help you analyze your
| | 03:21 | enterprise's data effectively.
| | Collapse this transcript |
| Pivoting a PivotChart| 00:00 | The real power of a PivotChart comes to the
fore when you rearrange your data dynamically.
| | 00:04 | A task that would take several minutes
if done by hand, takes just a few seconds
| | 00:08 | when you summarize your data using a PivotChart.
| | 00:10 | In a PivotChart, the row and column
headers provide the basic structure for your
| | 00:13 | PivotChart and those are here in the
Axis Fields, which provide the categories
| | 00:18 | or the vertical axis, and Legend Fields,
which provide the data series indicated
| | 00:23 | here as FirmA and FirmB, and finally
you have the Revenue values here in the
| | 00:29 | Values area, and those are plotted
in the data area of the PivotChart.
| | 00:34 | Changing row and column header
positions changes the data's arrangement within
| | 00:38 | the chart but exactly how each pivot
affects a PivotChart depends on the
| | 00:41 | PivotChart's chart type.
| | 00:43 | So let's say that I were to move Company
down here below Year and Month. I'd get
| | 00:47 | a very crowded chart as you can see.
| | 00:51 | If I take Month and put it in Legends
then I get my data summarized by month,
| | 00:57 | then by company and then by year.
| | 01:00 | I f I put Year and Month in the Legends
Field area, then I get a separate data bar
| | 01:05 | for 2008 January, 2008 February and so on.
| | 01:08 | I am just showing these different
pivot positions to give you an idea of what
| | 01:12 | happens when you pivot your PivotTable.
| | 01:14 | This is definitely not a chart
you'd want to display at a meeting.
| | 01:17 | Now I'll return Month and Year
to here and move Company back.
| | 01:25 | One last thing I'd like to show you
is how to defer updating the PivotTable
| | 01:28 | until you've completed all the
pivots that you want to make.
| | 01:31 | If you work with a large data source
and particularly,one that you have to
| | 01:34 | access over a network, whether it might
be some network lag or other delays, then
| | 01:38 | delaying updates allows you to make all
of your changes and then only update the
| | 01:42 | data in the PivotTable once.
| | 01:44 | So if I check the Defer Layout Update
box, make my changes, I'll just put Year
| | 01:50 | here and take Month out of the PivotTable.
| | 01:52 | I'll click Update and Excel updates the
PivotTable and I'm okay with all of my
| | 02:01 | changes happening as I make them for now on.
| | 02:03 | So I'll clear the Defer Layout Update checkbox.
| | 02:06 | Change in the PivotChart's arrangement
shifts the data's emphasis, enabling you to
| | 02:10 | examine the data from different
perspectives quickly and easily.
| | Collapse this transcript |
| Filtering a PivotChart| 00:00 | PivotCharts can summarize huge data
collections but many times you'll want to
| | 00:04 | limit the data displayed in a given category.
| | 00:07 | You can limit the data summarized
by a PivotChart by creating a filter.
| | 00:11 | There are two main ways to
filter the contents of a PivotChart:
| | 00:13 | by selection or by rule.
| | 00:16 | Filtering by selection means that you
display a list of values in the field and
| | 00:20 | select the values you want to display.
| | 00:21 | Filtering by rule means that you
create criteria that Excel uses to select
| | 00:25 | which values to display.
| | 00:27 | If you want to filter the values in
your PivotChart, you can go to the
| | 00:30 | PivotTable Field List and then click the
header for the field that you want to filter.
| | 00:35 | In this case, I'll filter by selection.
| | 00:37 | So for Year, I will clear Select All and
then select the year 2009 and click OK.
| | 00:45 | If I want to get rid of the filter, I
can go back to the field header, which has
| | 00:50 | a funnel indicating it is filtered,
click the down arrow and click Clear Filter
| | 00:56 | From "Year" and Excel restores my
PivotChart to its original configuration.
| | 01:00 | If I want to filter by rule, then I can click
the header of the field that I want to filter.
| | 01:06 | Again clicking the black down arrow,
point to Label Filters and in this case,
| | 01:12 | I want to display data only from
quarters three and four, so I'll create a rule
| | 01:17 | that displays data from quarters
greater than quarter number two.
| | 01:20 | So I have is Greater Than, type in a 2,
click OK and Excel limits the data.
| | 01:27 | Now if you want, you can pile filters
one on top of another. So let's say that I
| | 01:31 | went back to Year and I wanted to only
display values from 2009 with the quarter
| | 01:37 | three and four filter in place right now.
| | 01:39 | I click OK and I just see the
results from 2009 quarters three and four.
| | 01:44 | Creating filters gives you control over
the values that appear in your PivotChart.
| | 01:48 | When you want to narrow your focus and
examine a subset of your data, PivotChart
| | 01:52 | filters enable you to do just that.
| | Collapse this transcript |
|
|
