CORPORATE
CORPORATE
Go to “Home” tab > Click “Format As Table”
Result: The highlighted data range will be converted into table.
CORPORATE
When clicking on the table, there is “Table Design” tab
The “Table Design” tab has things like Show A Header Row; Show A Total
Row, Filter Button, Changing table style, Name the table.
CORPORATE
Formula: =sumifs(sum_range, criteria_range1, criteria 1,
criteria_range2, criteria2)
Result: The sum of the criteria in the sum_range will be returned
CORPORATE
Dynamic array function: Data range 2 & Data range 1, e.g. Date range 2 – Date range 1
Result: Multiple results of the calculation will be returned across the data range.
CORPORATE
All the dynamic array functions: 18
CORPORATE
Formula: =TOCOL(array,[ignore],[scan_by_column)
*only need put data range as array, ignore the following.
Result: The current data range will be converted into a vertical column of data.
CORPORATE
Formula: =Sort(array, [sort_index], [sort_order], [by_col])
*sort_index: put blank (skipped)
*sort_order: 1 (ascending), -1 (descending)
Result: The current data range will be sorted according to sort order.
CORPORATE
Formula: =Sequence(rows, [columns], [start], [step])
*rows: Usually use “CountA(“cell 1”#)
Result: The no. of sequence will be returned besides the cell 1 data range.
CORPORATE
Formula: =Xlookup(lookup_value, lookup_array, return_array, [if_not-found],
[match_mode], [search_mode])
*[if_not_found]: Represent the function IFNA or IFEEROR, just put the response needed
if the result is not found. Usually put “” to return as blank for NA.
*match_mode: 0 (Exact match), -1 (Exact match or next smaller items), 1 (Exact match or
next larger item)
*search_mode: 1 (Search first-to-last, -1 (Search last-to-first), 2 (Binary search - sorted
ascending order), -2 (Binary search – sorted descending order)
Result: The return_array value corresponding to the lookup value will be returned.
CORPORATE
Formula: =Indirect(ref_text, [a1])
*ref_text: Can be “cell 1 & cell 2” as a reference cell, OR “cell 1 & “!cell 2”) if the
text is on another sheet.
Result: The no. of sequence will be returned besides the cell 1 data range.
CORPORATE
CORPORATE
Use a single Xlookup with a criteria table (instead of using nested Ifs)
Result: The approximate corresponding value for the lookup value can be returned.
CORPORATE
Formula: =Textbefore(text, delimiter, [instance_num], [match_mode], [match_end],
[if_not_found]
*delimiter: separator between text
Result: The text before the delimiter will be returned.
CORPORATE
Formula: =Textafter(text, delimiter, [instance_num], [match_mode], [match_end],
[if_not_found]
*delimiter: separator between text
Result: The text after the delimiter will be returned.
CORPORATE
Formula: =MODE.SNGL(number1, number2, …)
*number1, number2: Data range 1, 2, …
Result: The most occurring cell will be returned.
CORPORATE
Formula: =MODE. MULT(number1, number2, …)
*number1, number2: Data range 1, 2, …
Result: The most occurring cells will be returned, this means
if there is 2 most occurring cells, both will be returned.
CORPORATE
Type =SUM() > click tab 1 > hold down Shift key > click nth tab > click the
desired cell
*Note: The structure of the data in all the tab should be the same.
Result: The sum of all the tab at the same desired cell will be returned.
CORPORATE
Formula: =Let( name1, name_value1, calculation or name2, [name_value2], …)
*name1, name2, …: Variable names
*name_value1, name_value2: The cell for the data of the variable
*calculation: mathematical computation using variable, e.g. GAS/MPG*Dist
Result: The result of the calculation of variables will be returned.
CORPORATE
Formula: =Mround (number, multiple)
*number: Usually a cell with figure/time
*multiple: Usually in colon form, e.g. “0:15” (nearest 15 minutes)
Result: The rounded nearest figure will be returned.
CORPORATE
Formula: =((End time – Start time)+(End time < Start time))*24 (hours)
*To calculate the difference between start & end time, including pass midnight.
Result: The difference between start & end time will be returned.
CORPORATE
Formula: =eomonth(start_date, months)
*start_date: The date that is available
*months: Ending month – Starting month, e.g. Starting month is Mar (start date in
March), Ending month is Jun, hence Jun – Mar = 3
Result: The difference between end of starting month and ending month is returned.
CORPORATE
Formula: =yearfrac(start_date, end_date, [basis])
*[basis]: Usually not used, it involves handling of different time zones like European,
American and others.
Result: The difference between start date and end date in the year form will be returned.
CORPORATE
Formula: =filter(array, include, [if_empty])
*include: Usually the criteria, e.g. cell 2 > cell 1
Result: All the data that meet the criteria will be returned in table.
CORPORATE
Formula: =filter(array, include, [if_empty])
*include: if more than 1 criteria, usually use*/+, e.g. criteria 1*criteria 2 (AND case),
criteria 1+criteria 2 (OR case)
Result: All the data that meet the criteria will be returned in table.
CORPORATE
Formula: =Sort (Unique(Tocol(array,[ignore],[scan by column]),[by col], [exactly once]))
*Tocol (): Usually only capture the whole array and then Enter.
*by_col: Can be skipped as Tocol has make it to a column
*exactly once: TRUE (Return items tha appear exactly once), FALSE (Return every distinct item)
Result: All unique data that meet criteria will be returned in vertical column in sort order.
CORPORATE
Formula: =Fact(number)
Result: The result of the factorial of the number will be returned.
CORPORATE
Formula: =Product(Sequence(number),number 2, number 3,…)
*Sequence (): As the column 1
*number 2, number 3, …: As the column 2, 3, …
Result: The result of the product of column 1, 2, 3 & … will be returned.
CORPORATE
Formula: =Permut(number, number_chosen)
*number_chosen: The number that can choose to multiply with the number, e.g. 6 means
1,2,3,4,5,6
Result: The result of the permutation will be returned.
CORPORATE
Formula: =Combin(number, number_chosen)
* number_chosen: The number that can choose to multiply with the number, e.g. 3
means 1,2,3
Result: The number of possible combinations (no repetition) will be returned.
CORPORATE
Formula: =CombinA(number, number_chosen)
* number_chosen: The number that can choose to multiply with the number, e.g. 5
means 1,2,3,4,5
Result: The number of possible combinations (allows repetition) will be returned.
CORPORATE
Formula: =CEILING.MATH(number, [significance], [mode])
* significance: Usually the multiples
Result: The number will be rounded up in the multiples of the significance.
CORPORATE
Formula: =FLOOR.MATH(number, [significance], [mode])
* significance: Usually the multiples
Result: The number will be rounded down in the multiples of the significance.
CORPORATE
Formula: =Vlookup(“x???...”, lookup array, column number, TRUE/FALSE)
*”x???...”: x represent a starting character, question mark (?) is wildcard function and
all these need to be in double quotes “”.
Result: The approximate or exact match that meet the criteria of wildcard function
(“x???) will be returned.
CORPORATE
Formula: =Countifs(criteria_range, “*xxx…*”)
“*xxx…*”: Put asterisk on both sides of a word/phrase to lookup the word/phrase in
middle in the array, all these must be in double quotes “”, which is a wildcard function.
Put asterisk at front side only indicates to find only those that begins with the
word/phrase and putasterisk at back to find only those that ends with the word/phrase.
Result: The number of counts that meet the criteria in the criteria range will be returned.
CORPORATE
Formula: =Countifs(criteria_range, “*xxx???*”)
“*xxx???*”: Put asterisk on both sides of a word/phrase to lookup the word/phrase
middle in the lookup array and question mark represent any character, all these must be
in double quotes “”, which is a wildcard function Put asterisk at front side only indicates
to find only those that begins with the word/phrase and putasterisk at back to find only
those that ends with the word/phrase.
Result: The number of counts that meet the criteria in the criteria range will be returned.
CORPORATE
Formula: =Sequence (rows, [columns], [start], [step])
*rows: Number of rows needed
*columns: Number of columns needed
*start: The starting number
*step: Difference between numbers, e.g. 1 means differ by 1 between numbers.
Result: The numbers will be returned in sequence with starting number at the top.
CORPORATE
Formula: =Image(source, [alt text], [sizing], [height], [width])
*alt text: Text to describe the image, usually in double quotes.
*sizing: 0 (fit cell), 1 (fill cell), 2 (original size), 3 (custom size)
Result: The image will be imported into excel from the source and meet the criteria of
sizing, height and width.
CORPORATE
Formula: =Code(text)
*text: Usually a cell.
Result: The code number of the text will be returned.
CORPORATE
Formula: =Char (number)
Result: The character of the code number will be returned, e.g. char (177) will return +
CORPORATE
Formula: =Substitute (text, old text, new text, [instance_num])
*Usually click text (a cell), type old text and new text then enter.
Result: The text with new text replacing old text will be returned.
