EXCELer.org

Um, that is not a facetious question. In point of fact, Excel actually can do your math homework. Or rather, and let's be frank here, it can help you check very quickly indeed if your child has doing her sums correctly.

And for most parents the world over, that is a godsend - while we would be very glad indeed if the kids were doign their homework correctly, ensuring this usually means having to solve all those problems yourself. And racking your brains over high-school algebra is the last thing you want to be doing at 7 p.m. on a rotten Wednesday evening.

Leave it all to Excel!

In this, the first part of what may well turn out to be a multi-part series, we'll explain how to solve two fairly simple problems in beginner's math: GCD and LCM.

Sounds vaguely and disturbingly familiar, don't they?

GCD is the Greatest Common Divisor, while LCM is the Least Common Multiple. In other words, GCD simply checks which is the biggest number that can divide into both (or all) of the numbers given, while LCM checks which is the smallest number that both (or all) of the given numbers can divide into.

Take the numbers 10 and 15, for example. The biggest number that is a common divisor of both is, of course 5. That's the GCD. And the smallest multiple for both of them is, of course, 30. That's the LCM.

There - now that we have successfully dispensed with the explanation, let's go ahead and try and figure out how to solve math homework using Excel: Episode 1.

Let's say you have been given three numbers (or rather, your kid has been given three numbers) 16, 22, 56. How does one find out the LCM?

Well, simplicity itself:

It's that simple, I tell you.

And that's the answer, right there:

DOing this without Excel might have taken you some time, but now - well, you'd hardly blame your kid if she thought you the cleverst parent alive, now would you?

And if you want to impress her further, just tweak the formula a bit to let her know of the GCD as well. That'll show the young 'uns that you've still got it, eh?

And there you have it: two (very simple) formulas that don't take more than a couple of minutes to make you look like The Coolest Parent Ever.

And what better feeling is there to have, pray?

We're all Excel addicts over here, make no mistake. Much as we like to poke fun at the green monster, we simply couldn't imagine being even remotely productive in office without those friendly cells staring back at us from the screen.

And if you, dear reader, are anything like us, we share a common trait. It saves us a lot of time, to be sure, but it can also be downright frustrating at times. I speak of, of course, our mutual loathing for mice.

I don't mean the squeaky real-life variety (although I don't mind admitting that I'm not the biggest fan in this case either), but the ones that move the cursor around on the screen. For a professional Excel user, lifting one's fingers off the keyboard in order to use the mouse while working on Excel is nothing short of anathema. Just not done.

And as I was saying, it works well enough most of the time. THis Excel tutorial is about one of the few times that it... didn't.

Have you ever had to auto-fill a series? Write 1,2,3 in a column and then autofill the remaining numbers up until 20? Of course you have had to - who hasn't?

And therefore, you also know how to go about doing it, right? Fill in a couple of seed values, select them, and point your mouse at the lower right corner, right where that extra little bulge is present, and then drag for as long as you like - yadda-yadda-yadda.

Ah, but the question-du-jour, obviously, is: how do you do this using your keyboard? And the answer had us stumped for a long, long time. An online search threw up not-very-convincing answers and it seemed as if we were going to have to use the mouse in this case, like it or not.

Ah, but never fear. Us intrepid Excel Everest explorers will stop at nothing to dig up an answer - even if it means accomplishing nothing else in our work day.

Presenting, therefore, the keyboard shortcut that comes to our rescue today:

ALT+H+F+I+S

Here's how it works... let's say you want it to extend 1,2,3 (shown above) to 10.

So, in the first instance, select the seed values, and extend the series out as long as you like.

Now hit the keys in the order shown above: ALT-H-F-I-S

A new pop-up box appears:

In which one simply needs to enter the incremental or step value, and the stop value - that is the value up until which point you want the series extended. Of course, it goes without saying that autofill works for ordinary series such as the one we have talked about here, or growth series, or date formats... or what have you - all can be completed using the keyboard shortcut we have spoek about here.

