Why can't I get a diagram in Excel? How to create a chart in Excel: Settings and Formatting. How to change chart type

In Excel you can work not only with numerical data. The program's tools also make it possible to construct diagrams of any complexity based on the data entered into the table. In this case, the design of the diagram can be customized at your discretion. In this article, we will look at techniques for constructing various charts using the tools available in Microsoft Excel.

The technique of creating diagrams is no more complicated than constructing ordinary tables in Excel, and anyone can master it easily and quickly.

1. Before you start building any chart, you need to create a table and fill it with data. The future diagram will be built on the basis of this table.
2. When the table is completely ready, you need to select the area that you want to display as a diagram, then go to the “Insert” tab. Here you will be presented for selection different types diagrams:
   • Histogram
   • Schedule
   • Circular
   • Hierarchical
   • Statistical
   • Spot
   • Cascade
   • Combined

   

   Note:
   Depending on the version of the program, the number of chart types may vary.

   There are also other types of charts, but they are not as common. You can view the full list through the “Insert” menu (in the program menu bar at the very top), then the “Diagram” item.

   

3. Once you have decided on the type of chart you need, click on the appropriate item. Next you will be asked to select a subtype. For example, if you select a histogram, the following subtypes will be offered: regular, volume, line, volume bar.
   
4. Select the appropriate subtype, click on it, after which the diagram will be automatically generated and displayed on the screen.
   
5. For example, the visual display of a regular histogram will look like this:
   

   The graph chart will be displayed as follows:

   

   And this is what a pie chart looks like:

   

How to work with charts

When the diagram is built, you can start working with it, as well as customizing its appearance. To do this, go to the “Design” tab. Here you can configure various parameters of the created diagram, for example, change the design style, change the type, subtype, etc.

For example, to change the type of chart and its subtype, click on the “Change chart type” button and select what we need from the list that opens.

By clicking on the “Add chart element” button, you can open a list of actions that will help you customize your chart in detail.

For quick setup You can also use the Express Layout tool. Suggested here various options chart design, and you can choose the one that best suits your purposes.

It is quite useful, along with the bars, to also have a specific data value for each of them. The data signing function will help us with this. We open the list by clicking the “Add chart element” button, here we select the “Data Labels” item and then the option that we like (in our case, “At the edge, outside”).

Done, now our diagram is not only visual, but also informative.

If you think the fonts in the chart are too small, you can adjust them. To do this, right-click, for example, on the data signature, select “Font...” from the menu that opens.

Here you can make the required changes and save them by clicking the “OK” button.

Chart with percentages

To create charts that require percentage display of data, the best option is to use the pie type.

The Italian engineer, economist and sociologist Vilfredo Pareto put forward a very interesting theory, according to which the 20% of the most effective actions taken provide 80% of the final result. It follows from this that the remaining 80% of actions provide only 20% of the achieved result.

This type of chart allows you to calculate the most effective actions that provide the greatest return. Let's try to build this diagram using the tools available in Microsoft program Excel. The most suitable chart type to achieve this goal is a histogram.

1. We create a table, for example, with product names. One column will indicate the purchase volume in monetary terms, and the other will indicate the profit received. The purpose of this table is to calculate the purchase of which product brings the maximum benefit when selling it.
   
2. We build a regular histogram. To do this, you need to select the table area, go to the “Insert” tab and then select the chart type.
   
3. After we have done this, a chart will be formed with 2 bars of different colors, each of which corresponds to data from different columns of the table.
   
4. The next thing to do is to change the column responsible for profit to the “Chart” type. To do this, select the required column and go to the “Designer” section. There we see the “Change chart type” button, click on it. In the dialog box that opens, go to the “Graph” section and click on the appropriate type of graph.
   
5. That's all that needed to be done. Pareto chart is ready.
   Further, it can be edited in the same way as we described above, for example, by adding the values ​​of bars and points with values ​​on the chart.
   

