Direct captioning of diagrams

Data captions and legends are important to understand the content of diagrams. Unfortunately the provided alternatives to caption are partially not legible. For example, sums on top of piled columns are not provided. You search in vain for standard functions such as legends which support the viewer not to look back and forth as they are placed directly next to the stream of data.

With our today´s trick it is possible to implement legible and at the same time dynamic captions:

Part 1: Sums on top of piled columns

  • Calculate sum values and insert as new stream of data (right click on diagram, “choose data”, add stream of data)
  • Change stream of data type to “XYZ” of the new stream of data (right click on new column, choose “Change stream of data type”)
  • Invisibly format items and fade in the data caption on top
Summe in Excel Diagramm

Part 2: Legend next to piled column

  • Fade out the standardised legend
  • Extend the column stream of data to an area with empty segments, so that there is enough space for the legend next to the last column
  • Calculate the X- and Y- coordinates for the legend text, depending on the data of the last column (see sample data file)
  • Insert a stream of data of the type “XYZ” (as in part 1)
Steuerung von Excel Legenden
  • (The X-values for the horizontal alignment of the points, can be divided into the data source capture only after the type “point XY” is selected)
  • Formatting points invisible and show data label right
  • First data label of the new data series, click twice (not double), is so marked a single label area
  • “=” type and click on the table cell in the desired legend text is, then Enter to confirm
Excel Template
  • Connect the remaining legend texts with the equivalent chart segments

Finished 🙂

Template Beschriftung direkt an der Säule