How to create a chart on a separate sheet. Building charts in Excel

Lesson objectives:

Introduce the concept of a diagram, explain the need for its use; introduce students to the types of diagrams; developing data analysis skills using various types of diagrams.
Development of creative abilities, logical and analytical thinking.
Moral education of the individual, formation of determination, accuracy when working on a computer

Preparation for the lesson:

There is text on the board homework
Presentation for demonstration while explaining new material (Appendix 3).
To check the knowledge gained in the previous lesson:

To secure the material on the desktops of each computer, place the file in Excel format with ready-made assignment texts (Appendix 4).

Lesson progress

I. Repetition of the studied material.

The group of students, at the discretion of the teacher, is conditionally divided into 3 subgroups: students of the first subgroup perform computer testing (Appendix 1), students of the second subgroup determine the value of the formula formed by copying from cards (Appendix 2).

A frontal survey is conducted with the remaining students:

Assigning and launching the Excel application.
Data types used in Excel.
The concept of relative and absolute references.
The concept of function. Types of functions (give examples).

II. Activation of knowledge.

The material is explained using a presentation (Appendix 3).

After the worksheet has been built on the worksheet, you can work with it. And although the table immediately displays the result after entering data into it, the calculations usually do not end there. Next, as a rule, an analysis of the results begins, an assessment of other options for the situation, i.e. reasoning on the topic “What would happen if…” (“... what would happen if 20 kg less pineapples were brought in, and 100 more fish?”).

This approach – enumerating options for solving a problem – is very useful. For many problems, this is the only way to find an acceptable solution to the problem. For these purposes, Excel is indispensable: we quickly change data and instantly get results.

With development computer technology One of the areas of computer application has developed - information visualization. There are several directions in this area: engineering graphics, business graphics and a number of others. Creating diagrams using a computer is one of the main means of business graphics.

III. Explanation of new material.

Diagram– this is a conventional representation of numerical quantities or their ratios graphically An example of a diagram, a special case of it, is the well-known graph of a function.

In the business, financial, and economic spheres, the role of diagrams is very great. How much time must a company economist spend gazing at the table, looking for the maximum and minimum values of various indicators in it, identifying the relationships between these values for various divisions! Naturally, such an analysis is greatly simplified with the presence of diagrams. Sometimes one or two glances at the chart are enough to get the information you need.

MS Excel charts provide the ability to graphically represent various numerical data. By selecting the chart type, layout, and style that you'll find in the new Office versions Excel 2007 is always available on the ribbon, each chart can be processed quickly and professionally.

You can build in Excel two types of charts: implemented and diagrams on separate sheets. Embedded ones are created on worksheets next to tables, data, and text and are used when creating reports. Charts on a separate sheet are convenient for preparing slides or for printing.

Excel offers a variety of chart types and provides a wide range of options for changing them (chart type, labels, legends, etc.) and for formatting all chart objects.

To create charts in MS Excel, you first need to prepare the data for charting and determine its type. The chart is created using the Chart Wizard. This is a program with great capabilities. To solve the problem, you need to select a range of cells in the table, the values of which should be presented in the form of a diagram, and understand for yourself: the data of which column (row) should be plotted along the X axis (i.e., considered as categories), and which columns (rows) – along the Y axis (considered as values).

The number of data series (Y) must be less than the category (X). Based on this, the location of the series is determined (in rows or columns); if the chart is built for a range of cells that has more columns than rows, or an equal number of them, then rows are considered data series. If a range of cells has more rows, then data rows are considered columns. Excel assumes that the titles associated with data series are considered to be their names and form the chart legend. Data interpreted as categories are considered category names and are plotted along the X-axis.

Excel 2007 supports a variety of chart types to help users display data in a way that is meaningful to specific audiences. When you create or edit an existing chart, you can choose from many available subtypes of each chart type.

Histograms. Data that is arranged in columns or rows can be depicted as a histogram. Histograms are used to show changes in data over a period of time or to illustrate comparisons between objects. Histograms typically display categories along the horizontal axis and values along the vertical axis.

Bar charts. They are similar to histograms (the difference is that they are rotated 90 0 clockwise). They are used to compare individual values at a certain point in time; they do not provide an idea of how objects change over time. The horizontal arrangement of the stripes allows you to emphasize positive or negative deviations from a certain value.

Bar charts can be used to show variances across different budget items at a specific point in time. You can drag the points to any position. Bar charts illustrate comparisons between individual elements.

Charts. Graphs allow you to depict the dependence of data (Y-axis) on a value that changes with a constant step (X-axis). The category axis labels should be arranged in ascending or descending order.

Graphs are most often used for commercial or financial data that is evenly distributed over time (displaying continuous data), or categories such as sales, prices, etc.

You may want to use graphs to display evenly spaced values, such as months, quarters, or fiscal years. This is especially important if you have multiple rows - for one row you can use the category axis. Graphs can also be used when there are several evenly distributed numeric labels, especially years. If there are more than ten number labels, it is better to use a scatter plot instead of a graph.

Pie charts. Data that is arranged in one column or row can be depicted as a pie chart. A pie chart shows the size of the elements of one data series in proportion to the sum of the elements. Data points in a pie chart are plotted as a percentage of the entire pie. These diagrams can be used when the components add up to 100%.

Scatter plots. A scatter plot shows relationships between numerical values in multiple data series or displays two groups of numbers as a single series of x and y coordinates.

A scatter plot has two value axes, with one set of values plotted along the horizontal axis (X-axis) and the other along the vertical axis (Y-axis). A scatter plot combines these values into a single data point and displays them at unequal intervals, or clusters. Scatter plots are typically used to present and compare numerical values, such as scientific, statistical, or engineering data. To display table data as a scatter plot, place the X-axis data in one row or column and the corresponding Y-axis data in adjacent rows or columns.