And there, that's it. Painfully obvious once you get the hang of it, of course, but for us addicts, a major problem solved!

Have any keyboard shortcuts of your own that you think the world doesn't know about? Share 'em with us!

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

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!

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

You've probably never heard of it, but Visicalc celebrated it's 32nd birthday this past week, and you should help yourself to a big chunk of birthday cake in it's honour.

And if you are wondering what or who Visicalc is, well, it's the world's first ever spreadsheet programme. As simple, and as momentous, as that.

Dan Bricklin and Bob Frankston were the pioneers in the field of spreadsheet programmes, daring to go where no geek had gone before. We've come a long way since then, of course, and today's latest and greatest version of Microsoft Excel can run rings around Visicalc without exerting itself too much, but back in the day (and we're talking THREE decades here), Visicalc was literally the coolest thing on a personal computer.

THe story goes something like this: Dan Bricklin was a student at Harvard, and had been wondering about the possibility of an electronic spreadsheet program for quite a while, but wasn't quite sure how to go about it. Fortunately for all of us, he simply decided to go ahead and do it, along with some help from a friend of his, Bob Frankston. Thus was born Software Arts, Inc. and thus was born the world first... calculedger.

There is a lesson in here for all of us, namely, being an excellent software innovator isn't quite the same as being the world's best copywriter.

On a more serious note, that (calcu-ledger) was the name that Dan figured would be most appropriate for his fledgling software product. In fact, as he mentions here, he actually wrote a paper about it while in college. "Dan Bricklin's Special Short Paper for the Harvard Business School Advertising Course"

The first version couldn't do too much, of course, but its value and potential was realized very quickly be investors, who were only too glad to get in on the business. Since then, there have been many versions of electronic spreadsheets, including Supercalc, Multiplan and of course, Lotus 1-2-3, which very quickly came to rule the roost.

And all of us know the programme that displaced Lotus 1-2-3, of course. And that's a whole new story in itself. But for the moment, let's stop working on that one million row monster, and pay homage to a simple little porgramme that allowed us to weave magic with numbers ever since it first blinked its nascent little green cursor an an Apple, all those many years ago.

You can find out a lot more about Visicalc and Bob on his homepage, located right here. There's a Wikipedia page about Visicalc as well, which should also be worth your time. But in any case, the next time you build a multi-sheet model in Excel, using a multitude of formulas and add-ins, pause for a moment and offer up a simple thank you to the pioneers of our little industry.

Here's to Visicalc!

We thought we knew almost all there is to know about the features of Excel, but doing research for this post threw up a couple of surprises for us as well!

In this tutorial, we talk about five relatively unexplored areas of Excel, with one special twist - they're right in front of your eyes... always!

That's right, they've been staring at us all along, but we just haven't figured out how to make good use of them. Curious to find out more? Well, read on!

1) The Honey-I-Hid-The-Ribbon Button: The ribbon is a fairly useful interface, and it has gotten all of us fairly used to it. But what if you need a larger dose of screenspace? Well, the solution is fairly simple, really. Just click on the little arrow near the right top of your screen, and hey presto!, the ribbon is gone. It's still accessible, of course - all you need to do is click the tab headings on top - but it's now hidden from view. Clicking on that little arrow gets the ribbon back to its pristine glory.

2) The Formula Expander: Ever needed to write a nested if loop that looks bigger than Texas and California combined? You'll immediately identify with how much of a problem fitting that formula in the formula bar is - and how much of a problem tracking the entire formula at one go can be. Well, again, fret not, because there's a simple trick to making the formula box as big as you want it, and it involves clicking on a small button towards the right of your screen.

3) Page Layout and Page Breaks: Demarcating the print area on your spreadsheet is always a little tricky. Finding out which button to click in order to be able to set the print area in itself is a challenge. A coupe of easy places to start are hidden in the lower right corner of your Excel window - do give them a whirl the next time you want to wage war with the printer.

