Search Not Just Numbers

Tuesday 28 April 2015

Excel Tip: Analysing large datasets

Before I get into this week's short post, a quick reminder that Mynda Treacy's Excel Dashboards course is closing on Thursday, so this is your last chance to register.

I have a couple of large projects on the go at the moment which are taking up a chunk of my time, so when Alex Bankoff of Udemy emailed me offering some excellent free content that I could share with you, I jumped at the chance!

David Taylor has prepared an excellent tutorial on how to use Excel to start making sense of very large datasets. His tutorial uses baby names in California since 1880, from US Social Security data.

As well as helping you to get a feel for big data, it also introduces PivotTables and PivotCharts along the way.

You can do the tutorial here, no signup required.

Have fun!




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 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".