Area charts. Area charts illustrate the magnitude of change over time and can be used to draw attention to a cumulative value along a trend.

For example, data showing profits over time can be displayed in an area chart to bring attention to overall profits.

By displaying the sum of the values of the series, such a diagram clearly shows the contribution of each series.

Surface diagrams. A surface plot is used when you want to find optimal combinations in two sets of data. Like a topographic map, colors and shading highlight areas of equal ranges of values. Surface charts can be used to illustrate categories and sets of data that represent numerical values.

Donut charts. Like a pie chart, a donut chart shows the relationship of parts to the whole, but can contain more than one series

Creating charts in Excel. To create a basic chart in Excel that you can later edit and format, first enter the data for the chart in a worksheet. Then simply highlight this data and select desired type charts on the ribbon (Insert tab, Charts group).

Changing diagrams. Once you've created your chart, you can make changes to it. For example, you can change the appearance of the axes, add a chart title, move or hide the legend, and add additional chart elements.

The following options for changing the diagram are available:

Change the appearance of the chart axes. You can specify the scale of the axes and change the spacing between values or categories.
Adding a title and caption to the diagram. You can add a chart title, axis titles, and captions to help explain the data displayed in a chart.
Adding a legend and data table. You can show or hide the legend, or change its location. Some charts can also display a table of data and the values represented in the chart.

Effective chart format. In addition to using the built-in chart style, you can easily change the formatting of individual chart elements, such as data markers, chart area, plot area, numbers, and text in titles and captions, which will attract attention and make the chart stand out.

The following chart formatting options are available:

Filling diagram elements. To draw attention to certain chart elements, you can fill them with color, texture, pattern, or apply a gradient fill.
Change the outlines of chart elements. To make chart elements stand out, you can change their color, line style, or line thickness.
Adding special effects to chart elements. To give the diagram completeness, you can apply special effects to its elements, for example, shadow, reflection, glow, smoothing, relief, or volumetric rotation.
Formatting text and numbers. Text and numbers in chart titles, labels, and legends can be formatted in the same way as text and numbers on a worksheet. You can also use WordArt styles to highlight text or numbers.

Change the type of an existing chart. For most flat charts, you can change the chart type for the entire chart, giving it a completely different look, or you can choose a different chart type for any single data series, turning the chart into a blended chart.

IV. Physical education moment.

V. Consolidation of acquired knowledge.

To create diagrams on desktops, there are Excel application files with ready-made tasks (Appendix 4).

When completing task 1, students determine the selected range of cells, explain the type of diagram, and supplement the diagram with captions and headings. The finished diagram is formatted. By analyzing the chart data, students must answer the question: what explains the decline in sales in the 90s.

Task 2 is completed independently. Students create a table of argument values, enter formulas, and copy them to calculate function values. Based on the available data, a graph is created and formatted. Determine the argument values at which the function has negative values.

Task 3 is also completed by students independently. It is necessary to draw up a diagram reflecting the share of the cost of computer devices in its total cost. Will the price of a computer noticeably increase when purchasing more powerful processor circuits and RAM?

VI. Blitz survey.

What is a diagram?
When does it become necessary to explore or display data using charts?
When are embedded diagrams used?
When are charts created on separate sheets?
Where to start building a diagram?
What are categories and values?
What types of diagrams do you know?
What do you know about histogram?
When are bar charts used?
What do the graphs show?
If the data is located in one column or row, use...
List other types of diagrams you know.
How to create a chart using existing data?
What ways do you know how to format charts?

VII. Homework assignment.

Create a table of the dependence of average air temperature depending on the time of year (divided by quarters). Use data obtained in biology lessons. Analyze the forecast results using charts.

VIII. Grading.

While recording homework, assign grades based on test results, check cards, and evaluate computer work. Give a quality assessment of the work.

IX. Summing up the lesson.

Generalization of the material. Addressing knowledge gaps.

Laboratory work No. 10

Topic: Building graphs and diagrams.

Purpose of work: Learn to build graphs and diagrams.

Brief theoretical information.

Creating a Chart

Charts are a visual means of presenting data and make it easier to make comparisons and identify patterns and trends in the data. For example, instead of looking at several columns of numbers on a worksheet, you can look at a chart to see whether sales are falling or rising by quarter, or how actual sales compare to projected sales. You can create a chart on a separate sheet or place it as an embedded object on a data sheet. The diagram can also be published on a Web page. To create a chart, you first need to enter data for the chart into the worksheet. Then highlight the data you entered and use the Chart Wizard to step-by-step creation charts with a choice of chart type and various chart parameters. You can also create a chart in one step without using the Chart Wizard. When created this way, the chart uses standard type and formatting options that you can change later.

Select the cells containing the data that you want to display in the chart.

In order for the column or row heading for new data to appear in the chart, the cells you select must include those that contain that heading.

Click the button Chart Wizard.
Follow the instructions in the Chart Wizard.

Adviсe

You can quickly create a chart without using the Chart Wizard.
You can create a chart from a PivotTable report, called a PivotChart report, that allows you to display different views data.
You can create a chart from noncontiguous ranges of cells.
If there are multi-level row or column names on the sheet, then they can also be reflected in the diagram. When creating a chart, highlight the row and column titles for each level. To ensure that the specified hierarchy is maintained when you add data to a chart, change the range of cells used to create the chart.

Create a chart in one step

To create a chart sheet that uses a standard chart type, select the data you want and press F11.
To create an embedded chart that uses a standard chart type, select the data you want, and then click the button. If the button Create current chart type missing, add it to your toolbar.

Change the range of cells used to create a chart

