If there is just one thing that I would do away with in Excel it is the Merge & Centre button found on the home tab in the Alignment section.
This may be a lovely useful feature to many of you but it can cause such havoc further down the line that I feel it really should not exist at all. Far too easy to use and causes too much stress. If you have a set of data that you wish to Sort or Filter or Pivot you may find that you do not get a true result if there are cells that have been merged together.
I am going to show you how to get the same look but without the same disaster. We will still be in the alignment area of Excel but not unfortunately as easy to get to. Maybe we need to create Macro?
Here is a basic spreadsheet with a title at the top.
It would look good if the title was centred across the columns of sales figures.
How to centre across columns
You could use the Merge and Centre button after selecting the area you want the title to be centred in. But I would suggest that you don’t and rather use the Centre across selection instead.
- Select the cells that you wish the title to centre across.
- Right mouse click inside this area.
- Select the Format cells… from the list.
This will take you to this dialog box
- Select the Alignment tab.
- In the very first area named Horizontal: Click on the dropdown arrow right of the word General.
- Select Centre across selection. Then select the OK button.
This will be the result in your spreadsheet.
This will look exactly the same as using the Merge & Centre tool but you have not merged the cells at all. You have as it were placed the text onto a rubber band and stretched it over the area you wanted. The text will always be in the centre no matter how many columns you delete, hide, stretch or decrease or insert. You still have the ability to place information in cells B1:E1 but this will stop the title being centred across the 5 columns if you do.
Here is an extended basic spreadsheet with merged cells the top of the figures.
Now try to create a simple pivot table of the whole data. Follow my tip on How to create a Pivot Table in Excel.
- Select a cell inside the data area and then select Pivot Table Tool from the Insert Tab.
Notice that Excel only picks up the first quarter information as the merged cells dictate. If you were to have the title merged across the whole data, you would be fooled into thinking you will get all the data to play with but this is what you get if you do.
The best thing is not to have any “fancy” formatting at all when you know that you want to create Pivot Tables and the like.
