Split Forename and Surname

The following formula 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 persons Forename and Surname are entered in full into a cell.
The formula use 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

Finding the Last Name

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.

Finding the Middle name

SUM using names in Excel

You can use the names typed at the top of columns or side of rows in calculations
simply by typing the name into the formula.

Try this example:
Go to cell C16 and then enter the formula =SUM(jan)
The result will show.
This formula can be copied to D16 and E16, and the names change to Feb and Mar.

If it does not work !
The feature may have been switched off on your computer.
You can switch it on by using Tools, Options, Calculation, Accept Labels in Formula.

Instant Charts in Excel using Shortcut key

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.

Click anywhere inside the table above.
Then press F11.

Filename formula in Excel

There may be times when you need to insert the name of the current workbook
or worksheet in to a cell.

This can be done by using the CELL() function, shown below.
C:\Downloads\[excel_formulas_108.xls] Filename formula
 =CELL("filename")

The problem with this is that it gives the complete path including drive letter and folders.
To just pick out the workbook or worksheet name you need to use text functions.

To pick the Path.
C:\Downloads\
 =MID(CELL("filename"),1,FIND("[",CELL("filename"))-1)

To pick the Workbook name.
excel_formulas_108.xls
 =MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-1)

To pick the Worksheet name.
 Filename formula
 =MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)

Brackets in Excel Formula

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 !

You may expect that 10 + 20 would equal 30
And then 30 * 2 would equal 60

But because the * is calculated first Excel sees the
calculation as 20 * 2 resulting in 40
And then 10 + 40 resulting in 50

Example 2 : The correct answer.

By placing brackets around (10+20) Excel performs this
part of the calulation first, resulting in 30
Then the 30 is multipled by 2 resulting in 60

Age Calculation in Excel

You can calculate a persons age based on their birthday and todays date.
The calculation uses the DATEDIF() function.
The DATEDIF() is not documented in Excel 5, 7 or 97, but it is in 2000.
(Makes you wonder what else Microsoft forgot to tell us!)

You can put this all together in one calculation, which creates a text version.
Age is 50 Years, 4 Months and 20 Days
 ="Age is "&DATEDIF(C8,TODAY(),"y")&" Years, "&DATEDIF(C8,TODAY(),"ym")&" Months and "&DATEDIF(C8,TODAY(),"md")&" Days"

Another way to calculate age
This method gives you an age which may potentially have decimal places representing the months.
If the age is 20.5, the .5 represents 6 months.

Auto Sum Shortcut Key

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

Try it:
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 =.

Show all formulas in Excel

You can view all the formula on the worksheet by pressing Ctrl and `.
The ' is the left single quote usually found on the key to left of number 1.

Press the same combination to see the original view.