All posts by XLCalibre

HR Dashboard Tip: Copy & paste chart formatting in Excel 2007

Sometimes you want to show several graphs with the same formatting or brand colours in a single HR management dashboard. After fiddling around selecting the exact design for your first chart you realize you have to remember it all and click through all the same options again. But there is another way!
First you need to click on your first, formatted graph and press CTRL+C to copy it. Then select the new, unformatted graph and press Alt H,V,S,T and then Enter. It may seem a bit long for a shortcut, but if you deal with dashboards regularly it can save a lot of time and ensure that your presentation is consistent. If you really can’t remember the shortcut keys, on the left side of the Home tab you’ll find the Paste dropdown. Select paste special and then formats. You’ll still save time.

Excel Keyboard Shortcuts – Control with Symbols

Another selection of keyboard shortcuts. Press Control and the following symbols:

 

` – Toggles between displaying cell values and formulas in the current worksheet.

~ – Cell format is General.

! – Cell format is Number with two decimal places, commas, and minus symbol for negative values.

@ – Cell format is Time hour and minute, and AM or PM.

# – Cell format is Date (dd-mmm-yy).

$ – Cell format is Currency (with 2 decimal places and negatives in brackets).

% – Cell format is Percentage (with no decimal places).

^ – Cell format is Exponential with two decimal places.

& – Adds outline border on selected cells.

* – Selects the range of cells around the active cell.

( – Unhides all hidden rows in the selection.

) – Unhides all hidden columns in the selection.

– – Delete the selected cells.

_ – Removes outline border on selected cells.

+ – Insert cells.

; – Enters the current date.

: – Enters the current time.

‘ – Copies a formula from the cell above.

” – Copies the value from the cell above.

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…

XL FORMULA FOCUS: Need to calculate length of service or age? Use YEARFRAC

Length of service is used all the time in HR – for example long service awards or calculating redundancy payments. Those of you fortunate enough to have a solid HR system in place will hopefully have some automation to keep you aware of upcoming key dates, but for others Excel can be the next best thing.

We will be using the YEARFRAC function:

YEARFRAC(start_date, end_date, [basis])

 

Let’s do an example…

1. In cell A1, enter your date of birth (for arguments sake use the format dd/mm/yyyy).

2. In cell B1, enter  =TODAY()  . This will return today’s date, which will update automatically as time goes on.

3. In cell C1, enter  =YEARFRAC(A1,B1,1)  .

 

The number one at the end of the formula is what is referred to above as the ‘basis’. It tells Excel on what basis to calculate the difference between start_date and end_date. Personally I’ve always used 1, which is described as Actual, and it has always been accurate and fit for purpose. If you’re unsure, while working on the YEARFRAC formula hit the fx button next to the formula bar, and then select ‘help on this function’. It will give you a bit more information about the YEARFRAC function and detail the various basis options.

 

So, cell C1 should now show you the number of years between your start date and end date (in this case how old you are today). Depending on how you format this cell (and assuming its not your birthday – if it is happy birthday!) you will probably have a number of decimal places shown. This is fine if you want a very precise figure, but in many practical applications you will want completed years (e.g. I am 30 years old, not 30.153663 years old). So for this we’re going to use the INT function (short for Integer).

 

4. In cell D1, enter INT(C1)  .

 

You will now have your age shown as an integer.

If you wanted to you could condense this into fewer cells.

 

5. Select cells B1, C1 and D1 and press delete.

6. In cell B1, enter this:

=INT(YEARFRAC(A1,TODAY(),1))

You can now use this formula repeatedly, changing the contents of cell A1 to any date that you want.

If you’ve made it this far Well Done! You’ve used 3 different functions in one formula to give a practical output.

If you liked that, here’s a post about EDATE, a really straightforward date function that I use to calculate the of probation periods or notice periods.

Learn Any Area of Excel using these 80 Links

Here’s a fantastic post from Chandoo, a prolific Excel blogger that I follow: http://chandoo.org/wp/2011/12/12/learn-excel-by-topic/

The post contains links to 80 different lessons across a wide spectrum of Excel functionality.

Check it out and see what you can learn.

If there’s an Excel topic you want to know more about just tweet @xlcalibre, e-mail admin@xlcalibre.com.

Just for fun… Heads or Tails?

You have a row of 1,000 coins, all of which are heads up. You flip the second, fourth, sixth, and all other even coins over so that they’re tails. Then you flip over the third, sixth, and all other coins which are multiples of three. Now you do this for every fourth coin, every fifth coin, etcetera, all the way up to every thousandth coin. Which coins are heads up, and why?

I heard this puzzle mentioned on TV the other day on Growing Pains of a Teenage Genius, and decided to solve it with Excel.

This spreadsheet lets you change the number of coins, or even change the number of sides of the object (a coin is a two sided object, but you could change it to a six sided dice for example).

Heads or Tails

#growingpainsofateenagegenius #excel #puzzle #logic

Excel Keyboard Shortcuts – Easy as Control + 123…

Continuing our series of Microsoft Excel Keyboard Shortcuts, we’re looking at what the number keys do when you press them with Control…

1 – Format cells

2 – Bold

3 – Italic

4 – Underline

5 – Strikethrough

6 – Hide/Unhide Object

7 – Hides or displays the standard toolbar

8 – Hides or displays the outline symbols

9 – Hide selected row(s)

0 – Hide selected column(s)

A-Z of Control Keyboard Shortcuts for Microsoft Excel

Here’s a list of Excel keyboard shortcuts. Just hold control and press the letter and hey presto! We’ll look at some of them in more detail in other posts, but this gives you a quick summary…

A – Select All

B – Bold

C – Copy

D – Fill down

E – N/A

F – Find

G – Go to

H – Replace

I – Italic

J – N/A

K – Insert hyperlink

L – Create table

M – N/A

N – New workbook

O – Open

P – Print

Q – N/A

R – Fill across

S – Save

T – Create table

U – Underline

V – Paste

W – Close workbook

X – Cut

Y – Redo

Z – Undo