# Excel 2007: Creating Charts with Dynamic Data

## with Curt Frye

Excel 2007: Creating Charts with Dynamic Data shows how simple data in Excel can be used to build exciting and updatable charts. Microsoft Most Valuable Professional Curt Frye explains how to use both named ranges and Excel tables to create charts that adjust as the data changes. Curt demonstrates how to specify the number of points in a chart and how to use a drop-down list to select which data series is displayed. Finally, he shows how to use PivotChart reports to reorganize and visualize data on the fly. Exercise files accompany the course.
Topics include:
• Creating an expanding chart using formulas Specifying the first point and the number of points for a series Plotting points at regular intervals in a series Filtering a PivotChart report to find the right information
author
Curt Frye
subject
Business, Charts + Graphs
software
Excel 2007
level
Intermediate
duration
40m 47s
released
Dec 04, 2009

Ready to join? subscribe

Keep up with news, tips, and latest courses.

submit Course details submit clicked more info

• ### FAQs

Search the closed captioning text for this course by entering the keyword you’d like to search, or browse the closed captioning text by selecting the chapter name below and choosing the video title you’d like to review.

Introduction
Welcome
 00:00 (Music playing.) 00:03 Hi! I'm Curt Frye. 00:05 Welcome to Excel 2007: Creating Charts with Dynamic Data. 00:08 In this course, I'll show you how to visualize your data using charts you can control. 00:12 I'll begin by showing you how to create dynamic named ranges and Excel tables, 00:16 both valuable tools that enable you to create dynamic charts. 00:19 Then I'll demonstrate how to apply those techniques. 00:22 We'll look at ways to create charts that expand to include all data entered 00:26 into a range or Excel table, to determine how many data points you want to 00:30 display in your chart, and to use a d rop-down list to select which data series 00:34 to visualize in your chart. 00:36 Finally, I'll show you how to create PivotCharts, which enable you to reorganize 00:40 and visualize your data on the fly. 00:42 So let's get started with Excel 2007: Creating Charts with Dynamic Data. Collapse this transcript
Using the exercise files
 00:00 If you are a premium member of the lynda.com Online Training Library or if you 00:04 are watching this tutorial on a disc you have access to the exercise files used 00:08 throughout this title. 00:09 This is a relatively short course so I put all the exercise files for this 00:12 title in a single folder. 00:15 On my computer I have them in a folder entitled Exercise Files and you can 00:19 see them listed here. 00:20 If you are a monthly or annual subscriber to lynda.com, you don't have access 00:24 to the Exercise Files, but you can follow along on the screen or create your own resources. 00:29 Let's get started! Collapse this transcript
1. Defining Named Ranges
Creating a named range
Creating an Excel table
2. Creating Dynamic Charts Using Formulas
Creating an expanding chart
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
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 Data
Creating a PivotChart
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
Conclusion
Goodbye
 00:00 Thanks so much for spending your time with Excel 2007: 00:03 Creating Charts with Dynamic Data. 00:05 I hope you've learned a lot of usable techniques and that you're able to apply 00:08 them successfully to your business. 00:10 Thanks again! Collapse this transcript

Are you sure you want to delete this bookmark?

### Bookmark this Tutorial

#### Description

{0} characters left

#### Tags

Separate tags with a space. Use quotes around multi-word tags. Suggested Tags:
cancel

### bookmark this course

{0} characters left Separate tags with a space. Use quotes around multi-word tags. Suggested Tags:

Error:

go to playlists »

# Create new playlist

 name: description: save cancel

# You must be a lynda.com member to watch this video.

Every course in the lynda.com library contains free videos that let you assess the quality of our tutorials before you subscribe—just click on the blue links to watch them. Become a member to access all 98,695 instructional videos.

If you are already an active lynda.com member, please log in to access the lynda.com library.

# Get access to all lynda.com videos

You are currently signed into your admin account, which doesn't let you view lynda.com videos. For full access to the lynda.com library, log in through iplogin.lynda.com, or sign in through your organization's portal. You may also request a user account by calling 1 1 (888) 335-9632 or emailing us at cs@lynda.com.

# Get access to all lynda.com videos

You are currently signed into your admin account, which doesn't let you view lynda.com videos. For full access to the lynda.com library, log in through iplogin.lynda.com, or sign in through your organization's portal. You may also request a user account by calling 1 1 (888) 335-9632 or emailing us at cs@lynda.com.

Your organization has a limited access membership to the lynda.com library that allows access to only a specific, limited selection of courses.

You don't have access to this video.

You're logged in as an account administrator, but your membership is not active.

Contact a Training Solutions Advisor at 1 (888) 335-9632.

# How to access this video.

If this course is one of your five classes, then your class currently isn't in session.

If you want to watch this video and it is not part of your class, upgrade your membership for unlimited access to the full library of 1,899 courses anytime, anywhere.

You can always watch the free content included in every course.

Questions? Call Customer Service at 1 1 (888) 335-9632 or email cs@lynda.com.

# You don't have access to this video.

You're logged in as an account administrator, but your membership is no longer active. You can still access reports and account information.

To reactivate your account, contact a Training Solutions Advisor at 1 1 (888) 335-9632.

# Need help accessing this video?

You can't access this video from your master administrator account.

Call Customer Service at 1 1 (888) 335-9632 or email cs@lynda.com for help accessing this video.

site feedback

Thanks for signing up.

We’ll send you a confirmation email shortly.

By signing up, you’ll receive about four emails per month, including

• new course releases
• special announcements

Keep up with news, tips, and latest courses with emails from lynda.com.

By signing up, you’ll receive about four emails per month, including