Wednesday, September 12, 2012

Give me a break!

Some of us are so madly in love with Excel that we at times end up using it as a word processing engine, ignoring MS-Word or its equivalents completely.

When you go overboard and start typing longish sentences of text, you would naturally find that Excel treats them with disdain. In other words, you see that your sentence goes a real, long way and it starts at Column A and ends up at say, Column H.

But you are sure to have situations where you prepare a report full of numbers and statistics, accompanied by typical disclaimers or notes at the bottom of the table. If you have to take a nice printout, the disclaimers also should wrap up to the same column size as the data in the above table. So, how do you ensure compliance?

Here are the most obvious choices:

a) Assuming that your data table runs from Column A to Column D and your notes run up to Column H, you will first select the entire set of cells from Column A to D in the notes section, and do a “Merge & Center” (using the button with an “a” in the center – part of the standard formatting toolbar) so that the entire text flows in within the same columns as the data table.

b) Select the merged cells and now click on the “Wrap Text” button in the formatting toolbar to wrap the text nicely in to the merged cells

c) Adjust the row height appropriately so that the entire wrapped text is nicely visible.

Okay, may be this is a fairly simple task. But you can achieve steps a) and b) in one go by selecting the cells, clicking on Ctrl+1 to bring up the formatting dialog box, and choosing “Wrap text” and “Merge cells” checkboxes and clicking Ok.

Now, let’s look at a slightly complex question – what do you do when you want the sentences to be broken at a particular point and flow the next sentence in another line – instead of just continuing and wrapping on?

What I have seen many people do is this – they type the first sentence in one cell/merged cells. They go down to the next row and type the next sentence and so on. This also serves the purpose, but is a hard, round-about way to achieve the result. 

Let’s look at a smart option to get this done.

In comes the keyboard shortcut – Alt+Enter key. When you are entering a stream of sentences, and you want a line break just press this key combination and you will notice that Excel introduces a line break nicely, while allowing you to carry on with your task of typing the next sentence.

This shortcut makes it so simple that you won’t have to cry “Give me a break” ever again! 

Give me a shout if you want any specific topic to be touched upon. I already have a request for Pivot Tables, and would be covering it soon. 

Note: Some of the tips shown here are extracts from my book on "Excel for the CEO" - details available You can also find this in the ebook edition "Excel for the Small Business Owner" available for online ordering at

1 comment:

Bill said...

If Microsoft didn't want us to type our letters in Excel, they wouldn't have given us Home, Fill, Justify! Good post P K Hari!