Excel Everest

Tips and tricks

Preserving Leading Zeros

Resources,Tips and tricks Sean Duffy

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

Functions,Resources,Tips and tricks Sean Duffy
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. The SPACEBAR Series 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. SHIFT+END+DOWN 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. ALT+SHIFT+RIGHT 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? CTRL+SHIFT+’ 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 th

Top 5 Excel Formulas That Are Just Plain Weird

Formulas,Tips and tricks Sean Duffy

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…

A Treasure Trove. Literally.

Resources,Tips and tricks Sean Duffy

Every now and then, our sojours on the Internet seas lead us to places where not too many people have trodden before.

These virgin, unexplored islands are often that-a-ways for a reason; they’re poorly designed, perhaps, or maybe the content is not too useful. We just heave resigned sighs and set sail from them, in search of better lands.

Every now and then, though, we just luck out, plain and simple. And the link that we’re going to share with you today is one such internet treasure.

The unimaginatively titled “Site Map | Microsoft Office” – I mean, c’mon! – is actually a repository of link related to Microsoft Office products. And when I say repository, I mean every single link one could ever hope to keep in one’s bookmarks and when I say Microsoft Office products, I mean every single last one of ‘em.

Just insaney good stuff, this.

Just scroll down from the rather drab SmartArt-ish graphic at the top of the page…

… and immerse yourself in the Excel links section.

We’ve previously talked about Excel’s own help section, and it is pretty darn good. But if ever you need to forage through the big bad world wide web… well, this is as good a place as any to start.

Happy surfing!

Page Layouts: Confusion Reigns Supreme!

Resources,Tips and tricks Sean Duffy

While we are unabashed fans of Microsoft Excel here at Excel Everest, there are some things that can drive us up the wall.

And the leader on the Black Sheep List undoubtedly has to be that much-maligned, much-misunderstood feature: Page Layout. If you broke out into hives upon reading that dreaded name and are seriously contemplating diving under the desk until the storm blows over – hey, we know exactly how you feel!

This blog post is actually a dipping of the toes into the water, if you will; we just want to take a quick poll about how many of you think a deeper post about Page Layouts is warranted. If there are enough of you, and we see no reason why not, we’ll return with an in-depth tutorial.It is also is a bit of a rant – you’ve been warned!

First things first: Microsoft Excel does itself no favors by having a tab called “Page Layout”…

… and a button called “Page Layout” in the tab View

Now, that’s just bad design. Trust us when we say that using Page Layout is confusing – a tab and a button with the same name is just cruel!

Well, anyhow, when we say Page Layout, we mean the button in the tab “View” – unless specified otherwise.

There, now that we’re clear about that – here’s what Page Layout does:

It helps you visualize how a printout will look. In other words, if you were to hit the print button, what would the printer make of your current spreadsheet? Well, whatever you see on the screen after hitting the Lage Layout button, that’s what.

As you can see in the screenshot above, Excel allows you to configure your layout as per your requirements. You can adjust headers and footers, the lage layout itself, decide how much content you need per page and so on and so forth. Think of it as the advanced version of “Print Preview” – which in fact is what this is.

But in our honest opinion, Microsoft bungled up this bit. In fact, Microsoft itself has, in a way, admitted as much. They have  a blog up asking us users of Excel about how many of us use Page Layout, and in what way. Feel free to head on there and fill up the survey – it can only help.

As you can see, when I filled in the survey, the results indicated that most people did not use it at all, or used it very sparingly inde

Need an Excel Template? Vertex42 Probably Has it.

Add-ons,Resources,Tips and tricks Sean Duffy

A fundamental reason we built Excel Everest is that Excel is useful in many facets of life. Recently we’ve been playing around with some templates from Vertex42.com, a site that embodies this to the core, and they’re spectacular. Check them out if you’re looking for an easy way to plan your mortgage, put together a wedding budget, build a contact list, etc. 

Below is our editor’s account of the kick-ass meal planner provided by Vertex42!

I’m going to describe a recurring nightmare that I have at least three times a week…