Excel is one of the most best programs for working with tables. Almost every user has it on their computer, since this editor is needed both for work and for study, while performing various coursework or laboratory assignments. But not everyone knows how to make a chart in Excel using table data. In this editor you can use a huge number of templates that were developed in Microsoft. But if you do not know which type is better to choose, then it would be preferable to use automatic mode.

In order to build such an object, you must perform the following steps.

1. Create some table.

1. Highlight the information on which you are going to build a chart.

1. Go to the "Insert" tab. Click on the "Recommended Charts" icon.

1. You will then see the Insert Chart window. The options offered will depend on what exactly you select (before clicking the button). Yours may be different, since everything depends on the information in the table.

1. In order to build a diagram, select any of them and click on “OK”.

1. In this case, the object will look like this.

Manually selecting a chart type

1. Select the data you need for analysis.

1. Then click on any icon from the specified area.

1. Immediately after this, a list of different object types will open.

1. By clicking on any of them, you will get the desired diagram.

To make it easier to make a choice, just point at any of the thumbnails.

What types of diagrams are there?

There are several main categories:

• histograms;

• graph or area chart;

• pie or donut charts;

Please note that this type suitable for cases where all values ​​add up to 100 percent.

• hierarchical diagram;

• statistical chart;

• dot or bubble plot;

In this case, the point is a kind of marker.

• waterfall or stock chart;

• combination chart;

If none of the above options suits you, you can use combined options.

• superficial or petal;

How to make a pivot chart

This tool is more complex than those described above. Previously, everything happened automatically. All you had to do was choose appearance and the desired type. Everything is different here. This time you will have to do everything manually.

1. Select the required cells in the table and click on the corresponding icon.

1. Immediately after this, the “Create PivotChart” window will appear. You must specify:
   • table or range of values;
   • the location where the object should be placed (on a new or current sheet).
2. To continue, click on the “OK” button.

1. As a result of this you will see:
   • empty pivot table;
   • empty diagram;
   • Pivot chart fields.

1. You need to drag the desired fields into the areas with the mouse (at your discretion):
   • legends;
   • values.

1. In addition, you can configure exactly what value you want to display. To do this, right-click on each field and click on “Value Field Options...”.

1. As a result, the “Value Field Options” window will appear. Here you can:
   • sign the source with your estate;
   • Select the operation that should be used to roll up the data in the selected field.

To save, click on the “OK” button.

Analyze tab

Once you've created your PivotChart, you'll be presented with a new Analyze tab. It will immediately disappear if another object becomes active. To return, just click on the diagram again.

Let's look at each section more carefully, since with their help you can change all elements beyond recognition.

PivotTable Options

1. Click on the very first icon.
2. Select "Options".

1. This will open the settings window. of this object. Here you can set the desired table name and many other parameters.

To save the settings, click on the “OK” button.

How to change the active field

If you click on this icon, you will see that all tools are not active.

In order to be able to change any element, you need to do the following.

1. Click on something on your diagram.

1. As a result, this field will be highlighted in circles.
2. If you click on the “Active Field” icon again, you will see that the tools have become active.

1. To make settings, click on the appropriate field.

1. As a result, the “Field Options” window will appear.

1. For additional settings go to the "Markup and Print" tab.

1. To save the changes made, you must click on the “OK” button.

How to insert a slice

If you wish, you can customize the selection based on specific values. This feature makes it very convenient to analyze data. Especially if the table is very large. In order to use this tool, you need to take the following steps:

1. Click on the “Insert Slice” button.
2. As a result, a window will appear with a list of fields that are in the pivot table.

1. Select any field and click on the “OK” button.

1. As a result of this, a small window will appear (it can be moved to any convenient place) with all the unique values ​​(totals) for this table.

1. If you click on any line, you will see that all other entries in the table have disappeared. All that remains is where the average value matches the selected one.

That is, by default (when all the lines in the slice window are highlighted in blue), all values ​​are displayed in the table.

1. If you click on another number, the result will immediately change.

1. The number of lines can be absolutely any (minimum one).

Both the pivot table and the chart based on its values ​​will change.

1. If you want to delete a slice, you need to click on the cross in the upper right corner.

1. This will restore the table to its original form.

