When most Excel users create charts, they make them way too large. For many reasons, using mini-charts is much easier to read.
To illustrate, this Excel dashboard report contains 28 charts:
An Excel dashboard report with 28 mini-charts. Click the image to see it full size.
This report, which is included in the IncSight DB plug-n-play dashboard kit, contains roughly ten times the number of charts included in a typical Excel report. But when you view the image in full size, you’ll see how easy the charts are to read.
One challenge in creating charts like the ones above is that of handling the X axis correctly. Generally, when you create an Excel chart and then reduce it to the actual size of the report above, the X axis labels are slanted. This makes them difficult to read and takes up too much space in the chart area. The figure below shows a better way to handle the X axes.
This figure shows that the X axes of Excel mini-charts can be easy to read.
There are three key steps to setting up the X-axis labels shown here. The first step is to display 13 months of data, rather than 12. When we do this, Excel labels both the first and last month in the chart.
The second step is to set up your data with the date labels you need. In the figure above, column A contains date serial numbers for the values shown. Column B contains formulas that transform the date values into the text labels we need to display in our chart. Here are key formulas for the cells shown:
B2: =TEXT(A2,”mmm”)&CHAR(13)&TEXT(A2,”yyyy”)
B3: =TEXT(A3,”mmm”)
(If your computer is set up for a language other than English, you might need to replace “mmm” and “yyyy” with the format labels required for your language.)
The CHAR(13) part of the formula in cell B2 forces a carriage return when the text is displayed in a chart.
Copy cell B2 to cell B14, then copy cell B3 to the range B4:B13.
The third step is to force Excel to align the labels horizontally. To do so, select the X-axis labels in the chart, press Ctrl+1 to launch the Properties dialog, and then:
- In New Excel, choose the Alignment tab. Then click the up arrow once in the Custom Angle spinner control; then click the down arrow once.
- In Classic Excel, choose the Alignment tab. Then click the up arrow once in the Degrees spinner control; then click the down arrow once.
After you choose OK, you’ll see that the labels are horizontal, not slanted. This is because changing the spinner control up and down as I’ve described turns the automatic alignment setting off and specifies an alignment value of zero, which is what we want.
This is a quick description of one of several steps needed to create mini-charts like those shown at the top of the page. Chapter 2 of my e-book, Dashboard Reporting With Excel, explains the entire process.
{ 1 trackback }
Comments on this entry are closed.