It involves me standing in the middle of the kitchen, with a furrowed brow and with some gentle headscratching involved. There is but one question running through my head on endless loop, and no answer forthcoming for the longest time ever. The question being: What do I make for dinner? And here’s the thing: this nightmare is for real! I live it! Every week!

I don’t know about you, but planning meals for my home gets my goat like nothing else on earth. It’s tedious, it’s boring and it’s frustrating. It is also a known and acknowledged imagination-killer.

Imagine my delight, therefore, when I stumbled upon an easy, permanent solution to my problem. And knowing as you do by now my eternal love for all things Excel, imagine how my delight doubled when I realized that the solution came in a neat little Excel package that was… wait for it… free! You see what I mean when I said the Excel Tutorial this time around was manna from heaven?

Well, to cut a long story short, Vertex42 is a site that specializes in Excel templates – of which they have many more than we can count in one blog. You should head over to the site, pronto, and give it an approving once-over. Go on, give it a look – we’ll wait.

Impressive, isn’t it? Exactly the same reaction I had when I first saw it. But as I was saying, the thing that made me happiest was the Weekly Meal Planning Template. The template allows you to download an Excel file (although PDF versions for simple printouts are also available) which you can use to plan your meals.

Here’s what the file looks like:

Enter the date in the relevant cell, and head on over to breakfast. Now this is where things get really interesting:

Because the cell next to breakfast has a drop down button that contains a list that is guaranteed to get your tummy rumbling. Pre-fed into that cell are various options for breakfast – and pre-fed into the cell adjacent to it are options for accompaniments for breakfast. And it’s the same level of convenience i

Visualizing Data (And it’s easier said than done!)

Resources,Tips and tricks Sean Duffy

Excel in its latest and greatest avatar has significantly improved upon the ability to create charts that:

  1. Look respectable (think of the truly horrible grey background that charts had in Excel 2003 and earlier)
  2. Convey meaningful information (the ability to creat new kinds of charts has been added)
  3. Can be interpreted easily (with color schemes and chart combinations that make sense!)

Still, given your data, creating a chart that is easy to read, conveys meaningful information and communicates a relevant message can be difficult. But hey, what are we here for? To make your Excel life as easy as possible, that’s what for! This particular Excel tutorial focusses on getting out that perfect chart – read on to find out more!

Take a look at the data given below:

Now, if you look at that table long enough, some things make themselves apparent. Jim, it would seem, is sheer awesomeness – highest sales by far. But hey, hang on a second, he’s not very efficient, is he? In fact, he needs to make a lot of calls, or sales visits, to get a succesful conversion.

And Peter – lowest sales on record, but what a salesman! He’s able to convert 95 times out of 100, and who wouldn’t want a batting average like that? Now, our problem is, can we put up a chart that tells this story effectively?

Well, creating a chart is simple enough – choose your data range, head on over to the Insert tab, and click a chart that catches your fancy – a bar chart would work well in this case.

Choosing the very first chart (first under 2D Columns) will give a chart that looks rather like this:

Which is well enough as things go, but there are a couple of problems. The first, and most obvious problem, is the fact that “% Successful Calls” is on the same vertical axis as “Sales”. Since sales are around the 200 mark (or thereabouts), and percentages are necessarily capped at 100%, well, “% Succesful Calls” variation doesn’t come across too well.

There’s a workaround though: right-click on the red bars, choose “Format Data Series”, and opt to have these bars shown on the “Secondary Axis”

Umm, whoops. That actually creates a secondary problem, as you can see in the chart below:

Top 5 Excel Keyboard Shortcuts

Equations,Resources,Tips and tricks Sean Duffy

Excel Keyboard Shortcuts – My Top 5 List

There is, I assure you, a very high degree of correlation between amount of time you spend on Excel, and the number of Excel keyboard shortcuts you know. Over time, it becomes especially aggravating to move your hands from the keyboard and on to your mouse in order to use some feature or the other in Excel. It might seem like a trifling thing to begin with, but those seemingly inconsequential seconds can add up to become entire eras in their own right.