Select the chart to edit.
Select a team Initial data in the menu Diagram and then select the tab Data range.
Make sure the entire link in the field is selected Data range.
In your worksheet, select the cells that contain the data you want to appear in the chart.

In order for the column or row name for new data to appear in the chart, the selected cells must include those that contain this name.

Representing data in a chart

The chart is linked to the data from which it is created and updates automatically when the data changes.

Embedded Charts. The embedded chart is treated as a graphic object and saved as part of the worksheet on which it was created. Embedded charts should be used when you want to display or print one or more charts along with worksheet data.

Chart Sheets. A chart sheet is a separate sheet in a workbook that has its own name. Chart sheets should be used when you want to view or edit large or complex charts separately from the data, or when you want to save screen space for working with a sheet.

Change the values displayed in a chart

The values displayed in the chart are associated with the sheet from which the chart was created. When the data in the worksheet changes, the chart updates automatically.

Open the worksheet that contains the data displayed in the chart.
Enter new data in the cells whose values you want to change.
Press ENTER.

Note. This procedure Not applicable to PivotCharts report. To change the values in a PivotCharts report, use different source data.

Deleting data series

To delete data from a sheet and a chart at the same time, just delete it from the sheet and the chart will be resized automatically. Use the following procedure to remove data series from a chart only, without removing the corresponding data from the worksheet.

Select the data series to delete.
Press the DELETE key.

Note. This procedure does not apply to PivotChart reports. To remove a series from a PivotChart report, you can use a different data source for the PivotChart report or hide the details for the series in the report.

Changing an embedded chart to a chart sheet and vice versa

Select the chart you want to change or move.
On the menu Diagram select item Location.
To place a chart on a new chart sheet, in the group Place a chart on a worksheet select option separate, and then provide a name for the new chart sheet.

To place the chart as an embedded object in the worksheet, select the option available, then select the sheet name in the field available, and then click the button OK. Move the embedded chart to the desired position on the worksheet.

©2015-2019 site
All rights belong to their authors. This site does not claim authorship, but provides free use.
Page creation date: 2016-04-27

Task 2. Create pie chart based on average testing results on a separate sheet.

Execution technology:

1. Select data ranges containing student names and test averages. In our case, these are non-adjacent ranges of cells B1:B11 and H1:H11. To select non-adjacent ranges, first select the first range, then press and hold the Ctrl button on your keyboard while selecting the second non-adjacent range.

2. Press the button Chart Wizard(or select from the menu Insert team Diagram).

3. In the dialog box Chart Wizards select chart type Circular. Leave the chart view selected as default. Click Next.

4. In the second dialog window Chart Wizards Make sure you select the correct range for the chart. Click Next.

5. In the third dialog window, select the tab Data Signatures and check the box next to the field Values. Selecting a tab Legend, add a legend at the bottom of the chart.

6. In the last window Chart Wizards set the radio button to the field Place the chart on a separate sheet. Click Ready.

Setting up chart elements

Chart element is a component of a chart, such as an axis, data point, title, or legend, each of which can be selected and formatted.

Task 3. Make changes to the pie chart created on a separate sheet.

Execution technology:

1. Activate the Excel sheet where you created the pie chart.

2. Add a title to the chart (or change it) " Average testing scores in the group ", selecting the menu command Diagram – Chart options– tab Headings.

3. Format the entered name by calling the context menu by right-clicking on the diagram name and selecting Chart title format. Place a border around the title of the chart, and select the color, line type, and line thickness for this border as you wish. Make the settings for the font used in the title as you wish.

4. Click within the chart itself to make it active. Change the chart type by right-clicking on the chart area from the context menu and selecting Chart type or by executing in the menu Diagram team Chart type. In the window Chart type select Volumetric version of a cut pie chart. Click OK.

5. Running the command Chart options from the menu Diagram, in the tab Legend uncheck the box Add legend, and in the tab Data Signatures enable the checkbox next to the field Category names. Click OK.

6. Rotate the diagram 90 0 . To do this, right-click on the diagram area, thereby calling up the context menu. Select menu item Data series format and in the window that opens, tab Options. Set the rotation angle to 90 0 . Click OK.

7. Click in the diagram area so that one of the diagram parts is highlighted. By calling the context menu, select Data point format or select a team Selected data element from the menu Format.

8. In the tab View dialog boxes Data Item Format change the fill color of the chart share. For any other portion of the chart, select a fill texture or pattern V additional window Filling methods.

Task 4. Create a donut chart yourself based on the test results for one student from the group on a separate sheet. Customize it as you wish.

A quick way to create charts

In order to quickly create a chart, you need to select the source data (in our case, this is the range B1:H11) and press the key F11. The diagram will appear on a separate sheet in the entire sheet.

Task 5. Create a chart Test results using quick way creating diagrams.

Building a graph

Graphs are typically used to show changes in a series of values.

Task 6. Construct a graph reflecting the dynamics of the test results of the first three students in the group.

Execution technology:

1. Select an area to plot the chart without including testing averages. (In our case this is the range B1:G4).

2. Press the button Chart Wizard(or select from the menu Insert team Diagram).

3. In the Diagram Wizard dialog box, select the chart type - Schedule and the first of the proposed options for the type of graphs. Click Next.

4. In the second dialog box, make sure that the data range for the graph is selected correctly. Click Next.

5. In the third dialog window in the tab Headings give the diagram a title " Test results chart " Label the axes: X axis – test number; Y axis – test results in % .

6. In the tab Grid lines add intermediate grid lines for the X-axis and Y-axis.

7. Add a legend to the right of the chart.

8. Add a data table to the graph. (Tab Data table). Click Next.

9. In the last dialog window, turn on the switch in the window Place the diagram on an existing sheet. Click Ready.

