Tag Archives: calculate

More Essential Excel Keyboard Shortcut Combos

When you get used to using keyboard shortcuts with Excel, you start to realise that you use some shortcuts together all the time. We talked through my Top 5 Excel Keyboard Shortcut Combos before, but what other combos are there? What narrowly missed the (short)cut?

1. Change font type and font sizeAlt+HFF [type font name], Alt+HFS [type font size]

Sometimes I forget to paste formats, or notice that my worksheet looks messy due to different font types and font sizes that may have been pasted in over time. I often find myself using the shortcuts above (usually with something like Arial and then 10), to quickly tidy things up.

2. Ungroup all grouped rows and columnsCTRL+A, Alt+AJ

Every now and then I inherit a spreadsheet from someone else that has clearly had a lot of work go into it. They’ve spent a lot of time building something really in depth – so much so that they’ve made it more concise by grouping columns and rows together so you just see the key information. But I need to understand how it works, so I have to go through and unhide all of the grouped columns, group by group. This shortcut unhides all of these groups in one fell swoop (N.B. it’s not for unhiding hidden columns).

3.  See what part of a formula works out toF2, [optionally select part of the formula] F9, Escape

Big formulas can be hard to get your head around, and if they’re not working you need to break them down to see where the problem lies. F2 dives straight into the formula – you may then choose to highlight a particular element of it that may not be working. Pressing F9 will calculate just that one part of the formula. Often I will have each part calculated so I can see the big picture and check that my logic is correct. Pressing Escape is important as it means you exit the formula without changing it – otherwise it stays with all of its parts calculated and is no longer dynamic.

4. All borders, with a thick border around the outsideAlt+HBA, Alt+HBT

I think small tables of data look tidy with thin borders all over, with thick border around the outside. This combo does that really quickly.

5. Close a workbook and re-open itAlt+FC, Alt+FR1

Sometimes if I make a mistake and want to go back to the latest version quickly without saving, I’ll want to quickly close and re-open the workbook I’m in. Alt+FC closes the workbook (if you’ve made changes press N at this point to quickly tell Excel not to save them – be sure that you really want to do this), and Alt+FR1 opens the most recently opened file. Clearly if it was the second last workbook that you opened, you’ll need to press Alt+FR2.

So there you have it – five more easy but useful shortcut combos.

Just for Fun… Blackjack / 21 Probability in Excel

Using Microsoft Excel to predict 21 (Blackjack) Odds
Using Microsoft Excel to predict 21 (Blackjack) Odds

 

This spreadsheet uses macros and formulas to predict odds in the card game Blackjack (aka 21). It deals you random cards one at a time and shows you the probability of your hand value if you hit again.

[showmyads]

Calculate Blackjack Odds in Microsoft Excel
XLCalibre Blackjack in Excel

Have a look and let me know what you think (don’t forget to enable macros).

[showmyads]

By the way, if you like this you probably want to check out our Excel slide puzzle game or some of our other Just for Fun posts

XL FORMULA FOCUS: SUM, AVERAGE, PRODUCT, POWER (an introduction to Excel math functions)

SUM AVERAGE PRODUCT & POWER by XLCalibre
SUM AVERAGE PRODUCT & POWER by XLCalibre

The picture above gives you a step by step encounter with 4 of Excel calculation functions. The numbers being used are in cells A1, B1 and C1. To make it easier to follow we’ve used the numbers 1, 2 and 3.

[showmyads]

The blue cells contain the names of the functions we are using. Below each blue box is what you need to enter in the Formula Bar to make things happen. The next line shows the formula using the actual numbers instead of cell references. The line below that is the calculation that’s taking place, and last of all is the answer produced.

XL FORMULA FOCUS: VLOOKUP for currency conversion

VLOOKUP is an essential Excel tool. Even if it’s the first function you learn, don’t worry, it’s actually pretty straight forward, and is really useful.

The purpose of the VLOOKUP function is to find your chosen value in a table of data and tell you what the corresponding value is in another part of that table. If that doesn’t make sense, follow this example and you’ll see what it means and why many Excel users consider it one of the most useful functions in their arsenal.

Currency conversion is essential when calculating international reward programs, like bonuses or salary review. You’ll usually need to work from a budget in a single currency to show the overall cost. So we’re going to use currency conversion to demonstrate the VLOOKUP function.

