Contents
Данное пособие рассказывает об основах работы с диаграммами в Excel и является подробным руководством по их построению. Вы также узнаете, как объединить два типа диаграмм, сохранить диаграмму в качестве шаблона, как изменить тип диаграммы, заданный по умолчанию, изменить размер или переместить диаграмму.
Excel charts are essential for visualizing data and keeping track of current trends. Microsoft Excel provides powerful charting functionality, but finding the right tool can be tricky. Without a clear understanding of what types of charts are and for what purpose they are intended, you can spend a lot of time fiddling with various elements of the chart, and the result will only have a remote resemblance to what was intended.
We will start with the basics of creating charts and step by step how to create a chart in Excel. And even if you are new to this business, you can create your first chart in a few minutes and make it exactly as you need it.
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 it easier to comprehend large amounts of information or to show the relationship between different subsets of data.
Microsoft Excel allows you to create many different types of charts: bar chart, column chart, line chart, pie and bubble chart, scatter and stock chart, donut and radar chart, area chart and surface chart.
There are many elements in Excel charts. Some of them are displayed by default, others, if necessary, can be added and configured manually.
| 1. Chart area 2. Chart title 3. Construction area 4. Горизонтальная ось (ось категорий) 5. Вертикальная ось (ось значений) | 6. Axis name 7. Data Series Points 8. Chart legend 9. Data Signatures |
Create a chart in Excel
To present data to users in the most meaningful way, you need to choose from a variety of chart types. You can also make a combo chart using several chart types.
To create a chart in Excel, start by entering numeric data into a worksheet and then follow these steps:
1. Prepare data for charting
Most Excel charts (such as bar charts or bar charts) do not require any special arrangement of data. The data can be in rows or columns, and Microsoft Excel will automatically suggest the most appropriate chart type (you can change it later).
To make a beautiful chart in Excel, the following points can be helpful:
- The chart legend uses either the column headings or data from the first column. Excel automatically selects the data for the legend based on the location of the source data.
- The data in the first column (or column headings) 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 to show on the chart
Select all the data you want to include in the Excel chart. Select the column headings you want to appear in the chart legend or as axis labels.
- If you need to build a graph based on adjacent cells, then just select one cell, and Excel will automatically add all adjacent cells containing data to the selection.
- To plot data in nonadjacent cells, select the first cell or range of cells, then press and hold Ctrl, select the rest of the cells or ranges. Note that you can plot a non-contiguous cell or range graph only if the selected area forms a rectangle.
Tip: To select all used cells on a sheet, place the cursor in the first cell of the used area (click Ctrl + Hometo move to a cell A1), then press Ctrl + Shift + Endto 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 chart 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 pre-made charts that best fit the selected data.
In this example, we are creating a XNUMXD 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 Insert a chart (Insert Chart) with a list of available histogram subtypes in the upper part of the window. At the top of the window, you can select other chart types available in Excel.
Tip: To see all available chart types immediately, click the button View all diagrams (See All Charts) in the lower right corner of the section Diagrams (Charts) tab Insert (Insert) Menu ribbons.
In general, everything is ready. The chart is inserted on the current worksheet. Here is a voluminous histogram we got:
The graph looks good already, but there are still a few tweaks and improvements that can be made, as described in the Excel Chart Settings section.
Create a combo chart in Excel to combine two types of charts
If you want to compare different types of data in an Excel chart, then you need to create a combo chart. For example, you can combine a bar or surface chart with a line chart to display data with very different dimensions, such as total revenue and units sold.
In Microsoft Excel 2010 and earlier, creating combo charts was a time consuming task. Excel 2013 and Excel 2016 solve this problem in four easy steps.
- Select the data you want to use to build the chart. In this example, we are using a table Fruit Sales with fruit sales amounts and average prices:
- On the Advanced tab Insert (Insert) click the button View all diagrams (See All Charts) in the lower right corner of the team group Diagrams (Charts) to open the dialog Insert a chart (Insert Chart).
- In the dialog box that appears, click the tab All diagrams (All Charts) and select a category Combined (Combo). Several ready-made combo charts will appear at the top of the dialog box. Click on any of them to see a preview of the graph. The chances are high that you will find a diagram to your liking. For our data, the second type of combo chart is perfect – Гистограмма с группировкой и график на вспомогательной оси (Clustered Column and Line on Secondary Axis).Учитывая, что ряды данных (Amount и Price) have different dimensions, we need an auxiliary axis to clearly see the values for both series on the chart. If none of the ready-made Excel combo charts has a secondary axis, then simply select the one that you like best and turn on the construction on the secondary axis in the parameters of one of the data series.
Если готовые комбинированные диаграммы не совсем подходят, то выберите вариант Custom Combination (Custom Combination) – Last in the list with a pen icon, and set up the desired chart type. Don’t forget to enable plotting on the minor axis for one of the data series.
- Press OKto insert a combo chart into an Excel sheet. Ready!
Учитывая, что ряды данных (Amount и Price) have different dimensions, we need an auxiliary axis to clearly see the values for both series on the chart. If none of the ready-made Excel combo charts has a secondary axis, then simply select the one that you like best and turn on the construction on the secondary axis in the parameters of one of the data series.
Finally, some touches can be added, such as the chart title and axis titles. The finished combo chart might look something like this:
Setting up 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 make the chart easier to read.
In the most recent versions of Microsoft Excel 2013 and Excel 2016, charting has been greatly improved and a new way to access chart formatting options has been added.
In general, there are 3 ways to customize charts in Excel 2016 and Excel 2013:
- Select a chart and find the required options on the tab Working with charts (Chart Tools) Excel menu ribbons.
- Right-click on a chart element and select the appropriate item from the context menu. For example, in the figure below, the context menu item for setting the chart title is highlighted:
- Also, to customize charts, you can use special icons that appear in the upper right corner of the chart as soon as you click on it.
Icon Chart elements (Chart Elements) shows a list of all elements that can be changed or added to the chart. It shows only those elements that are applicable to the selected chart type. Icon Chart elements (Chart Elements) supports live view, so you can hover over an element and see how the chart will look when you use that element. Icon Chart styles (Chart Styles) allows you to quickly change the styles and colors of the chart. Icon Chart Filters (Chart Filters) allows you to show or hide the data displayed on the chart.
Click the icon to access additional options. 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 options you need:
We hope that this brief overview of chart customization features has helped you get a general idea of how you can customize charts in Excel. In the following articles, we’ll take a detailed look at how to customize various chart elements, including:
Save a Chart Template in Excel
If you really like the created chart, then you can save it as a template (.crtx file) and then apply this template to create other charts in Excel.
How to create a chart template
In Excel 2013 and 2016, to save a chart as a template, right-click on the chart and select from the drop-down menu Save as template (Save as Template):
In Excel 2010 and earlier, the function Save as template (Save as Template) находится на Ленте меню на вкладке Constructor (Design) in the section A type (Type).
After clicking Save as template (Save as Template) dialog box will appear. Save a Chart Template (Save Chart Template), where you need to enter the name of the template and click the button Save (Save).
By default, the newly created chart template is saved to a special folder Charts. All chart templates are automatically added to the section Patterns (Templates) that appears in dialog boxes Insert a chart (Insert Chart) и Change the chart type (Change Chart Type) в Excel.
Please note that only the templates that have been saved in the folder Charts will be available in Patterns (Templates). Make sure you don’t change the default folder when you save the template.
Tip: If you have downloaded chart templates from the Internet and want them to be available in Excel when you create a graph, save the downloaded template as .crtx file in folder Charts:
C:UsersИмя_пользователяAppDataRoamingMicrosoftTemplatesCharts
C:UsersИмя_пользователяAppDataRoamingMicrosoftШаблоныCharts
How to use the chart template
To create a chart in Excel from a template, open the dialog box Insert a chart (Insert Chart) by clicking on the button View all diagrams (See All Charts) in the lower right corner of the section Diagrams (Charts). On the tab All diagrams (All Charts) go to section Patterns (Templates) and select the one you need from the available templates.
To apply a chart template to an already created chart, right-click on the chart and select from the context menu Change chart type (Change Chart Type). Или перейдите на вкладку Constructor (Design) and press the button Change chart type (Change Chart Type) in the section A type (Type).
In both cases, a dialog box will open. Change the chart type (Change Chart Type), where in the section Patterns (Templates) you can select the desired template.
How to delete a chart template in Excel
To delete a chart template, open the dialog box Insert a chart (Insert Chart), go to section Patterns (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 Remove (Delete) in the context menu.
Using the default chart in Excel
Диаграммы Excel, заданные по умолчанию, значительно экономят время. Всякий раз, когда нужно быстро создать диаграмму или просто взглянуть на тенденции в данных, диаграмму в Excel можно создать буквально одним нажатием клавиши! Просто выберите данные, которые будут включены в диаграмму, и нажмите одно из следующих сочетаний клавиш:
- Alt + F1to insert a default chart on the current sheet.
- F11to create a default chart on a new sheet.
How to change the default chart type in Excel
When you create a chart in Excel, the bar chart is used as the default chart. To change the default chart format, do the following:
- Нажмите кнопку View all diagrams (See All Charts) in the lower right corner of the section Diagrams (Charts).
- In the dialog box Insert a chart (Insert Chart) right click on the chart or chart template in the section Patterns (Templates) and select Make it default (Set as Default Chart) in the context menu.
- Press OKto save changes and close the dialog box.
Resizing a Chart in Excel
Чтобы изменить размер диаграммы Excel, щёлкните по ней и при помощи маркеров по краям диаграммы перетащите её границы.
Another way is to enter the desired value in the fields Figure height (Shape Height) и Shape Width (Shape Width) в разделе Size (Size) tab Framework (Format).
To access additional options, press the button View all diagrams (See All Charts) in the lower right corner of the section Diagrams (Charts).
Move a chart in Excel
При создании графика в Excel, он автоматически размещается на том же листе, где находятся исходные данные. Можно переместить диаграмму в любое место на листе, перетаскивая её с помощью мыши.
If you find it easier to work with the graph on a separate sheet, you can move it there as follows:
- Select chart, go to tab Constructor (Design) and press the button Move Chart (Move Chart).
- In the dialog box Moving a chart (Move Chart) select On a separate sheet (New Sheet). If you plan to insert several chart sheets into your workbook, give the new sheet a descriptive title and click OK.
If you want to move the chart to an existing sheet, select the option on existing sheet (Object in) and select the desired sheet from the drop-down list.
To export a chart outside of Excel, right-click on the chart border and click Copy (Copy). Затем откройте другую программу или приложение и вставьте туда диаграмму. Вы можете найти еще несколько способов для экспорта диаграмм в данной статье — Как сохранить диаграмму Excel как картинку.
This is how charts are created in Excel. I hope you found this overview of the main features of charts useful. In the next lesson, we will take a closer look at customizing various chart elements, such as chart title, axis titles, data labels, and so on. Thank you for your attention!