10. By changing the size of the graph, achieve the best display of all the data shown on it.

11. Add to the resulting graph value labels for best and worst test results. To do this:

§ Select the point of the best (worst) result on the graph so that only this point is highlighted.

§ Select a team Data point format by calling the context menu.

§ Turn on the checkbox next to the field Values in the tab Data Signatures.

§ Click OK.

Task 7. Independently construct a graph reflecting the test results of the next three students from the group, using the form Volumetric version of the chart.

Speaker Deck SlideShare

Basics of working with diagrams. How to choose the right chart for your data. Quick formatting charts and layout changes. Fine-tuning the diagram. Using Tools quick analysis.

Microsoft Office Specialist Exam Skills (77-420):

Theoretical part:

Basics of working with charts (graphs)

Video version

Text version

Let's return a little to the rules for placing information on Excel sheets; we already know that a sheet Excel workbooks consists of cells containing three types of data: text, formulas or numbers, but Excel also provides a so-called hidden layer on which charts, images and everything that can move freely above the surface of the sheet are placed. Diagrams are also called graphs.

Excel has simply the richest capabilities for constructing charts of various types. It is almost impossible to learn all the tricks, as world gurus admit. Excel programs, such as John Walkenbach. The reason is not so much the wealth of settings for Excel charts, but the inexhaustible possibilities of their use. In fact, this is a constructor that can be used to implement spectacular charts that are not available in the standard Excel set. We will look at some of these implementations; perhaps they will encourage you to create your own masterpieces of sample visualization.

Working with charts

The “Insert” tab of the “Diagrams” and “Sparklines” group is responsible for working with diagrams (they are also called infolines, mini-diagrams placed in one cell).

Commands for inserting charts

Working with charts is no different from working with other functionality in Excel: you select the data that should be visualized and click on the command of the selected chart, specifying the specific subtype of the chart, for example, if it should be a graph, then what it should be: simple, voluminous, with markers, etc.

You can bring up the Insert Chart dialog box by clicking the call triangle in the lower right corner of the Charts group of the Insert tab and select a specific chart type and subtype from there.

After inserting a chart into an Excel sheet, two tabs become available to the user for fine-tuning and data management: “Design” and “Format”.

Using the Design tab, the user can change the chart type, select or edit data, add or remove specific elements, and select a design style or layout.

The “Design” tab will become available after selecting the diagram; commands responsible for the diagram layout are concentrated here

If using the “Design” tab the user can apply a design style to the entire diagram or change the color scheme, then the “Format” tab contains commands responsible for formatting the elements of the diagram, for example, you can change the color of only one column. This tab is also responsible for the dimensions of the entire diagram.

The “Format” tab will become available after selecting the diagram; commands for formatting the diagram and its individual elements are concentrated here

Microsoft has simplified the process of adding new data to visualize it on a chart; the user just needs to place the cursor in the cell of the range with the data and select the desired chart type. Excel will try to determine the boundaries of the range and display the result as a chart on the screen.

However, this is not best way, for simple ranges, of course, is suitable, but it is better to initially select the range or ranges with data, and then specify the desired type of chart, so you can be sure that only the necessary data will be visualized in the chart.

To construct a chart, you can use several ranges; they can be either adjacent or located at a distance from each other; in this case, you should hold down the Ctrl key when selecting ranges.

The data for the chart can be on the sheet with the chart, another sheet, or a separate workbook

It will not be possible to immediately select several of these “scattered” ranges; they will need to be added after creating the diagram itself.

Adding data to a chart after it has been built can be identified as a third option for creating charts.

Data is added to the chart using the “Select Data” command from the “Data” group of the “Design” tab or the chart context menu.

Not the best way to create, it’s better to start from the completed data.

In fact, the distinction between construction methods is arbitrary, because you can select data, build a chart, and then add additional ranges to it.

There is a fourth way to create a chart - using the quick analysis tool, we will look at it later in this lesson.

What to highlight?

Charts are built using numerical data, which can be either constants (entered directly into a cell) or the result of formula calculations. However, there is also text information, which is used for titles, axis labels, or legends. Moreover, by selecting data for a chart, you can immediately capture the text labels of the ranges.

Excel is good at identifying data and signatures for it

It is impossible not to notice that Excel not only correctly selected the type of chart (combined with an additional axis), but also correctly combined the names of several cells. All that remains is to add the names and labels of the axes (if necessary). Even a well-versed user would have to spend time constructing such a diagram.

Note

If the chart is highlighted and click fast printing, then only the diagram will be sent for printing.

Moving a chart and resizing a chart

As mentioned earlier, charts in Excel, along with some other elements, are placed on a hidden layer of the sheet; they are not attached to cells, and accordingly, they can be freely moved by simply dragging the mouse.

When selecting a diagram, you need to be extremely careful and click on an empty area inside the diagram, or on its edge, because Clicking on an element inside the diagram, for example, an axis label or title, will lead to its selection and moving operations will affect this element.

Hot combination

Moving a chart while holding down a key Ctrl will cause it to be copied.

If you move the mouse cursor to the border of the diagram and drag, the size of the diagram will change, and the internal elements will increase/decrease in proportion to the change in size. This size adjustment is rough; if you need to precisely set the width and height, this is done in the “Size” group of the “Format” tab with the diagram selected.

If you select several charts and set the size, they will all become the same size

By default, the diagram is added to the same sheet from which the insertion command was executed; such diagrams that occupy an entire sheet are called embedded. In Excel, you can place a chart on a separate sheet, this can be done in the following ways:

Build an embedded diagram and transfer it to a separate sheet. The “Move Chart” dialog box is called either through the context menu on the chart itself, or from the “Design” tab, the “Move Chart” command.
You can build a diagram on a separate sheet right away, just select the source data and click function key"F11".

