... that is the question.

And the answer might surprise you more than a little.

Merging cells is something we do quite often, and for very good reasons. When the text is deomstrably larger than a single cell, it often makes sense for aesthetic purposes to try and fit it, or merge it, across multiple cells.

Except there's one rather large, inconvenient problem. Merging cells can be quite problematic, since any subsequent formatting must of necessity account for these merged cells. Formatting borders, filling colors, or even simple shifting of merged cells can throw up error messages that are time consuming to solve at best, and supremely frustrating at worst.

That's the bad news.

The good news is that there is a percfectly acceptable solution. The only caveat being the fact that it is hidden away, deep within Excel's darkest recesses. In true, time honored Microsoft tradition, it reveals itself only to the most determined researcher, and that after many hours of clicking around. But for you, dear reader, there's a shortcut to nirvana: read on!

Here's what you need to do:

Select the text that you need to fit into a single cell:

Move your mouse over to the "Font" section on the Home tab:

Click on the little arrow that is (barely) visible towards the bottom right of that box.

The "Format Cells" pop-up box should materialize  - make sure you choose the "Alignment" tab.

Under the Text Alignment section, for the Horizontal drop-down box, choose "Center Across Selection"

And it's as simple as that!

It looks for all the world as if the cells have been merged, but in reality, all that has happened is that the text has literally been centered across that seelction, with the intervening borders removed.

Works like a charm, and is much more convenient than merging cells.

Now, if we could only figure out just why it's hidden away in that little niche...

This is actually the third post in the series (the first one was about PowerPoint, while the second one was about MS Word), and it is the most relevant for our little community here.

But first things first: who's Guy Kawasaki?

Oh, just your regular Apple-employee-who-designed-the-first-Macintosh, along with a couple of other things of note. Nobody really special, you know.

Seriously though, if Guy is talking, it usually is worth your while to stop and listen. So when we noticed that he was talking about Excel, we really listened. And haveing listened, we think you should tilt an ear in his direction as well.

The powers that be at Microsoft decided to partner with Guy and get him to talk about three different and disparate activities that entreprenuers need to do - create a pitch, a plan and a financial forecast. It's the financial model that interests us, of course, since the friendly green monster we know and love has been used to create it.

There's a lot to like about the model, the details of which are given in this post, while the actual template itself is here. Guy and his partner Bill Reichert have done a very good job of building the template as well as explaining both the template itself as well as the reasoning that goes behind it, so we won't reinvent the wheel here. What we will reiterate is that going through the post and the model won't hurt you one little bit - quite the opposite, in fact.

Have fun!

Tips, Tricks, and Insights

It's almost as if a formula is having doubts about its very existence.

Now, before you guys begin to doubt if I've had a couple too many, I'll explain myself. I got asked by a fried if Excel has a formula that identifies if a particula cell has a formula in it.

In other words, let's say you want to check if cell A1 has a formula in it. So you should, hypothetically speaking, be able to enter a formula like "=CHECKFORMULA(A1)" in A2, and if there does happen to a formula entered for Cell A1, it should return the value "TRUE". And if not, "FALSE". If you see what I mean.

Seemed simple enough, and although I hadn't come across such a formula myself, I was fairly sure that such a formula would exist within Excel's vast repertoire. But as I began to search through them, a sinking realization dawned on me... there's no such formula!

Which led me to look online for a solution, of course, and as it turns out, there happens to be a rather simple solution, although it does involve the use of macros.

Now, people generally react to even a casual mention of the word macro in much the same fashion as they react to algebra, Collaterized Debt Obligations and the Great White Shark: a neatly balanced mixture of fear and loathing. But worry not: in today's tutorial, we'll guide you through a drop-dead simple way of creating a custom formula in Excel.

Now, first things first: credit where credit's due. The solution was provided on a Yahoo forum by a lady called Anna M (thanks, Anna!), and we're just going to describe it in greater detail.

So here we go:

First, open fire up Excel, and hit ALT+F11 on your keyboard. That should open up your VBA editor. As Douglas Adams might have said, "Don't Panic!" It's just another window, and that's all there is to it!

Up at the very top, look for Insert, and click on it:

Choose Modules:

And in the new window, simply paste the code exactly as it appears below in the main window:

Function IsFormula(c)
IsFormula = c.HasFormula
End Function

And you don't even have to save the file! Simply close the window, and you're all set! You have just created a user developed formula... congratulations!

Now, to check if it actually works:

In the spreadsheet, enter any old formula in any old cell. Let's say, 3+4 in cell B3:

And in cell C3, type in our very own secret sauce recipe:

Now, upon hitting Enter, we should get to see that magic grouping of letters: TRUE

...and voila!

As simple as all that! Now, you'll probably have to do this every time you fire up Excel, and your macro security settings might need a little tweaking, but nothing out of the ordinary, I promise you. All in all, if you do this right (and it is simple to do, I'm sure you'll agree), you should have this custom formula up and running in no time.

As always, if you know of a simple way to do this, send your emails/comments in right away, and we'll put 'em up ASAP!

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

It's not exactly rocket science, and to a true data-head, it's actually a little redundant, but hey, you have to start somewhere!

Confused? We're talking about MIcrosoft Excel taking it's first baby steps in going social.

The good folks over on the Excel Blog just came up with a post about an app on Facebook called Photo Vote, called "Poll Your Friends With Photo Vote" which allows you to create a poll on Facebook. They've also enabled a little bit of Excel integration which allows you to slice the data by demographic variables that Facebook automatically collects.

For example, say you put up a poll about which team is better: The Red Sox or the New York Yankees (personally, I don't see why you'd need a poll for that. The Red Sox, any day. Duh!) and you get 100 friends to vote. Well, you can now see stuff such as:

• Of the grey-matter-challenged friends who did choose to vote for the Yankees, what percentage were female, and what percentage were male
• What was the age distribution of your respondents, broken up by response
• And so on and so forth

Now, it's an idea worth trying, but there are a couple of problems with this approach, as far as we're concerned:

1. People who are not data-heads don't really care about the analysis in the first place
2. Even if you imagine a situation in which they will want to segment the data, they will usually just want to see the results - they don't really care which program is running in the back-end
3. For data-heads, this is just a toy

Tips, Tricks, and Insights