Saturday, May 31, 2014

Excel Shortcut Keys

Comprehensive List of Excel Keyboard Shortcuts

I am sharing some of the most important Excel shortcuts, which makes your work simpler & faster. These are very useful and extremely time saving ones. You may want to remember a few to boost your productivity.

Select the whole column
CTRL + SPACE

Select the whole row
SHIFT + SPACE

Select table
SHIFT + CTRL + SPACE bar

Save File
CTRL + s

Select visible cells only
ALT + ;

Select entire region
CTRL + A

Select range from start cell to far leftSHIFT + Home

Select range from start cell to end in direction of arrow
SHIFT + End + arrow

Select a continuous range of data (e.g. pivot), no matter where your cursor is
CTRL + *

Select blank cells
F5 + ALT + S + K + ENTER

Select all cells with comments
CTRL + SHIFT + O

Select all cells that are directly or indirectly referred to by formulas in the selection
CTRL + SHIFT + {

Select all cells with formulas that refer directly or indirectly to the active cell
CTRL + SHIFT + }

Selects all the way to a1 from cursor position
CTRL + SHIFT + HOME

Select cells in the direction of arrow
CTRL + SHIFT + Arrow

Previous sheet
CTRL + Page Up

Next sheet
CTRL + Page Down

Launch GO TO Dialog (from here you can select special or jump to a cell or range)
F5

Go to top left (will go to top left of freezed pane if set)
CTRL + Home

Go to last non-blank cell
CTRL + end

Go to previous sheet
CTRL + PgUp

Go to next sheet
CTRL + PgDn

Print
CTRL + p

Toggle between workbooks in a given session of excel.
CTRL + TAB

Change the type of cell reference from relative to absolute or semi-absolute
F4

Repeat whatever you did last
F4

Debug portions of a formula (select and press)
F9

Sum range (Formulas)
ALT + =

Enter array formula (Formulas)
CTRL + SHIFT + Enter

Select array formula range (Formulas)
CTRL + /

Display range names (can be used when typing formulas) (Formulas)
F3

Evaluate formulas. (its easy to remember when working with some “tuf” formulas!) (Formulas)
ALT + TUF

Copy a formula from above cell and edit (Formulas)
CTRL + '

Display the formula palette after you type a valid function name in a formula (Formulas)
CTRL + A (while writing a formula)

