When it comes to tricky problems in Excel, we're always glad to help.

Today's tutorial is about a problem that threatened to confound for a little while, and led to a pretty neat solution. Read on to find out more!

A reader sent in a query about a problem that he was facing - he had a column of dates, and he wanted to extract the month from them. Here's a quick glance at the data:

Now, in an adjacent column, our friend wanted April, August, October and so and so forth. Essentially, extract the month, in text format. The obvious solution, of course, is the use of the "=MONTH()" formula".

Except that this returns a number, not text. So our first result would be "4" and not "April".

Umm, whoops.

We researched a little bit, and to the best of our knowledge, Excel doesn't have a formula that will return the month value in text format.

So we did the next best thing, which is to make use of a VLOOKUP formula. The VLOOKUP formula is one of the most powerful, yet easy-to-use formulas out there. It is pliant, it is flexible and it lends itself to a surprisingly large number of otherwise intractable problems.

So here's what we did. First of all, in another part of the sheet, we created an index of months and their text values:

Next: adjacent to our dummy data, we made use of the month formula:

And in the adjacent column, we wrote up a simple VLOOKUP:

K2:L13 is where we have stored our "index" of months and their corresponding textual values, of course.

Now, a couple of ways to simplify this even further. First things first, as discussed in an earlier post, we could simply name the index.

And we could use the "=MONTH()" formula in the VLOOKUP formula itself:

Ta-da! And that, my friends, is that!

Now, given that this is Excel, there are probably a million other ways to work this problem out, so we're all ears. If you know of a solution that will work out better than this, well, do let us know in the comments below! And as always, if you have more problems like these - do get in touch!

Update: David Onder sends in a solution that is simplicity itself, and it seems to work just fine.

=TEXT(C2,"MMMM")

wherein the date is entered into cell C2 should give you just the result that you need.

Replace "MMMM" with "dddd" or "yyyy" to get either the day or the year, respectively. Thanks a ton, David - that works out a lot better!

Formulas

There's many a wondrous thing that Microsoft Excel can do. Some of these wondrous things also stray over onto the quirky side, and today's tutorial is about a formula that is wondroous and quirky at the same time.

The IFERROR formula is a handly little formula that enables one to buy insurance whie asking other users to enter data. Imagine if you will a scenario in which you ask employees of your sales organization to enter data into two columns.

The data is simplicity itself, of course. All you want them to do is enter in the first column the number of successful sales calls, and in the second, the total number of calls. You will use this data to compute the percentage of successful calls in the next column. But whoops, hang on a second - there could be problems with this method.

Take a dekko at the fourth entry - although the employee has made 21 successful calls, she has mistakenly entered a value of zero in the column for total calls. Now, it is obviously a number higher than 21, but it cannot be zero for sure. What can we do to ensure that these kind of erros cannot occur?

As it turns out, there is more than one workaround to this problem. We'll use the IFERROR function to find a  workable solution.

The IFERROR syntax goes something like this: IFERROR(value, value_if_error)

Here's what the final result will look like:

In this case, the third column was filled up using the following version of the IFERROR formula:

As you can see, the first argument in the formula evaluates the formula, and if the formula is going to result in an error (as it will in the case of the fourth row), an error message is displayed, which can be customized as per your requirement. THis helps because people filling up your Excel file have an idea of what went wrong - a lot more helpful than a cryptic message like the one below, I'm sure you'd agree.

There you have it then - an easy, simple way to ensure that your data entry remains error free.

Do you know of any other useful ways in which the IFERROR formula can be applied? Do let us know in the comments below!

Formulas

Every now and then, my work involves wading through an ocean of data. I'm sure I'm not the only one in these data driven days, so it's fairly certain that I have plenty of company the world over.

And I'm equally certain that most members of my tribe experience a sinking feeling the first time they cast an eye over the large amount of data that needs analyzing. Cast your ocular equipment over this sample, for example:

Enough to give anybody a sinking feeling, eh?

And it goes on and on and on! Go down a couple of rows, and then a dozen and then a hundred, but there's no end in sight. Go right for a couple of columns, and then a dozen.... but you get the idea.

Now it's almost a given that I'm going to have to use pivots or some equally big, nasty weapon to subjugate this monster, and that's fine. But to begin with, I'd prefer to know the size of what I'm dealing with - and what I'd like is a couple of functions that tell me just that: how many rows, pray tell, and how many columns?

Turns out, there are a couple of functions that do nothing but just that: return the number of rows and columns for a specified array.

And here's how it works:

Head on over to the first cell in your gargantuan array, like so. 

Hit ALT-I-R a couple of times, followed by ALT-I-C a couple of times. These are keyboard shortcuts that insert rows and columns in your sheet, leaving it looking something like this. 

