How to make a diagram in Excel. How to make a chart in Excel? How to apply a pre-made style

Useful tips

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

appeared on the tape new section "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 a general name or axes only, display a legend, a 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 and click on it right click mouse 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, it will be added new month, by pulling 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 a regular table we have a dynamic table in Excel - when it changes, the chart 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.

Rate this article:

Excel provides a significant range of capabilities to the user for graphical presentation of data. You can create diagrams either on the same worksheet with the table or on a separate sheet workbook which is called chart sheet. A chart created on the same worksheet as a table is called implemented. To build charts in Excel use:

Chart Wizard;

Charts panel.

The Chart Wizard allows you to create several types of charts, for each of which you can choose a modification of the main chart option.

Chart elements

The main components of the diagram are presented in the following diagram:

Note: For a 3-D diagram, the components are slightly different.

Chart types

Depending on the selected chart type, you can get different data displays:

bar charts And histograms can be used to illustrate the relationship of individual values or show the dynamics of changes in data over time certain period time;

schedule reflects trends in data changes over certain periods of time;

pie charts designed to visually display the relationship between parts and the whole

scatter plot displays the relationship between numerical values of several data series and represents two groups of numbers as a single series of dots, often used to represent scientific data;

area chart highlights the amount of change in data over time by showing the sum of entered values, and also shows the contribution of individual values to the total;

donut chart shows the contribution of each element to the total, but, unlike a pie chart, can contain several data series (each ring is a separate series);

radar chart allows you to compare general values from several data series;

surface diagram used to find the best combination of two data sets;

bubble chart represents a type of scatter plot, where two values determine the position of the bubble, and the third determines its size;

stock chart often used to display stock prices, exchange rates, temperature changes, and scientific data

In addition, you can build diagrams of the so-called non-standard types that allow you to combine them in one diagram Various types data presentation.

When working with a non-standard chart type, you can quickly view the chart. Each custom chart type builds on a standard type and includes additional formatting and options such as legend, grid, data labels, minor axis, colors, patterns, fills, and locations various elements diagrams.

You can use one of the built-in custom chart types or create your own. Non-standard diagram types are found in books.

Building charts using the Chart Wizard

To create a chart on a worksheet, you need to select the data that will be used in it and call Chart Wizard. Either one row of data (or a separate row in a table, or a separate column) or several can be selected.

Note: data not included in the rectangular block is selected when the Ctrl key is pressed.

To call Chart Wizards used:

menu item Insert|Diagram;

button Chart Wizard on standard panel tools

The Chart Wizard involves several steps that the user must take.

The standard view of the Diagram Wizard dialog box is shown in Fig. 1.

Fig.1. Chart Wizard

The created diagrams can be edited. Excel allows you to:

Resize chart

Move charts on a worksheet

Change the chart type and subtype, adjust the color of series or data points, their location, customize the image of series, add data labels

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're not that 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 great amount data and subsequently conveniently visualize them. 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 diagrams 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 Microsoft chart Office Excel 2003:

First, open Microsoft Office Excel and here we create a new workbook 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 diagrams. You immediately thought about how to make a diagram in Microsoft Office Word, so let's look at some sequence of actions.

In order to start plotting in Microsoft Office Word, you must have at least the slightest understanding of how to make charts and graphs using another program - Microsoft Office Excel
So, let's launch 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 the workspace will change its appearance. The left side of the screen will be a Word document, and the right side will be an Excel document. On Excel sheet we need to enter the data we need, set the necessary parameters, rename the coordinate axes and change the data range.
The moment you enter data into Excel, you can observe changes in Word document, which is on the left side of the screen. After finishing editing the chart data, you just need to close 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.

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 a graphical representation of numerical data, where information is represented by symbols (bars, columns, lines, sectors, and so on). Graphs in Excel are usually created to make things easier to understand. large volumes information or show relationships between different subsets of data.

Microsoft Excel allows you to create many different types of charts: bar chart, histogram, line chart, pie and bubble charts, scatter and stock charts, donut and radar charts, area charts, and surface charts.

