Search Not Just Numbers

Tuesday 29 October 2013

Excel Tip: Finding the maximum or minimum value in a list

Before we crack on with this week's post, just a quick little note about Mynda Treacy's Excel Dashboards course.

I know that many of you have purchased the course and taken advantage of the early bird discount. As when we offered the course last time, the course has been very popular and the feedback has been excellent.

If you haven't already done so, you can still get the course until 30th October and, although you have missed the discount, the course still represents excellent value and I will still throw in my Introduction to Pivot Tables course free of charge.

You can get the course here.

OK, on with this week's tip. We all know how to sum a list of numbers, but Excel offers a number of other functions for analysing a list, and today I want to introduce two of them that enable you to find the maximum or minimum value in a list.

This could be useful for all sorts of reasons - finding the maximum sales in any particular month, or the lowest score achieved in a test, for example.

Excel offers a simple way of doing this with the MAX and MIN functions.

Both functions have the following syntax:

=MAX(number1,number2,etc.) you can have as many numbers as you like in this list.

So, for example:

=MAX(23,45,12,65,21)  will return 65

and

=MIN(23,45,12,65,21)  will return 12

These numbers can be cell references, or ranges of cells - and this is usually of far more practical use.

So, say the cells A1 to A5 contain the numbers 23,45,12,65 and 21, then:

=MAX(A1:A5)  will return 65

and

=MIN(A1:A5)   will return 12

And that's really all you need to know.

Excel Expert 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 22 October 2013

Excel Tip: Centre titles without merging

Before we get into today's post, just a little reminder that the 20% discount offer on Mynda Treacy's Excel Dashboards course expires on Thursday. So if you've been thinking about it, now's the time to do something about it, if you want to save some money.

Today's post is a follow-up to an earlier post, "Do you really need to merge those cells?" where I highlighted the dangers of merging cells.

It became clear in some of the comments on forums that I had highlighted the problems but had been less than clear on alternatives.

This post is intended to address that.

Just to recap, the problems highlighted in that earlier post were:

  • Data containing merged cells can not be treated like a normal data table - meaning that we can't use all of the tools that we might want to use for referring to a properly formatted data table, such as pivot tables, SUMIF, etc;
  • Copying and pasting ranges is restricted to those with cells merged in the same way;
  • Fill down doesn't work if any of the cells in the range to be filled are merged;
  • Even if we unmerge all of the cells, this rarely solves the problem, as this action will assume that the merged cell contents should be placed in the top left cell of the unmerged range - which may not be where you want them to be. Also, having done this, it is often not clear where your data is, as the labels may now be in a completely different place.
Now, by far the most common reason cells tend to be merged is when we are looking to centre titles across multiple columns.

This is usually done be selecting the range of cells in the title row that we wish to centre the text across and clicking Merge & Center.

Fortunately, Excel provides a very simple alternative to Merge & Center, but instead of placing a button for it prominently on the Home ribbon (Merge & Center is slap-bang in the middle of the Home ribbon), you need to go into the Alignment tab of the Format Cells dialogue box.

You can access this by either clicking the little expansion arrow of the alignment section, just under Merge & Center, or by right-clicking the selected cells, selecting Format Cells and then the Alignment tab.

However you get there - once you are on the Alignment tab, the first drop-down box under text alignment is labelled Horizontal. There is an option on this drop-down of Center Across Selection. Visually, this does exactly the same as Merge & Center but crucially, it does not merge the cells.

It still leaves the content in the leftmost cell, but all of the other cells remain intact.

One point to note, however, is that this is only possible horizontally, i.e. across the cells, unlike Merge & Center which will work vertically too. However the horizontal centring is far more common and this approach provides one less reason to merge cells.


Excel Expert 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 15 October 2013

Excel Tip: Dashboards and Charts

Mynda Treacy's excellent Excel Dashboards online course is once again open for registration and once again there is an excellent offer for those of you who don't hang about - more about that later in this post.

Back in July, Mynda wrote a guest post for me on the subject of Excel Dashboards, which you can read here. This is becoming a very important skill and is often now required by employers. Mynda's post is a really good introduction and if, for whatever reason, you do not take up the offer on the training course, I would recommend that you at least read Mynda's guest post.

Charts

To celebrate the re-opening of Mynda's course, I have arranged to offer her eBook, 30 Chants for Better Charts, absolutely free to subscribers.

Many of us use charts regularly in our work, but few of us do it well, and Mynda's eBook provides some excellent tips on how best to format charts so that they look professional and convey the information that they are supposed to, clearly.

If you're not already a subscriber to Not Just Numbers, simply enter your name and email address in the box at the top right of the blog and you will receive Mynda's eBook, as well as other freebies, and updates on new posts to the blog.

