Sat 13 Aug 2016

Article 42 - Generating an Odd Order Magic Square in Excel (VBA)

By |Saturday, August 13th, 2016|Categories: Articles, VBA|Tags: , , , , , , , , , , , |1 Comment

A magic square needs no introduction and we come across it many times. A magic square is a square grid and the minimum size of a magic square is 3x3. The whole numbers in magic square appear only once and all cells are filled. The horizontal rows, vertical columns and main and secondary diagonals all add up to the same number. This number is called magic constant. The more about magic square can be read here - https://en.wikipedia.org/wiki/Magic_square

(more…)

Sat 22 Aug 2015

Tips & Tricks 105 - Column Chart with Primary and Secondary Axes

By |Saturday, August 22nd, 2015|Categories: Tips and Tricks|Tags: , , , , , , |0 Comments

You want to draw a column chart with two data series and one you want to make a primary and one you want to make secondary.

Below, you have drawn a normal column chart in first chart. The second series (orange one), you have converted to secondary axis. Now, it has overlapped the first series. You have tried all options to adjust Series Overlap and Gap Width but still first data series is not visible.

If you want to overcome this, read on.....

1

1. Right Click on Chart > Select Data > Add
Give a name to this series, let's call it Dummy1.
Put a value large enough in Series Values: box. The default populated is ={1} which you can make to ={80} for my case. The purpose is to make this series visible. Hence, you need to choose this value appropriately.

2. Now, right click on Dummy1 > Format Data Series > Primary Axis

3. Right Click on Chart > Select Data > Edit Dummy1. Put ={0} in Series Values: box

1

4. Move Dummy1 to between our original two series.

1

5. Add another series with name Dummy2 and put ={0} in Series Values: box.

6. Move Dummy2 to after Dummy1 and before Sales2.

1

7. Now, delete Dummy1 and Dummy2 from your legends.

You have a perfect looking column chart.

1