Free Microsoft Excel Training From ExcelHelp
Microsoft Excel can be a very powerful tool to help companies or individuals organize and analyze statistics with ease. Knowing how to properly use Excel can enhance the experience, speed of use and allow for much more efficient and productive work. The expert team at ExcelHelp has identified the most common formulas and formatted them into an easy to read free Microsoft Excel training document. Below you will find Excel formulas and functions with a description and syntax, essentially giving you a free excel tutorial!
Formulas and Functions
| Formula |
Description |
Syntax |
Simple Example |
|---|---|---|---|
| & | combines cells together into one cell | =(1st cell combining) & (second cell combining) | =A1&B1 |
| ABS | Absolute Value | =ABS(Cell or value) | =abs(B1) |
| AND | Returns true if all statements are true, false if any of them are not | =AND(1st criteria, second criteria) | =and(A1=”company”,B1=”Yes”) |
| AVERAGE | Returns the average | =AVERAGE(Range of cells) | =average(A1:A50) |
| CHOOSE | Returns a different cell/value based upon the value of another cell | =CHOOSE(cell determining which you want chosen must be #, value1, value2, …) | =choose(A1,B1,B2,B3) |
| CLEAN | Removes all non-printable characters from a cell | =CLEAN(cell you are referencing) | =clean(A1)) |
| COLUMN | Returns the column rumber of the reference | =COLUMN(cell you are referencing) | =column(D1) |
| COUNT | Counts the number of cells included | =COUNT(cells you are counting) | =count(B5:K5) |
| COUNTA | counts non-blank cells | =COUNTA(cells you want to count the non-blank cells in) | =counta(A1:A50) |
| COUNTBLANK | counts blank cells | =COUNTBLANK(cells you want to count the blank cells in) | =countblank(A1:A50) |
| COUNTIF | counts the number of cells that meet a criteria | =COUNTIF(cells you are looking for criteria in, criteria) | =countif(A1:A50,”company”) |
| DATE | Creates a date format cell based upon day, month, year inputs | =DATE(year,month, day) | =date(A1,B1,C1) |
| DAY | Returns the day of a given date cell | =DAY(date cell reference) | =day(A1) |
| DB | Depreciation function | =DB( cost, salvage, life, period, number months ) | =db(A1,B1,5, 3,6) |
| DDB | Double declining balance depreciation | =DDB( cost, salvage, life, period) | =ddb(1000,500,4,2) |
| EXACT | Tells you if 2 cells are identical in formatting and text. Returns true if they are, false if different | =EXACT(1st cell, 2nd cell) | =exact(A1,B1) |
| FIND | Finds a value/text in a cell and returns where it is located within cell | =FIND(text/value searching for, cell searching, starting point in cell <- optional) | =find(“company”,A1) |
| HLOOKUP | Finding information in a table based on an identifier in another row | =HLOOKUP(Value matching, range it is looking in, number of rows down, exact “0” or close “1” ) | =hlookup(“company”,A1:B50,2,0) |
| IF | Looks to see if a statement is true. If it is, it does one thing, if not, another | =IF(statement you want to check, what to do if true, what to do if false) | =if(A1=company,”company”,”Not company”) |
| INDEX | returns a cell value from an array of data | =INDEX(range of data, row number, column number) | =INDEX(A1:D50,20,3) |
| ISERROR | if the cell referenced is any sort of error, returns true, otherwise false | =ISERROR(cell you are checking for errors) | =iserror(A5) |
| ISNA | if the cell referenced is #N/A returns true, otherwise false | =ISNA(cell you are checking for #N/A) | =isna(A5) |
| ISNUMBER | if the cell referenced is a number returns true, otherwise false | =ISNUMBER(cell you are checking) | =isnumber(A5) |
| ISBLANK | if the cell referenced is a number returns true, otherwise false | =ISBLANK(cell you are checking for blanks) | =isblank(A5) |
| ISTEXT | if the cell referenced is text returns true, otherwise false | =ISTEXT(cell you are checking) | =istext(A5) |
| LEFT | Returns the left part of a cell | =LEFT(cell you are pulling from, number of characters you want to bring from cell starting on the left) | =left(A1,5) |
| LEN | Counts # of characters in a cell | =LEN(cell you are counting) | =len(A1) |
| LOWER | Makes all letters in the cell lower case | =LOWER(cell you are adjusting) | =lower(A1) |
| MATCH | Searches for a value in a range and returns its location | =MATCH(value looking for, range looking in, 0 for exact match or 1 for closest) | =match(2008,A1:A50) |
| MAX | Maximum Value | =MAX(Range of cells) | =max(A1:B50) |
| MID | Combination of right and left functions, returns a specified portion from the middle of the cell | =MID(cell you are pulling from, number of characters from the left side you want to start at, number of characters you want to bring over) | =mid(A1,2,5) |
| MIN | Minimum Value | =MIN(Range of cells) | =min(A1:B50) |
| MONTH | Returns the month of a given date cell | =MONTH(date cell reference) | =month(A1) |
| NOW | Populates with current time and date | =NOW() | =now() |
| NPV | Calculates net present value | =NPV(discount rate, value1, value2, value3…) | =npv(8%,A1:A10) |
| OFFSET | returns a cell value based on its physical relation to a reference cell | =OFFSET(starting point, rows, columns) | =offset(A1,3,5) |
| OR | Returns true if either statement is true, false if neither | =OR(1st criteria,second criteria) | =or(A1=”company”,A1=”ABC”) |
| PROPER | Puts a cell in “proper format” with capital first letters of each word, lower case the rest | =PROPER(Cell you are adjusting) | =proper(A1) |
| RAND | Returns a random number between 0 and 1 | =RAND() | =rand() |
| RANK | Ranks values in an array | =RANK(Cell you are ranking, cells you are ranking it in, order <=optional) | =rank(C5,C1:C20,0) |
| REPLACE | Replaces characters in a reference cell | =REPLACE(old text, start of replacement, number of_chars, new text ) | =replace(“GRB”,1,3,”CB”) |
| RIGHT | Returns the right part of a cell | =RIGHT(Cell you are pulling from, number of characters you want to bring from Cell starting on the right) | =right(A1,5) |
| ROUND | Rounds a numeric value in a cell | =ROUND(cell you are referencing, number of digits to round to) | =round(A1,2) |
| ROUNDDOWN | Rounds a numeric value down | =ROUNDDOWN(cell you are referencing, number of digits to round to) | =rounddown(A1,0) |
| ROUNDUP | Rounds a numeric value up | =ROUNDUP(cell you are referencing, number of digits to round to) | =roundup(A1,0) |
| ROW | Returns the row number of a cell | =ROW(Cell you want to see row # for) | =row(B5) |
| SEARCH | Finds a value/text in a cell and returns where it is located within cell | =SEARCH(text/value searching for, cell searching, starting point in cell <- optional) | =search(“company”,A1) |
| STDDEV | Calculates standard deviation | =STDDEV(Range of cells) | =stddev(A1:A50) |
| SUMIF | Summing rows, columns or groups of data if they meet certain criteria | =SUMIF(Data being used to match criteria, criteria,Data potentially being summed) | =sumif(A:A,”company”,”B:B”) |
| TEXT | Adjusts the format of text | =TEST(cell you are adjusting,” format you want it in in quotes”) | =text(A1,”mmddyy”) |
| TRIM | removes any spaces at the beginning or end of a cell | =TRIM(cell you are referencing) | =trim(A1) |
| UPPER | Capitalizes all letters in the cell | =UPPER(cell you are adjusting) | =upper(A1) |
| VLOOKUP | Finding information in a table based on an identifier in another column | =VLOOKUP(value matching, range it is looking in, number of columns over, exact (0) or close (1) | =vlookup(“company”,A1:B50,2,0) |
| WEEKDAY | Returns the day of the week for a given date | =WEEKDAY(date cell reference) | =weekday(A1) |
| YEAR | Returns the year of a given date cell | =YEAR(date cell reference) | =year(A1) |
Need a deeper and more robust level of Excel training and tutorials? Contact us to set up on-site Excel training with one of our expert Excel consultants.