Site icon Excel Help

Free Excel Training From ExcelHelp

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.

Exit mobile version