In order to remove this slice window, you need to take a few simple steps:

1. Right-click on this element.
2. After this, a context menu will appear in which you need to select the “Delete ‘field name’” item.

1. The result will be as follows. Please note that the panel for setting the fields of the pivot table has again appeared on the right side of the editor.

How to insert a timeline

In order to insert a slice by date, you need to take the following steps.

1. Click on the appropriate button.

1. In our case, we will see the following error window.

The point is that to slice by date, the table must have the appropriate values.

The operating principle is completely identical. You will simply filter the output of records not by numbers, but by dates.

How to update data in a chart

To update information in the table, click on the corresponding button.

How to change build information

To edit a range of cells in a table, you must perform the following operations:

1. Click on the “Data Source” icon.
2. In the menu that appears, select the item of the same name.

1. Next, you will be asked to specify the required cells.
2. To save the changes, click on “OK”.

Editing a chart

If you are working with a chart (no matter which one - regular or summary), you will see the “Design” tab.

There are a lot of tools on this panel. Let's take a closer look at each of them.

Add element

If you wish, you can always add some object that is missing in this diagram template. To do this you need:

If you don't like the standard template when creating a chart, you can always use other layout options. To do this, just follow these steps.

1. Click on the appropriate icon.
2. Select the layout you need.

You don't have to make changes to your object right away. When you hover over any icon, a preview will be available.

If you find something suitable, just click on this template. The appearance will automatically change.

1. If you want to see what this will look like on your chart, just hover over any of the colors.

1. To save changes, you need to click on the selected shade.

In addition, you can use ready-made themes registration To do this you need to do a few simple operations.

1. To save changes, click on the selected option.

In addition, manipulations with the displayed information are available. For example, you can swap rows and columns.

After clicking this button, you will see that the diagram looks completely different.

This tool is very helpful if you cannot correctly specify the fields for rows and columns when building a given object. If you make a mistake or the result looks ugly, click on this button. Perhaps it will become much better and more informative.

If you press again, everything will go back.

In order to change the range of data in the table for plotting a chart, you need to click on the “Select data” icon. In this window you can

• select the required cells;
• delete, change or add rows;
• edit the horizontal axis labels.

To save the changes, click on the “OK” button.

How to change chart type

1. To simplify your selection, you can hover over any of the thumbnails. As a result, you will see it in an enlarged size.

1. To change the type, you need to click on any of the options and save using the “OK” button.

Conclusion

In this article, we took a step-by-step look at the technology for constructing charts in the Excel editor. In addition, special attention was paid to the design and editing of created objects, since it is not enough to be able to use only ready-made options from Microsoft developers. You must learn to change the appearance to suit your needs and be original.

If something doesn't work for you, you may be highlighting the wrong element. It must be taken into account that each figure has its own unique properties. If you were able to modify something, for example, with a circle, then you won’t be able to do the same with the text.

Video instruction

If for some reason nothing works out for you, no matter how hard you try, a video has been added below in which you can find various comments on the actions described above.

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 necessary for visualizing data and monitoring current trends. Microsoft Excel provides powerful functionality for working with charts, 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 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 charts: bar chart, histogram, line chart, pie and bubble charts, scatter and stock charts, donut and radar charts, area charts, 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 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 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 bar or surface chart with a line graph to display data with very different dimensions, such as total revenue and number of 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 the chart experience and added a new way to 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 chart customization features helped you get a general idea of ​​how you can customize charts 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 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, 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 the desired value in 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 the chart on a separate sheet, you can move it there as follows:

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!

As Confucius once said, a picture is worth a thousand words. By adding charts to worksheets, we not only add interest to worksheets that involve a lot of numbers, but we also illustrate trends and anomalies that might otherwise go unnoticed. In Excel, the process of creating charts is simplified as much as possible and, in addition, it is possible to experiment with different types graphical representations. So let's find out how to create a chart in Excel?

Charts in Excel

