Excel Everest

Functions

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

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.

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

A-Counting We Will Go!

Equations,Formulas,Functions,Tips and tricks Sean Duffy

So there are 21 students in your class, and you have just finished entering the marks they recieved in your latest exam (which was for a 100 marks). Curiosity assails you, and you wonder how many of your students got more than 40. Such is life with academicians – they sometimes think of the most random thoughts.

Still – here’s the list for your perusal:

Now, as we pointed out in the earlier post, there is the index finger method. But, as we also pointed out in the post, it is an almost guaranteed migraine inducer. This Excel tutorial points out a solution to your problem, and throws in a bonus – no migraine!

Use the COUNTIF function. The COUNTIF function only counts those values in a range which match a condition within the formula – if the condition is not met, no counting.

Here’s the formula one would use in our example:

Remember – non-numeric parts of your condition must be in inverted commas (“>”), and an ampersand (&) is needed to connect the text with the number (“>”&40).

But apart from that little niggle, you are more or less set to go. Hitting Enter will tell you that 15 students got more than 40 marks – I would consider that a decent performance really – wouldn’t you?

Our professor, of course, will not cease from his musings. Having nothing better to do on a lazy Tuesday afternoon, he wonders how many students got marks between 40 and 60. 

Well, if you happen to be the hapless TA helping out the Professor-with-the-insatiable-curiosity, fret not. And turn instead to the COUNTIFS function. The COUNTIFS function is the proverbial Big Brother to the COUNTIF function, with the ability to deal with more than one condition at a go.

It’ll look something like this:

And what’s more – it’ll return what you wanted it to – the number of students who scored between 40 and 60 – 5 in this case.

Keep these Count formulas in mind when you’re dealing with tricky situations and large amounts of data – it can save you a lot of time, and a lot of head-scratching!

Mapping the Great Unknown

Formulas,Functions,Resources,Tips and tricks Sean Duffy

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.

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 a

Thank You, Transpose formula!

Formulas,Functions Sean Duffy

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!

Using The Search Formula in Excel

Formulas,Functions,Tips and tricks Sean Duffy

Here’s a little known trick about Excel that will come as a pleasant surprise to folks who have spent many an hour in unravelling ‘messy’ data.

Say you’re working with an Excel sheet that has a data set as seen below.  Numbers have been fed into a column, but the formatting is not consistent. Some entries read (Roll_No_1) while others are (R12). What you need at the end of the day is just the number, without any of the preceding text. 

The easy, simplistic and slightly crude way would be to look at each cell and make a note of the the numbers, but good luck to you if you are handling admissions at a university, with around 13,000 applications to go. That’s a very long night indeed.

But there is a quicker way around this conundrum, and it’s quite simple too!

Excel has this formula called the SEARCH formula – all it does is look up a particular piece of text within another piece of text. So if you asked it to search for the letter ‘e’ in ‘text’, it would come up with the answer ’2′. 

After that, all you need to do is use the ‘RIGHT’ function to arrive at just what you need – the numbers themselves.

Painless, quick and simple, right? Try it the next time you’re working with data that is tricky to navigate – you’ll be surprised at how powerful this simple formula can be.

Do you know of any other uses of SEARCH that totally rock? Share ‘em with us! Let us know in the comments below!

Simplifying Loan Payments with Microsoft Excel

Formulas,Functions,Tips and tricks Sean Duffy

All of us have been tempted every now and then to succumb to the allure of that fancy new LED HD TV, or perhaps the latest and greatest version of the iPhone. Or maybe you’ve had your eye on that fancy sports car and can’t wait to own one.

Well, except for the lucky few who are able to fall back on their millions, these kind of temptations imply some kind of a loan.

And here’s the thing – there’s an entire industry out there that tries it’s best to confuse you about exactly how much is due from you, month on month. Some of them will tell you that no payments are due for up to six months after you buy the product, while others will talk about zero down payment. Still others will lure you with ‘teaser rates’… you get my drift, right?

Time for the good news – Microsoft Excel steps up to the plate, and simplifies matters for you.

Here’s how:

Let’s say you’ve decided to go and buy yourself the simply the best vacation around – a month week stay at the most expensive resort in Hawaii, first class tickets to and fro – the works. The only problem is, your travel agent estimates the costs to come out to around 100,000 USD. That’s a cool hundred grand, right there.

So if you were to take a loan, exactly how much would the monthly payment be?

Well, if you were to take a five year loan at 10%, here’s what the basic information looks like.

So, at the end of the day, what’s the monthly damage to your wallet? Ignore all of what the agent has to say, and rush to your laptop. Excel has the answer!

The syntax is simple:

  1. PMT(rate, nper, pv, [fv], [type])
  2. rate: is nothing but the interest rate payable, which in our case is 10%
  3. nper: the number of periods of payments (60 months as far as we’re concerned)
  4. pv: the principal value (100 grand in our little tale)
  5. fv: the future value, which you can safely ignore here
  6. type: if you ignore this, Excel assumes that payments are due at the end of the period. If you put in the value 1 (one), Excel assumes that payments are due at the beginning of the period.

Well, simply enter the values as shown below, and ta-da! You’re good to go!

Once you hit enter, Excel tells you exactly how much you will owe on this loan for the terms mentioned. Anything more, and you got a problem on your hands!

Have You Ever Used Named Ranges?

Functions,Tips and tricks Sean Duffy

The ultimate nightmare when working with a spreadsheet that somebody else has prepared involves trying to figure out exactly what is going on. Billions of cups of coffee must have been drunk the world over in honor of simply trying to get a grip on what is going on!

