Excel Tutorial - Excel Everest

Preserving Leading Zeros

Tips and tricks, ResourcesExcel Everest

The always awesome Chandoo comes up with a tip that needs to shared as widely as possible, more about which in just a second.

Have you ever needed to enter numbers in a sheet where for whatever reason, they need to be entered as 001, rather than 1?

Of course you have, who hasn’t? Well, in that case, you have also obviously suffered from the bang-my-head-against-the-wall syndrome, because Excel is, in this case, more stubborn than an obdurate mule.

If you don’t know what I’m talking about, simply fire up an Excel sheet and enter “001″ without the inverted commas in any cell

… and hit Enter

Slightly frustrating, isn’t it? You want Excel to show the leading zeros, but Excel couldn’t care less.

There are a couple of  workarounds to this, which are mentioned in Chandoo’s blog post linked to above, but there’s one that is even simpler than the solutions he has mentioned… simply append a leading ‘ to the number you wish to enter.

Once you press enter, the leading zero’s don’t disappear!

The only thing is that Excel gently reminds you that what you’ve entered as text really seems to be a number:

And the number, since it has been formatted as text, is left-aligned. But it is a pretty quick workaround all the same!

The Top 5 Keyboard Commands You Really Should Know

Tips and tricks, Resources, FunctionsExcel Everest

Well, I’ll be honest here. There are more than five. In fact, there are probably fifty. On the other hand, five are easy to remember, and the title is a lot more catchy.

In all seriousness though, what we are talking about in this Excel tutorial are commands that are inordinately useful and for the most part, little known. But get to know them hear, master them, and watch productivity zoom ever upwards.


 CTRL+SPACEBAR is a particularly gratifying shortcut to get acquainted with, since it selects the entire row in which a cell happens to be active. Particularly useful for copying for large swathes of data from one column to another. ALT+SPACEBAR does exactly the same thing when it comes to rows, while CTRL+ALT+SPACEBAR does exactly what you’d expect it to – go ahead and give it a try! The SPACEBAR series works like a charm for large amounts of data, when you’d rather not take the trouble of scrolling to the end of the data in order to select it entirely.


 Which brings us rather conveniently to the next shortcut. If you want to select a range of data that (it would seem) extends down for many rows altogether, you want to select the first cell in the data, like so:

…and simply press SHIFT+END+DOWN (the down arrow, that is). You’re through! The entire data is selected. This works in all directions, so if you are at the start of a particularly large and cumbersome array, pressing SHIFT+END+RIGHT, followed by SHIFT+END+DOWN will select the entire array. The only thing you need to keep in mind here is that this works assuming there are no missing rows/columns in your data. So long as you keep that in mind, you’ll find this is a pretty useful trick to know.


The “Group” feature in Excel has been a boon for many an intrepid spreadsheet traveler. It helps you literally group two or more columns/rows together – something that comes in particularly useful for large complicated models. Well, the ALT+SHIFT+RIGHT command helps you group rows/columns together in a jiffy. ALT+SHIFT+LEFT ungroups them. Simple, eh?


 This one’s not all that well known, and getting used to it is a useful, pleasant surprise. All of us have faced situations in which we need to enter the same value in a cell as is present in the one above. For example, let’s say cell B3 has the value 4. Well, B4 should also have exactly the same value. Enter CTRL+SHIFT+’. It does exactly what is required, no more, and certainly no less. (By the way, you might want to give CTRL+SHIFT+>, and CTRL+SHIFT+< a try as well – they can copy formulas over from the left and the top of the active cells respectively)


Imagine a spreadsheet that is large, complex and let’s be frank here, horrendously complicated. It’s reached you via email at the wrong end of a Friday afternoon, and making sense of it is a task at which Hercules would have quailed. How best to go about unraveling this monstrosity?

Well, here’s a trick that you’ll be able to teach old Hercules if ever the need arises. Simply point your mouse over to the last cell in which the final result resides, and press CTRL+SHIFT+{

It highlights all the cells which the active cell refers to while calculating it’s result.

230 is the result of adding all of the cells above. If one were to select that cell and press CTRL+SHIFT+{, one would see this:

There, that’s all for this time around. Know any snazzy hidden gems of your own when it comes to Excel and keyboard shortcuts?!

Top 5 Excel Formulas That Are Just Plain Weird

Formulas, Tips and tricksExcel Everest

You might think this post to be a little offbeat, a little off the beaten path – and we wouldn’t argue with you.

What we have done in this Microsoft Excel tutorial is looked through Excel’s capacious archives to come up with five formulas that won’t be used all that often. Sure, there’ll be people over the world who use these day in and day out, and thank heavens for the very existence of these formulas – but there won’t be mroe than ten of them, I’m sure.

All right, that’s an exaggeration, maybe – but seriously: how many of you knew about the “Roman” formula, for example?

a) The Roman:

The Roman formula does what the Roman formula promises. It returns the Roman value of an Arabic integer. Arabic integers are the good old numbers that we always end up using. 543, for example. But I bet you a rather sizeable sum you can’t come up with the Roman numeral equivalent. Well, Excel can. 

It’s DXLIII, for what it’s worth. It may be a neat party trick, or it may be of great help if you are in academics. But it’s a useful thing to know, the Roman formula – that’s for sure.


b) Combin

In how many ways can you choose 2 objects out of, say, 50? Brings back horrific memories of math in the sixth grade or thereabouts? Are you about to break out into hives at the mere thought of that accursed blackboard and those dreaded sums?

Fret not, want not. Fire up Microsoft Excel, and enter “=combin(50,2)” in any cell and hit enter. Learn that there are 1225(!) ways of choosing 2 objects out of 50. Who would have thought?

c) Now

We couldn’t resist putting this one in for it’s immense, well, coolness. Any formula called “Now” deserves to get in simply because it is called “Now”. Still, it does what you think it would. It simply returns today’s date and time as of… wait for it… now.

d) Rept

This is Bart Simpson’s dream come true. If only the blacboard was a spreadsheet!

The rept formula takes a text string as an input, and spews it out as many times as you like.

Take that…

Use this…

…to get this…

5) Bahttext

And we saved the best for last. We confess, we don’t quite get what this is for. Put a number (say 23) in a cell (say B2) and enter the formula “=bahttext(B2)” in any other cell (without the quotation marks). Excel returns the number converted into the Baht text, with the word “baht” added at the end. It looks something like this:

Excel’s help section does tell us that the output can be changed in the Regional Settings tab in your Control Panel (assuming you are running Windows) – but quite why it should be in Baht to begin with (and why the formula should be called bahttext in the first place) is sadly not revealed.

That being said, if any of you do know the answer to this little mystery, do let us know in the comments!