IN Excel charts there are many elements. 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 appropriate graph type (you can change this 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 build a graph based on adjacent cells, then just select one cell and Excel will automatically add all of them to the selection. adjacent cells containing data.
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 in this example, we create 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 want to compare different types of data in an Excel chart, you need to create a combination chart. For example, you can combine a histogram or surface chart with line graph to display data with very different dimensions, such as total revenue and units sold.

In Microsoft Excel 2010 and later earlier versions creating combination charts was a labor-intensive 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 parameters 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 special folder Charts. All chart templates are automatically added to the section Templates(Templates), which 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 a chart you've already created, right-click on the chart and context menu select 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, run the following actions:

Resize a chart in Excel

To resize Excel charts, click on it and use the handles on the edges of the diagram 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 schedule on separate sheet, you can move it there like this:

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 in detail at the features of setting up various chart elements, such as the chart title, axis titles, data labels, and so on. Thank you for your attention!

Charts and graphs in MS Excel (part of the MS Office suite) are used to display data graphically, which is more clear from the user's point of view. Diagrams are convenient watch the dynamics changes in the values of the studied quantities, compare various data, present a graphical dependence of some quantities on others.

Reading and assessment large quantity Data that is visualized using graphs and charts is greatly simplified. Excel has an effective multifunctional a tool for this visualization, thanks to which you can build charts and graphs of various types and purposes. Simply irreplaceable in analytical research.

In the figure we see a standard dependence graph in Excel, it shows and labels the main elements.

On this moment Application versions 2003, 2007, 2010, 2013, 2016 are used. The processes for constructing graphs and diagrams in them have some differences, primarily in the interface. The main ones will be indicated below.

How to build a graph in Excel

Excel supports Various types graphs for the most clear and complete display of information. Graphs are being built by points, which are connected by segments. The more points, the less distortion in the graph, the more smoothly the function changes in dynamics.

To create a graph (like a chart) in MS Excel you first need to enter numeric data on the sheet on the basis of which it will be built. Typically for a chart two columns are enough, one of which will be used for the X axis (argument), the second for the Y axis (function) - this can be expressed by a formula or simply a list of data dependent on the argument.

Highlight data range. Then, by selecting the desired chart type on the tab Insert in Group Diagrams– click Insert graph(to view the dynamics of data changes). If you want to build a graph by points, you should take Scatter plot(if you have 2 rows of data, one of which depends on the second).

Microsoft Excel 2003

Microsoft Excel 2013

The graph can be placed either on one sheet with data or on a separate one.

How to make a diagram

Similar to graphs, charts are built based on data in the columns of a table, but some types (pie, donut, bubble, etc.) require the data to be arranged in a certain way. To build a diagram you need to go to the tab Diagrams. For example, let's look at how to do circular.

For such a diagram one the column is the data labels, second– the numerical series of data itself.

Select a range of cells with labels and data. Then Insert, press Diagrams and choose the one that suits your requirements chart type(in our case Circular).

A diagram will be automatically created, which you can use if necessary. change in properties at your discretion. You can change style, layout, captions, axes, background and many other settings.

Circle-type diagrams demonstrate the proportions of parts relative to something whole and are presented as a set of sectors that make up the circle with the corresponding values displayed. This is very useful when you want to compare some data against a total value.

Building a sinusoid

Suppose you need to construct a graph of a function representing sinusoid. This will require enterdata sines of angles.

To calculate the values of sines, you need to enter the first cell of the data series Sin enter formula= SIN(RADIAN(A3)), where A3 is the corresponding argument. Then column stretch for the lower right corner. We get the required range of values.

Further building a schedule, pressing Insert, Schedule, in the same way as before.

As you can see, the resulting graph is not sufficiently similar to a sinusoid. For a more beautiful sinusoidal dependence, you need to enter more values angles (arguments) and the more, the better.

How to add a title to a chart

If you want to change the name, make it more understandable, or remove it altogether, you will need to do the following actions. IN Excel versions 2003 you need to click anywhere on this diagram, after which you will see the panel Working with charts, with tabs Layout, Format And Constructor. In Group Layout/Captions select Name diagrams. Change the parameters you need.

The title can be associated with any table cell by marking the link to it. The associated title value is automatically updated when it is changed in the table.

How to customize axes and add titles

In addition to other functions, you have the ability to customize the axes - scale, gaps between categories and values. You can add divisions on the axes and specify the values of the distances between them, add and format the names of the axes, configure display or hide grid.

Regarding the settings for titles, labels, axes and others in Office 2013 , then there is more to do easier and more convenient: just a couple of clicks on changeable visual components and using the context menu attached to them.

Add or change a legend

Thanks to the legend on the graph, it is determined whether the parameter belongs to a particular column.

Excel charts available option legend settings - change the location, display it or hide it.

Go to the tab Designer/Select Data for version 2003 or in the context menu Select data for version 2013.

A window for selecting a data source will open, in which you can change range of data used, change axes labels and legend elements (rows), parameters for each row separately.

As you can see, to build a function in Excel, it is necessary to have two factors - a tabular and a graphical part. The MS Excel office suite application has an excellent element of visual presentation of tabular data in the form of graphs and charts, which can be successfully used for many tasks.