Today’s Excel tutorial tells you how to simplify your own spreadsheet, and make it a lot easier to understand and workaround with.

Here’s a quick example:

The names of seven people are shown along with their age. Now, one way to compute their average age is simply to go to a cell and type in the formula “=Average()” and be done with it.

Another, far more considerate and professional way involves the following:

First of all, select all the ages. Like so.

Now, head on over to the “Name Box”. This typically lies just above cell A1. With the range selected, enter the word “Ages” in the box, without the parentheses.

Now, when you want to do any kind of calculation on these ages, all you need to do is , for example, “=average(ages)” in literally any cell in the spreadsheet to have your answer on a plate!

The advantage is you need not enter any ranges for these calculations ever again. Entering named ranges simplifies both entering calculations as well as understanding spreadsheets. Try and use named ranges in your line of work, and you’ll be appreciating it’s stark simplicity and ease-of-use in next to no time.

It’s a Bird! It’s a Plane! It’s Paste Special!

Functions,Tips and tricks Sean Duffy

Imagine, if you will, a scenario that goes something like this. 

You have a Microsoft Excel file that contains (apart from lots of other things) a column with percentage data. Isn’t that much difficult to imagine, is it?

There – you don’t even have to stretch your imagination that little bit – that’s what the data looks like. Now, the more observant among you will have promptly noticed that the data is actually in integer notation, not percentage form.

What’s worse is, if you try to convert it into percentages by clicking on that percentage button on the home tab…

… the column turns into this, well, monstrosity.

Familiar scenario? Well, this Excel tutorial explains how to gaily skip over this rather petty (but time-consuming hurdle) and move on to matters more deserving of your attention.

The conventional way would be to either manually write over the original data (overwrite 43 as .43, 23 as .23 and so on) and then use that percentage sign, or to insert a second column which divides the first by 100. Either ways, it is time consuming, and involves much gnashing of the teeth.

Well, un-gnash those teeth, ladies and gentlemen, because we are going to invoke a rather nifty weapon from the versatile arsenal of Paste Special. Here’s what you have to do:

simply enter 100 in any cell of your choice, and copy that cell.

Select the range on which the operation needs to be performed, and choose paste special (the keyboard shortcut for which happens to be ALT+E+S)

Under operation, you’ll notice there are two buttons over on the right: Multiply, and Divide. Choose Divide, hit OK, and this is what you get:

Voila, problem solved!

Rapid Fire Excel Tutorial: Using the Ribbon Efficiently

Functions Sean Duffy

In this Rapid Fire Excel Tutorial, we provide some tips for using the Microsoft Excel ribbon in Excel 2007 and Excel 2010 as efficiently as possible. 

n short, 

1. Learn the basic layout of the ribbon. Also know that the three most important tabs to be familiar with are the Home, Insert, and Data tabs. 

2. Learn how to close and open the ribbon. The ribbon takes up quite a bit of screen space in Excel 2007 and 2010. Learning how to close the ribbon (ctrl-F1 among other methods) will save you time and make you a more efficient Excel user. 

3. Learn how the Alt key works. The Alt key allows you to quickly access all of the functionality of the ribbon. Learn how to use it quickly and easily and navigate in and out of the layers of the ribbon by using the indicated letters as keyboard shortcuts, or escape to move up a level. 

5 Things You Should Know About Paste Special

Functions,Tips and tricks Sean Duffy

We’d like to cover five things you should know about paste special. But, hey, first things first, you should know about Paste Special itself!

The last time around, we spoke about the Transpose formula, and its miraculous ability to send you home in next to no time. Well, as it turns out, there’s an even easier way to warm yourself by the hearth at a reasonable hour, and it’s called Paste Special.

Paste Special is CTRL-V’s big brother, and boy does it pack a punch. In what’s to come, we’ll walk you through five things that Paste Special does in its own inimitable way. Try it out for yourself, and watch your productivity metrics zoom.

1. Transposes, fast: Now, I’ll be the first to admit that the Transpose formula is the cat’s whiskers, but I’ll also be the first to wish that there was a simpler way of doing things! And as it turns out, there is – it’s called the transpose option in Paste Special.

And say you want to switch it around to swap the rows and columns… Transpose it, in simpler words. Well, all you need to do is select said table, copy it, and head over to a fresh new range of cells. But (and here’s the trick) instead of simply pasting the table over, right click the first cell, and from the options that appear, choose Paste Special. And from within the pop-up box that appears, choose – you guessed it – transpose.

Easy as pie, isn’t it? Of course, we have one small table as an example here – when you have dozens of table, each with dozens of rows and columns, this feature really is like manna from heaven.

2. Copy just the formats over: Have you ever had that sinking feeling, looking over an Excel file that has the numbers down pat, but doesn’t even come close to the designated formatting? Take it from me, a long time sufferer of many a corporate design handbook, there is nothing quite as mind numbing as applying a standardized set of formats to hundreds of tables in dozens of sheets. 

What will make your life easier is the option of copying just the formats over – choose a table on which formatting has been applied, copy it, but while pasting, choose formats only – and voila – borders, colors, fonts and italics et al will be magically transported over.

3. Copy Values: The flip side of it works just as well, really. If you want to copy over just the values, sans formatting, simply choose the Paste Special option of “Values” – this will copy only the meat (the number or the value itself) over, and leave all the fancy trimmings aside. Again, for those in the know, a positive boon, no less.

4. Column Widths: Excuse me if I sound like a kid in a candy shop, but the ability to magicall

1 2