Excel spreadsheet processor is one of the most popular programs for working with spreadsheets. This program knows almost everything that might be needed when working with tables.
Among other things, Excel allows you to build charts based on the data in the table. It is this function that we will consider in this article. Here you can learn how to build a chart in Excel using data in a table. The instructions will be relevant for Excel 2007, 2010, 2013 and 2016.
Step No. 1. Preparing data for constructing a chart.
In order to build a chart in Excel, you need source data. These initial data should be presented in the form of a table. In this case, the columns and rows of this table must be signed. Overall it should look something like the screenshot below.
Step No. 2. Select the source data.
In order for Excel to understand exactly what data you want to use to build a chart, you need to select it in the table. To do this, select the area with your initial data with your mouse. It should be noted that the selection area should include not only the data itself, but also the names of rows and columns.
Step No. 3. Create a chart in Excel.
Once the source data has been prepared and selected, you can proceed directly to constructing a chart in Excel. To do this, without removing the selection from the source data, go to the “Diagrams” tab and find there a block of buttons called “Diagrams”.
Using these buttons you can create any type of chart. For example, you need to build a histogram. To do this, simply click on the “Histogram” button and select one of the proposed chart options.
As a result of these actions, in Excel spreadsheet a diagram should appear.
The diagram that appears can be moved to any place on the sheet convenient for you. This is done by simply dragging with the mouse.
Step #4: Setting up a chart in Excel.
If you are not satisfied with the appearance of the constructed diagram, you can change it. To do this, select the diagram with the mouse and go to the “Design” tab or the “Format” tab. Customization tools are available on these tabs appearance chart, as well as its color and even type. As a result of setting up the external one, you can get, for example, a diagram like the one in the screenshot below.
If you want to change the area in which the data should be used to build a chart, then select the chart with the mouse, go to the “Design” tab and click on the “Select Data” button.
After this, the “Select Data Source” window will appear. Now simply select another area with data with the mouse and click on the “Ok” button.
As a result, the chart will automatically change to reflect the new source data.
This tutorial covers the basics of working with charts in Excel and is detailed instructions by their construction. You'll also learn how to combine two chart types, save a chart as a template, change the default chart type, and resize or move a chart.
Excel charts are essential for visualizing data and monitoring current trends. Microsoft Excel provides powerful functionality for working with diagrams, but find the right tool it can be difficult. Without a clear understanding of what types of graphs there are and for what purposes they are intended, you can spend a lot of time fiddling with various elements of the chart, and the result will have only a vague resemblance to what was intended.
We'll start with the basics of charting and step by step to create a chart in Excel. And even if you are new to this business, you can create your first chart within a few minutes and make it exactly as you need.
Excel Charts - Basic Concepts
A chart (or graph) is graphical representation numerical data, where information is represented by symbols (bars, columns, lines, sectors, and so on). Graphs in Excel are typically created to make large amounts of information easier to understand or to show relationships between different subsets of data.
Microsoft Excel allows you to create many different types of graphs: bar chart, histogram, line graph, pie and bubble chart, scatter and stock chart, donut, radar, area, and surface charts.
There are many elements to Excel graphs. Some of them are displayed by default, others, if necessary, can be added and configured manually.
Create a chart in Excel
To create a chart in Excel, start by entering numerical data into a worksheet, and then follow these steps:
1. Prepare data for charting
Most Excel charts (such as histograms or bar charts) do not require a special arrangement of the data. The data can be in rows or columns, and Microsoft Excel will automatically suggest the most suitable type graphics (you can change it later).
To make a beautiful chart in Excel, the following points may be helpful:
- The chart legend uses either the column headings or the data from the first column. Excel automatically selects data for the legend based on the location of the source data.
- The data in the first column (or column headers) is used as the x-axis labels in the chart.
- The numeric data in the other columns is used to create the y-axis labels.
For example, let's build a graph based on the following table.
2. Choose what data you want to show on the graph
Select all the data you want to include in your Excel chart. Select the column headings you want to appear in the chart legend or as axis labels.
- If you want to plot a graph based on adjacent cells, you can simply select one cell and Excel will automatically add all adjacent cells that contain data to the selection.
- To create a graph based on data in nonadjacent cells, select the first cell or range of cells, then press and hold Ctrl, select the remaining cells or ranges. Please note that you can only plot from non-adjacent cells or ranges if the selected area forms a rectangle.
Advice: To select all used cells in a worksheet, place the cursor in the first cell of the used area (click Ctrl+Home to go to cell A1), then click Ctrl+Shift+End to expand the selection to the last used cell (lower right corner of the range).
3. Paste the chart into an Excel sheet
To add a graph to the current sheet, go to the tab Insert(Insert) section Diagrams(Charts) and click on the icon of the desired chart type.
In Excel 2013 and Excel 2016, you can click Recommended Charts(Recommended Charts) to view a gallery of ready-made charts that work best for your selected data.
In this example, we are creating a volumetric histogram. To do this, click on the arrow next to the histogram icon and select one of the chart subtypes in the category Volume histogram(3D Column).
To select other chart types, click the link Other histograms(More Column Charts). A dialog box will open Inserting a chart(Insert Chart) with a list of available histogram subtypes at the top of the window. At the top of the window, you can select other chart types available in Excel.
Advice: To immediately see all available chart types, click the button View all charts Diagrams(Charts) tab Insert(Insert) Menu ribbons.
In general, everything is ready. The diagram is inserted into the current worksheet. This is the volumetric histogram we got:
The graph already looks good, but there are still a few tweaks and improvements that can be made, as described in the section.
Create a Combo Chart in Excel to Combine Two Chart Types
If you need to compare Various types data in an Excel chart, you need to create a combo chart. For example, you can combine a histogram or surface chart with line graph to display data with very different dimensions, for example, total income and the number of units sold.
In Microsoft Excel 2010 and earlier versions, creating combination charts was a time-consuming task. Excel 2013 and Excel 2016 solve this task in four simple steps.
Finally, you can add some finishing touches, such as the chart title and axis titles. The finished combo chart might look something like this:
Customizing Excel Charts
As you have already seen, creating a chart in Excel is not difficult. But after adding a chart, you can change some of the standard elements to create an easier-to-read chart.
In the most latest versions Microsoft Excel 2013 and Excel 2016 have significantly improved work with charts and added new way access chart formatting options.
In general, there are 3 ways to customize charts in Excel 2016 and Excel 2013:
To access additional options, click the icon Chart elements(Chart Elements), find the element you want to add or change in the list and click the arrow next to it. The chart settings panel will appear to the right of the worksheet, here you can select the desired parameters:
We hope that this short review functions for setting up charts helped you get general idea about how you can customize graphs in Excel. In the following articles, we'll take a closer look at how to customize various chart elements, including:
- How to move, adjust, or hide a chart legend
Saving a chart template in Excel
If you really like the created chart, you can save it as a template ( .crtx file) and then use this template to create other charts in Excel.
How to create a chart template
In Excel 2010 and earlier, the function Save as template(Save as Template) is located on the Menu Ribbon tab Constructor(Design) in the section Type(Type).
By default, the newly created chart template is saved in a special folder Charts. All chart templates are automatically added to the section Templates(Templates) that appears in dialog boxes Inserting a chart(Insert Chart) and Changing the chart type(Change Chart Type) in Excel.
Please note that only those templates that were saved in the folder Charts will be available in the section Templates(Templates). Make sure you do not change the default folder when saving the template.
Advice: If you downloaded chart templates from the Internet and want them to be available in Excel when you create a chart, save the downloaded template as .crtx file in folder Charts:
C:\Users\Username\AppData\Roaming\Microsoft\Templates\Charts
C:\Users\Username\AppData\Roaming\Microsoft\Templates\Charts
How to use a chart template
To create a chart in Excel from a template, open the dialog box Inserting a chart(Insert Chart) by clicking the button View all charts(See All Charts) in the lower right corner of the section Diagrams(Charts). On the tab All diagrams(All Charts) go to section Templates(Templates) and select the one you need from the available templates.
To apply a chart template to an already created chart, right-click on the chart and select from the context menu Change chart type(Change Chart Type). Or go to the tab Constructor(Design) and press the button Change chart type(Change Chart Type) in section Type(Type).
In both cases a dialog box will open Changing the chart type(Change Chart Type), where in the section Templates(Templates) you can select the desired template.
How to Delete a Chart Template in Excel
To delete a chart template, open the dialog box Inserting a chart(Insert Chart), go to section Templates(Templates) and click the button Template management(Manage Templates) in the lower left corner.
Button press Template management(Manage Templates) will open the folder Charts, which contains all existing templates. Right-click on the template you want to delete and select Delete(Delete) in the context menu.
Using the default chart in Excel
Excel's default charts are a huge time saver. Whenever you need to quickly create a chart or just look at trends in your data, you can literally create a chart in Excel with just one keystroke! Simply select the data to be included in the chart and press one of the following keyboard shortcuts:
- Alt+F1 to insert a default chart in the current worksheet.
- F11 to create a default chart in a new worksheet.
How to Change the Default Chart Type in Excel
When you create a chart in Excel, the default chart is a regular histogram. To change the default chart format, follow these steps:
Resize a chart in Excel
To resize an Excel chart, click on it and use the handles on the edges of the chart to drag its borders.
Another way is to enter desired value into the fields Figure height(Shape Height) and Figure width(Shape Width) in the section Size(Size) tab Format(Format).
To access additional options, click the button View all charts(See All Charts) in the lower right corner of the section Diagrams(Charts).
Moving a chart in Excel
When you create a graph in Excel, it is automatically placed on the same sheet where the source data is located. You can move the chart anywhere on the sheet by dragging it with the mouse.
If it is easier for you to work with a graph on a separate sheet, you can move it there in the following way:
If you want to move the chart to an existing sheet, select the option On an existing sheet(Object in) and select the required sheet from the drop-down list.
To export a chart outside of Excel, right-click the chart border and click Copy(Copy). Then open another program or application and paste the chart there. You can find several more ways to export charts in this article -.
This is how charts are created in Excel. I hope you found this overview of the basic charting features helpful. In the next lesson we will look at the configuration features in detail. various elements charts, such as chart title, axis titles, data labels, and so on. Thank you for your attention!
Now we'll talk about how to represent series of numbers in in a visual form by using graphs and charts in Excel.
What are graphs and charts for in Excel? Imagine that you are preparing for a speech or writing a report. It’s just that dry numbers will look much worse than the same numbers presented in clearly bright and colorful images.
By using graphs and charts in Excel You can convincingly demonstrate the benefits of your project, show the results of your work, or the work of an entire enterprise, or demonstrate the operation of a machine or mechanism.
Let's go straight to practice. Let there be a number of figures that characterize the activity of the enterprise for each month during the year. Write in the cell A1 - January, in a cell A2 - February. Then select both of these cells, move the cursor to the point in the lower right corner of the selected range, and drag the cursor with the left mouse button pressed to the cell A12. You will have a series of months from January to December.
Now in cells B1 to B12 write some numbers.
Select menu item Insert, then select the Excel chart view you want to use. There are a lot of options: histogram, graph, pie, line . In principle, you can go through them all in turn: each Excel chart will display given numbers in my own way.
For example, select one of the chart types Schedule. An empty window will appear. Now click on the icon Select data , and highlight your cells with numbers. Click OK. That's all, the schedule will be created.
Graph in Excel you can make it much more interesting than it looks now. For example, there are numbers at the bottom. What if you want the month names to be displayed below the horizontal axis? To do this, click on Select data , above the window Horizontal Axis Labels press the button Change, and select the cells with the names of the months. Click OK .
Now let's rename the element Row1, which is located under the graph, to something more specific. Click again on Select data , and above the window Legend Elements press the button Change. Write the name of the row in the box, for example: Data for 2012 . Click OK .
Basically, the element Row1 You can delete it altogether so that it is not visible in the table if you do not need it. Select it and delete it with the button Delete
.
Now fill in the cells from C1 before C12. Click on the table window. You will see that the column cells B highlighted with a blue frame. This means that these cells are used in Excel chart. Drag the corner of the blue frame so that the frame also covers the column cells C. You will see that the graph has changed: another curve has appeared. This way we can select any range of numbers to display in the Excel graph.
Each element Excel charts(curves, chart field, vertical and horizontal axis numbers) can be formatted. To do this, select the corresponding element, right-click on it, and select Data series format
.
In addition, if you double-click on the diagram, that is, you will be taken to editing it, you can, in addition to the source data, change Excel chart layout and style. There are a lot of options there - you just need to click on the scroll sliders to the right of these items. You can also move the Excel chart to a separate sheet if you wish.
The question remains: how to show it on a chart in Excel dependent data series
? The chart type used for this is Spot. First, write rows of numbers in the cells: in the first column there is a row of numbers that will be along the horizontal axis, and in the second column there is a row of dependent numbers corresponding to the first column - they will be displayed along vertical axis. Then choose one of scatter plots Excel, highlight these columns and you're done.
You can get more detailed information in the “All courses” and “Utilities” sections, which can be accessed through the top menu of the site. In these sections, articles are grouped by topic into blocks containing the most detailed (as far as possible) information on various topics.
You can also subscribe to the blog and learn about all new articles.
It does not take a lot of time. Just click on the link below:
Most people nowadays work while sitting in front of a computer. Yes, computers occupy a huge place in our lives. Many people don’t even use calculators anymore; it’s enough to create a table in which you just need to insert the formula, and the program itself will calculate everything. Sometimes it is better to use charts, with which you can visually monitor changes in a particular indicator. You are not so familiar with office programs, and immediately wondered how to build diagrams, don’t worry, everything is quite simple.
Building charts in Microsoft Office Excel
Microsoft Office Excel is enough powerful tool, allowing you to process a huge amount of data and subsequently conveniently visualize it. One way to visualize data is with an Excel chart, which allows you to create a huge number of different charts. Now let's look at how to make charts in Microsoft Office Excel 2003 and 2010. Creating charts in these two versions is different from each other, and the process of creating charts in version 2007 is very similar to version 2010, which is why we will not consider it.
About how to build a chart in Microsoft Office Excel 2003:
- First, open Microsoft Office Excel and create here new book, in which we create a table with data
- Select the created table
- Go to the “Insert” menu and click on “Diagram”
- A window will open in front of us where we need to select a data source to create a chart. Due to the fact that we selected the source table before creating the chart, we must fill in the data in the “Range” field ourselves, and then click “Next”.
- Next we have the next window where we must specify the settings for our diagram. Here you can specify the name of the chart itself, the name of its axes, you can customize the legend, grid lines and set data labels.
- Next, you need to select the place where our diagram will be inserted and click “Finish”.
And now let’s talk about how to create a chart in Microsoft Office Excel 2010. Everything is simple here - almost all the steps are the same as in version 2003, the only difference is that after clicking the “Insert” button, the chart appears on the current sheet. And to customize the created chart, you need to right-click on it and select “Format Chart Area”, where you can find many different settings.
Charts in Microsoft Office Word
Microsoft Office Word is text editor, but despite this, its work is not limited to just entering text. It is also possible to work with graphic objects and even with diagrams. You immediately thought about how to make a diagram in Microsoft Office Word, so let's look at some sequence of actions.
- To start plotting in Microsoft Office Word, you must have at least the slightest notion about how to make charts and graphs using another program - Microsoft Office Excel
- So, launch the Word editor and create new document(or use an existing file), then place the mouse cursor in the place where our diagram will be located. Then go to the “Insert” tab, where we click on the “Illustrations” section and click on “Diagram”.
- Next, the “Insert Chart” window will open in front of us, which will be divided into two parts. Here in the left you need to select the “Graph” item and left-click on it, highlighting it in this way. On the right side you need to select the type you like for our graph. Select the required thumbnail using the left mouse button and click OK.
- Then we wait for the opening Microsoft document Office Excel. Don't be surprised, but Workspace will change its appearance. Left-hand side screen - this will be a Word document, and the right side will be Excel. On the Excel sheet, we need to enter the data we need, set the necessary parameters, rename the coordinate axes and change the data range.
- At the moment when you enter data into Excel, you can observe changes in the Word document on the left side of the screen. After finishing editing the chart data, you just need to close the Excel document using the cross located in the upper right corner.
- If you suddenly want to change the chart, you need to go to the “Working with Charts” context menu; it will become active if you select any element in the chart area. Even if we made a mistake in entering data, we can go to the “Working with Charts” menu, find the “Designer” tab, where in the “Data” section simply click the “Change Data” button.
Here's how to make a diagram using a text editor.
Information presented in the form of a table is perceived by a person faster than text, and if the same values are shown on a diagram, then they can be easily compared and analyzed.
In this article we will look at how to make a chart in Excel from a table.
Let's take the following range as an example. It displays the number of goods sold by a certain employee for specific month. Select all the values with the mouse, along with the names of the rows and columns.
Choosing the right type
Go to the “Insert” tab and in the “Charts” group select desired type. For this example Let's build a histogram. Select one of the proposed histograms from the list and click on it.
Excel will automatically produce the result. The axes are labeled on the left and bottom, and the legend is on the right.
How to work with her
A new section has appeared on the feed "Working with diagrams" with three tabs.
On the Design tab you can "Change chart type", change row and column, choose one of the layouts or styles.
On the Layout tab you can give it common name or for axes only, display legend, grid, and enable data labels.
On the “Format” tab, you can select the fill, outline and shape effect, and style for the text.
Adding new data
Now let's look at how to add new values to it.
If the table is created manually
For example, we added sales information for “June” to our original range. Select the entire column, right-click on it and select “Copy” from the context menu, or press “Ctrl+C”.
Select the diagram and press “Ctrl+V”. A new field will be automatically added to the Legend and data to the histogram.
You can add them in another way. Right-click on the diagram and select from the menu "Select data".
In the “Row Name” field, select the month, in the “Values” field, select the column with sales information. Click “OK” in this window and the next one. The schedule will be updated.
If you used a smart table
If you often need to add information to the original range, then it is better to create a “smart table” in Excel. To do this, select everything together with the headings, on the “Home” tab, in the “Styles” group, select "Format as table". You can choose any style from the list.
Put a tick in the box "Table with headers" and click "OK".
It looks like this. You can expand it by pulling the lower right corner. If you pull to the side, a new month will be added; if you pull to the bottom, you can add a new employee. Let's add a new month and fill in the sales information.
New rectangles are added to the histogram as the cells are filled. Thus, from an ordinary one we have a dynamic table in Excel - when it changes, the diagram is automatically updated.
In the example, the “Histogram” was considered. Using the same principle, you can build any other diagram.
To build a circle, select the appropriate item in the “Diagrams” group. From the data table, select only employees and sales for January.
A bar chart is constructed in exactly the same way as a histogram.