4) Freezing top rows (or columns): I'll bet you didn't know there was a button for that all along, now did you? Oh, but it's there all right! Right at the very top of the scroll bar lies an innocuous little button that you can drag down to freeze as many rows as you wish.And it's there for column as well, to the left and the right of the horizontal scroll bar - a pretty nifty way to quickly freeze as many rows and columns as you need. Convenience personified, this little feature.

5) Customize the Quick Access Toolbar: Wouldn't it be nice if some commands would show up  irrespective of which tab in the ribbon was active? Of course it would, and that is just what the Quick Access Toolbar does. Not only can you add standard commands such as email, print and so on, but you can also use the "More Commands" option to add whichever button (from any ribbon) you like.

There, those are our five there-but-you-can't-see-them tips for the day. Got any other hidden gems that we should be using more? Have at it in the comments!

We more or less expect to be bombarded with queries on how-to-do-this-in-Excel on a daily basis, but every now and then, the trickle of questions turns into a deluge. And while we really like helping out folks with questions on Excel, some of them needn't even arise in the first place.

And that's because the solution to most problems lies in the No.1 resource for Excel tips and tricks - and it also happens to be the most underutilized one. The Help section in Excel comes loaded with all kinds of information, and all of it is accesible in myriad ways. In today's tutorial, we are going to talk about five ways in which you can, literally, help yourself.

1) Press the F1 button: This might be a duh! moment for some of you, and it might be a godsend for others. Whichever camp you fall into, it helps to remind oneself that rather than firing up your search engine, or speaking to a colleague in the next cubicle, it might be worth your while to simply hit F1 and search within the archives of Excel's help section itself. They are voluminous, they are very well written, and it is only rarely that you will not be able to find an answer there. In fact, if your query is simply along the lines of finding out more about a particular function or feature, rather than an advanced question, Excel's help is simply the best resource out there. It's free, it's offline (although they have an online section as well) and it's literally a keystroke away.

2) Contextual Help: A rather less well known feature is the abillity to pull up contextual help. As we mentioned in the first tip, pressing F1 is easy enough, but if you know what you're looking for, it might make sense to use the method we're outlining now. Let's assume that you want some help on the SmartArt feature. In this case, all you need to do is click on the Insert tab, and hover your mouse over the SmartArt icon. 

When you press F1 in this case, the help section opens up with help on SmartArt. Pretty useful tip, right?

3) The Little Question Mark: Microsoft has been relatively thoughtful when it comes to giving you quick access to help, and yet another way yo pull up the help menu is by clicking on the little question mark that often accompanies pop-up boxes within Excel. It's located at the right top corner, adjacent to the cross that closes the window, and it also pulls up contextual help in a jiffy.

4) Pin the Help Window: Some of you are going to want to send in thank-you cards our way after hearing of this tip, I'm sure. The thing is, Excel opens up the help section in a new window, and that makes it a little difficult to keep switching between the help section and the file on which you need help in the first place. Sure ALT+TAB is just a couple of keystrokes away, but viewing both windows at one time would be great. In other words, it would be really cool if you could resize the help window, and keep it on top while working in the main Excel file, right? Well, .... ta-da!

I don't know if you ever noticed it before, but there's a little "pin" button on the top of the help window, that does just that... it "pins" the Help window on top. So you don't need to keep switching between windows every time you need to refer to the help section and work on your file at the same time.

5) Help on Help: This may be a little meta, but it is possible to find out more stuff about Excel by clicking on the Office button in Office 2007 (it's located at the left top corner) or on the File button in Office 2010. Once you click on the Help button, you can find out which version of Excel you happen to be running, whether the product has been activated or not, and best of all - a way yo get in touch with Microsoft. Particularly useful if you want to let them know about that dream new feature that you cannot believe hasn't been included yet.

There - those are the Top 5 ways in which you can use the Help feature on Excel to, well, help yourself. Know of any we have missed? Do let us know in the comments below!