Search Not Just Numbers

Loading...

Tuesday, 21 April 2015

Excel Tip: Multiple lines of text in one cell

Before we get into this week's post, just a quick reminder that you can still get 20% off Mynda Treacy's Excel Dashboards course until Thursday, so don't dither any longer if you want to partake!

This week I want to talk about entering multiple lines of text in one cell.

I'm not talking about wrapping text which simply runs the text over multiple lines when it doesn't fit in the width of the cell (this can be enabled for any cell by selecting the cell and clicking Wrap Text on the Home ribbon). I'm talking about choosing where the line breaks are to show different bits of information on different lines (in the same cell), or to break longer text into paragraphs,

This can be particularly useful in mail-merge data, for the body of an email, for example.

There are two different scenarios where you may want to do this which need addressing slightly differently.

The first, and simplest, scenario is when you are entering the text directly into the cell.

In this case, you can simple press Alt-Enter whenever you need a line break (in the same way that you might hit Enter in a Word document). When you do this, notice that Excel has automatically switched on Wrap Text for the cell (Wrap Text on the Home ribbon will be highlighted when the cell is selected).

The second scenario is when you are generating the text using a formula. Here you need to refer to the line feed character by its code.

We can use the CHAR function to refer to a character by its character code,

CHAR(10) is the Line Feed character.

We can build up the text in our cell using the & character to join the text together.

So, say that we have the text for paragraph 1 in cell A1, and the text for paragraph 2 in cell B1, then we can join them together using & as follows:

=A1&B1

However, the text from both cells will simply by joined together, without even a space to separate them, never mind a line break.

However, we can insert the Line Feed character as follows:

=A1&CHAR(10)&B1

At first this might appear to look exactly the same, however the line feed is there and will be used in a mail merge, for example.

It will have no effect on how it is displayed in Excel, until we switch Wrap Text on for the cell (it is not automatic in this case). We can now see that a line break has been forced where we put CHAR(10).

You can use this to create quite complicated sections of text, built up from multiple paragraphs selected using IF statements and or lookups.



If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Tuesday, 14 April 2015

Excel Tip: Free Excel Dashboards Webinar

Free Excel Dashboards Webinar
In place of my usual tip this week, I wanted to tell you about a free webinar that the excellent Mynda Treacy is running each day this week.

Mynda is the author of the Excel Dashboards course that many of you have already purchased and speak very highly of.

If you haven't already purchased the course, this webinar is a great chance to learn some of the tips completely free of charge.

You can choose which day and time suits you, and book your place, at the link below:

Free Excel Dashboards Webinar

Alternatively, the dashboards course itself is once again open for new delegates for a limited time, with a 20% discount if you register by Thursday 23rd April:

Register for Excel Dashboards Course


Excel Dasboard Course

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Tuesday, 7 April 2015

Excel Tip: Showing the weekday for a date

I hope everyone who celebrates it has had a good Easter break. I've just got a quick post to ease you back into work this week.

Have you ever wanted to show the weekday's name in a date? Or, show the name of the weekday for any particular date. Excel makes this much easier than you might think.

You can do this using number formats.

If you right-click a cell with a date in it and select Format Cells, Number Format, then select Custom, you can design your own number format by typing into the Type: box.

A typical UK date format, might be dd/mm/yyyy. This will show the day and month as a two digit number and the year as a four digit number.

So, for example, the 7th April 2015 would be 07/04/2015.

The day, could be shown as ddd or dddd, and these work as follows:

ddd gives the three letter version of the day, e.g. Tue

whereas dddd gives the full name of the day, e.g. Tuesday

The month works similarly, so mmm would return Apr and mmmm would return April.

We could type a custom format such as:

dddd, dd mmmm yyyy

This would show the same date as:

Tuesday, 07 April 2015

Alternatively, you could simply show the day itself by using a format as:

dddd

Using this format, the cell will still hold the full date, but will just display the day, e.g. Tuesday.

You can also use this format in the TEXT function, to return the name of the day as text in another cell.

Say that the date (07/04/2015) is in cell A1, then:

=TEXT(A1,"dddd")

will return the text Tuesday.



If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Tuesday, 31 March 2015

Excel Tip: You don't know what you don't know - dollar signs revisited

We're all learning, all of the time, when it comes to Excel. This week, I thought I would cover a topic I've covered before, but from a different angle.

Using the dollar sign in an Excel formula is one of those simple, but powerful, things that I constantly find that people have different layers of understanding of.

Part of the problem, is that once we think we know something, we are much less likely to learn any more about it.

I see this regarding the dollar sign all of the time, and although there is nothing complicated about it, I typically see four different levels of understanding. There may even be a fifth, as I'm still learning too!

As this is such a powerful tool, you might want to check to make sure that you're not missing any of them.

Level One
"I've seen them in other people's formulae, and wondered what they did."

I would certainly recommend reading my earlier post on them, as you are missing out on a really powerful (and simple) tool.

Level Two
"I use them to fix a reference to a cell, for when I copy a formula"

Yes, you can fix a cell by using two dollar signs, e.g. $A$1, but did you know that each dollar sign has its own purpose. The one before the column fixes the column, and the one before the row fixes the row.

So $A1 will always refer to column A (and the row will change relatively), whereas A$1 will always refer to row 1 (and the column will change relatively).

Again, I would recommend reading my earlier post for a fuller explanation.

Level Three
"I use them in almost every formula to fix cells, rows or columns. I don't know what I'd do without them!"

I was at this level for years. In fact I was at this level when I wrote the blog post referred to above! Then one day, I was looking over a client's shoulder and saw the dollar signs changing at the press of a key. It was one of those "Hang on! Show me what you did there." moments.

Using the function key F4 while on a cell reference in a formula (whether it already has dollars or not), will toggle through all of the options, e.g. if the reference is A1, then repeatedly pressing F4 will have the following effect:

First press:   $A$1
Second press:   A$1
Third press:   $A1
Fourth press:   A1

Keep pressing and it will go through that loop each time.

When I discovered this, I wrote a follow-up post. Keep learning!

Level Four
"I press F4, probably more than any other key!"

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".