Before we start looking at charts in Excel, I would like to say a few words about them. Remember how your high school algebra teacher tried to teach you how to solve equations using graphs, plotting some values ​​on the X-axis and others on the Y-axis? Naturally, at that time your head was busy with other things, and you did not listen to him very carefully. You may have been saying to yourself, “I’ll never need this crap once I graduate from school and get a cool job!”

But, as they say, never say never, and school knowledge of algebra can come in handy. Even though Excel automates most of the chart creation process, you need to be able to differentiate the X-axis from the Y-axis in case your chart isn't built correctly. To refresh your memory, the X axis is horizontal and is located at the bottom of the chart, and the Y axis is vertical and is located on the left side of the chart.

Most charts use only these two axes, with Excel plotting the categories along the X-axis ( categories), and their values ​​are along the Y axis. The X axis is sometimes called temporal, because many charts plot time periods along it, such as months, quarters, or years.

Keep in mind that the worksheet values ​​represented graphically in the chart are dynamically linked to the cells. Any change to the corresponding cell in the worksheet is automatically reflected in the chart.

Excel users have access to a number of quick and simple ways creating charts based on source data. But before you use one of them, select the data from which you will create a chart. To do this, place the cell cursor in the data table area. If you need to select a piece of data from a large table, select the values ​​and headings that will be included in the new chart.

My favorite way to create charts in Excel is to use the button Recommended Charts(Recommended Charts) located on the ribbon tab Insert(Insert). A dialog box will appear on the screen Inserting a chart(Insert Chart) with tab selected Recommended Charts(Recommended Charts). To see what the data will look like if you select a particular chart, click on the thumbnail on the left side of the window. Once you find the chart you want, click on the button OK. As a result of these actions, the diagram will be embedded in the current sheet.

To the right of the button Recommended Charts there is a group All Diagrams(Charts) ribbon tabs Insert. This group contains drop-down collection buttons that allow you to select different chart types and styles.

1. Insert a histogram or bar chart(Insert Column Chart or Bar Chart). Select a histogram, 3-dimensional histogram, bar chart, or 3-D bar chart.
2. Insert Hierarchical Chart(Insert Treemap Chart). Insert a tree diagram or sunburst diagram. This type of chart is used to compare parts to the whole, and when categories have multiple columns organized in a hierarchical structure.
3. Insert a waterfall or stock chart (InsertStockorWaterfallChart). Select a waterfall or stock chart.
4. Insert a graph or area chart (InsertLineChartorAreaChart). Inserting two or 3D graphics or a 2D or 3D area chart.
5. Insert statistical chart (Inserthistrogramcharts). Insert a histogram, Pareto chart, or box-whisker chart.
6. Insert Combo Chart (InsertComboChart). Insert a 2D combined area histogram, clustered histogram, or stacked surface chart.
7. Insert a pie or donut chart (InsertPieorDoughnutChart). Select a 2D or 3D pie chart or a 2D donut chart.
8. Insert dot (X,Y) or bubble chart (InsertScatter (X,Y)orBubbleChart). Insert a 2D scatter (X,Y) or bubble chart.
9. Insert a surface or radar chart (InsertSurfaceorRadarChart). Insert one of the surface or radar charts.

Using the collections associated with a specific chart button, select the chart you want, and then click that button to embed the chart in the worksheet.

If you use the ribbon tab buttons Insert If you still couldn’t find the right diagram, then use the tab All diagrams(All Charts) dialog box Inserting a chart. Various chart types and styles are available in this window. To open this window, click on the group dialog box open marker Diagrams tabs Insert. Then select the tab All diagrams to access full list diagrams.

How to Create a Chart in Excel Using Quick Analysis Tools

If you need to create a chart based on a subset of a data table, use quick analysis tools. To do this, follow these steps:

1. Click the Quick Analysis tool icon located near the lower right corner of the selected cell range.

A palette of quick analysis tools will appear on the screen with the selected tab Formatting(Formatting). This palette also provides various conditional formatting options.

1. Click the Charts tab button (Charts), located at the top of the quick analysis tool palette.

