Go to referenced cell:
Ctrl + [ : Go to precedent cell
Ctrl + ] : Go to dependent cell
Ref:http://windowssecrets.com/forums/showthread.php/111721-Go-To-Referenced-Cell-(Excel-2003-SP2)
Concatenate cells faster:
Ctrl + Select Cells
Use with =CONCATENATE()
It will insert cells separated by comma
Ref: https://www.excelcampus.com/keyboard-shortcuts/concatenate-range-of-cells/
Copy Text from cell:
e.g. 123 - String of lines
=MID(A1,1+FIND("-",A1),LEN(A1))
FIND() will look for "-"
MID() will start from one character after "-" and end after length of cell
Use with =TRIM() to remove trailing space
Ref: https://fiveminutelessons.com/learn-microsoft-excel/extract-text-cell-excel
Autofill Shortcut:
Create a sequence e.g. A01, A02, A03
Select the sequence with extra blank cells to extrapolate
Alt + E + I + S > Type : AutoFill
Sum Shortcut:
Alt + =
This inserts the sum function. Now just select the range.
Calculate Sum of filtered cells:
=SUBTOTAL(9,CN8:CN194)
9 - Function for Sum
The SUBTOTAL function ignores any rows that are not included in the result of a filter, no matter which function_num value you use
Ref: https://support.office.com/en-us/article/SUBTOTAL-function-7B027003-F060-4ADE-9040-E478765B9939
Calculate Sum based on Criteria:
=SUMIF($C$8:$C$172,$B200,E$8:E$172)
Range: C8:C172 (e.g. Automotive, Realty, Others..)
Criteria: B200 (e.g. Automotive)
Sum Range: E8:E172 (e.g. Values adjacent to Automotive)
SUMIF function is used to sum the values in a range that meet criteria that you specify
Ref:https://support.office.com/en-us/article/SUMIF-function-169B8C99-C05C-4483-A712-1697A653039B
Convert Number to Text:
=TEXT(A1,"0")
Useful when doing a VLOOKUP with text values.
E.g read number 100 as text 100
Ctrl + [ : Go to precedent cell
Ctrl + ] : Go to dependent cell
Ref:http://windowssecrets.com/forums/showthread.php/111721-Go-To-Referenced-Cell-(Excel-2003-SP2)
Concatenate cells faster:
Ctrl + Select Cells
Use with =CONCATENATE()
It will insert cells separated by comma
Ref: https://www.excelcampus.com/keyboard-shortcuts/concatenate-range-of-cells/
Copy Text from cell:
e.g. 123 - String of lines
=MID(A1,1+FIND("-",A1),LEN(A1))
FIND() will look for "-"
MID() will start from one character after "-" and end after length of cell
Use with =TRIM() to remove trailing space
Ref: https://fiveminutelessons.com/learn-microsoft-excel/extract-text-cell-excel
Autofill Shortcut:
Create a sequence e.g. A01, A02, A03
Select the sequence with extra blank cells to extrapolate
Alt + E + I + S > Type : AutoFill
Sum Shortcut:
Alt + =
This inserts the sum function. Now just select the range.
Calculate Sum of filtered cells:
=SUBTOTAL(9,CN8:CN194)
9 - Function for Sum
The SUBTOTAL function ignores any rows that are not included in the result of a filter, no matter which function_num value you use
Ref: https://support.office.com/en-us/article/SUBTOTAL-function-7B027003-F060-4ADE-9040-E478765B9939
Calculate Sum based on Criteria:
=SUMIF($C$8:$C$172,$B200,E$8:E$172)
Range: C8:C172 (e.g. Automotive, Realty, Others..)
Criteria: B200 (e.g. Automotive)
Sum Range: E8:E172 (e.g. Values adjacent to Automotive)
SUMIF function is used to sum the values in a range that meet criteria that you specify
Ref:https://support.office.com/en-us/article/SUMIF-function-169B8C99-C05C-4483-A712-1697A653039B
Convert Number to Text:
=TEXT(A1,"0")
Useful when doing a VLOOKUP with text values.
E.g read number 100 as text 100
