Whether you love or hate using Excel, it is an extremely useful tool in a workplace or even home to easily calculate, analyse, audit and display data in a logical and meaningful way.
I’ve been working in a number of roles over the past decade including Technology auditing and risk management and Excel is one of the key tools that I have needed to be familiar with as part of my daily work. Knowing the formulas listed below can help to change an otherwise manual task into an automatic and repeatable process. This post assumes you have a basic knowledge of Excel and is to take you through some very useful formulas for both basic and advanced levels.
For more information on each example, click through to see a demonstration .
Basic Formulas
Summing up numbers
| SUM | =SUM(number1, [number2], …) | Adds all the numbers in a range of cells |
| SUMIF | =SUMIF(range, criteria, [sum_range]) | Adds the cells specified by a given condition or criteria |
| SUMIFS | =SUMIFS(sum_range, criteria_range1, criteria1, …) | Adds the cells specified by a given set of conditions or criteria |
Counting Cells
| COUNT | =COUNT(value1, [value2], …) | Counts the number of cells in a range that contains numbers |
| COUNTIF | =COUNTIF(range, criteria) | Counts the number of cells within a range that meet the given condition |
| COUNTIFS | =COUNTIFS(criteria_range1, criteria1, …) | Counts the number of cells specified by a given set of conditions or criteria |
Basic Statistics
| AVERAGE | =AVERAGE(number1, [number2], …) | Returns the average (arithmetic mean) of its arguments, which can be numbers or names, arrays, or references that contain numbers |
| MIN | =MIN(number1, [number2], …) | Returns the smallest number in a set of values. Ignores logical values and text |
| MAX | =MAX(number1, [number2], …) | Returns the largest number in a set of values. Ignores logical values and text |
| MEDIAN | =MEDIAN(number1, [number2], …) | Returns the median, or the number in the middle of the set of given numbers |
| MODE | =MODE(number1, [number2], …) | Returns the most frequently occurring, or repetative value, in an array or range of data |
Logic / Conditional
| IF | =IF(logical_test, [value_if_true], [value_if_false]) | Checks whether a condition is met, and returns one value if TRUE and another value if FALSE |
| AND | =AND(logical1, [logical2], …) | Checks whether all arguments are TRUE, and returns TRUE if all arguments are TRUE |
| OR | =OR(logical1, [logical2], …) | Checks whether any of the arguments are TRUE and returns TRUE or FALSE. Returns FALSE only if all arguments are false |
| NOT | =NOT(logical) | Changes FALSE to TRUE and TRUE to FALSE |
| ISNA | =ISNA(value) | Checks whether a value is #N/A, and returns TRUE or FALSE |
| ISERR | =ISERR(value) | Checks whether a value is an error other than #N/A, and returns TRUE or FALSE |
| ISBLANK | =ISBLANK(value) | Checks whether the reference is to an empty cell, and returns TRUE or FALSE |
Date Conversion
| EOMONTH | =EOMONTH(start_date, months) | Returns the serial number of the last day of the month before or after a specified number of months and start of month |
| DAY | =DAY(serial_number) | Returns the day of the month, a number from 1 to 31 |
| MONTH | =MONTH(serial_number) | Returns the month, a number from 1 (January) to 12 (December) |
| YEAR | =YEAR(serial_number) | Returns the year of a date, an integer in the range 1900 and 9999 |
| DATE | =DATE(year, month, day) | Returns the number that represents the date in Microsoft Excel date-time code. Julian date example |
Date Calculation
| DAYS | =DAYS(end_date, start_date) | Returns the number of days between two dates |
| NETWORKDAYS | =NETWORKDAYS(start_date, end_date, [holidays]) | Returns the number of whole workdays between two dates |
| NOW | =NOW() | Returns the current date and time formatted as a date and time |
| TODAY | =TODAY() | Returns the current date formatted as a date |
Data Cleanup
| TRIM | =TRIM(text) | Removes all space from a text string except for single spaces between words |
| CONCATENATE | =CONCATENATE(text1, [text2], …) | Joins several text strings into one text string |
| LEN | =LEN(text) | Returns the number of characters in a text string |
| RIGHT | =RIGHT(text, [num_chars]) | Returns the specified number of characters from the end of a text string |
| LEFT | =LEFT(text, [num_chars]) | Returns the specified number of characters from the start of a text string |
| MID | =MID(text, start_num, num_chars) | Returns the characters from the middle of a text string, given a starting position and length |
Rounding
| ROUND | =ROUND(number, num_digits) | Rounds the number to a specified number of digits |
| ROUNDUP | =ROUNDUP(number, num_digits) | Rounds a number up, away from zero |
| ROUNDDOWN | =ROUNDDOWN(number, num_digits) | Rounds a number down, toward zero |
Looking up Values
| VLOOKUP | =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) | Looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify. By default, the table must be assorted in ascending order. |
| INDEX | =INDEX(array, row_num, [column_num]) | Returns a value or reference of the cell at the intersection of a particular row and column, in a given range |
| MATCH | =MATCH(lookup_value, lookup_array, [match_type]) | Returns the relative position of an item in an array that matches a specified value in a specified order |
| Wildcards | “*” | Usually followed or preceded by the concatenation & to be used for lookups or formulas. |
Calculations on a filtered list
| SUBTOTAL | =SUBTOTAL(function_num, ref1, [ref2], …) | Returns a subtotal in a list or database |
Key Excel Shortcuts
| Edit Cell | F2 | To edit a cell |
| Undo / Redo | Ctrl + z / Ctrl + y | Used to undo a mistake or to redo what you mistakenly undid |
| New Line in cell | Alt + Enter | To add a new line character into a cell |
| New Row | Ctrl + “+” | A quick way to insert a large number of new rows instead of right clicking and inserting row |
| Absolute Refernces | F4 | While editing a cell, you can rotate through the “$” for cell references for absolute references. This is useful to avoid having to type it in manually |
| Skip to end of row | Ctrl + Arrow | To quickly navigate to the end of the row (or next blank cell) |
| Autofill cells | Ctrl + D | While highlighting the cells, to fill cells with a value or autofill the formula |
Key Excel Tools
| Conditional Formatting | Located under Ribbon Home > Styles > Conditional Formatting | Based on a certain criteria, format a cell (e.g. font, background colour, etc.) |
| Slicer Filters | Located under Ribbon Design > Tools > Insert Slicer (only when selecting a table) | Add a filter that can be clicked based on the available values. Useful for dashboards. |
| Freeze Panes | Located under Ribbon View > Window > Freeze Panes | Lock the view of columns or rows so that scrolling will keep the specified rows/columns visible |
| Text to columns | Located under Ribbon Data > Data Tools > Text to Columns | For a pasted data source, transform the data into columns based on either specific characters (e.g. “,”) or based on a specified fixed width |
| Remove Duplicates | Located under Ribbon Data > Data Tools > Remove Duplicates | Select a list of cells and remove all the duplicates |
| Group | Located under Ribbon Data > Outline > Group | Group columns or rows to make an outline (i.e. ability to expand or collapse) |