When you run a business, it's a good idea to keep track of how it performs over time. You can track total sales, number of new customers, and so on. When you keep track of data over time and write down when you capture that data, you can summarize it using a line chart. So let's take a look at the data that I have here in my chart. I have the series of months, January through December, and I also have the sales for that month. So my data is laid out by time; that means that I am able to summarize it effectively in a line chart. To do that, I make sure that a cell within the data list is selected, so Excel knows what to summarize, and then on the Charts tab of the Ribbon, I can click Line and select a line style.
In this case, I will just go with a basic one. Click Line and Excel creates the chart for me. So you can see that we have the individual values for January, February, March, and so on, and Excel has drawn a line between each of the individual points. The reason behind that is that you will get a better idea of the trend. Now I am going to go to another worksheet and show you how Excel handles multiple series of data inside of a line chart. So here we have an extension of the data that we had before, except instead of data for only one series, I have four series.
I have each of these four varieties, olive oil, and I have sales per month for them. So what I am going to do is click any cell inside the data list, and then on the Chart tab of the Ribbon click Line and then select the line type. And when I do, Excel creates a chart. It's a very messy and colorful chart. So that points to the fact that you need to be careful about how you create your charts and how much data you try to squeeze in them. But if I resize it by dragging this corner here, it becomes a little more clear.
You can focus in on each of the sales and how they have trended over time, over the course of the year. So, for example, Jalapeno started out high, sort of fell off a cliff, and so on, and you can do the same for other sets of values. Now finally I want to show you a way that Excel can give you problems, and I want to show you how to fix it, because this will come up time and again, and it will bite you if you don't know what to do about it. So let's go to Sheet3, by clicking the Sheet tab, and you will see that I have an innocent-looking data series.
I have a set of years and some sales values. I am going to click a cell inside the data list, click Line, and create a line chart. Now what I expected to see were the years along the bottom and sales in the body of the chart. What I see instead is a blue line representing the years, and the numbers one through six, indicating the number of measurements. So this would be number 1, number 2, number 3, number 4, number 5, and number 6. In other words, because year is a numerical value, Excel tried to plot it in the chart.
It's a reasonable mistake to make, but it's also one that needs to be fixed, so here is how you do that. With the charts still selected, and you are still on the Charts tab of the Ribbon, in the Data group, you click Select. This dialog gives you information about the data plotted in the chart, and here we see two data series: Year and Sales, which correspond to the columns of your data list. Well, year shouldn't be in the body of the chart; it should provide the values here: the category or X axis labels. That's here along the bottom of the chart.
So what you need to do is remove the series from the interior of the chart. So there it went when I clicked Remove. It disappeared from here, but the data isn't gone. It's still in your worksheet. Now what you need to do is click in the axis labels box and click the Collapse dialog button here. Now you can go into your worksheet and select the cells that provide the values for the horizontal axis, and those values are in cells A2 through A7.
Those are selected, and I can see them up here as a cell reference. When I click the Expand dialog button, I see that I have Sales in the interior of the chart, the category or X axis labels are A2 through A7. I can already see that Excel has added those values here. So when I click OK, my chart looks the way I expect it to. Line charts give you a quick visual summary of how your data has trended over time. If you notice a sudden increase or decrease in your sales, or other data, you should identify when the change occurred and investigate to see why it happened.
Author
Released
10/26/2010- Customizing the Ribbon
- Formatting worksheets, cells, and cell data
- Sorting and filtering data
- Working with formulas
- Detecting formula errors
- Creating charts
- Importing data
- Inserting objects and graphics
- Using PivotTables
- Recording macros
- Sharing workbooks
Skill Level Beginner
Duration
Views
Q: Where can I learn more about Excel formulas?
A: Discover more on this topic by visiting Excel formulas on lynda.com.
Related Courses
-
Introduction
-
Welcome1m 12s
-
-
1. Getting Started with Excel
-
Customizing the Ribbon4m 20s
-
Setting program preferences3m 20s
-
Getting help in Excel4m 16s
-
2. Managing Workbooks
-
Setting workbook properties4m 14s
-
3. Managing Worksheets, Cells, and Cell Data
-
Creating an Excel table4m 43s
-
Sorting worksheet data3m 2s
-
Creating a custom sort order3m 54s
-
Filtering worksheet data4m 6s
-
Managing worksheets5m 28s
-
4. Summarizing Data Using Formulas and Functions
-
Adding a formula to a cell3m 59s
-
Creating an AutoSum formula3m 22s
-
Managing scenarios4m 59s
-
5. Formatting Worksheet Elements
-
Managing text alignment3m 56s
-
Copying cell formats4m 2s
-
Managing cell styles3m 16s
-
Managing Office themes3m 31s
-
6. Working with Charts
-
Creating pie charts2m 32s
-
Creating line charts4m 34s
-
Creating XY (scatter) charts1m 49s
-
Creating stock charts4m 11s
-
Adding trendlines to charts4m 14s
-
Creating sparkline charts4m 31s
-
7. Working with External Data
-
Using hyperlinks6m 1s
-
8. Working with Objects
-
Adding and adjusting images5m 38s
-
Creating WordArt2m 34s
-
9. Exploring PivotTable Reports
-
Creating a PivotTable report4m 37s
-
Pivoting a PivotTable report3m 17s
-
Applying a PivotTable style2m 20s
-
Creating and editing styles2m 59s
-
10. Reviewing and Sharing Your Spreadsheets
-
Checking spelling3m 32s
-
Managing workbook comments3m 40s
-
Exporting to other formats1m 33s
-
Protecting a workbook2m 36s
-
-
11. Automating Workbooks Using Macros
-
Running an existing macro4m 56s
-
Recording a macro3m 56s
-
Adding comments to a macro2m 43s
-
-
Conclusion
-
Additional resources1m 1s
-
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.
CancelTake notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: Creating line charts