Microsoft Excel Training | Online Excel Training

Equations

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

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!

Evaluating a formula in Excel

Equations Sean Duffy

BODMAS.

It’s been a while, but I still remember the shiver of apprehension that would run up my spine at the very mention of the term. If you don’t know what I’m talking about, you might want to wander over to this link.

Brings back all those algebraic memories, eh?

And the reason I bring this up is because today’s Excel tutorial talks about how Excel spares you the trouble of evaluating if you have got a complicated formula just right. Here’s how:

Imagine if you had to write a formula that would double 14, divide the result by 3, add 12 to this result and finally subtract 2 from whatever answer you got. I know, I know – this is stretching your imagination a bit, but humor me.

You’d write something like this, then, wouldn’t you (correct me if I’m wrong!)

12+(14*2)/3-2

Still, wouldn’t it be nice if Excel could walk you through this formula, step by step?

Yes it would, and yes Excel can. Here’s how:

Simply write out the formula in a cell, and go over to the Formula tab. Choose Evaluate Formula:

It goes without saying that you should click on this button making sure that you have selected the cell in which the formula has been entered, of course.

A new window should pop-up now:

Excel underlines the term it shall evaluate first (14*2). Once you click on evaluate, the result shall appear in italics in the next round of evaluation:

And so on and so forth. It’s a pretty useful trick to make sure you have got increasingly complicated formulas down just right – isn’t it?