Draw a graph in excel. Building charts with multiple data series in MS EXCEL

Excel is the most popular spreadsheet program. In Excel, it is very convenient to perform economic, statistical and other calculations, as well as to generate various tables and reports.

One of the most common tasks when working with Excel is plotting. Thanks to the Excel tools, creating a graph is quite easy. In this article, we will consider this task and talk about how to build a graph in Excel.

Step #1: Preparing data for the chart.

In order to build a graph in Excel, we need initial data. You will have your initial data, and we will look at the process of creating graphs using the example of such a simple table with information about the harvest.

Step number 2. Select the data that will be used to build a graph in Excel.

In order for the Excel program to immediately receive the necessary data when creating a graph, you need to select the area in which they are located.

Step number 3. Go to the "Insert" tab

Without removing the selection, from the area we need, go to the tab "Insert".

Step number 4. Create a chart using the appropriate button.

On the Insert tab, find the Graph button. Click on this button and select one of the chart design options from the drop-down menu. You can change the layout later.

Step No. 5. Setting up the created chart.

Once you've created a graph, a small floating graph window should appear on the Excel sheet. You can move this window to any place convenient for you.

Step No. 6. Setting the parameters of the constructed chart.

After plotting the graph, Excel allows you to configure almost all of its parameters. For example, if you incorrectly specified the data area, then you can use the "Select data" function. This function allows you to change the area from which data is used to create a graph.

To do this, select your chart and open the "Designer" tab. On this tab there is a button " Select data».

Click on this button and select the correct area and click on the OK button.

Excel then rebuilds the graph using the new data. In addition, on the tabs " Design", " Layout"And" Format»You will find many settings that change the appearance of the graph. This will allow you to create a graph that will fit in with the style of your document.

Plotting a function dependency graph is a characteristic mathematical problem. Everyone who is familiar with mathematics at least at the school level has built such dependencies on paper. The graph shows how the function changes depending on the value of the argument. Modern electronic applications allow this procedure to be carried out with a few mouse clicks. Microsoft Excel will help you in building an accurate graph for any mathematical function. Let's take a look at the steps on how to graph a function in excel using its formula

Plotting a Linear Function in Excel

Graphing in Excel 2016 has been greatly improved and made even easier than in previous versions. Let's analyze an example of plotting a linear function graph y=kx+b on a small interval [-4;4].

Preparation of the calculation table

We enter the names of the constants k and b in our function into the table. This is necessary to quickly change the schedule without altering the calculation formulas.

Setting the Step of Function Argument Values

• In cells A5 and A6, we enter the notation for the argument and the function itself, respectively. The formula entry will be used as the title of the chart.
• Enter in cells B5 and C5 two values ​​of the function argument with a given step (in our example, the step is equal to one).
• Select these cells.
• Move the mouse pointer over the lower right corner of the selection. When a cross appears (see the figure above), hold down the left mouse button and drag to the right to column J.

The cells will automatically be filled with numbers whose values ​​differ by the given step.

Autocomplete Function Argument Values

Attention! The formula entry begins with an equal sign (=). Cell addresses are written on the English layout. Notice the absolute addresses with the dollar sign.

Writing a calculation formula for function values

To finish entering the formula, press the Enter key or the check mark to the left of the formula bar at the top above the table.

We copy this formula for all values ​​of the argument. We stretch the frame to the right from the cell with the formula to the column with the final values ​​of the function argument.

Copying a Formula

Plotting a Function

Select a rectangular range of cells A5:J6.

Feature table selection

Go to tab Insert in the toolbox. In chapter Diagram choose Spot with smooth curves(see figure below). Let's get a diagram.

Building a chart of type "Graph"

After construction, the coordinate grid has unit segments of different lengths. Change it by dragging the side markers to get square cells.

Linear function graph

Now you can enter new values ​​for the constants k and b to change the graph. And we see that when you try to change the coefficient, the graph remains unchanged, but the values ​​​​on the axis change. Fixing. Click on the diagram to activate it. Further on the ribbon of tools in the tab Working with charts tab Constructor choose Add chart element - Axes - Additional axis options..

Entering the mode of changing parameters of coordinate axes

A settings sidebar will appear on the right side of the window. Axis Format.

Editing coordinate axis parameters

• Click the Axis Options drop-down list.
• Select Vertical (Value) Axis.
• Click the green chart icon.
• Set the interval of the axis values ​​and the unit of measurement (circled in red). We set the units of measurement Maximum and minimum (Preferably symmetrical) and the same for the vertical and horizontal axes. Thus, we make a single segment smaller and, accordingly, we observe a larger range of the graph on the diagram. And the main unit of measurement is the value 1.
• Repeat the same for the horizontal axis.

Now, if we change the values ​​of K and b , we get a new graph with a fixed grid of coordinates.

Plotting Other Functions

Now that we have a basic table and chart, we can plot other functions by making small adjustments to our table.

Quadratic function y=ax 2 +bx+c

Do the following:

• =$B3*B5*B5+$D3*B5+$F3

We get the result

Graph of a quadratic function

Cubic parabola y=ax 3

