Basic Terms in Excel

Basic Terms in Excel

1. Functions to Remove Extra Characters

     

CLEAN	Removes all non-printable characters from a supplied text string
TRIM	Removes duplicate spaces, and spaces at the start and end of a text string

2. Functions to Convert Between Upper & Lower Case 

     

LOWER Converts all characters in a supplied text string to lower case PROPER Converts all characters in a supplied text string to proper case (i.e. letters that do not follow another letter are upper case and all other characters are lower case) UPPER Converts all characters in a supplied text string to upper case 3. Cutting Ucing Together Text Stringsp & Pie CONCAT Joins together two or more text strings (New in Excel 2016 (or Excel 2019 for Mac) - replaces the Concatenate function) CONCATENATE Joins together two or more text strings (Replaced by Concat function in Excel 2016) LEFT Returns a specified number of characters from the start of a supplied text string MID Returns a specified number of characters from the middle of a supplied text string RIGHT Returns a specified number of characters from the end of a supplied text string REPT Returns a string consisting of a supplied text string, repeated a specified number of times TEXTJOIN Joins together two or more text strings, separated by a delimiter (New in Excel 2016 (or Excel 2019 for Mac))

4. Excel Logical Functions

Boolean Operator Functions
AND	Tests a number of user-defined conditions and returns TRUE if ALL of the conditions evaluate to TRUE, or FALSE otherwise
OR	Tests a number of user-defined conditions and returns TRUE if ANY of the conditions evaluate to TRUE, or FALSE otherwise
XOR	Returns a logical Exclusive Or of all arguments (New in Excel 2013)
NOT	Returns a logical value that is the opposite of a user supplied logical value or expression (i.e. returns FALSE is the supplied argument is TRUE and returns TRUE if the supplied argument is FALSE) Functions Returning Constant Values TRUE Returns the logical value TRUE FALSE Returns the logical value FALSE

5. Excel Date and Time Functions

    

Creating Dates & Times
DATE	Returns a date, from a user-supplied year, month and day
TIME	Returns a time, from a user-supplied hour, minute and second
DATEVALUE	Converts a text string showing a date, to an integer that represents the date in Excel's date-time code
TIMEVALUE	Converts a text string showing a time, to a decimal that represents the time in Excel

Current Date & Time
NOW	Returns the current date & time
TODAY	Returns today's date

Extracting The Components of a Time
HOUR	Returns the hour part of a user-supplied time
MINUTE	Returns the minute part of a user-supplied time
SECOND	Returns the seconds part of a user-supplied time

Extracting The Components of a Date
DAY	Returns the day (of the month) from a user-supplied date
MONTH	Returns the month from a user-supplied date
YEAR	Returns the year from a user-supplied date
WEEKNUM	Returns an integer representing the week number (from 1 to 53) of the year from a user-supplied date
ISOWEEKNUM	Returns the ISO week number of the year for a given date (New in Excel 2013)
WEEKDAY	Returns an integer representing the day of the week for a supplied date

Performing Calculations with Dates
EDATE	Returns a date that is the specified number of months before or after an initial supplied start date
EOMONTH	Returns a date that is the last day of the month which is a specified number of months before or after an initial supplied start date
WORKDAY	Returns a date that is a supplied number of working days (excluding weekends & holidays) ahead of a given start date
WORKDAY.INTL	Returns a date that is a supplied number of working days (excluding weekends & holidays) ahead of a given start date, using supplied parameters to specify weekend days (New in Excel 2010)
DAYS	Calculates the number of days between 2 dates (New in Excel 2013)
DAYS360	Calculates the number of days between 2 dates, based on a 360-day year (12 x 30 months)
NETWORKDAYS	Returns the number of whole networkdays (excluding weekends & holidays), between two supplied dates
NETWORKDAYS.INTL	Returns the number of whole networkdays (excluding weekends & holidays), between two supplied dates, using parameters to specify weekend days (New in Excel 2010)
YEARFRAC	Calculates the fraction of the year represented by the number of whole days between two dates

6. Excel Lookup and Reference Functions

Data Lookup Functions
HLOOKUP	Looks up a supplied value in the first row of a table, and returns the corresponding value from another row
VLOOKUP	Looks up a supplied value in the first column of a table, and returns the corresponding value from another column
LOOKUP	Searches for a specific value in one data vector, and returns a value from the corresponding position of a second data vector
GETPIVOTDATA	Extracts data stored in a Pivot Table
CHOOSE	Returns one of a list of values, depending on the value of a supplied index number
MATCH	Finds the relative position of a value in a supplied array

7. Basic Mathematical Operations

   

SUM	Returns the sum of a supplied list of numbers
PRODUCT	Returns the product of a supplied list of numbers
POWER	Returns the result of a given number raised to a supplied power
SQRT	Returns the positive square root of a given number
QUOTIENT	Returns the integer portion of a division between two supplied numbers
MOD	Returns the remainder from a division between two supplied numbers
AGGREGATE	Performs a specified calculation (e.g. the sum, product, average, etc.) for a list or database, with the option to ignore hidden rows and error values (New in Excel 2010)
SUBTOTAL	Performs a specified calculation (e.g. the sum, product, average, etc.) for a supplied set of values

    