The described operations also work in the opposite direction.

One of the features new to Excel 2013 is called "Recommended" charts. Having previously selected the data, you should run the “Recommended Charts” command from the “Charts” group of the “Insert” tab.

The familiar Insert New Chart window will appear, opened in the Recommended Charts tab. Excel will analyze the selected range and offer several chart options that in the best possible way interpret the data. If none of the options suits you, you can use the “All diagrams” tab, or select the closest option, and then fine-tune it.

In considering the recommended charts, it would not be amiss to mention the “Row/Column” command, which in one click will change the data along the axes, for example, when analyzing revenue for six products of a fictitious company, Excel incorrectly defined the axes.

Quickly changing a column/row in a chart is useful if Excel has incorrectly identified a data series

Sometimes, swapping rows and columns is useful from the point of view of the analysis being carried out. So, in the first case, the profitability of products by month is shown, while in the second comparative analysis profitability of various products by month. In other words, from the first graph we can conclude that March turned out to be the most profitable for all products, and from the second, that the fifth and second products are the leaders in profitability.

Choice the right type charts for data visualization

Video version

Text version

Selecting data to represent in a chart

Diagrams is a graphical representation of numerical data. Perception graphic information easier for a person. However, an important role also plays right choice data, and the type of chart that will represent the data.

For example, in Figure 3 there are options for presenting sales data for the past year.

The diagram of the first option, due to significant differences in the absolute values of the categories, is not indicative from the point of view of analysis. Despite significant changes in the percentage of sales in different periods, it is very difficult to notice this on the chart and the whole meaning of such a presentation is lost.

The second option uses two diagrams, here you can see the difference in sales, but, firstly, in this specific case you can get by with one graph, and, secondly, to display the trend would be better suited a graph, not a histogram.

The third option is presented as a combined (mixed) diagram, which allows not only to visually evaluate the absolute indicators, but also perfectly shows the trend, well, or the lack thereof.

Diagrams are usually created to convey a specific message. The message itself is shown in the title, and the diagram already provides clarity of the statement. To ensure that the presenter is understood correctly, it is very important to choose the right type of chart that will best represent the data. In this lesson question we will look at when to use which type of chart.

Universally recognized guru table processor Excel - John Walkenbach notes that in the vast majority of cases, the message that needs to be conveyed through a chart is a comparison and identifies these types of comparisons:

Comparing multiple elements. For example, sales by region of the company
Comparison of data over time. For example, monthly sales volumes and the general development trend of the company
Relative comparison. In other words, allocating a share as a whole. Here the best option there will be a pie chart
Comparison of data ratios. A scatter plot can do a good job of showing the difference between income and expenses
Comparison by frequencies. A histogram can be used to display the number of students whose performance falls within a certain range
Definition of non-standard indicators. If there are many experiments, a chart can be used to visually identify “anomalies,” or values that differ significantly from the rest.

How to choose the right chart type in Excel

It is impossible to answer this question unambiguously; it all depends on what kind of message the user wants to convey using the diagram. For example, if a company has 6 products that it sells and has data on sales revenue for the past period, then there are two chart options that can be used:

Histogram – if you need to visually compare income from different products
Pie chart - if you need to determine the share of each product in the company's total revenue.

It’s interesting that if you use the recommended charts, Excel itself will offer both the first and second types. Here, however, there are several more options that are absolutely wrong to use, for example, a chart, a funnel, or an area with accumulations.

Sometimes you can build several options and visually determine the best one. Here, let’s look at the diagrams that Excel offers the user and typical scenarios for their use.

Histograms

If not the most, then one of the most common types of diagrams. The histogram represents each point as vertical column, whose height corresponds to the value. Histograms are used to compare discrete sets of data.

In Excel 2016 there are 7 various types histograms: with grouping, with accumulation, normalized with accumulation, the same 3 types in a volumetric version and just a volumetric histogram. You should be careful with 3D diagrams; at first glance they may seem attractive, but in most cases, they are inferior in information content to their two-dimensional counterparts.

Bar charts

If you rotate the histogram 90 degrees, you get a bar chart. The scope of these charts is similar to histograms and, in general, histograms are perceived better, but if the category labels are long enough, then they will look more harmonious on a bar chart.

Excel has incorrectly determined the chart title, it needs to be replaced

In Excel, there are 6 types of bar charts, all types are similar to histograms, but there is no three-dimensional bar chart, since there is no subtype that would allow placing multiple data series on the third axis.

Schedule

An extremely common type of chart, used to display continuous data or trends.

Exchange rates, company income or losses for a certain period of time, website traffic and many other indicators are best depicted in a graph.

In Excel, you can build 7 different subtypes of graphs, including three-dimensional ones.

Pie charts are used to show proportions relative to a whole. All values taken for the pie chart must be positive; negative values will be converted by Excel automatically. Pie charts are convenient to use when you need to depict: a company’s market share, the percentage of successful/failing students, a niche specific program among competitors, etc.

Pie charts are used to show part of a whole. One of the rare cases when a three-dimensional version looks both beautiful and informative.

There is no need to specify values as percentages and ensure that their sum equals 100; Excel will automatically sum the values and distribute the shares. A pie chart is built from one series of data and is one of those rare cases when a three-dimensional chart can look both harmonious and informative. In total, there are 5 types of pie charts, two of which allow for secondary data decoding.

Excel itself will determine what data to put on the secondary chart and, as a rule, will do it incorrectly. Fortunately, correcting the situation is quite simple: in the parameters of the secondary chart series, you can configure not only how many values to include in the secondary chart decoding, but also define some other parameters, for example, size.