Which is why keyboard shortcuts are a blessing from above, and which is why you should know as many as possible. I’ll let you in on a little secret though: even power users have their own personal favorites that they just can’t do without! And in this tutorial on MS Excel, I am going to talk about my personal top five keyboard shortcuts. They aren’t rocket science, each of these five, but if you integrate them into your routine while working on Excel, you’ll soon find yourself saving a lot of time.

  1. Alt+E+S: The Paste Special is a tool that has saved many an Excel user from collapsing in a heap on the psychiatrist’s couch, and with good reason. It allows you to copy cells over with special tips and tricks, such as pasting cells and retaining only the values, or only the formats, – or what-have-you. You can even paste the data after transposing it, that is, you can copy a row of data, and paste it as a column. But invoking the magic in Excel can be done by either clicking on the little black triangle under Paste on the Home ribbon…

or by hitting Alt+E+S. And once you have practiced it a bit, you’ll see that the keyboard shortcut works a lot better and a lot faster. Go ahead, practice it for a while, we’ll wait.

2. CTRL+Pg Up and CTRL+Pg Dn: This one took me a while to get used to actually, and I’ll admit, it was immensely frustrating until I did make it a habit. Windows users have been indoctrinated thoroughly into using Alt + Tab when they want to switch windows – and the natural tendency of Excel users is to hit that same combination of keys if they want to switch sheets in an Excel file. Except, of course, it doesn’t quite work that way. To switch sheets, you need to hit CTRL+ Pg Up or CTRL + Pg Dn. Keep this Excel 2010 keyboard shortcut in mind to save yourself the trouble of going “Arrrgghh!” every few minutes or so. I speak from personal experience!

3. Alt+W+V+G: Spreadsheets sans gridlines look much better than, well spreadsheets non-sans gridlin

Using Microsoft Excel for Presentations Instead of PowerPoint

Resources,Tips and tricks Sean Duffy

If you’re doing a presentation, you’ve got to use PowerPoint. 

Or that, at any rate, seems to be the law in the corporate world. Still, have you ever given the matter more thought? If your presentations are going to consist almost entirely of tables, data and numbers, is there really any point in copying over all that data into a presentation?

Worse, adjusting the fonts, colors and schema to suit the presentation is just so many extra hours of work. The next time you have to make a presentation that has more data than you know what do with in MS-PPT, you might consider giving Excel a try.

Divide your analysis into different sheets

Excel can provide hyperlinks to different sheets. At the end of each stage of that complicated analysis that will win your company billions of dollars, just provide a quick hyperlink to the next sheet. This way, you can segment your story into different sections, and keep each stage relatively simple.

Charts and Smart Art Are Equally Easy in Excel

Who said you couldn’t rustle up an impressive story in MS Excel? Excel has almost all of the basic requirements you need to prepare an impressive presentation, including Smart Art. So just go ahead and give making basic lists and designs a go within Excel itself. After all, why get out of your comfort zone?

Excel is more flexible than PowerPoint

If you need to show the impact upon different variables due to a change in one parameter, Excel makes your life easy! Keep just one sheet in Excel, and have the scenarios you want to demonstrate dependent on your formulas. 

Remember, the whole point of a presentation is to tell a story – and nobody ever said that that story must be told in PowerPoint.

Let Me Count The Ways

Functions,Tips and tricks Sean Duffy

Elizabeth Browning had a way with words, it must be said. For today’s post, we’re going to borrow a line from one of her very best poems – we have already gone ahead and borrowed it, actually. It’s the title of this post, and a very relevant title it is too.

And why is it relevant? It is relevant because in today’s Excel tutorial, we’re going to count the number of ways we can count in Excel. You read that right – the number of ways we can count in Excel!

Turns out there is more than one way in which you can count in Excel – so let’s go ahead and learn how.

