Help with the most common Excel shortcut keys

excel

#1

Hi Team,

Please help with the list of most common shortcut keys and functions to perform regular manipulations and processing, It will help me a lot to accelerate my excel data manipulations.

Thanks,
Pankaj


#2

Pankaj,

Below is list of common shortcut keys and functions, those will definitely help you to improve your excel skills.

Shortcut Keys:

KEY	DESCRIPTION
CTRL+1	Displays the Format Cells dialog box.
CTRL+9	Hides the selected rows.
CTRL+0	Hides the selected columns.
CTRL+A	"Selects the entire worksheet.
CTRL+B	Applies or removes bold formatting.
CTRL+C	Copies the selected cells.
CTRL+D	Uses the Fill Down command to copy the contents and format of the topmost cell of a selected range into the cells below.
CTRL+F	Displays the Find and Replace dialog box, with the Find tab selected.
CTRL+G	Displays the Go To dialog box.
CTRL+H	Displays the Find and Replace dialog box, with the Replace tab selected.
CTRL+I	Applies or removes italic formatting.
CTRL+N	Creates a new, blank workbook.
CTRL+O	"Displays the Open dialog box to open or find a file.
CTRL+SHIFT+O selects all cells that contain comments."
CTRL+P	"Displays the Print dialog box.
CTRL+SHIFT+P opens the Format Cells dialog box with the Font tab selected."
CTRL+S	Saves the active file with its current file name, location, and file format.
CTRL+U	"Applies or removes underlining.
CTRL+SHIFT+U switches between expanding and collapsing of the formula bar."
CTRL+V	Paste the copied content
CTRL+W	Closes the selected workbook window.
CTRL+X	Cuts the selected cells.
CTRL+Y	Repeats the last command or action, if possible.
CTRL+Z	"Uses the Undo command to reverse the last command or to delete the last entry that you typed.
CTRL+PgUp	Switches between worksheet tabs, from left-to-right.
CTRL+PgDn	Switches between worksheet tabs, from right-to-left.
CTRL+SHIFT+(	Unhides any hidden rows within the selection.
CTRL+SHIFT+)	Unhides any hidden columns within the selection.
F1	 Displays the Microsoft Office Excel Help task pane.
F2	  Activate the edit mode of cell
F9	 Calculates all worksheets in all open workbooks.
SHIFT+F9 calculates the active worksheet.
CTRL+ALT+F9 calculates all worksheets in all open workbooks, regardless of whether they have changed since the last calculation."
PAGE DOWN	Moves one screen down in a worksheet.
CTRL+PAGE DOWN moves to the next sheet in a workbook.
PAGE UP	Moves one screen up in a worksheet.
CTRL+PAGE UP moves to the previous sheet in a workbook.
SPACEBAR	In a dialog box, performs the action for the selected button, or selects or clears a check box.
CTRL+SPACEBAR selects an entire column in a worksheet.
SHIFT+SPACEBAR selects an entire row in a workshee

Common Function

