In bar diagrams multiple streams of data can be grouped (side by side) or piled (on top of each other). Both display formats are important. Sometimes it is also practical to combine both forms. E.g. two piled columns side by side per month.

Due to the fact that the combination of piled and grouped columns is not available within Excel, we need our today’s trick to accomplish our illustration:

  • reserve a separate segment area for the streams of data of your diagram. E.g. two columns per month plus one column of space between months
  • Transfer the values from your input area into the reserved segment (via segment source or formula)
  • compile a piled bar diagram from the specifically built formula
  • Adjust the space width of columns to a very small value such as 5%
  • As needed pigment the columns and fade in captions
  • Add legible sums and legends (see our trick “captions”)

Finished 🙂

 

It´s mostly about variations when presenting data to decision makers, such as planned, target or empirical values. Only these variations allow us to reasonably evaluate the actual numbers.

Variations should play a major role during the visualisation as well. Today´s trick shows us that this is feasible within Excel only with a few clicks in specific cases:

  • Compile a diagram with two curve streams of data
  • Mark the diagram and add the variation columns by choosing the menu diagram draft
  • Format the variation columns red or green respectively. The column width can be adjusted to one of the curve stream of data by right click. (parameter “space width”)
  • Add data caption and legend if applicable (see our trick “captions”)

Finished 🙂

 

 

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

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)
  • (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
  • Connect the remaining legend texts with the equivalent chart segments

Finished 🙂