So how many people are there on that list? The no-brainer way of doing it would be to keep an index finger on the first name, narrow your eyes in focussed concentration, and begin a slow journey downwards. It might induce a migraine eventually, but it will get the job done. A far easier way (certainly on on your eyes!) is to select the entire range, and look at the bottom right of your screen. Excel obligingly provides you with the answer.

Or, if you have a formula fetish, you could use the first of our functions – the Count function.

Except whoops! If you hit Enter over there, Excel will stubbornly refuse to accept the fact that there are names in the range, and will return a value of “0″. This is because you can only use the Count function in ranges where you have numeric data – you’ll want to keep that in mind, because it has the power to save you a lot of heartburn!

So how do you count the names in there? Well, Excel, like we said, can count in many ways – and you can always use the COUNTA function. The COUNTA function counts only those cells in a range that are not empty. Since the cells above are expressly, well, not empty, COUNTA should return a value of 11 (the number of names in that range). Which it does!

There – that’s one mystery solved for the day. Having given ourselves a congratulatory pat on the back, we proceed to the next task at hand. Have a look-see at the data below.

Deleting Duplicates in a Jiffy

Resources,Tips and tricks Sean Duffy

Some people, it would seem, will just not get it. Ever.

So on the day of the all hands meeting at your firm, you’d put up a small Excel file outside the entrance, with just one column to be filled: names of people attending. All that people had to do was enter their name while entering the room. That was it, so that you could have a quick and ready estimate of the number of people who attended. Simplicity itself, right?

But no! No system, it would seem, is fail proof. 

Because a quick glance shows you that certain geniuses have entered their name multiple times. Apparently, going out for a cup of coffee and coming back in implies having attended the meeting twice. Sigh.

Well, the damage is done, and there is no use crying over spilt milk. Correcting for multiple entries is your job, so how do you do it? That’s what this Excel tutorial focuses upon, and the good news is – it just takes one simple click!

Having selected the first row in the column for which duplicates need to be removed, head over to the data tab, and look for the “Remove Duplicates” button.

Clicking on it will give rise to a new window:

And clicking OK will, as if by magic, remove all those pesky duplicates.

There – what might have taken hours for a firm with hundreds of employees now takes seconds. Leaving you free to, say, go for a cup of coffee yourself.

And what a very pleasant thought that is, isn’t it?

Matrix Multiplication. And All That Jazz.

Functions,Resources,Tips and tricks Sean Duffy

Verily is it said: Excel can do stuff you didn’t know existed.

Well, we don’t know if that qupte has actually been made, but anybody who is even fleetingly familiar with Microsoft Excel can look you in the eye and give a cool assertive nod – because it’s true!

For example, did you know that Excel can do matrix multiplication? Matrices are those slightly confusing, bemusing arrays of rows and columns that reprise bad dreams about math exams where most of us are concerned. For people in academia or research, they are the very bedrock of almost all things math. So in case you need to deal with matrices, there is no need to fire up an advanced stats or math program – good old Excel will do just fine. 

And in this Excel tutorial, we will show you how to do just that. So here goes!

Imagine (if you will) that you have a matrix sitting in a sheet.

This is, in mathematical parlance, a 3X3 matrix. In plain simple English, this is an array with three rows, and three columns. Now we need to invert it. So how does one go about doing this in Excel? Well, here’s how.

Choose, for the first part, an array of exactly the same dimension (3 by 3 in this case)

Now make your way to the formula bar, and type in the following command:

The cells in the formula bar are where your original matrix is located, of course.

Now remember (and this is very important!) – simple hitting the ENTER key will not work, because you are working with a rather special creature in Excel called array formulas. For the result to be shown as an array, you need to tell Excel that the expected answer is not in a single cell, but rather, across an array of cells. And just how do you tell Excel this? By pressing CTRL-SHIFT-ENTER, rather than just ENTER itself.

So go ahead and do that, and here’s what Excel should show:

So the matrix has been inverted. But how do you know if this has been done for sure?

As it turns out, multiplying the original matrix by the inverted matrix throws up an identity matrix (which is a special kind of matrix in which the diagonal from the top left to the bot

1 2 3 5