You Can Be An Excel Guru Too!

Shortcuts and More shortcuts!
Tips and More Tips!

Nice Excel Reference to get make you fall for Excel
www.exceltip.com
www.mrexcel.com
www.functionx.com


How do i select entire list using shortcut?
Press : CTRL + SHIFT + Down Arrow

How do i convert date into text with my format?
=text(select date cell, "dddd")

how do i convert my currency to RM?
1. select cells
2. format --> cells
3. Custom
4. Type "RM" #,###0.00
5. OK
show me

How do i convert my currency to ¥?
1. select cells
2. format --> cells
3 Custom
4 Type press ALT with 157 #,###0.00
5. OK















How to lock only one cell?
1. select all the cells in the worksheets. Press Ctrl + A.
2. go to Format --> Cells...
3. from the Protection Tab, remove the locked cell, i.e unchecked the locked cell
4. click OK
5. select the cell you wish to lock, that only one cell.
6. go back to format --> cells...
7. from the protection tab, check the locked cell.
8. click OK
9. go to Tools --> Protection --> Protect sheet...
10. enter password, reconfirm password
11. OK



How do i toggle between fill series and copy cells?

if u drag using the + sign and the number increase, then u drag with pressing down your control key.
1. select the cell to drag down.
2. point to the bottom right of the cell u select and u see the fill handle
3. dont drag yet, press down ctrl key, u will see another plus sign above the fill handle
4. drag down while holding your ctrl key



Reference in Excel

* Relative Reference E7

* Absolute Reference $E$7

* Mixed Reference E$7 or $E7





how do i find years of service?
=( todays date - hire date )/ 365
=(now() - C6)/365


How do i highlight(format to different color) hire date based on years of service?
1. select range of dates
2. Format --> Conditional Formatting
3. Change "value is" to "formula is"
4. = (now()-first hire date cell)/365> reference cell
5. Format cell - bold, red text
6. OK

How do I change date to serial number?
1. select date
2. press CTRL+SHIFT+ ~


free fonts
www.007fonts.com


How do i record my RM macro?
Go to tools --> macro --> record new macro or bring out visual basic toolbar
1. Start Recording
2. Format --> Cells...
3. Custom Format
4. Type "RM" #,##0.00
5. OK
6. Stop Recording


How do i record cent symbol macro?
Go to tools --> macro --> record new macro or bring out visual basic toolbar
1. Start Recording
2. Format --> Cells...
3. Custom Format
4. Type #,##0 press ALT with 155
5. OK
6. Stop Recording

how do i record my header and footer using macro?
Go to tools --> macro --> record new macro or bring out visual basic toolbar
1. Start Recording
2. View --> Header & Footer
3. Click on "Custom Header"
4. Type Company Name
5. Ok
6. Choose Page 1 of ? from Footer List
7. OK
8. Stop Recording