Wednesday, 21 December 2016

Excel Quick Chart Shortcut


Excel Quick Chart Shortcut
You can create a chart quickly without having to use the chart button on the toolbar by pressing the function key F11 whilst inside a range of data.

Year
Price
2010
150
2011
200
2012
250
2013
300
2014
350
2015
400
2016
450
2017
500

Click anywhere inside the table above.
Then press F11.


Monday, 19 December 2016

How to separate first name middle name and last name in excel


Split Forename and Surname    
               
The following formulas are useful when you have one cell containing text which needs to be split up.
One of the most common examples of this is when a person’s Forename and Surname are entered in full into a cell.
               
The formula uses various text functions to accomplish the task.
Each of the techniques uses the space between the names to identify where to split.

Finding the First Name

Full Name
First Name
Paul Turner
Paul
 =LEFT(C14,FIND(" ",C14,1))
Alex McLaren
Alex
 =LEFT(C15,FIND(" ",C15,1))
Claire Connelly
Claire
 =LEFT(C16,FIND(" ",C16,1))

Finding the Last Name

Full Name
Last Name
Paul Turner
Turner
 =RIGHT(C22,LEN(C22)-FIND(" ",C22))
Alex McLaren
McLaren
 =RIGHT(C23,LEN(C23)-FIND(" ",C23))
Claire Connelly
Connelly
 =RIGHT(C24,LEN(C24)-FIND(" ",C24))

Finding the Last name when a Middle name is present
  • The formula above cannot handle any more than two names.
  • If there is also a middle name, the last name formula will be incorrect.
  • To solve the problem you have to use a much longer calculation.

Full Name
Last Name
Billie Claire Darby
Darby
Paul Timothy Calvin
Calvin
Darren Michael Smith
Smith
 =RIGHT(C37,LEN(C37)-FIND("#",SUBSTITUTE(C37," ","#",LEN(C37)-LEN(SUBSTITUTE(C37," ","")))))

Finding the Middle name

Full Name
Last Name
Billie Claire Darby
Claire
Paul Timothy Calvin
Timothy
Darren Michael Smith
Michael
 =LEFT(RIGHT(C45,LEN(C45)-FIND(" ",C45,1)),FIND(" ",RIGHT(C45,LEN(C45)-FIND(" ",C45,1)),1))

Friday, 16 December 2016

How to calculate time in excel



Time Calculation 

Excel can work with time very easily.
Time can be entered in various different formats and calculations performed.
There are one or two oddities, but nothing which should put you off working with it.
See the TimeSheet example for an example.

Typing time
When time is entered into worksheet it should be entered with a colon between the hour and the minutes, such as 12:30, rather than 12.30

1:30
12:30
20:15
22:45
  • Excel can cope with either the 24hour system or the am/pm system.
  • To use the am/pm system you must enter the am or pm after the time.
  • You must leave a space between the number and the text.

1:30 AM
1:30 PM
10:15 AM
10:15 PM

Finding the difference between two times
You can subtract two time values to find the length of time between.

Start
End
Duration
1:30
2:30
1:00
 =D24-C24
8:00
17:00
9:00
 =D25-C25
8:00 AM
5:00 PM
9:00 AM

If the result is not shown correctly, you may need to reformat the answer. Look at the section about formatting further in this worksheet.

Adding time
  • You can add time to find a total time.
  • This works well until the total time goes above 24 hours.
  • For totals greater than 24 hours you may need to apply some special formatting.

Start
End
Duration
1:30
2:30
1:00
8:00
17:00
9:00
7:30 AM
5:45 PM
10:15
20:15

Formatting time
  • When time is added together the result may go beyond 24 hours.
  • Usually this gives an incorrect result, as in the example below.
  • To correct this error, the result needs to be formatted with a Custom format.

Example 1 : Incorrect formatting

Start
End
Duration
7:00
18:30
11:30
8:00
17:00
9:00
7:30
17:45
10:15
Total
6:45
 =SUM(E49:E51)

Example 2 : Correct formatting

Start
End
Duration
7:00
18:30
11:30
8:00
17:00
9:00
7:30
17:45
10:15
Total
6:45
 =SUM(E56:E58)

How To Apply Custom Formatting
The custom format for time use a pair of square brackets [hh] on either side of the hours indicators.

1. Click on the cell which needs the format.
2. Choose the Format menu.
3. Choose Cells.
4. Click the Number tag at the top right.
5. Choose Custom.
6. Click inside the Type: box.
7. Type [hh]:mm as the format.
8. Click OK to confirm.










Wednesday, 14 December 2016

Excel Timesheet Templates



This is simple example of a Time sheet.

Instructions :
Type the week start date in cell C3, the Week beginning.
Use the format dd/mm/yy, the name of the day will appear automatically.
The date is then passed down to the Day column.

Type the amount of hours you are expected to work in G3, the Normal Hours.
This is used later to calculate if have worked over or under the required hours.

Type the times you arrive and leave work in the appropriate columns.
Use the format of hh:mm.

Note :
  • The Total Hours cell has been formatted as [hh]:mm.
  • This ensures the total hours can be expressed as a value above 24 hours.
  • If the [hh]:mm format had not been used the Total Hours would show as : (17:00)
  • If the [hh]:mm format does not show in the cell format dialog box
  • on your computer, it can be created using Format, Cells, Number, Custom.

Tuesday, 13 December 2016

How to use brackets in Excel formulas

How to use brackets in Excel formula's

Sometimes you will need to use brackets, (also known as 'braces'), in formula.
This is to ensure that the calculations are performed in the order that you need.
The need for brackets occurs when you mix plus or minus with divide or multiply.

  • Mathematically speaking the * and / are more important than + and - .
  • The * and / operations will be calculated before + and - .
Example 1 : The wrong answer !

5
10
2
25
=G6+G7*G8
  • You may expect that 5 + 10 would equal 15 and then 15 * 2 would equal 30.
  • But because the * is calculated first Excel sees the calculation as 10 * 2 resulting in 20 and then 20 + 5 resulting in 25
Example 2 : The correct answer.

5
10
2
30
=(G6+G7)*G8

By placing brackets around (5+10) Excel performs this part of the calculation first, resulting in 15. Then the 15 is multiplied by 2 resulting in 30.

Monday, 12 December 2016

How do use autosum shortcut key in Excel


Autosum shortcut key in Microsoft Excel

Auto sum shortcut key in Microsoft Excel. Quickly create a Microsoft Excel formula to get the sum of all cells by highlighting the cells you want to know the value of and pressing Alt and = together.

Instead of using the AutoSum button from the toolbar, you can press Alt and = to achieve the same result.

Try it here :

Move to a blank cell in the Total row or column, then press Alt and =.
or
Select a row, column or all cells and then press Alt and =.

Mon
Tue
Wed
Total
Jan
10
20
30
60
Feb
20
30
40
90
Mar
30
40
50
120
Apr
40
50
60
150
Total
100
140
180