Challenges: Monday Morning Mulling: May 2019 Challenge
3 June 2019
On the final Friday of each month, we set an Excel problem for you to puzzle over for the weekend. On the Monday, we publish a solution. If you think there is an alternative answer, feel free to email us. We’ll feel free to ignore you.
To recap, the problem we had last week was the inability of the Center Across Selection feature to vertically align content in cells. We highlighted that we were unable to ‘Center Across Selection’ a selection of cells vertically to have them appear merged and aligned vertically:
Camera Solution
The first solution we are going to present uses the ‘Camera’ command in Excel.
The Camera command can be accessed by enabling it in the ‘Customize the Quick Access Toolbar’ through Excel options:
With the Camera command enabled we can take a ‘picture’ of the cells we wish to have vertically aligned, then create a picture on Excel. We can then rotate the picture so that the text is aligned vertically.
However, this method has its drawbacks. Formatting the picture to be aligned with the cells is tricky. Any attempts at alignment cause the cells to look odd:
The picture also does not respond to adding or removing columns, and changing the dimensions for the cell that was originally referenced breaks the picture:
Shapes Solution
We can use shapes to achieve vertical alignment without running into any of the problems encountered from using a picture from the Camera command.
The first step is to create a ‘Rectangle’ shape:
Draw the rectangle out, then click on it and reference the cell we wish to have aligned vertically (B2):
Center the text using the alignment settings on the Home tab:
Place the rectangle over the cell it is currently referencing (B2), and rotate the rectangle so that we have it vertically aligned and place it over the cells that we wish to cover (B2:C4):
Once snapped to grid, the shape will respond to adding or removing columns or rows, and resize itself accordingly:
Do note that if you wish to change what the cell says we have to use the arrow keys on the keyboard to navigate to the cell. As clicking on the cell with the mouse will result in Excel selecting the shape instead.
That’s our solution for this month’s Final Friday Fix, how did you fare?
The Final Friday Fix will return on Friday 28th June with a new Excel challenge. In the meantime, please look out for the Daily Excel Tip on our home page and watch out for a new blog every business workday.