After selecting a tab Diagrams buttons are displayed Ruled With grouping(Clustered Vag), Stacked ruler(Stacked Vag), Histogram with grouping(Clustered Column) Stacked histogram(Stacked Column) Spot(Scatter) and Other charts(More Charts). The first five buttons allow you to preview what the charts created from the selected data will look like. After selecting the last button, Other diagrams, a dialog box will appear on the screen Inserting a chart with dedicated tab Recommended charts. In this window you can choose from a wide variety of chart types.

1. To see what the different types of charts you create in Excel based on the selected data, select the corresponding chart button in the Quick Analysis tool palette.

After selecting the button for the chart type you need, a large thumbnail of the chart will appear on the screen, which will be created based on the selected tabular data. This thumbnail appears above the Quick Analysis tool palette as long as the mouse pointer is over the corresponding chart button.

1. Once you find the chart you want, click on the corresponding button on the Quick Analysis toolbar to select it.

Excel will create a floating (or embedded) chart that resides in the current worksheet. This chart will be activated so you can move and change it as you wish.

In the screenshot, the first quarter sales data (along with column headings) in the table has been selected. After selecting a range of cells and clicking on the Quick Analysis Tools icon displayed near the lower right corner of the selected cell range, the tab was selected Diagrams. A histogram with grouping was selected in the Quick Analysis tool palette. A histogram thumbnail with grouping appears above the Quick Analysis tool palette.

The created diagram is activated and the area occupied by it is automatically selected. This allows you to move the chart to any part of the sheet by dragging. While the chart is selected, individual areas of the chart are highlighted in different colors. The titles used for the chart legend are colored red. The color purple is used to highlight headings that serve as labels for the horizontal axis (category axis, or x-axis). Blue color is used to highlight values ​​represented as columns in a histogram, as well as for the vertical axis labels (value axis, or y-axis). A set of contextual tabs will also appear on the ribbon. Working with charts(Chart Tools), including contextual tabs Constructor(Design) and Format(Format), and the tab will be selected Constructor.

How to create a chart in Excel on a separate sheet?

Sometimes you want to display the chart you just created on a separate worksheet, and you don't want to have to deal with moving the embedded chart you created with Quick Analysis tools or searching for buttons on the Ribbon tab Insert. In such situations, position the cell cursor anywhere in the data table from which the chart will be created (or select a range of cells in a large table), and press<F11>.

Excel will create a grouped histogram based on the data in the table or a selected range of cells on the chart sheet (Chart 1). The settings of a chart located on a separate sheet can be changed in the same way as the settings of an embedded chart.

How to move and resize a chart?

Immediately after creating the diagram, you can easily resize it and move it anywhere on the worksheet, since it remains selected. You can always tell when a graphic object, such as a chart, is selected because there are sizing handles that look like small squares along its outline. These markers are located at the corners and in the middle of the charting area. The following three buttons will also appear near the lower right corner of the selected chart.

ChartElements). This button is indicated by a plus sign and allows you to change chart elements such as chart titles, legend, grid, error bars, and trend lines.

ChartStyles). This button is indicated by a brush symbol and allows you to change the chart layout and select a different color scheme.

ChartFilters). This button is indicated by a conical funnel icon and is intended to change the data series presented in the chart or the labels displayed in the legend or along the category axis.

If an embedded chart is selected (either immediately after creation or after clicking on it), a set of contextual tabs is displayed on the ribbon Working with diagrams (ChartTools), containing its own contextual tabs Constructor (Design) And Format(Format), and each group of cells presented in the chart is highlighted in a different color.

The embedded chart, highlighted on the worksheet, can be moved and scaled.

• To move the chart, place the mouse pointer anywhere on the chart and drag it to a different position.
• To resize the chart (you can stretch it in any direction if it looks distorted), place your mouse pointer over one of the selection handles. When the pointer changes to a double-headed arrow, drag the handle (the direction depends on which handle you selected and whether you want to stretch or shrink the diagram in that direction).