Alternate between displaying cell values and displaying cell formulas (Formulas)
CTRL + ` (Single Left Quotation Mark)

Calculate formulas (Formulas)
F9

Select all precedent cells (Formulas)
CTRL + [

Select all dependent cells (Formulas)
CTRL + ]

Format Selection (cells, objects, charts) (Formatting)
CTRL + 1

Bold a cell’s content (Formatting)
CTRL + B

Format Painter – Paste formats from selection (Formatting)
ALT + EST

Format as number with 2 dp (Formatting)
CTRL + SHIFT + 1

Format as local currency (Formatting)
CTRL + SHIFT + 4

Format as percentage with 0 dp (Formatting)
CTRL + SHIFT + 5

Hide row (Formatting)
CTRL + 9

Hide column (Formatting)
CTRL + 0

Unhide row (Formatting)
CTRL + SHIFT + 9

Unhide column (Formatting)
CTRL + SHIFT + 0

Display the style command format menu (Formatting)
ALT + '

Sets/removes strikeout in current cell (Formatting)
CTRL + 5

Show/hide the top bar when you have a group (Formatting)
Crtl + 8

Single border around selected cells (Formatting)
CTRL + SHIFT + 7

Sort (Formatting)
ALT + DS

Insert hyperlink (Formatting)
CTRL + K

Freeze panes (Formatting)
ALT + WFF

Remove grid lines or (alt+t)ov(alt+g)[enter] (Formatting)
ALT + WVG (2007+)

To wrap lines (Formatting)
ALT + HW (2007+)

Save as (Excel Options)
F12

Collapse the ribbon (press again to expand) (Excel Options)
CTRL + F1

Opens print preview (Excel Options)
CTRL + F2

Maximize the current window (Excel Options)
ALT + SPACE X

Activate next window (Excel Options)
ALT + TAB

Activate previous window (Excel Options)
ALT + SHIFT + TAB

Close an excel workbook (Excel Options)
crtl + F4

Split screens (Excel Options)
ALT + W + S

Create a pivot table in new sheet (of course after selecting the range) (Everything Else)
ALT + DPF

Create a pivot table in the same sheet. (Everything Else)
ALT + DPN

Show visual basic editor (Everything Else)
ALT + F11

Macro dialog (Everything Else)
ALT + F8

Apply/remove filter (Everything Else)
ALT + DFF

Keep filter on columns, but show all rows (Everything Else)
ALT + DFS

Insert pivot table (Everything Else)
ALT + NVT

Turn filter on or off (Everything Else)
CTRL + SHIFT + L

Paste values only (Editing)
ALT + ESV

Edit a cell, place cursor at the end (Editing)
F2

Show in-cell drop down with previously entered values (Editing)
ALT + Down arrow

Fills down value from cell above (Editing)
CTRL + D

Add a comment or Edit comment (Editing)
SHIFT + F2

Insert new sheet (Editing)
SHIFT + F11

Insert row (Editing)
CTRL + +

Delete row (Editing)
CTRL + -

Copy (Editing)
CTRL + C

Paste (Editing)
CTRL + V

Cut (Editing)
CTRL + X

Undo (Editing)
CTRL + Z

Get a line break inside the cell (Editing)
ALT + Enter (while editing the cell)

Clear all contents (Editing)
ALT + EAA

Copy (Editing)
CTRL + insert

Paste (Editing)
SHIFT + Insert

Make chart/pivot chart (Editing)
F11

Edit a cell in Apple Macs (Editing)
CTRL + U

Copy the value from the cell above the active cell into the cell or the formula bar (Editing)
CTRL + SHIFT + "

Copies whatever is in the cell to the left of it. (Editing)
CTRL + R

Delete box (cell, row, column) (Editing)
ALT + ED

Insert box (cell, row, column) (Editing)
ALT + IE

Enter current date (Auto Complete)
CTRL + ;

Enter current time (Auto Complete)
SHIFT + CTRL + :

Tuesday, May 27, 2014

F4 Magic in MS-Excel

F4 Magic:

The F4 key provides a shortcut for creating absolute or mixed references in a formula.

Once you have selected a cell to enter it into a formula:

- Press F4 key once to create an absolute reference, e.g. $A$8. This 'locks' or 'fixes' the cell into the formula so that the reference to this cell doesn't change when the formula is copied to other cells in the spreadsheet.

- Press F4 twice to create a mixed reference which locks Excel onto a particular row, e.g. A$8

- Press F4 three times to create a mixed reference with locks onto a particular column, e.g. $A8

- Press F4 again make the cell a relative reference again !!

Try it out....

Thursday, May 22, 2014

ODD Function in MS-Excel

C
D
3
Number
Rounded To
Next Odd
4
2
3
 =ODD(C4)
5
2.4
3
 =ODD(C5)
6
2.9
3
 =ODD(C6)
7
3
3
 =ODD(C7)
8
3.4
5
 =ODD(C8)
9
3.9
5
 =ODD(C9)

What Does It Do?
This function rounds a number up to the next highest whole odd number.

Syntax
=ODD(NumberToBeRounded)

Formatting
No special formatting is needed.

N Function in MS-Excel

Learn how to use the Excel N function with syntax and examples.

C
D
3
Original
Converted
4
1
1
 =N(C4)
5
3 1/2
3.5
 =N(C5)
6
3.5
3.5
 =N(C6)
7
3.50%
0.035
 =N(C7)
8
25-Dec-98
36154
 =N(C8)
9
TRUE
1
 =N(C9)
10
FALSE
0
 =N(C10)
11
Hello
0
 =N(C11)
12

0
 =N(C12)


What Does It Do?
This function converts a numeric entry to its mathematical value.
Anything which will not convert is shown as 0 zero.
Excel does not really need this function, due to the fact that Excel calculates in this way naturally. The function is included for compatibility with other spreadsheet programs.

Syntax
=N(NumericEntry)

Formatting
No special formatting is needed.

MONTH Function in MS-Excel

C
D
3
Original Date
Month
4
1-Jan-98
1
 =MONTH(C4)
5
1-Jan-98
January
 =MONTH(C5)

What Does It Do?
This function extracts the month from a complete date.

Syntax
=MONTH(Date)

Formatting
Normally the result will be a number, but this can be formatted to show the actual month by using Format,Cells,Number,Custom and using the code mmm or mmmm.

Example
The =MONTH function has been used to calculate the name of the month for your birthday.

F
20
Please enter your date of birth in the format dd/mm/yy
3/25/1962
21
You were born in
January
 =MONTH(F20)

MINUTE Function in MS-Excel

C
D
3
Number
Minute
4
5/22/2014 21:56
56
 =MINUTE(D4)
5
9:15:00 PM
15
 =MINUTE(D5)
6
0.02
28
 =MINUTE(D6)
7
0.52
28
 =MINUTE(D7)
8
1.52
28
 =MINUTE(D8)

What Does It Do?
The function will show the minute of the hour based upon a time or a number.
Only the fraction part of the number is used as it is this which relates to time of day.

Syntax
=MINUTE(Number)

Formatting
The result will be shown as a normal number between 0 and 59.

Example
The =REPT() function has been used to make a digital display for the current time.
The time functions of =HOUR(), =MINUTE() and =SECOND() have been used in conjunction with the =NOW() as the basis for the number of repeats.
To update the clock press the function key F9.

C
26
Clock
27
Hour 
|||||||||||||||||||||| 22
28
Minute 
| 01
29
Second 
|||||||||||||||||||||||||||||||||||||||||||||||| 48
 =REPT("|",HOUR(NOW()))&" "&TEXT(HOUR(NOW()),"00")
 =REPT("|",MINUTE(NOW()))&" "&TEXT(MINUTE(NOW()),"00")
 =REPT("|",SECOND(NOW()))&" "&TEXT(SECOND(NOW()),"00")

Related Information
To convert a time in hh:mm format to decimal format.
F
38
Enter a time in hh:mm format :
2:45
40
The same time converted to a decimal :
2.75
 =F38*24
42
To extract the hours as a decimal :
2
 =INT(F38*24)
44
To extract the minutes as a decimal :
0.75
 =MOD(F38*24,1)

To convert a time in decimal format to hh:mm format.
F
49
Enter a time in decimal format :
3.75
51
The same time converted to hh:mm format is :
3:45
 =F49/24
53
To extract the hours in hh:mm format :
3:00
 =INT(F49)/24
55
To extract the minutes in hh:mm format :
0:45
 =MOD(F49,1)/24

The three formula above have also been formatted as hh:mm using the Format, Cells, Number, Time command.


NOW function in MS-Excel

The current Date and Time
5/22/2014 21:18
 =NOW()
41781.88886
 =NOW()

What Does It Do?
This function shows the current date and time. The result will be updated each time the worksheet is opened and every time an entry is made anywhere on the worksheet.

Syntax
=NOW()

Formatting
The result will be shown as a date and time. If it is formatted to show as a number the integer part is used for the date and the decimal portion represent the time.