Some tips, tricks, and techniques for improving the appearance of charts in Microsoft Excel.

The charting tools in modern versions of Microsoft Excel offer improved aesthetics and functionality compared to earlier versions. Although the charts look better, some options to enhance their functionality aren't immediately obvious. In this article, I'll demonstrate some useful tips, tricks, and techniques for creating effective charts in Excel.

Introduced in Microsoft Office 2010 is the capability to apply grayscale pattern fills to your charts. To utilize this feature, select a chart and navigate to Chart Tools > Layout Tab. From the dropdown menu at the top left of the ribbon, choose the series you wish to modify. Next, select Format Selection (located just below the dropdown) and choose Fill > Pattern Fill. For a monochrome chart, set the foreground color to black and the background color to white, then pick a fill pattern for the series. Repeat these steps for the second series, selecting a different pattern. It’s not mandatory to use black and white; simply choose distinct patterns to ensure the chart remains understandable when printed in grayscale or copied in black and white.

You can fill a chart with a pattern in Excel 2010 and higher, allowing it to be printed on a monochrome printer or photocopied in black and white.

Saving an Excel Chart as an Image

You can save a chart as an image directly within Excel for use in other projects like reports or websites. To accomplish this, resize the chart on the worksheet to a suitable size. Go to File > Save As, select the desired save location, and from the Save As Type dropdown, choose Web Page (.htm; .html). Enter a filename and click Save.

This converts the worksheet into an HTML file, and since HTML files cannot include images, the chart is saved separately and linked to the HTML file. Locate your chart in the folder where the HTML file was saved. For instance, if your file is named sales.htm, the images will be stored in a folder called sales_files. The image is saved as a standalone PNG file. Naturally, you’ll also need to save your Excel workbook if you intend to keep both the data and chart for future editing in Excel.

You can save a chart as an image file if you need to use it in another project later on.

Adjusting Column Overlap and Spacing

If you believe your chart would appear better with wider columns or overlapping ones, you can achieve this. To adjust the overlap between two chart series or modify the spacing between columns, right-click any series on the chart and select Format Data Series. Use the Series Overlap option to separate the series by dragging toward Separated or overlap them by dragging toward Overlapped.

You can adjust the Gap Width to bring the series closer together or push them further apart. If you have two chart series and the smaller one ends up behind the larger one after setting the overlap, you'll need to change the chart plot order. First, set the overlap as desired. Then, right-click the visible data series and choose Select Data. Click on Series 1 and press the Move Down button to place it behind Series 2. This alters the plotting sequence so the smaller data appears in front of the larger data.

Making Bars Larger

When dealing with date-based data, you might notice that the bars in your chart are very thin. The solution is to click the Excel chart's X-axis, right-click, and choose Format Axis. Select Axis Options and then click on Text Axis to enable it. This modifies the axis plotting method, resulting in wider bars. You can then adjust the gap width if needed to make them even wider.

Plotting on a Secondary Axis

When working with very small data, such as percentages, alongside much larger figures like millions, the percentages may become invisible. To address this issue, plot the percentages on a different axis. Select the chart and choose Chart Tools > Layout Tab. From the chart element selector in the top-left corner, select the challenging series. Click Format Selection below this and, in the series options group, select Secondary Axis and click Close. Without deselecting the series, choose Chart Tools > Design Tab and select Change Chart Type. Now you can choose a different chart type, such as a line chart. Since a specific series is selected, this change applies only to that series, creating a combination chart—for example, a column chart with a line chart overlay. I prefer the text on the chart axes to match the color of the corresponding chart elements. Thus, if I have green bars, I'll type green text on the axis related to that, and a red line will have red font on its axis.

Creating Combined Charts

Although Microsoft Excel doesn’t explicitly indicate it, you can easily create combined charts. To do this, select the data and plot the initial chart type, such as a column chart. Then, select the series you want to display differently, like a line chart, and choose Chart Tools > Design Tab > Change Chart Type. Select the second chart type. Some combinations, like bar and column, don’t make sense, but a line and column chart work well together.

Automatically Expanding Excel Charts

If your data is expected to grow over time, you can configure your chart to expand automatically as new data is added to your dataset. To do this, format your data as a table by selecting the data and, from the Home Tab, choose Format as Table. Now, because your data is formatted as a table, if you base your chart on this table data, adding more data to the table will automatically expand the chart.

Smarter Chart Titles

You can set the chart title to pull its content from a cell on the worksheet. First, add a chart title using Chart Tools > Layout Tab > Chart Title and position it, for example, above the chart. Click inside the chart title box, then click on the formula bar on the ribbon and enter the cell reference containing the desired title. If you need to prefix it with the sheet name, so D5 on Sheet 1 should read as blank. Now, whenever the content of that cell changes, the chart title will update accordingly.

Variable Colors for Excel Charts

When your chart plots only one data range, Excel colors all bars the same color. To change this, click on the series, right-click, and choose Format Data Series followed by Fill. If only one series is included in the chart, an option Vary colors by point will be available.

Certainly, you can always select an individual series, right-click, and choose Format Data Point to set a specific color for that data point.

Handling Zeros and Missing Data

When your chart contains zero values or missing data, you can control how zeros are displayed by selecting the chart series, then choosing Chart Tools > Design Tab and clicking Select Data > Hidden and Empty Cells. Here, you can decide whether empty cells should appear as gaps, zeros, or, if the chart is a line chart, whether data points should be connected by lines. Click Ok once you’ve made your selection.

Note: This applies only to missing values, not zeros.

Charting Non-Contiguous Data

To chart data that isn’t aligned in adjacent columns, use the Excel shortcut of holding the Ctrl key while selecting each data range. After selecting the ranges, proceed to create your chart from the chosen data.

Saving a Chart as a Template

To save a chart as a template for repeated use,

The above is the detailed content of Excel Charts: Tips, Tricks and Techniques. For more information, please follow other related articles on the PHP Chinese website!