Once the chart is proportionally sized and in the desired location on the worksheet, deselect it (click anywhere on the sheet outside the chart area). Once the selection is deselected, the selection markers, as well as the buttons (Chart Elements), Chart styles(Chart Styles), (Chart Filters) and a set of contextual tabs Working with charts(Chart Tools) will disappear from the screen.

To select the chart again for editing, resizing, or moving, click anywhere on the chart. Once you've done that, the selection handles and set of contextual tabs Job With diagrams will appear on the screen again.

How to move a chart to a separate sheet?

Even though Excel automatically places all new charts on the same worksheet that contains the original data (unless, of course, you use the key trick<F11>), sometimes it is more convenient to place them on a separate sheet. To move an embedded chart to its own sheet, follow these steps:

1. Click the Move Chart button (Movechart), located on the Constructor contextual tab.
2. In the Move Chart dialog box that opens, set the radio button to “on a separate sheet” ( Newsheet).
3. (Optional) In the text box next to the radio button, rename the worksheet you are creating from the standard Chart1 to something more meaningful.
4. Click OK to close the dialog box and open a new sheet with the chart placed on it.

If, after moving the chart to a separate sheet, you decide to return it to the data sheet, click the button Move chart contextual tab Constructor. Then click on the button An object(Object), select the name of the desired worksheet from the associated drop-down list and click OK.

How to Create a Chart in Excel: Edit Charts Using the Contextual Design Tab

On the tab Constructor, related to a set of contextual tabs Working with diagrams, There are buttons designed to perform any transformations on the new diagram.

Context tab Constructor contains the following groups of buttons.

1. Chart Layouts (ChartLayouts). To change specific chart elements such as titles, data labels, legend, and other elements, click the button Add a chart element(Add Chart Element). Note that most of the options are duplicated in the Chart Elements palette, which appears when you click the This button is located to the right of the highlighted embedded chart. To select a new layout for the selected chart, click the button Express layout(Quick Layout).
2. ChartStyles). Click the button Change colors(Change Colors) to display a pop-up palette including a variety of full color and monochrome color schemes, which can be applied to the chart. In the Chart Styles collection, you can view or select a style for the current chart.
3. Data (Data). Click the button Row column(Switch Row/ Column) to immediately swap the data used for the legend and category axis. Click the button Select data(Select Data) to open a data source selection dialog in which you can not only swap the legend and categories, but also edit any of these categories.
4. Type (Ture). To change the chart type, click the button Change chart type(Change Chart Type), and then in the dialog box that opens, go to the tab All diagrams select the chart type you need.
5. Location (Location). Click the button Move chart(Move Chart) to move the chart to a new chart sheet or to another worksheet.

How to Create a Chart in Excel: Customizing Chart Elements

Using the button (Chart Elements), indicated by a plus, which is displayed after selecting a chart, you can add various elements to the chart. To add a chart element, click the button. A list of the main elements of the diagram will appear on the screen.

After checking the checkbox, the corresponding element immediately appears in the diagram area. To delete specific element diagrams, clear the corresponding checkbox.

To add or remove part of a chart element, or change the layout for elements such as (Chart Title), Data Labels(Data Labels), Data table(Data Table) Error limit(Error Bars) Legend(Legend) or Trend line(Trendline), select the required option from the corresponding submenu of the chart element.

For example, to change the position of the chart title, click on the item's submenu and select the appropriate option.

• Above the diagram(Above Chart). Add a chart title or move it above the chart area and center it.
• Bycenter(overlay) (Centered Overlay Title). Adds or moves the chart title to the top of the charting area, aligned to the center.

• Extra options(More Options). Displays the panel (Format Chart Title) on the right side of the Excel window. In this panel, you can select various formatting options for the chart title by clicking the buttons Shading and borders(Fill & Line) Effects(Effects) and Size and properties(Size and Properties). The indicated buttons are located under the list header Title options Text Options Text fill and outline(Text Fill & Outline), Text effects(Text Effects) and Inscription(Textbox).

How to Create a Chart in Excel: Adding Data Labels to Individual Chart Series