• Sum
Definition: Adds all the numbers given as an argument
Syntax: Sum(Numerical Aruguments)
Example: =SUM(d2:d5)
• Product
Definition: Multiply all the numbers given as an argument
Syntax: Product(Numerical Aruguments)
Example: =PRODUCT(c2:d2)
• SQRT
Definition: Return square root of a number
Syntax: SQRT(Number)
Example: =SQRT(81)
• Average
Definition: Return average of numbers given as an argument
Syntax: Average(Numerical Aruguments)
Example: =AVERAGE(D2:D5)
• Min
Definition: Return minimum number of given set of values
Syntax: Min(Numerical Aruguments)
Example: =Min(D2:D5)
• Max
Definition: Return maximum number of given set of values
Syntax: Max(Numerical Aruguments)
Example: =Max(D2:D5)
• Median
Definition: Return median of numbers given as an argument
Syntax: Median(Numerical Aruguments)
Example: =Median(D2:D5)
• Ceiling
Definition: Rounds a number up to the nearest multiple of significance
Syntax: CEILING(number,Significance)
Example: =CEILING(11,7)
• Floor
Definition: Rounds a number down to the nearest multiple of significance
Syntax: Floor(number,Significance)
Example: =FLOOR(11,7)
• INT
Definition: Round a number down to nearest integer
Syntax: INT(Number)
Example: =INT(14.55)
• ROUND
Definition: Round a number to specified number of digits
Syntax: ROUND(Number,num_digits)
Example: =ROUND(14.55,1)
• FACT
Definition: Return the factorial of a number
Syntax: Fact(Number)
Example: =FACT(3)
• Vlookup()
Definition: Look a value in left most column of a table then return the given column value based on offset number. It can match return as exact or similar both.
Syntax: Vlookup(Key_tolookup, Range_to_lookup, offset_number [,TRUE/ FALSE])
Example: =Vlookup(A1,$C$2:$E$5,2,FALSE)
• Count
Definition: Count the number of cells contains numerical values
Syntax: Count(Range of Cells)
Example: =COUNT(C1:C5)
• Counta
Definition: Count the number of non empty cells
Syntax: CountA(Range of Cells)
Example: =COUNTA(C1:C5)
• Countif
Definition: Count number of cells that meet given single condition
Syntax: Countif(Criteria Range, Criteria)
Example: =Countif(B2:B5,“Female”)
• Countifs
Definition: Count number of cells that meet given multiple conditions
Syntax: Countifs(Criteria Range1, Criteria1,Criteria Range2, Criteria2,…)
Example: =Countifs(B2:B5,“Female”,C2:C5,“Student”)
• Match
Definition: Return a relative position of a value in an array that matches with given criteria
Syntax: Match(Lookup_Value,Lookup array,match type (0/1))
Example: =MATCH(“Anil”,$B$3:$B$45,0)
• Sumif
Definition: Add the value of given cells based on given single condition
Syntax: Sumif(Criteria Range, Criteria,Sum Range)
Example: =SUMIF(B2:B5,“Female”,D2:D5)
• Sumifs
Definition: Add the value of given cells based on given multiple conditions
Syntax: Sumifs(Sum range,Criteria Range1, Criteria1,Criteria Range2, Criteria2,…)
Example: =SUMIFS(D2:D5,B2:B5,“Female”,C2:C5,“Student”)
• Averageif
Definition: Return the average of cells based on given single condition
Syntax: Averageif(Criteria Range, Criteria,Average Range)
Example: =AVERAGEIF(B2:B5,“Female”,D2:D5)
• Averageifs
Definition: Return the average of cells based on given multiple conditions
Syntax: Averageifs(Average range,Criteria Range1, Criteria1,Criteria Range2, Criteria2,…)
Example: =AVERAGEIFS(D2:D5,B2:B5,“Female”,C2:C5,“Student”)
• Row
Definition: Return the row number of given cell or if input argument is blank it give row number of current row
Syntax: Row([Cell reference])
Example: =Row()
• AND
Definition: Return TRUE, if all condition are TRUE else FALSE
Syntax: AND(Connditin1, Conditin2…)
Example: =AND(D2>=5000,C2>=8)
• OR
Definition: Return TRUE, if any of condition are TRUE
Syntax: OR(Connditin1, Conditin2…)
Example: =OR(C2>12,D2>6000)
• IF
Definition: It is conditional function, execute TRUE statement if condition is TRUE else execute FALSE statement
Syntax: IF(Condition,TRUE Statement, FALSE Statement)
Example: =IF(D2>=7000,E4+100,E4-100)
• Month
Definition: Return numerical identification of month (between 1 to 12) of any date argument
Syntax: Month(Date argument)
Example: =MONTH(E2)
• Day
Definition: Return numerical identification of day (between 1 to 31) of any date argument
Syntax: Day(Date argument)
Example: =Day(E3)
• Year
Definition: Return year of any date argument
Syntax: Year(Date argument)
Example: =Year(E4)
• Date
Definition: Return date based on Year, month and day as an argument
Syntax: Date(Year,Month,Day)
Example: =DATE(1983,12,30)
• Today
Definition: Return Current Date
Syntax: Today()
Example: =TODAY()
• Now
Definition: Return Current Date and Time
Syntax: Now()
Example: =NOW()
• Datedif
Definition: Return the year, month and day difference between two dates
Syntax: Datedif(Old date, Recent date,<Y/M/D>), Y for Year, M for Month and D for Days difference
Example: =DATEDIF(E2,Date(2014,7,17),“Y”)
• Weekday
Definition: Return the day of week e.g. 1 for Sunday
Syntax: WEEKDAY(DATE)
Example: =WEEKDAY(DATE(2013,1,1))
• Weeknum
Definition: Return the week number of week in a year
Syntax: WEEKNUM(DATE)
Example: =WEEKNUM(DATE(2014,6,1))
• Eomonth
Definition: Return the last day of month before and after specified number of months
Syntax: EOMONTH(date,specified number of months)
Example: =EOMONTH(DATE(2014,6,1),0)
• Quartile
Definition: Return the quartile of dataset
Syntax: Quartile(Range,Quartile)
Example: =QUARTILE(D2:D5,1)
• Stdev
Definition: Return standard deviation based on dataset
Syntax: Stdev(dataset)
Example: =STDEV(D2:D5)
• Left
Definition: Returns the specified number of characters from start of the string
Syntax: Left(string,Number of characters)
Example: =LEFT(“Amit”,2)
• Right
Definition: Returns the specified number of characters from end of the string
Syntax: Right(string,Number of characters)
Example: =Right(“Amit”,2)
• Mid
Definition: Returns the specified number of characters from the string starting from given position
Syntax: Mid(String,Start Position, Number of Characters)
Example: =MID(“Amit”,2,1)
• LEN
Definition: Returns the number of characters in text string
Syntax: LEN(String)
Example: =LEN(“Amit”)
• LOWER
Definition: Converts all characters in text string to lower case
Syntax: Lower(String)
Example: =LOWER(“Amit”)
• Proper
Definition: Converts all characters in text string to Proper case
Syntax: Proper(String)
Example: =Proper(“amit”)
• UPPER
Definition: Converts all characters in text string to Upper case
Syntax: Upper(String)
Example: =UPPER(“amit”)
• Rept
Definition: Repeat string a given number of times
Syntax: Rept(String,Number of times)
Example: =REPT(“Am”,5)
• CONCATENATE
Definition: Join Multiple text string into one
Syntax: Concatenate(String1,String2,String3….)
Example: =CONCATENATE(“Am”,“it”," Kumar")
Find
Definition: Returns starting position of sub string within another string, it is case sensitive and default it starts seraching from first position (it is optional)
Syntax: find(find_string,string[,start position])
Example: =FIND(“A”,“Amit”)
Search
Definition: Returns starting position of sub string within another string, default it starts seraching from first position (it is optional)
Syntax: Search(find_string,string[,start position])
Example: =SEARCH(“a”,“Amit”)
TRIM
Definition: Removes duplicate spaces, and spaces at the start and end of a text string
Syntax: TRIM(String)
Example: =TRIM(" te rt ")
Value
Definition: Convert a text value into number
Syntax: VALUE(Text)
Example: =VALUE(“45”)
EXACT
Definition: Tests if two supplied text strings are exactly the same and if so, returns TRUE; Otherwise, returns FALSE. (case-sensitive)
Syntax: Exact(Text1,Text2)
Example: =EXACT(“Amit”,“Amit”)
• ISERROR
Definition: Tests if an initial supplied value (or expression) returns an error and if so, returns TRUE; Otherwise returns FALSE
Syntax: ISERROR(Value)
Example: =ISERROR(4/0)
ISNUMBER
Definition: Tests if a supplied value is a number, and if so, returns TRUE; Otherwise, returns FALSE
Syntax: ISNUMBER(VALUE)
Example: =ISNUMBER(45)
• ISText
Definition: Tests if a supplied value is text, and if so, returns TRUE; Otherwise, returns FALSE
Syntax: ISTEXT(Value)
Example: =ISTEXT(45)

Hope this helps!

Regards,
Sunil