How to change the legend of a chart in Excel. Add Legend to Right with Overlap to add a legend to the right of the chart with the plot area overlapping. Excel spreadsheet editor
Lesson 7.1
Excel spreadsheet editor.
Diagrams
Benefits of diagrams. No matter how you design the table, its data will be less readable than their graphical representation in charts. For an example, look at the figure:
According to the table, you will not immediately notice in which month the company’s income was the greatest and in which the least. You can, of course, use the “Sorting” tool, but then the general idea of the seasonality of the company’s activities is lost. Here, the general presentation of the graph allows you to trace the seasonality of sales activity, which brings more or less profit during certain periods of the year. Data recorded in a table is perfect for detailed calculations and calculations. But diagrams have the following advantages:
· improve data readability;
· simplify general orientation through large volumes of data;
· allow you to create high-quality presentations of reports.
BUILDING DIAGRAMS IN EXCEL
It is better to use diagrams to present the company's achievements. Charts are good for analyzing relative data. For example, forecasting the dynamics of sales growth or assessing the general growth trend of the enterprise's potential.
To create a chart, follow these steps:
1. Fill the range of cells A1:C4 with values as shown in the figure:
2. Select the range A1:C4 and select a tool on the “Insert” - “Histogram” - “Histogram with Grouping” tab.
3. Click on the histogram to activate it and open the additional “Work with Charts” menu. Three tool tabs are also available there: “Designer”, “Layout”, “Format”.
4. To change the axes in the chart, select the “Design” tab, and on it the “Row/Column” switch tool. So you change the values: rows to columns.
5. Click on any cell to deselect the chart and thus deactivate its configuration mode. Now you can work as usual.
Now let's build a pie chart that needs to be labeled with a title:
1. Select the range A1:B4 in the source table.
2. Select "Insert" - "Circular". From the group different types diagrams, select “Cut Circular”.
3. Label the title of your diagram. To do this, double-click on the title with the left mouse button and enter the text as shown in the figure:
After signing the new title, click on any cell to deactivate the chart settings and return to normal operation.
CHANGING THE DIAGRAM
It is not always possible to immediately create a chart in Excel that meets all the user’s requirements.
It is initially difficult to determine which type of chart is best to present data in: a volumetric chart, a stacked bar chart, or a chart with markers.
Sometimes the legend is more of a hindrance than a help in presenting the data and it is better to turn it off. And sometimes you need to connect a table with data to a diagram to prepare a presentation in other programs (for example, PowerPoint). Therefore, it is worth learning how to use chart settings in Excel.
Create a table with data as shown below. Select the table with the data and select the “Insert” - “Histogram” - “Histogram with Grouping” tool.
This diagram needs to be edited as follows:
· remove legend;
· add a table;
· change the chart type.
Remove legend from chart
1. Left-click on the chart to activate it (select it) and select the tool: “Working with Charts” - “Layout” - “Legend”.
2. From the drop-down list of options for the Legend tool, select the option: “None (Do not add legend).” And the legend will be removed from the chart.
There are 3 ways to do this:
1. Define string names directly
Right-click on the chart and click Select Data, then edit the series names directly as shown below.
You can specify the values directly, e.g. Series 1 or specify a range, for example. =A2
2. Create a chart that defines the top series and axis labels
Just select your data range (in the same format as I did) and create a simple histogram. Labels should be detected automatically. 
3. Define Legend (Series Names) Using VBA
Likewise, you can define series names dynamically using VBA. Simple example below:
ActiveChart.ChartArea.Select ActiveChart.FullSeriesCollection(1).Name = "=""Hello"""
This will override the title first. Just change the index from (1) to eg. (2) etc. to change the names of the following episodes. What does VBA do above? It sets the series name to Hello , since "=""Hello""" translates to ="Hello" (" must be escaped by the previous ").
To begin with, this is the task. Colleagues today asked us to increase the marker size in the pie chart legend without changing the font. Like that:
This is a pie chart generated automatically. The only thing I changed was the position of the legend (I moved it to the right side of the diagram).
As you can see, the markers in the legend are very small. It’s very easy to enlarge them - select the legend and increase the font: 
As a result, the markers grew larger, but the inscriptions also became obscenely large. Is it possible to enlarge just the markers?
To be honest, I don't know the right answer to this question. If someday some Excel guru reads this entry, please answer what official science says about this. I didn’t find the right solution “head-on”, but I found a workaround. It turns out that if you select not the entire legend, but a separate element of it, changing the font will look slightly different - for the selected element both the marker and the inscription will increase, and for the rest only the marker. 
To take advantage of this fact, we need an additional, “extra” legend element. Let's create it. To do this, stretch the chart data range down one line. The added line will not contain any data or title, but in the legend it will be assigned its own color (yellow in my case): 
Let's change this color to transparent (to do this, select the legend element, go into its properties and in the "fill" section mark "no fill") 
The element has now become invisible. Without leaving the element's properties, let's increase its font: 
As you can see, the markers are enlarged, but the font of the inscriptions is not. We got a standard pie chart with a standard legend and large, convenient markers, the color of which will not be lost when printing, resizing, etc.
A legend is a set of text labels and sample graphical representations of data that help the reader associate the colors and types of images in a chart with the data they represent. The legend text is determined by the names of the data series plotted on the chart. You can rename a legend item by changing the text in the data series. If you are uncomfortable with legend placement when using a particular chart type, move the legend to the right or left, place it at the top or bottom of the chart, or place it overlapping on the left or right side of the chart. Data labels display the meaning of the data directly on the chart - for the reader's convenience, whereas in a data table, the data values are displayed in the chart's associated table next to the chart. If the suggested options do not suit you, you can set your own options in the Format dialog box
To change the chart legend:
Select the chart you want to convert.
Click the Legend button and select one of the following options:
No, to hide the legend;
Add Legend Right to add a legend to the right of the plotting area and align it to the right;
Add Legend on Top to add a legend on top of the plot area and align it to the top edge;
Add Legend Left to add a legend to the left of the plot area and align it to the left;
Add Legend Below to add a legend below the plotting area and align it to the bottom edge;
Add Legend to Right with Overlap to add a legend to the right of the chart with the plot area overlapping;
Add Legend Left with Overlap to add a legend to the left of the chart;
Additional legend options to set Extra options legends.
To change data labels on a chart:
Click the Layout tab in the Chart Tools group.
Click the Data Signatures button, and then select one of the following options:
No. to hide data signatures:
Show to show data labels for the selected range;
Additional data signature options to set user data signature options.
The available options may vary depending on the type of chart you select.
To show or hide a data table in a chart:
Select the chart you want to edit.
Click the Layout tab in the Chart Tools group.
Click the Data Table button, and then select one of the following options:
No, to hide the data table;
Show Data Table to show the data table below the chart;
Show data table with legend keys to show the data table below the chart with legend keys;
Additional data table options.
It is not always possible to immediately create a graph and chart in Excel that meets all user requirements.
Initially, it is difficult to determine which type of graphs and charts is best to present data in: a volumetric chart, a stacked bar chart, or a chart with markers.
Sometimes the legend is more of a hindrance than a help in presenting the data and it is better to turn it off. And sometimes you need to connect a table with data to the graph to prepare a presentation in other programs (for example, PowerPoint). Therefore, it is worth learning how to use the settings for graphs and charts in Excel.
Changing graphs and charts
Create a table with data as shown below. You already know how to build a graph in Excel using data. Select the table with the data and select the “Insert” - “Histogram” - “Histogram with Grouping” tool.
The result is a graph that needs to be edited:
- remove legend;
- add table;
- change chart type.
Chart legend in Excel
You can add a legend to the chart. To solve this problem we perform the following sequence of actions:
- Left-click on the chart to activate it (select it) and select the tool: “Working with Charts” - “Layout” - “Legend”.
- From the Legend tool's drop-down list of options, select the option: "None (Do not add legend)." And the legend will be removed from the chart.
Table on graph
Now you need to add a table to the chart:
- Activate the graph by clicking on it and select the “Chart Tools” - “Layout” - “Data Table” tool.
- From the drop-down list of options for the “Data Table” tool, select the option: “Show Data Table”.
Types of graphs in Excel
- Select the “Chart Tools” - “Design” - “Change Chart Type” tool.
- In the “Change chart type” dialog box that appears, specify the names of groups of chart types in the left column - “With areas”, and in the right section of the window select “With areas and stacking”.
For complete completion, you still need to label the axes on the Excel graph. To do this, select the tool: “Working with Charts” - “Layout” - “Axis Titles” - “Name of the Main Vertical Axis” - “Vertical Title”.
Near the vertical axis there is a place for its title. To change the vertical axis title text, double-click on it with the left mouse button and enter your text.
Delete the schedule to move on to the next task. To do this, activate it and press the key on the keyboard – DELETE.
How to change the color of a graph in Excel?
Based on the original table, create a graph again: “Insert” - “Histogram” - “Histogram with grouping”.
Now our task is to change the fill of the first column to gradient:
Tools for complex design of gradient fills on charts are now available to you:
- name of the workpiece;
- direction;
- corner;
- gradient points;
- color;
- brightness;
- transparency.
Experiment with these settings, and then click “Close”. Please note in the "Name of the workpiece" are already available ready-made templates: flame, ocean, gold, etc.
How to change data in Excel graph?
A graph in Excel is not a static picture. There is a constant connection between the graph and the data. When the data changes, the “picture” dynamically adapts to the changes and thus displays the current indicators.
We will demonstrate the dynamic connection of the graph with data at finished example. Change the values in the cells of the range B2:C4 of the original table and you will see that the indicators are automatically redrawn. All indicators are automatically updated. It is very comfortable. There is no need to re-create the histogram.