Data labels display the numeric values ​​of worksheet cells that correspond to specific points on a chart. To add data labels to the selected chart and position them accordingly, click the button Chart elements located to the right of the selected chart, and select the checkbox Data Labels(Data Labels). A submenu will appear with the following options available.

• In the center(Center). Centering the labels on the data points on the chart.
• At the edge, inside(Inside End). Placing labels inside, near the edge of the data points.
• At the base, inside(Inside Base). Placing data labels inside, at the base of the data points.
• At the edge, outside(Outside End). Placing labels on the outside, near the edge of the data points.
• (Data Callout). Add text and numeric labels that appear in callouts that describe data points.

• Extra options…(More Data Label Options). Panel display Data Signature Format(Format Data Labels) on the right side of the Excel window. In this panel, you can select various data label formatting options by clicking the buttons Shading and borders(Fill & Line) Effects(Effects) and Size and properties(Size and Properties). These buttons are located under the list header Title options(Title Options). Under the heading Text Options(Text Options) buttons are located Text fill and outline(Text Fill & Outline), Text effects(Text Effects) and Inscription(Textbox).

How to add a data table to a chart?

Sometimes, instead of labels that may overlap data points in a chart, it makes sense to insert a table of data below the chart.

To add a data table to a selected chart (or change its location or format), click the button located to the right of the selected chart and select the checkbox Data table. From the submenu that appears, you can select one of the following options.

• Show legend keys(With Legend Keys). Selecting this option displays a table at the bottom of the chart that includes color keys that are used in the legend to highlight the different data series in the first column.
• Don't show legend keys(No Legend Keys). Select this option to create a data table at the bottom of the chart area (without a legend).
• Extra options…(More Options). Panel display Data Table Format(Format Data Table) on the right side of the Excel window. From this task pane, you can select various data table formatting options by clicking the buttons Shading and borders(Fill & Line) Effects(Effects) and Size and properties(Size and Properties). These buttons are located under the list header Title options(Title Options). Under the heading Text Options(Text Options) buttons are located Text fill and outline(Text Fill & Outline), Text effects(Text Effects) and Inscription(Textbox).

If you no longer need a table at the bottom of the chart, select No from the Data Table button drop-down menu.

How do I change the title of a chart?

Initially, generic titles such as “Axis Title” and “Chart Title” are added to the chart. To replace these useless titles with others, click on the title itself or on the drop-down button Add a chart element, located in the group Current fragment(Current Selection) tabs Format set of contextual tabs Working with charts, and then select the desired chart element. Excel will let you know that a particular element is highlighted by enclosing it in a frame.

Once the title is selected, click on the insertion point in the text and edit the title as you would any worksheet cell. You can click on the title to select it completely, enter a new title and press the key to replace the old header with a new one. If you want to place title text on two lines, click where you want the line break to be and press .

When you're finished editing the title, click anywhere else on the chart (or worksheet if you're done editing the chart).

How to Create a Chart in Excel: Formatting Chart Titles

If headings are added to a chart, Excel uses the Calibri font by default; in this case, a 14-point font is selected for the chart title, and a 10-point font for the axes titles. To change the title font or its attributes, select the title and use the group buttons Font(Font) tabs home(Noshe).

With Live Preview, you can see the font under your mouse pointer directly on the diagram before you select it. To do this, expand the buttons attached Font(Font) and Font size(Font Size) lists and then hover over different items.

If you need to change any other header formatting attributes, you can do this using the contextual tab Format from a set of contextual tabs Working with charts. To format the text field that contains the title, click one of the following group buttons Shape styles(Shape Styles).

• Shape styles(Shape Styles). These thumbnails, along with drop-down style collections, allow you to format both the text itself and the attributes of the field containing it.
• Filling a shape(Shape Fill). This button allows you to select a fill color for the title field.
• Figure outline(Shape Outline). This button allows you to select a color for the field outline.
• Shape Effects(Shape Effects). This button allows you to apply a new effect (Shadow, Glow, etc.) to the text field.

To format only the text of headings, use the icons from the group Word Styles Art (WordArt Styles).