Why do that? Well,  so that you can type in the following command in cell A1:

Now fill in the argument by selecting the first cell in your array, and hitting SHIFT-END-DOWN. If you hit ENTER post this little operation, Excel quickly, quietly and efficiently (as it its wont) returns just what you wanted, the number of rows in that rather large array.

Much the same operation along transpose-ian lines will give you the number of columns in your database as well. And hey, who doesn't like a little formatting? So a touch here and a touch there will leave you with the following:

The point being, whichever unfortuante soul (including you!) needs to work on this file knows exactly what she is up against. It also helps with preliminary data validation, as you might have guessed.

How do you guage your opponents in Microsoft Excel? Got any other methods that you think are spiffier?

Formulas, Tips, Tricks, and Insights

I bet you've been here: Hundreds of tables to be downloaded from the internet, and a report that has to be delivered yesterday. It has got to be error-free, of course, along with being well formatted, neatly laid out and in a standardized layout that brooks no exceptions. And you've just about finished figuring out all your data sources, and are getting ready to prepare the final report when you notice (dear me!) that your base data files has data in this layout:

And of course the standardized layout requires them to be laid out top to bottom, rather than left to right.

Much weeping, and perhaps an occasional joust of head butting with the wall - that would be the standard response, right?

Wrong! Not when your friendly spreadsheet software with the comforting green logo is around - this sounds like a job for Microsoft Excel.

For the Transpose function within Microsoft Excel, actually.

Doing this using the transpose function is just a little bit tricky, but pay a little attention, and you should be able to pull this rabbit out of the hat! First up, select a range of cells that does not overlap with the original range - make user that the dimensions are the same as the original, but reversed.

In other words, there are two rows and twelve columns here, so your new range should have two columns and... you guessed it... twelve rows.

Enter the following formula (you want to, of course, adjust the numbers shown to suit your requirements)

And what you get is the following:

Umm, whoops. That didn't quite work out, now did it? But hey, help's only a click away. Selecting the forst column, and formatting the cells in the data format will have you with your data just the way you wanted.

And off you go! That should help you get home on time after all.

There's a much, much easier way to do this as well - it involves using some nifty Paste Special commands - and that's what we'll talk about the next time around. In the meantime, feel free to let us know what you think in the comments below!

Formulas

Complicated spreadsheets are a double edged sword - for the Excel enthusiast, they are fun to prepare. But no matter how much of an enthusiast you are, decoding somebody else's excesses is a right royal pain.

And this is especially true when it comes to decoding the thought process that somebody else put into making an Excel file with 50 spreadsheets. Multiple formatting styles, different cell widths and heights, dates, text and number formatting that varies by column are just some of the problems that you are likely to face. Of course, it doesn't end there - certain rows or columns might be hidden, others might be resized and so on and so forth.

If you are the unfortunate soul upon whom lies the heavy responsibility of editing or adding to this spreadsheet... well, it would seem there are long nights in store for you, my friend.

Well, not quite. 

An especially impressive arrow in Excel's quiver is the "Cell" formula. The "Cell" formula can take multiple arguments ans return a wide variety of information about the cell in question, unlocking all of it's myriad mysteries.

For example, let's say you need information about whether the text in a cell is formatted as a date, or if it simply is text. One way to find this out might be to right click on the cell, and check its formatting. But you can't go about doing that for a thousand cells at once, right?

Enter the "Cell" formula.

The cell formula is a quick and easy way to find out what exactly lies in a cell in terms of formatting, contents and so on. Here's a quick and easy example:

D1, in the middle cell, is the value that is returned if you use the formula shown in the cell to the right. It simply tells you that the contents of the first cell are formatted as a date, and in particular, in the D1 format. Particularly useful if you have to figure out varying date formats over a thousand rows or so.

Of course, there are many such tricks up the "Cell" formula's sleeve. Here's another one:

This tells you if the cell contains text or numbers. Quite often, what seems like numbers actually turns out to be a cell containing text - and good luck trying to edit that complex formula you entered in a cell many, many spreadsheets away. A "Cell" formula in a nested "if" formula can save you a lot of time.

Sometimes, the variant shown above can turn out to be particularly useful - simply displaying the contents of a cell elsewhere.

This one is, I think, my personal favorite. It returns the width of a cell - very useful if you are as wonkish as I am about getting the formatting on every spreadsheet just right. I hate different widths across columns, across spreadhseets, and this formula has saved me many a frustrating hour.

Here's a full list of all the arguments possible in this formula - you'd probably want to check these out, it's a very helpful list indeed.

Any innovative uses of the "Cell" formula that you have had occasion to come up with in the past? Let us know in the comments!

Formulas