Contents
- Black and white patterns
- Save a chart in Excel as a picture
- Adjusting overlap and spacing between columns
- Zoomed Graphs
- Building on the second axis
- Create combo charts
- Auto-growing charts in Excel
- Smart Chart Title
- Colors to choose from for Excel Chart
- Managing zeros and empty cells
- Create a chart from discontiguous data
- Save the chart as a template
Some tips, tricks and tricks for creating amazing charts in Microsoft Excel.
The beauty and functionality of the charting tools in Microsoft Excel 2010 and 2007 are far superior to those available in earlier versions of Excel. Better chart design is immediately visible, but not all of the features you can use to make your charts more functional are available right from the start. In this article, I will give you some useful tips, tricks, and charting techniques in Excel that will help you work more efficiently.
Black and white patterns
New in Microsoft Office 2010 is the ability to fill your chart with grayscale patterns. To see how it all works, select the diagram, open the tab Chart Tools (Working with charts) > layout (Format) and press Fill (Filling) > Pattern Fill (pattern fill). To create a monochrome chart, set the option to Foreground Color (Foreground) black color, for Background Color (Background) is white and select a pattern to fill the data series with. Repeat the same steps for the next row of data, choosing a different pattern. You don’t have to use white and black, just choose different patterns for different data series so that the chart remains clear even if you print it on a monochrome printer or copy it on a black and white copier.
In Excel 2010, you can set a chart’s data series to pattern fill so that it can later be printed on a monochrome printer or made a black and white copy.
Save a chart in Excel as a picture
You can save a chart in Excel as a picture and use it later anywhere, for example, in a report or for posting on the Internet. You will have to do this in a roundabout way, and here is the easiest of them. Make the desired size of the chart on the sheet. open Fillet (File) > Save As (Save as), specify where to save the file and in the drop-down list Save As Type (File type) select Web Page (*.htm;*.html) (Web page), set a name and click Save (Save).
This will turn the Excel sheet into an HTML file, and because HTML files cannot contain pictures, the chart will be saved separately and linked to the HTML file. You will find your chart in the same folder where you saved the HTML file. If your file was named Sales.html, then the picture you need will be placed in a subfolder Sales.files. The drawing will be saved as a separate file. PNG.
Of course, if you don’t want to lose your data and chart and continue working with them in the future, you need to save your Excel workbook first.
You can save the graph as a drawing in a separate file if you need to use it in another project.
Adjusting overlap and spacing between columns
If you think the chart will look better when the bars become wider or overlap, you can do so. To set overlaps between two data series of a chart or change the spacing between columns, right-click on any of the data series in the chart and select from the context menu that appears. Format Data Series (Data series format). Adjust parameter Series Overlap (overlapping rows):
- to move the data series apart, drag the slider to the side Separated (with clearance);
- to create an overlay of data series, drag the slider to the side Overlapped (With overlap).
You can move the rows closer or further apart by changing the parameter Gap Width (Side clearance). If you have two data series, and you want to arrange them overlapping each other, but the second column of the graph should be hidden behind the first one, then you will have to change the order of plotting the graph. First, set up the overlay the way you want the columns to overlap. Then right-click on the row of data you see and click Select Data (Select data). Click on Series 1 (Row 1) and press down arrow to place it under Series 2 (Row 2). This will change the order of the data series in the chart so that you can see the smaller bar in front of the larger one.
Zoomed Graphs
When you build a chart based on data containing calendar dates, you may find that the columns in it are very narrow. To solve this problem, you need to select here is H graphic, right-click on it and press Format Axis (Axis format). Select Axis Options (Axis options) and then the option Text Axis (Text axis). These actions change the way the axis is displayed, making the chart columns wide. In the future, if necessary, you can adjust the gaps between the columns and make them wider.
Building on the second axis
If you are plotting very small values, such as percentages, next to much larger values (expressed, for example, in millions), these percentages will be lost and not visible. The problem can be solved by plotting percentages on a different axis.
Select the chart and go to the tab Chart Tools (Working with charts) > layout (Format) and in the section Сhart Element (Current Fragment) Select a row of data in the upper left that is hard to see. Click the button Format Selection (Format selection), which is located immediately below the selection list of fragments, and in the section Series Option (Row Options) select Secondary Axis (Minor Axis) and press Fermer (Close). Without removing the selection, go to the tab Chart Tools (Working with charts) > Design (Constructor) and click Change Chart Type (Change chart type). You can now select a different chart type for the second row, such as a graph. As a result, you will get a combined chart – histogram bars with a line chart superimposed on them.
I like the color of the text on the axes of the chart to match the color of the chart elements plotted along that axis. Therefore, if my graph is drawn in green, then I will make the text on the corresponding axis green, and the graphics drawn in red will have red labels on the axes.
Create combo charts
While Microsoft Excel doesn’t specifically highlight the fact that combo charts can be created, it’s easy to do so. To do this, select the data and build the first graph (for example, a histogram). Then select the data you want to display in a different view (such as a line graph) and click Chart Tools (Working with charts) > Tab Design (Constructor) > Change Chart Type (Change Chart Type) and select the chart type for the second chart. Some types of charts inherently cannot be combined with each other, such as the bar chart and the bar chart. If we take a bar chart and a line chart, they look great together!
Auto-growing charts in Excel
If the amount of your data will increase over time, then you can build a chart so that it expands as data is added. To do this, format the data as a table by selecting it and clicking on the tab Home (main) command Format as Table (Format as a table). Now your data is formatted as a table, and if you build a chart based on the table, then increasing the amount of data in the table will automatically expand the chart.
Smart Chart Title
You can create a title for the chart that will use the contents of one of the cells on the worksheet. First of all, create a header with the command Chart Title (Chart title) tab Chart Tools (Working with charts) > layout (Format) and place it, for example, above the graph. Click in the chart’s title area, then in the formula bar under the Ribbon, and then type in the cell reference that contains the data you want to place in the title. If you want to title it with the name of the table, then enter the expression: =Sheet1!$B$2Where Sheet1 is the name of the sheet, and $B$2 – absolute reference to the cell with the table name. Now, as soon as the content of this cell changes, the title of the chart will immediately change.
Colors to choose from for Excel Chart
If your chart only displays one range of data, you’ll find that Excel colors all of the chart’s columns with the same color. This can be corrected by selecting a data series, right-clicking on it, clicking Format Data Series (Data series format) and then Fill (Fill). If only one data series is used in the chart, then the option will be available. Vary colors by point (Multicolored dots).
Of course, you can always select an individual column, right-click on it, select Format Data Point (Data Point Format) and set a custom color for that point.
Managing zeros and empty cells
If your data contains zero values or empty cells, you can control how the zeros are displayed on the chart. To do this, select a series of data, open the tab Chart Tools (Working with charts) > Design (Constructor) and click Select Data (Select data) > Hidden and Empty Cells (Hidden and empty cells). Here you can choose whether empty cells will be displayed as gaps, as zero values, or if your graph has a linear view, instead of an empty value, a straight line will be drawn from point to point. Click OKwhen you select the desired option.
This only works for missing values, not nulls!
Create a chart from discontiguous data
To build a graph in Excel from data that is not located in adjacent columns, select the ranges you are interested in while holding down the key Ctrl. When all the desired ranges are selected, feel free to create a chart as usual.
Save the chart as a template
To save a diagram as a template, available for reuse over and over again, the first step is to create this diagram and customize its appearance as you wish. Then select it, open the tab Chart Tools (Working with charts) > Design (Constructor) and click Save as Template (Save as template). Enter a template name and click Save (Save).
Later, you can apply this template during the creation process or to an already created diagram to give it the exact same look. To do this, click on the diagram, then on the tab Chart Tools (Working with charts) > Design (Designer) select Change Chart Type (Change chart type), open section templates (Templates), select the desired template and press OK.
These tips and tricks will help you create better looking charts in Excel 2007 and 2010 faster and more efficiently.