• Express styles. Icons from the WordArt style gallery allow you to apply a new WordArt style to the selected heading text.
• Fill text(Text Fill). This button allows you to select the title text color from the palette.
• Text outline(Text Shape) This button allows you to select an outline color for title text characters from a palette.
• Text effects(Text Effects). This button allows you to select an effect to apply to the text (for example, shadow, glow, reflection, etc.) from a drop-down list.

How to Create a Chart in Excel: Formatting Category and Value Axes

If a chart displays a lot of values, Excel doesn't care much about how the value (or category) axes on a 3D bar chart or scatter chart are formatted.

If you don't like how the values ​​on the X or Y axis are displayed, change the axis format. To do this, follow these steps:

1. Click on the appropriate axis or select an axis name from the list of chart elements. (This is the first button in the Current fragment group ( CurrentSelection) of the Format contextual tab (Format).) From the drop-down list, select Horizontal Axis (of categories) (Horizontal (Category)Axis) for horizontal axis or Vertical axis (values) (Vertical (Value)Axis) - for vertical.

Excel will limit the selected axis to an outline with selection handles.

1. Click the Format Selection button (FormatSelection), located in the Current fragment group of the Format contextual tab.

The taskbar will appear Axis format(Format Axis), on which under the title Axis parameters(Axis Options) there are icons that allow you to change the axis settings.

1. To change the scale, appearance of labels, and their placement on an axis, change the appropriate settings in the Axis Options section (this section is automatically selected when the Axis Format taskbar is displayed.)

Using these options, you can adjust the minimum and maximum values ​​(corresponding to the end labels), set the values ​​to be displayed in reverse order, and use a logarithmic scale. You can also display axis tick marks (hundreds, thousands, millions, etc.), change the location of the major and intermediate labels, and change the intersection point of the X and Y axes.

1. To change the formatting of the selected axis values, click the Number option and then click the Category drop-down list ( Ca tegory) select the desired number format and set the necessary settings. To assign the same numeric formatting to values ​​in the selected axis that you assigned to values ​​in worksheet cells, select the Link to Source check box ( Linked ThatSource).

For example, to select a number format with no decimal separators, select Numerical(Number), check the box Separate groups of digits(Use 1000 Separator (,)) and enter zero in the field Number of decimal places(Decimal Places).

1. To change the alignment and orientation of the selected axis labels, click the Size and Properties button ( Size & Properties), located in the Axis Options section of the Axis Format taskbar, and then in the Vertical Alignment drop-down lists ( VerticalAlign-ment) and Text Direction (TextDirection) select the appropriate values.
2. Click the Close button (Close) to close the Format Axis task pane.

As you set new axis parameters, the program displays them on the chart. However, these changes will actually be applied to the diagram only after clicking the button Close taskbar Axis format.

To change the default font and its size, as well as other attributes of the labels along the selected axis, click on the corresponding group button Font tabs home.

Conclusion

Now you know, how to create a chart in Excel different ways. Learned how to move and resize a diagram. Mastered the method of creating diagrams on a separate sheet. Learned how to change chart elements and format text in a chart. We hope this article was useful to you.

Information that is displayed visually is much easier to perceive, this is a proven fact. The trend is especially pronounced in different comparisons. Below we will look at how to build a chart in Excel using table data. It will be step-by-step instruction Without further ado, we will also touch on a number of related issues.

Creating a diagram

We will solve the problem different ways, 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 diagram, you should initially have the data that will form its basis. Therefore, select the entire column of numbers from the plate and press the button combination Ctrl + C.

1. Next, click on the Insert tab and select a 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 configured the final result as follows:

1. Let's give the sign a name. In our case it is Product prices. To get into editing mode, double-click on the chart title.

1. You can also get into the editing mode by clicking on the button marked with the number 1 and selecting the function.

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

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

Comparison of different values

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

1. Copy the numbers in the second column.

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

There are hundreds of other types of graphs in the program, they can be found in the Insert menu. Through trials and combinations, each one 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. Moreover, we will do this with the conclusion of the percentage. Let's get started.

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

1. Click on the Insert tab again and select the pie chart from the list of styles.