This task can be accomplished using TODAY and NOW functions.
=TODAY() Current date
=NOW() Current date and time
The date and time used are taken from the computer's system clock.
TODAY() and NOW() functions do not update continuously. They update their respective values only when the worksheet is calculated or when a macro containing the function is run.
2014年5月4日 星期日
Calculate the time difference between two times
Calculate the time difference between two times
=TEXT(C4-B4,"H") Hours between two times.
=TEXT(C5-B5,"HH:MM") Hours and minutes between two times.
=TEXT(C6-B6,"HH:MM:SS") Hours, minutes, and seconds between two times.
=TEXT(C4-B4,"H") Hours between two times.
=TEXT(C5-B5,"HH:MM") Hours and minutes between two times.
=TEXT(C6-B6,"HH:MM:SS") Hours, minutes, and seconds between two times.
Assigning RANKs to numbers in a list
RANK function, assigns a rank based on the size of a number compared to other numbers in a list.
The syntax for RANK function is:
= RANK ( number, ref, [order] )
Number - Cell reference of the number to be ranked.
Ref - Range of cells to use in ranking the number.
Order - Whether the number is ranked in ASCENDING or DESCENDING order.
Type "0" (zero) to rank in descending order (largest to smallest).
Type "1" (one) to rank in ascending order (smallest to largest).
Example: Using RANK Function
1. Data table contains names (B4:B13) and scores (C4:C13) of 10 students
2. Enter the rank function in cell D4
3. Drag select cells C4 to C13 in the spreadsheet to enter the range into the dialog box.
4. In [order] in the dialog box, type 0 (zero) to rank the number in descending order.
Note: Lock the function while dragging it through Column D so that the ref range does not get dragged along. Use $ for the same. Example : =RANK(C5,$C$4:$C$13,0).
One can either type $ each time before C, 4, C & 5 or use F4 key before C4 and C13 to insert $
The syntax for RANK function is:
= RANK ( number, ref, [order] )
Number - Cell reference of the number to be ranked.
Ref - Range of cells to use in ranking the number.
Order - Whether the number is ranked in ASCENDING or DESCENDING order.
Type "0" (zero) to rank in descending order (largest to smallest).
Type "1" (one) to rank in ascending order (smallest to largest).
Example: Using RANK Function
1. Data table contains names (B4:B13) and scores (C4:C13) of 10 students
2. Enter the rank function in cell D4
3. Drag select cells C4 to C13 in the spreadsheet to enter the range into the dialog box.
4. In [order] in the dialog box, type 0 (zero) to rank the number in descending order.
Note: Lock the function while dragging it through Column D so that the ref range does not get dragged along. Use $ for the same. Example : =RANK(C5,$C$4:$C$13,0).
One can either type $ each time before C, 4, C & 5 or use F4 key before C4 and C13 to insert $
VALUE Function - Convert number displayed as Text to Value
Excel mixes up data types at times and sees numerical data as text data. For eg. when you import data into Excel from another program / internet.
In such a situation, functions like SUM or AVERAGE lead to calculation errors.
VALUE Function converts numbers seen as Text to Values and make them function normally for SUM, AVERAGE and other numerical functions.
The syntax for the VALUE function is:
= VALUE ( Text )
Text – Data to be converted. It can be actual data in quotation marks or a cell reference.
In such a situation, functions like SUM or AVERAGE lead to calculation errors.
VALUE Function converts numbers seen as Text to Values and make them function normally for SUM, AVERAGE and other numerical functions.
The syntax for the VALUE function is:
= VALUE ( Text )
Text – Data to be converted. It can be actual data in quotation marks or a cell reference.
EXCEL : CONCATENATE FUNCTION
the CONCATENATE function, is used to join two or more words or text strings together.
The syntax for the CONCATENATE function is:
= CONCATENATE ( text1, text2, ... )
Based on the Excel spreadsheet, the CONCATENATE function would return the following:
=CONCATENATE(B2, C2) would return "Elephant"
The syntax for the CONCATENATE function is:
= CONCATENATE ( text1, text2, ... )
Based on the Excel spreadsheet, the CONCATENATE function would return the following:
=CONCATENATE(B2, C2) would return "Elephant"
How to password protect a few cells in a worksheet?
All cells in an Excel spreadsheet are locked by default. This makes it very easy to protect data by simply applying the PROTECT SHEET or PROTECT WORKBOOK option.
Unlocking selected cells permits changes to be made to these cells after the protect sheet/workbook option has been applied.
Step 1: Unlock Cells in Worksheet
1. Select cells which you don’t want to lock (Example – Cells B2 : E3 – See Image)
2. Click on the HOME tab.
3. Choose the FORMAT option on the ribbon to open the drop down list.
4. Click on LOCK CELL option.
5. The LOCK CELL option works like an ON/OFF button. Since all cells are initially locked in the worksheet, clicking on the option has the affect of Unlocking the selected cells B2 : E3.
Step 2: Password Protect a Worksheet
1. Click on the HOME tab.
2. Choose the FORMAT option on the ribbon to open the drop down list.
3. Click on PROTECT SHEET option to open the Protect Sheet dialog box.
4. Enter Password and Protect .
Unlocking selected cells permits changes to be made to these cells after the protect sheet/workbook option has been applied.
Step 1: Unlock Cells in Worksheet
1. Select cells which you don’t want to lock (Example – Cells B2 : E3 – See Image)
2. Click on the HOME tab.
3. Choose the FORMAT option on the ribbon to open the drop down list.
4. Click on LOCK CELL option.
5. The LOCK CELL option works like an ON/OFF button. Since all cells are initially locked in the worksheet, clicking on the option has the affect of Unlocking the selected cells B2 : E3.
Step 2: Password Protect a Worksheet
1. Click on the HOME tab.
2. Choose the FORMAT option on the ribbon to open the drop down list.
3. Click on PROTECT SHEET option to open the Protect Sheet dialog box.
4. Enter Password and Protect .
Shortcut to insert a new worksheet into a workbook ALT+SHIFT+F1
Shift + F11 works too
ALT+I+W also works
ALT+I+W also works
Shortcut to insert comment to a cell Shift + F2
Shortcut to delete it
Right click on mouse and press M
Or use keyboard near windows button and presss M
Right click on mouse and press M
Or use keyboard near windows button and presss M
Split First Name and Last Name
First formula contain 2 function.
FIND - determines the position of your delimiter
LEFT - returns the character from start upto the delimiter
2nd function
SUBSTITUTE - removes the first name from your complete name list
Highlight the column with the data you want to SPLIT
Go to DATA > Text to Column
Go to DATA > Text to Column
Choose Delimited then click next.
Choose your delimiter, in my example, its SPACE, then click next.
Choose your desired format and choose your destination cell.
You just need to highlight the first cell where the new data will start (eg =D6).
*For multiple first or last name, it will be separated accordingly.
You just need to highlight the first cell where the new data will start (eg =D6).
*For multiple first or last name, it will be separated accordingly.
訂閱：
文章 (Atom)