To build, follow these steps:

• Change the title on the first line
• In the third line we indicate the coefficients and their values
• In cell A6 we write the designation of the function
• In cell B6, enter the formula =$B3*B5*B5*B5
• Copy it to the entire range of argument values ​​to the right

We get the result

Cubic parabola plot

Hyperbola y=k/x

To build a hyperbola, fill in the table manually (see the figure below). Where before there was a zero value of the argument, we leave an empty cell.

• Change the title on the first line.
• In the third line, we indicate the coefficients and their values.
• In cell A6 we write the designation of the function.
• In cell B6, enter the formula =$B3/B5
• We copy it to the entire range of values ​​of the argument to the right.
• Removing a formula from a cell I6.

To correctly display the graph, you need to change the range of initial data for the chart, since in this example it is larger than in the previous ones.

• Click Chart
• On the tab Working with charts go to Constructor and in the section Data click Select data.
• The data entry wizard window will open.
• Select a rectangular range of cells with the mouse A5:P6
• Click OK in the wizard window.

We get the result

Hyperbola graph

Construction of trigonometric functions sin(x) and cos(x)

Consider an example of plotting a trigonometric function y=a*sin(b*x).
First fill in the table as in the picture below

Table of values ​​of the sin(x) function

The first line contains the name of the trigonometric function.
The third line contains the coefficients and their values. Pay attention to the cells in which the values ​​​​of the coefficients are entered.
The fifth line of the table contains the values ​​of the angles in radians. These values ​​will be used for chart labels.
The sixth line contains the numerical values ​​of the angles in radians. They can be written manually or using formulas of the appropriate form =-2*PI(); =-3/2*PI(); =-PI(); =-PI()/2; …
The seventh line contains the calculation formulas of the trigonometric function.

Writing the calculation formula of the sin (x) function in Excel

In our example =$B$3*SIN($D$3*B6). Addresses B3 and D3 are absolute. Their values ​​are the coefficients a and b, which are set to one by default.
After filling in the table, we proceed to plotting the graph.

Select a range of cells A6:J7. Select a tab in the ribbon Insert In chapter Diagrams specify the type dotted and view Spot with smooth curves and markers.

Chart construction Scatter with smooth curves

As a result, we get a diagram.

sin(x) plot after inserting chart

Now let's set up the correct display of the grid, so that the graph points lie at the intersection of the grid lines. Follow the steps Working with charts -Designer - Add chart element - Grid and enable three line display modes as shown in the figure.

Setting up the grid when plotting

Now go to point Additional grid line options. You will have a sidebar Construction area format. Let's make the settings here.

Click in the diagram on the main vertical Y-axis (should be highlighted with a box). In the sidebar, set the axis format as shown in the figure.

Click on the main horizontal axis X (should be highlighted) and also make settings according to the figure.

Setting the format of the horizontal x-axis of the function graph

Now let's make data labels over the points. Execute again Working with charts -Designer - Add chart element - Data labels - Top. You will be substituted with the numbers 1 and 0, but we will replace them with values ​​from the range B5:J5.
Click on any value 1 or 0 (picture step 1) and in the signature parameters check the Values ​​from cells box (picture step 2). You will immediately be prompted to provide a range with new values ​​(Figure step 3). Specify B5:J5.

That's all. If done correctly, then the schedule will be wonderful. Here's one.

To get the graph of a function cos(x), replace in the calculation formula and in the title sin(x) on the cos(x).

In a similar way, you can build graphs of other functions. The main thing is to write down the computational formulas correctly and build a table of function values. I hope you found this information useful.

PS: Interesting facts about the logos of famous companies

Dear reader! You have read the article to the end.
Did you get an answer to your question? Write a couple of words in the comments.
If no answer is found, indicate what you are looking for.

Information that is displayed visually is much easier to perceive, this is a proven fact. The trend is especially clear in different comparisons. Below we will look at how to build a chart in Excel from table data. This will be a step-by-step instruction without unnecessary "water" and we will also touch on a number of related issues.

Create a chart

We will solve the problem in different ways, but you will choose the most suitable one. So let's get started.

bar chart

This type is suitable when we just need to visually display values, or compare them with others.

1. In order to start creating a chart, you should initially have the data that will form its basis. Therefore, select the entire column of numbers from the plate and press the Ctrl + C button combination.

1. Next, click on the tab [k]Insert and select the histogram. It will display our data in the best possible way.

1. As a result of the above sequence of actions, a diagram will appear in the body of our document. First of all, you need to adjust its position and size. To do this, there are markers that can be moved.

1. We set up the end result as follows:

1. Let's give the plate a name. In our case, this is [k]Product prices. To enter edit mode, double-click on the diagram title.

1. You can also get into the editing mode by clicking on the button marked with the number [k]1 and selecting the function [k] Name of axes.

1. As you can see, the inscription appeared here.

This is what the result looks like. In our opinion, it is quite good.

Comparison of different values

If you have several values, you can also add them here, so we can get great material for visual comparison.

1. Copy the numbers of the second column.

1. Now select the chart itself and press Ctrl+V. This combination will insert the data into the object and force it to be ordered with columns of different heights.