Excel Dashboards

The Excel Dashboards course is video based and available online 24/7. It comes with comprehensive Excel workbooks and several sample dashboards to keep. There’s also an option to download the videos, plus Mynda personally provides support for the first 6 weeks of the 12 month membership.

Dashboards are an incredibly valuable tool in today's market for consultants, analysts and managers, but Excel doesn't make it straightforward to build highly professional and interactive dashboards. That's why this type of training is crucial.

What people are saying about the course:

The previous classes have been a huge hit with many people saying how they love the cool techniques and how they've been able to impress their colleagues and clients by using them in all sorts of reports, not just dashboards. 

Others have said the course has prompted them to take a whole new approach to producing their monthly reports. 

I highly recommend the  course but don't take my word for it. You can read comments from past students and find out more here.

Bonus 20% Off

If you join the class by 24th October you can get it for 20% off plus I'll include my Introduction to Pivot Tables course absolutely free, just email me your receipt and I'll send you my bonus.

So, do yourself a favour and check out the course . The price is incredibly fair, the course is awesome and it'll transform your Excel reports and possibly even your Excel career.

Learn Excel Dashboard Course

Disclosure: I make a small commission for students who join Mynda's course, but as you know I don't just recommend anything and everything. It has to be of outstanding quality and value, and something I can genuinely recommend. After all, if doesn't live up to what I've promised you'll think poorly of me too and I don't want that. Oh, and just watching the course videos won't transform your career, you have to actually put it into practice, but then you know that. 

Excel Expert Course

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".

Tuesday 8 October 2013

Excel Tip: Don't do this with your table headings!

Another quick tip this week. This time it is a little bugbear of mine that I often see done by people not realising the problems it can cause them later.

It is really simple to get right if you know you need to!

See if you can guess what it is from the image. If you can't, I would recommend that you read on.

The problem is entering multi-row titles on any table of data.

I am not just talking about the table feature in Excel, but any table of data, such as the one in the picture.

In an earlier post I wrote about how to lay out data to make it useful.

The table format above fits all of these requirements, but falls down on the headings. Simply because you have used more than one line for the heading, it won't work properly if you decide you want to use AutoFilter, or a PivotTable!

This is really simple to avoid.

You can type the heading on one row and then apply word wrap to the cells on that row (select "Wrap Text" from the Home ribbon). If you want specific line breaks in the text, you can use Alt+Enter to insert a line break within the cell.

The data then fits all of the criteria to be used in a PivotTable, or to enable AutoFilter.

That's it - I'll get off my soap box now!

Don't forget that Mynda Treacy's Excel dashboards course will be available again soon for a short period of time. If you're not subscribed to the blog, do so now (at the top right of the blog) so that I can let you know when it's available - and to be notified of future Excel Tips.

Excel Expert Course

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".

Tuesday 1 October 2013

Excel Tip: The power of NOW - Using the current time and date in Excel

Just a short post today because I have a very tight deadline to meet on a publication I am writing for the ICAEW (The Institute of Chartered Accountants here in England) on Automating Management Accounts in Excel.

Before we get into the post though, I have some exciting news for those of you who missed out on Mynda Treacy's Excel Dashboards course last time around. The course will be made available again for a limited time later this month. In the meantime, you can (re)read Mynda's guest post on the subject of Excel Dashboards here.

If you want to make sure you don't miss out this time, ensure that you are subscribed to the blog as I will keep subscribers updated. If you are not a subscriber, you can do so by leaving your email address in the form at the top right of the blog.

Today's post is a quick tip on how to use the current time and date in Excel.

There are two very similar functions that Excel provides for this - NOW and TODAY.

Both functions have no arguments but must still be followed by the (empty) brackets.

=NOW() returns the current date and time

=TODAY() returns the current date (actually it returns midnight at today's date)

I covered how Excel handles dates and time in a post a couple of weeks ago. Looking at the functions in those terms - NOW returns the full serial number for the current date and time and TODAY returns that serial number rounded down to the nearest whole number,

This functions can be used on their own, or as values in formulae, e.g using =TODAY()-A1 to calculate the elapsed time since the date in A1.

One caution to add to using these functions is that the values only update when Excel recalculates. Assuming you have the default calculation settings in Excel, recalculation will occur when any value changes in the spreadsheet, and when the spreadsheet is opened.

In most practical applications this is not a problem for the TODAY function unless the spreadsheet is open and unedited overnight. Obviously, it can be more of an issue with the NOW function.

You can, however, use the F9 key to force a recalculation at any point.

Right, back to Automating Management Accounts!

Excel Expert Course

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".