Conditional Sums
SUMIF	Adds the cells in a supplied range, that satisfy a given criteria
SUMIFS	Adds the cells in a supplied range, that satisfy multiple criteria (New in Excel 2007)

8. Excel Statistical Functions

Count & Frequency
COUNT	Returns the number of numerical values in a supplied set of cells or values
COUNTA	Returns the number of non-blanks in a supplied set of cells or values
COUNTBLANK	Returns the number of blank cells in a supplied range
COUNTIF	Returns the number of cells (of a supplied range), that satisfy a given criteria
COUNTIFS	Returns the number of cells (of a supplied range), that satisfy a set of given criteria (New in Excel 2007)

9. Finding the Largest & Smallest Values
MAX	Returns the largest value from a list of supplied numbers
MAXA	Returns the largest value from a list of supplied values, counting text and the logical value FALSE as the value 0 and counting the logical value TRUE as the value 1
MAXIFS	Returns the largest value from a subset of values in a list that are specified according to one or more criteria. (New in Excel 2019)
MIN	Returns the smallest value from a list of supplied numbers
MINA	Returns the smallest value from a list of supplied values, counting text and the logical value FALSE as the value 0 and counting the logical value TRUE as the value 1
MINIFS	Returns the smallest value from a subset of values in a list that are specified according to one or more criteria. (New in Excel 2019)
LARGE	Returns the Kth LARGEST value from a list of supplied numbers, for a given value K
SMALL	Returns the Kth SMALLEST value from a list of supplied numbers, for a given value K

Introduction to Basic Mathematical Funtion

1. SUM

The SUM function is the first must-know formula in Excel. It usually aggregates values from a selection of columns or rows from your selected range.

=SUM(number1, [number2], …)

Example:

=SUM(B2:G2) – A simple selection that sums the values of a row.

=SUM(A2:A8) – A simple selection that sums the values of a column.

=SUM(A2:A7, A9, A12:A15) – A sophisticated collection that sums values from range A2 to A7, skips A8, adds A9, jumps A10 and A11, then finally adds from A12 to A15.

=SUM(A2:A8)/20 – Shows you can also turn your function into a formula.

2. AVERAGE

The AVERAGE function should remind you of simple averages of data such as the average number of shareholders in a given shareholding pool.

=AVERAGE(number1, [number2], …)

Example:

=AVERAGE(B2:B11) – Shows a simple average, also similar to (SUM(B2:B11)/10)

3. COUNT

The COUNT function counts all cells in a given range that contain only numeric values.

=COUNT(value1, [value2], …)

Example:

COUNT(A:A) – Counts all values that are numerical in A column. However, you must adjust the range inside the formula to count rows.

COUNT(A1:C1) – Now it can count rows.

4. COUNTA

Like the COUNT function, COUNTA counts all cells in a given rage. However, it counts all cells regardless of type. That is, unlike COUNT that only counts numerics, it also counts dates, times, strings, logical values, errors, empty string, or text.

=COUNTA(value1, [value2], …)

Example:

COUNTA(C2:C13) – Counts rows 2 to 13 in column C regardless of type. However, like COUNT, you can’t use the same formula to count rows. You must make an adjustment to the selection inside the brackets – for example, COUNTA(C2:H2) will count columns C to H

5. IF

The IF function is often used when you want to sort your data according to a given logic. The best part of the IF formula is that you can embed formulas and function in it.

=IF(logical_test, [value_if_true], [value_if_false])

Example:

=IF(C2<D3, ‘TRUE,’ ‘FALSE’) – Checks if the value at C3 is less than the value at D3. If the logic is true, let the cell value be TRUE, else, FALSE

=IF(SUM(C1:C10) > SUM(D1:D10), SUM(C1:C10), SUM(D1:D10)) – An example of a complex IF logic. First, it sums C1 to C10 and D1 to D10, then it compares the sum. If the sum of C1 to C10 is greater than the sum of D1 to D10, then it makes the value of a cell equal to the sum of C1 to C10. Otherwise, it makes it the SUM of C1 to C10.

6. TRIM

The TRIM function makes sure your functions do not return errors due to unruly spaces. It ensures that all empty spaces are eliminated. Unlike other functions that can operate on a range of cells, TRIM only operates on a single cell. Therefore, it comes with the downside of adding duplicated data in your spreadsheet.

=TRIM(text)

Example:

TRIM(A2) – Removes empty spaces in the value in cell A2.

7. MAX & MIN

The MAX and MIN functions help in finding the maximum number and the minimum number in a range of values.

=MIN(number1, [number2], …)

Example:

=MIN(B2:C11) – Finds the minimum number between column B from B2 and column C from C2 to row 11 in both columns B and C.

=MAX(number1, [number2], …)

Example:

=MAX(B2:C11) – Similarly, it finds the maximum number between column B from B2 and column C from C2 to row 11 in both columns B and C.