A separate subtype of a pie chart is a donut chart—a pie chart for multiple data series. In this way, market share can be depicted over different time periods. But a donut chart can also be suitable for minor data decoding.

If you need to create a pie chart for several data series, then you need to select the donut chart

Scatter plots

Another common type of chart is scatter or scatter plots. The peculiarity of this type of chart is that they do not use a category axis and values are plotted along the X-axis and Y-axis. This type of chart is often used in statistical studies to initially determine the presence/absence of a relationship between two variables (number of applications and sales, student attendance and performance, athlete’s height and 100 m running speed, duration of work in the company and salary, etc. )

Scatter plots are popular in statistics

Excel provides 7 types of scatter charts and two of them are bubble charts, where the size of the bubble depends on the point values.

Bubble charts are not the best frequent guest in general reports, in fact, a bubble chart is a scatter chart with an additional series of data.

An interesting example of using a bubble chart was demonstrated by John Walkenbach, who “drew” the face of a mouse, let’s reproduce this example and us.

After entering the data and plotting the chart, you need to set the scale and set the “Values correspond to” switch to “bubble diameter”

In order to achieve this effect, I had to change the bubble size parameter to “diameter”, set the scale to 290 (default 100) and color the values, because By default, all values have the same color.

Area Charts

This type of chart is quite rare and consists of graphs with shaded areas. If you try to display several rows of data, then it is possible that another row will not be visible under one row, in this case the best way out will use a simple graph, or try to order the data series so that the series with smaller values is in the foreground.

When constructing an area chart, you need to be careful not to hide another behind one row.

If the graphs intersect, i.e. Overlapping cannot be avoided; you can make a transparent fill for the data row that is in the foreground.

As the name suggests, they were created to display prices on stock exchanges, however, they can be used in other cases, for example, using a stock chart you can visualize a graph of temperature changes for February 2016 in Kyiv.

A stock chart can be used not only for its intended purpose

Calculated with a dot in the center average temperature, and the line represents the daily spread. As you can see, February turned out to be surprisingly warm.

A fairly rare type of diagram that depicts a three-dimensional surface. Distinctive feature This type of chart is that color is used to highlight values rather than data series.

The peculiarity of a surface chart is that values are highlighted in color, not data series.

The number of colors on the surface diagram depends on the price of the main divisions along the value axis: one color - one value.

A surface chart in Excel is not fully three-dimensional and cannot be plotted with data points represented in an x, y, z coordinate system unless "x" and "y" are equal.

Radar charts

A radar chart is an analogue of a graph in the polar coordinate system. A radar chart has a separate axis for each category, the axes originate from the center, and the value is marked on the corresponding axis.

You can display multiple category axes in a radar chart

As an example of using a radar chart, you can analyze the weekly website traffic for a certain period, as you can see on weekends the number of visitors is significantly less.

If several data series overlap each other, similar to an area chart, you can either make the fill transparent, or you can choose the radar chart option without fill, then you will simply get a graph in the polar coordinate system.

Combined (mixed) chart

A combination or mixed chart is a chart that combines two types of charts. A striking example of the successful use of this type of chart is the very first figure in this question, when the bar chart shows the company's sales income, and the graph on the same chart shows the percentage change compared to the previous year.

In our Excel example correctly analyzed the source data and proposed this type diagrams in the recommended ones. If you haven’t received such an offer from Excel, then making a combination chart yourself is as easy as shelling pears:

Naturally, it is not possible to combine all types of diagrams; the choice is limited various types histograms and graphs.

If you are using Excel version older than 2013, the process for constructing a combination chart may differ.

New chart types in Excel 2016

All new diagrams will be of interest to a narrow circle of specialists, for example, funnel-shaped Microsoft chart recommends using to display the number of potential buyers per various stages sales

Funnel chart shows the number of potential buyers at different stages of sales

Let everyone decide for themselves how clear this visualization is.

Chart templates

Sometimes a lot of time is spent building and fine-tuning a diagram. If you need to build several diagrams of the same type and time-consuming, you can simply copy the source and fill it with new data each time.

Although this method has its right to exist, for these purposes it is better to save the original diagram as a template, and then use it just like any other type of diagram.

To save a diagram as a template:

Create a chart.
Format the diagram and make settings.
Select the diagram / call the context menu / select the command “Save as template...”
Give the template a name.

Excel chart templates have a *.crtx extension. All templates created by the user can be found in the Templates group; accordingly, if you need to change the type of an already created diagram, you should look for a previously saved template there.

Quickly format charts with styles and layout

Video version

Text version

After creating a diagram, you can change its parameters beyond recognition, detailed settings Diagrams will be covered in the next question, but for now we will apply pre-made design styles and layouts to the diagram. Using these tools, you can quickly select the appropriate appearance for your chart.

All the necessary tools are located on the additional “Design” tab; we will need commands from the “Chart Layout” and “Chart Styles” groups.

Styles determine the appearance of a chart, and layout determines the presence and placement of chart elements.

Chart styles are a pre-prepared set of visual parameters for a chart.

Predefined chart styles are responsible for external design elements of diagrams, such as fonts and colors, you can also experiment with the “Change Colors” command; it will allow you to select a set of colors in addition to the style.

Design styles, as well as color sets, depend on the theme of the Excel workbook itself.

Chart styles do not add or remove chart elements themselves; this is done by the Express Layout drop-down command.

If you have selected a more or less acceptable layout, but a certain element is missing or, conversely, is superfluous, then the drop-down command “Add chart element” will simply provide an abundance of opportunities for adding and arranging chart elements.

To apply a certain style or set of color palette, just as to add/remove certain elements of a diagram, it is not at all necessary to go to the “Design” tab. If you select a chart, three pop-up buttons appear nearby: the top one is responsible for deleting or adding chart elements, the middle one is responsible for changing the style or color palette.