Here’s our spreadsheet:

It’ll be easier for you to follow this if you have your own spreadsheet open to work through it with me. I’ve copied and pasted the latest exchange rates for pounds sterling (my local currency here in England) from Reuters’ website in columns A and B (but you’ll probably want to be consistent with the rates your finance department is using).

In the BLUE cell, D1, you enter your currency, in the format shown in the table. You may want to use data validation to put a dropdown for this, but how to do that will be the subject of another post. For our example, enter “USD” in this cell (don’t enter the quotation marks).

In the ORANGE, D3, cell enter an amount, let’s say 100. This is the number of United States Dollars (USD) that you will convert into pounds sterling.

In the GREEN cell, D5, we’re going to do our VLOOKUP. You start by typing in “=vlookup(” like I have here:

The grey box will appear below showing how this function works. You’ll notice the first thing it says is lookup_value. In this case we’re going to look for “USD” and return the conversion rate next to it. We’ve already entered this in cell D1, so we’ll just point our VLOOKUP to that, like so:

=vlookup(D1,

The comma after D1 is to show Excel that we’ve moved on to the next part of the function, which they described as table_array in their helpful grey box. This is our table of currencies and conversion rates, held in cells A1:B5. Note that you always need to make the first column of this array the place where your lookup values are. In this case, we’re looking for “USD”, and we start the range in column A. So we add this to our formula thus:

=vlookup(D1,A1:B5,

Again, a comma moves us on to the next part, col_index_number. We need to tell Excel how many columns across our table we’re going to look. There are two columns in our table, and we’re looking for the value (the currency conversion rate) in the second column, so we enter the number 2:

=vlookup(D1,A1:B5,2,

Again, remember the comma. This is the last part now. VLOOKUP gives you the option of whether you want to find an exact match for your lookup_value (in our case “USD”) or not. You will almost exclusively want to find an exact match (approximate matches might not always be what you would expect), so you need to enter FALSE here. Try not to worry about this part too much. For the time being always FALSE. As you develop your skills you may find a problem that requires TRUE, but this doesn’t happen so much in common uses an HR practitioner might have. So we end up with this:

=vlookup(D1,A1:B5,2,FALSE)

Notice that I’ve closed the brackets because this is the last part of the formula. I’ve also pressed enter, and you should too. So, what result does this formula give you? If you’ve followed everything so far, the answer should be 1.5875, the exchange rate for USD shown in our table. All you need to do now to make our example useful is multiply this by the value we entered in the orange cell, D3.

So select the green cell again, D5, and click at the end of your formula in the formula bar at the top of the screen. All we’re going to do is add “D3/” to the beginning of our formula, immediately after the =, meaning that we divide D3 by the result of our VLOOKUP. So your formula will now look like this:

=D3/VLOOKUP(D1,A1:B5,2,FALSE)

[showmyads]

The answer should now say 62.99 (and probably a fair few more decimal places), which is how many pounds you would get for $100. You can format this number to get rid of the extraneous decimal places – the shortcut for the formatting dialogue box is CTRL+1.

There is a copy of the finished spreadsheet here.

Was this helpful? Did it work for you? If you have any questions or comments, you can tweet @xlcalibre, join our group on LinkedIn, or contact us on Facebook or Google+.

 

Excel Keyboard Shortcuts – Function Keys

More Excel keyboard shortcuts – the Function keys

F1 – Excel Help

F2 – Edit active cell

F3 – Brings up the Paste Name box – allowing you to easily refer to any named ranges

F4 – Repeats the last action (or in a formula cycles through absolute reference options)

F5 – Go To

F6 – Flicks through the worksheet, ribbon, task pane, and zoom controls

F7 – Spell checker

F8 – Toggle Extended Selection – keeps highlighting cells until you switch it off

F9 – Calculate now (on active worksheet)

F10 – Just like pressing Alt

F11 – Create chart – uses range around the active cell

F12 – Save As

XL FORMULA FOCUS: Redundancy calculations using INDEX and MATCH

Learn Index Match in Microsoft Excel

This post will walk you through how to calculate statutory (in the United Kingdom) redundancy payments. It’s a good example of how you can use the INDEX and MATCH excel functions together to find data in a table. The table is based on one from www.direct.gov.uk (so an official source and correct at time of posting) – if you need to use this for real then you should check that the table has not become out of date.

We’ve got a spreadsheet here to get you started:

You’ll see there are two tabs in the spreadsheet – Calculation and Table. The Calculation tab is where we’re going to create our formulas, which will refer to the redundancy table in the Table tab.

In the Calculation tab we’ve already got things started for you, but it will be up to you to enter the formulas as we go along. The orange cells just contain values, and the blue cells contain formulas. Three blue cells are marked with an XLCalibre X, and that’s where you’ll be creating your formulas.

According to the legislation you need certain information to calculate a redundancy payment: completed year’s of service, age and weekly pay (we have a great post here to help you use formulas to calculate length of service and age). You’ll see there are spaces to put these details in, though we’ve got some example already there. If you enter the annual salary, there’s a formula using the IF function to calculate the weekly salary (with a cap of £430 as per the legislation). We’re not going to worry about this, as we’re focusing on combining the INDEX and MATCH functions…

The INDEX function

Returns a value or reference of the cell at the intersection of a particular row and column in a given range

That’s what Excel says about INDEX. Essentially it means “In this array of cells, what do I find if I look X cells down and Y cells across”. The function is structured like this:

=INDEX(Array, Row number, Column Number)

With the redundancy table we have a perfect example of an array that would make INDEX useful. We need to look down a number of rows to find the age, and then across to find the length of service. If only we knew how to tell Excel how many rows down and columns across it needs to look…

The MATCH function

Returns the relative position of an item in an array that matches a specified value in a specified order

=MATCH(Lookup Value, Lookup Array, Match Type)

So we can use MATCH to find how many rows, and then again to find how many columns. So our final formula will have this structure:

=INDEX(Array, MATCH(Lookup Value, Lookup Array, Match Type), MATCH(Lookup Value, Lookup Array, Match Type))

If that looks complicated don’t worry. We’re going to break it down starting with the first MATCH formula.

MATCH

Our first MATCH will be to find the number of rows down our table to look according to the person’s age. We’re going to put it in cell B10 of our spreadsheet. Start by putting typing

=MATCH(

The first thing we need is the lookup value (i.e. the age we’re going to find). I’ve already put this in cell B8, so we’re just going to point our formula there then add a comma. This means we can just change B8 if we want to change the age lookup value, rather than amending the formula. So we should now be here:

=MATCH(B8,

Now we need to enter the lookup array, which is the list of ages in our Table tab. You can just flick to that tab and select cells A4:A48, then press comma:

=MATCH(B8,Table!A4:A48,

We’re pretty much there now. The final part of the MATCH is called match type, and we are just going to enter 0 and close the bracket of the formula. 0 means that we want the function to find only an exact match for our lookup value, which is what is called for in this case. So here we are:

=MATCH(B8,Table!A4:A48,0)

This formula tells us how many rows down our age range our look up value in cell B8 is, in this case 24.

We now do the same with another MATCH to find the number of columns across to look for length of service. Try it yourself in cell B12, following and adapting the steps we’ve just gone through… …

You should finish with this formula:

=MATCH(B6,Table!B2:U2,0)

INDEX

=INDEX(Array, Row number, Column Number)

We already have our row number and column number from our MATCH formulas, so we can simply refer to those cells like this:

=INDEX(Array, B10, B12)

And now we just need the array that our INDEX is looking at. The reference will be as high and low as the reference in our row MATCH (from 4 to 48) and as far left and right as our column MATCH (from B to U). So the resulting array is B4:U48. Here it is in situ:

=INDEX(Table!B4:U48,B10,B12)

There you have it, your calculation should now work and you can change the age and length of service to your heart’s content. We’ve added a payment calculation so you can see the final result of the calculation.

If you want to go the extra mile and tidy things up you could put your MATCH formulas into your INDEX like so:

=INDEX(Table!B4:U48,MATCH(B8,Table!A4:A48,0),MATCH(B6,Table!B2:U2,0))

And this is now your bona fide INDEX MATCH combo. It may take you a few practices before you remember it every time, but it’ll be well worth the effort.

UPDATE: Now that you’ve learned how to combine INDEX and MATCH, check out this Gantt chart to see another example of how it can be used. Be warned, it’s part of a much bigger formula…