The program has hundreds of other types of graphs, they can be found in the [k]Insert menu. Through trials and combinations, each must be dealt with separately.

Percentage

In order to more clearly understand the role of the various cells of our table and its values ​​in general, we can compare the results in the form of a pie chart. And we will do this with the output of the percentage. Let's get started.

1. As in previous cases, we copy the data of our plate. To do this, just select them and press the key combination Ctrl + C. You can also use the context menu.

1. Again, click on the [k]Insert tab and select the pie chart from the list of styles.

In Microsoft Excel, you can create charts of varying complexity and of any type. Let's analyze the creation process using Excel 2007 as an example.

Let's say we have revenue data for a year. For clarity, we need to build a graph that would display the annual income.

To build a graph, you need to select a column with numbers (you do not need to select a header), select the tab Insert and find the option Schedule.

In our case, a regular graph will do, which displays changes by time interval or categories.

The schedule can be slightly corrected by replacing the numbers 1-12 months. To do this, right-click on the chart and select Select data. Now you can select either the month column or the entire table. In principle, the entire table can be selected immediately when creating a graph. This step was painted only for clarity - you can always add something to the chart or exclude it from it through Selecting a data source.

A legend is always added to the chart, which is sometimes not needed, especially if there is only one chart. To disable the legend, find the item on the toolbar Layout and in it we are looking for an option Legend. Click and select No (Do not add a legend).

Or you can simply select the legend by left-clicking the mouse and pressing the Delete key on your keyboard.

You can change the name of the chart (the name from the header is set by default) by clicking on the name in the chart itself. It will be indicated by a dotted line, after which you need to click on it again and enter the desired name.

If you need a graph with markers, then you need to right-click on the graph and select the item Change chart type. Among the proposed options for the schedule, you need to find Graph with markers.

If you don’t have the time or desire to customize anything, you can use layouts. To do this, select the graph, switch to the menu Constructor and find Express layout. Here you can choose one of the most relevant charting options.

If you need to change any data in the table, then you do not need to delete the chart - all changes in the column are immediately displayed on the chart.

What if you need to add another curve? To do this, click on the chart and select the item Select data.

In chapter Legend items (rows) there is already one row - in our case it is Income. Need to press a button Add.

In the window that opens, you can not prescribe anything. It is enough to remove the data from the field and select the data from the column. To do this, put the cursor in the field Row name and click on the name of the second column (in this case it is Expense). Then in the field Values delete =(1) and highlight all the numeric data of the Expense column. Then it remains to press twice OK. Result:

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

Reading and evaluating a large amount of data that is visualized using graphs and charts is greatly simplified. Excel has an efficient multifunctional tool for this visualization, thanks to which you can build charts and graphs of various types and purposes. Simply indispensable in analytical research.

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

At the moment, versions of the application 2003, 2007, 2010, 2013, 2016 are used. The processes for constructing graphs and charts in them have some differences, primarily in the interface. The main ones will be listed below.

How to build a chart in excel

Excel supports different types graphs for the most understandable and complete display of information. Graphs are being built point by point, 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 (as well as a chart) in MS Excel, you first need to enter numerical data on the sheet, on the basis of which it will be built. Usually for graphics two columns are enough, one of which will be used for the X (argument) axis, the second - for the Y (function) - this can be expressed by a formula or just a list of data dependent on the argument.

Highlight data range. Then, selecting the desired type of chart on the tab Insert in a group Diagrams– press 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 data series, one of which depends on the second).

Microsoft Excel 2003

Microsoft Excel 2013

The graph can be placed both on the same data sheet and on a separate one.

How to build a chart

Similar to graphs, charts are built on the basis of data in the columns of a table, but for some types (pie, donut, bubble, etc.) it is necessary that the data be arranged in a certain way. To build a diagram, you need to go to the tab Diagrams. For example, consider how to do circular.

For such a chart one column are data labels, second- the numeric data series itself.

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

A diagram will automatically be created, which you can change in properties of your choice. You can change style, layout, captions, axes, background and many other settings.

Pie charts show the proportions of parts relative to something whole and are presented as a set of sectors that make up a circle with the corresponding values ​​displayed. This is very useful when you want to compare some data with respect to a total value.

We build a sinusoid

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

To calculate the values ​​of the sines, you need to go to 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 desired range of values.

Further build a graph by 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 corners (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 the version of Excel 2003, you need to click anywhere in this chart, after which you will see a panel Working with charts, tabbed Layout, Format and Constructor. In a group Layout/Signatures select Name diagrams. Change the settings you need.

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

How to set up 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 to the axes and specify the values ​​of the distances between them, add and format the names of the axes, configure display or hide grid.

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

Adding or changing a legend

Thanks to the legend on the graph, the parameter belongs to a particular column.

Excel charts have option legend settings - change the location, show it or hide it.

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

The data source selection window will open, where you can change the range of data used, change the labels of the axes, and legend elements (series), parameters for each series separately.

As you can see, to build a function in Excel, two factors are required - the tabular and graphical parts. The MS Excel application of the office suite 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.