Deleting any element of the diagram is also possible by pressing the Delete key, with preliminary selection of the necessary (or not necessary, as you prefer) element.

The third pop-up control button will allow you to quickly perform certain manipulations with data. Here you can hide one or more data series without having to rebuild the chart itself.

Using styles and layouts will allow you to quickly change the appearance of your chart, but if you need more fine tuning You should use commands for manual formatting of chart elements.

Formatting charts in manual mode

Video version

Text version

Having a certain understanding of charts and their construction methods, let's take a closer look at the main elements of the chart: data series (the main element of the chart), axes (main on the left, auxiliary on the right), axis names, chart title, data labels, data table (duplicates the table from the sheet ), grid, legend (data series labels).

It is also customary to distinguish between the chart area and the plot area.

Chart area- this is all the internal space limited by the boundaries of the diagram.

Construction area- this is a space limited by axes; the area where the diagram is drawn can be moved within the area of the diagram itself.

The user can select an element by left-clicking on the element in the diagram and edit it, for example, enter a new value. If it is difficult to select an element with the mouse, a drop-down command is provided to select it in the “Current fragment” group on the “Format” tab.

Up to this point, we accepted the components of the diagram in the form in which they were presented in one or another design style; now we will format the elements manually.

It is worth noting that the number of chart elements depends on the type of the chart itself; you can delete or change the location of existing ones, as well as add missing elements, using the “Add Chart Elements” command in the “Chart Layout” group of the “Design” tab.

Formatting text labels

Text labels are axis labels, chart titles, series labels, etc. To format, you must first select the desired element, and then use the commands of the “Font” and “Alignment” groups. If necessary, you can open the Font dialog box and configure even more text label design options. Formatting text labels is no different from formatting regular text in Excel.

Text labels on a diagram are formatted using the “Font” and “Alignment” group commands

If you need to change the chart title or axis labels, users typically click on the element and begin changing the title right in the chart. You can do this, but you can also click on the text label (for example, the title of the chart), put the “=” sign and click on the cell where the intended title is stored. This method is good because the name in the cell can be changed using a formula and it will automatically be reflected on the diagram itself.

Formatting data series (data series)

The “Format” tab from the “Working with Charts” group of additional tabs is responsible for formatting data series. Before you start formatting, you need to learn how to correctly select a specific element of a row (column, point, sector, line, etc.)

So, the first click of the left mouse button on any element of the series will select the entire series, you can also select the entire series using the drop-down list from the “Chart Elements” group, and the second click on a specific element will lead to the selection of only this element, accordingly all formatting will apply only to him.

Note

The first click on a data series will select the entire series, and the second will select a specific element of the series. It can be formatted individually.

After selecting an element, subsequent formatting is performed using the tools of the “Shape Styles” group of the “Format” tab. Here you can select one of the preset styles for shapes, or select individual settings: line thickness, fill and its color, shadows, effects, etc. More in-depth settings are made using the shape formatting dialog box, which is called up by clicking on the triangle in the lower right corner of the group. By the way, the name of this window, as well as the list of editing commands available in it, depend on the element that is in at the moment highlighted.

Among the interesting parameters that are configured in this dialog box is the transparency of the fill color, which has not only an aesthetic function, but also a practical one. Transparency will be useful if some rows overlap.

Formatting a legend

Legend on Excel chart is the signature of a series of data. Like any other chart element, the legend can be removed, placed in different areas of the chart, or formatted.

Fine tuning appearance legends are created in the already familiar legend formatting dialog box; here, for example, you can change the color of the signature itself to the style of the data series on the chart, add mirror image or other effects.

How to properly move elements within a chart area

Moving chart elements within the chart area can be done in two ways: simply drag with the mouse and select a location with the “Add Chart Element” command. What is the best way to choose element placement?

If you need to make a small movement, for example, move the legend to the right and not in the center, then this is done with the mouse, but if you need to make drastic rearrangements, for example, place it not at the bottom of the construction area, but at the top under the title or on the side, then it is better to do it using the “Add diagram element” command, because in this case, all other elements will quickly adjust to the new chart layout. If you do such manipulations with the mouse, you will have to tinker a lot (change the size and position of other elements manually) and it is not a fact that the result will suit you.

ROW function

To conclude the review this issue noteworthy feature ROW, it appears in the formula bar when you select a specific series of data.

The ROW function is an inferior function; it cannot be used in worksheet cells and other functions cannot be used as arguments, however, you can edit the arguments of the function itself. The practical significance of such an event is questionable, because it’s easier to do everything through the appropriate teams, but for general development It will be useful to read.
The syntax of the ROW function is as follows:

ROW(row_name, category_labels, data_range, row_number, dimensions)

series_name – the optional argument contains a reference to the cell in which the series name used in the legend is written. In our case, this is cell B1;
category_labels – an optional argument that contains a link to the range of cells where labels for the category axis are written. In our case, this is the range A2:A32;
data_range – a required argument, contains a reference to the range of cells with data for the series. You can use non-adjacent ranges, in which case they will need to be separated by commas and enclosed in parentheses;
row_number – a required argument can only be a constant (a number directly in the formula). This argument makes sense only if there are several data series on the chart and shows the order of drawing; if the orange area is assigned the first number, it will be hidden behind the blue area;
sizes – used only for bubble charts, contains a link to a range with bubble sizes.

As you can see in the ROW function, references to ranges and cells are entered as absolute and with the obligatory indication of the sheet name. This may be a little confusing at first glance, but if you look closely, it's standard absolute references with a leaf reference. Here, by the way, there can be named ranges, but then you must specify the name of the book.

Several tools for quick analysis of the selected range are concentrated here.

Components of the Quick Analysis Tools command

You can apply tools to the selected range conditional formatting, build a chart, this is the fourth way to create a chart, which we mentioned earlier, calculate various total values (sum, average, etc.), convert the range into a table (we will consider tables further) and build sparklines (infolines, mini -diagrams) are small diagrams that fit into one cell.

When are sparklines useful?

If there is a table with many data series, then using a standard chart would lead to the construction of many graphs and the spectacle would not be the most visual; using sparklines will show the graph for each row of the range separately.

The situation is similar for the other two types of sparklines: “Histogram” and “Win/Loss”.

Adding sparklines to a worksheet

The first and probably the fastest way to create sparklines is using the quick analysis tool, but you can also create sparklines for the selected range using the “Insert” tab, “Sparklines” group. In this case, you will have to additionally specify the range for displaying infolines; in the case of the quick analysis tool, sparklines are added to the right of the selected range. The range must match the number of rows.

At the beginning of this lesson, we talked about the invisible layer where charts and text blocks are placed. Sparklines are actually charts as one of the types of data in cells, along with text, numbers and formulas. That is why when constructing sparklines, standard rules for filling cells apply, namely, auto-filling. You can build a sparkline for one row, and then use autofill to fill the remaining cells of the range.

Let's look at sparklines as a quick analysis tool in more detail.

After adding infolines to the worksheet, a special “Designer” tab appears for working with mini-charts.

Despite the wealth of settings on the tab, there are not so many adjustable parameters; they mainly relate to appearance.

The “Change data” command allows, in addition to data settings for all or a single sparkline, to configure the display of empty and hidden cells. These settings are similar to those for charts, by default empty values are ignored and the line is not broken, however, you can make it so that there are breaks in places with empty cells or they are treated as zero values.

In the “Show” group you can enable the display of markers for individual or all points. It is convenient, for example, to look at the maximum and minimum.

The “Style” group is entirely devoted to changing the appearance of sparklines; you can separately customize the marker color.

Using the last group, you can configure it so that the sparklines are perceived as a single whole and formatted accordingly, or you can ungroup them, then you can configure each sparkline individually.

The Axis command is the most important in terms of data presentation.

The Axis command allows you to configure various parameters

By default, each series has its own mini-chart, with its own maximum and minimum values, which is convenient for general dynamics, but not for comparing series. The example picture shows the same data, only in the bottom example the axis scale is set to the same for all sparklines. Due to the difference in values, it is impossible to visually trace the dynamics in rows 2-10.

By default, individual maximum and minimum values are set for each individual sparkline. This will allow you to track the dynamics regardless of absolute indicators

Other quick analysis tools

To wrap up our look at the Quick Analysis tool, let's format the selected range using conditional formatting, build a graph, add some totals, and add histogram-type sparklines.

The quick analysis tool placed the results under the data, with sparklines to the left of the data.

Quick repetition of material:

So-called memory cards, look at the card and try to answer, clicking on the card will display the correct answer. Memory cards are good for remembering key positions classes. All lessons in this course are equipped with memory cards.

Practical tasks:

In the practical part you will find tasks for the last lesson. After completing them, you have the opportunity to compare your version with the answer prepared by the lecturer. It is strongly recommended that you view the solution only after you have completed the task yourself. For some tasks there are small hints

View solution

We looked at ways to build embedded diagrams using Chart Wizards. In reports, you may want to place data and charts on the same worksheet. But what if you need several separate sheets (or slides printed from them) with diagrams for your presentation? In this case, it is better to build diagrams on separate sheets. In the next exercise, you will create a pie chart on a separate sheet.

Go to sheet 2008 1st quarter and select a range of cells B15: C18 .

On the menu Insert select team Diagram. A dialog box will open Chart Wizard (Step 1 of 4): Chart Type, where you need to select the chart type.

Select a tab Standard. In a group Type highlight Circular, in the group View select the first chart in the top corner. Click the button Next>. A dialog box will open Chart Wizard (step 2 of 4):.

Click on the tab shortcut Data range. Make sure the group Rows in option enabled columns.

Click on the tab shortcut Legend and uncheck Add legend. Then click on the tab shortcut Data Signatures and in the group Value labels enable options category And share. Uncheck Leader lines.

In a group Place a chart on a worksheet enable the option separate. The corresponding field will indicate the name of the sheet Diagram1. Click the button Ready. To the left of the sheet 2008 1st quarter the chart sheet will be inserted Diagram1.

If the labels on the chart overlap, you can drag them to a new location using the mouse. To do this, place the mouse pointer on the label, click left button mouse and, without releasing it, move the mark frame to a new location.

Changing chart data

If you decide that your chart is oversaturated with information, or, conversely, contains too little data, or contains incorrect values, you can add, remove or correct this data. You can also delete, add, or rearrange data series in a chart.

Before making changes to the embedded diagram, you need to select it by double-clicking on it with the left mouse button. A frame will surround the selected chart, and some chart-specific commands and menus will appear in the menu bar. The chart sheet automatically becomes the current sheet.

To delete a row of data, you need to select this row and press the key Delete. To deposit new row data into the embedded chart, you need to select the data and drag it onto the chart.

For charts on separate sheets, in this case the command is used Add data from the menu Diagram. To change the block of data used in the diagram, you need to select from the menu Diagram team Initial data, click on the tab shortcut Data range, select with mouse new block data and click on the button OK.

Adding and removing data series in a chart is done on the tab Row menu Initial data. You can change the order in which data series are displayed using context-sensitive menu commands. To do this, right-click in the diagram area on any of the labels and select the command Data series format V context menu. In the window that appears Data series format you need to use the tab options Row order.

In the following exercises, you will remove one data series from a chart and add another in its place, and then change the order in which the data series are displayed.