Quick-ways-to-access-Excel-Formulas

Excel Formulas(Quick Ways to access excel formulas)

This post focuses on quick ways to access Excel formulas with step-by-step description which are very useful in our day-to day life.

Advantages of Excel Formulas

  1. Performs automated math operations to data in spreadsheet.
  2. Cell reference in excel allows user to use the same formula for different values.
  3. Calculates large quantity of data in an efficient manner.
  4. Gives better and faster results.
  5. Maintain records of any organization.
  6. Microsoft Excel offers users hundreds of different functions for a variety of purposes but not everyone is aware about its usage.
  7. In addition to that it can also analyze personal finance.
  8. Helps in data analysis and boosting up productivity in any process.
  9. Knowing Excel makes a person more marketable.

Almost every person knows its basic working but very few are proficient with proper formulas.So it is very much important to know “how to use right formula at the right place”.

Apart from learning excel tips and tricks,a person should know smarter ways to complete work efficiently.Additionally,it is the need of an hour to be aware of every latest technology.

In order to save problems faced while sending a large video file.Please read the below article.

Article – How to compress a video?

This is finally the right place for people who have been struggling to find appropriate formulas.Because here you will find a list of quick ways to access Microsoft Excel Formulas.

Above all please refer the below data while doing calculations.

Top ProductsProduct CodeQuantityPrice
MobileA45000
CameraB32000
LaptopC21500
WatchesW53000
TVT64000
RefrigeratorR89000

SUM

Add the values for a range of cells.

=SUM(start range:end range)   

Excel Formulas - SUM FORMULA
SUM FORMULA

MIN

Finds minimum number between two or more numbers/from a range of numbers.

=MIN(start range:end range) or =MIN(Number 1,Number 2)

Excel Formulas - MIN FORMULA-1
MIN FORMULA-1

MIN FORMULA-2
MIN FORMULA-2

MAX

Similarly, this function finds maximum number between two or more numbers/from a range of numbers.

=MAX(start range:end range) or =MAX(Number 1,Number 2)

TODAY

Returns current date as described in settings.

=TODAY()

AVERAGE

Returns the average (arithmetic mean) of the arguments.

=AVERAGE( start range:end range)

Excel Formulas - AVERAGE FORMULA
AVERAGE FORMULA

In the above screenshot as you can see when average is calculated by =SUM/COUNT result is same as the result obtained from AVERAGE FORMULA but obviously result from AVERAGE Function is only a single step process.Hence this formula is an example that how easy it becomes if we are familiar with appropriate formula.

CONCATENATE

Joins several text strings into one text string.

= CONCATENATE(text1, [text2], ….)

Excel Formulas - CONCATENATE FORMULA
CONCATENATE FORMULA

CONVERT

It converts a number from one measurement system to another

= CONVERT(number,from_unit,to_unit)

Excel Formulas - CONVERT
CONVERT FORMULA

LEN

Returns the number of characters in a text string.

= LEN(text)

Excel Formulas - LEN FORMULA
LEN FORMULA

IF

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE.

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

Excel Formulas - IF FUNCTION
IF FUNCTION

SUMPRODUCT

Returns the sum of the products of corresponding ranges or arrays.It first multiples then adds the values of the input arrays.

= SUMPRODUCT(array1,[array2],[array3],….)

Note :

It saves lots of time and very useful formula for big calculations as it follows very few steps rather than other formulas which follows a lengthy process.Like,Firstly calculate the product of arrays for each cell and then add input arrays using SUM function.Instead of that use only one formula SUMPRODUCT().

Excel Formulas - SUM PRODUCT FORMULA
SUM PRODUCT FORMULA

LEFT & RIGHT

LEFT() function returns the specified number of characters from the start of a text string.

RIGHT() function returns the specified number of characters from the end of a text string.

= LEFT(text,[num_chars])

= RIGHT(text,[num_chars])

Excel Formulas - LEFT & RIGHT FUNCTION
LEFT & RIGHT FUNCTION

MID

Returns the characters from the middle of a text string,given a starting position and length.

= MID(text,start_num,[num_chars])

Excel Formulas - MID FUNCTION
MID FUNCTION

SUBSTITUTE

Replaces existing text with new text in a text string.

= SUBSTITUTE(text,old_text,new_text,[instance_num])

  • text -text for which you want to substitute characters.(Required)
  • old_text -text you want to replace.(Required)
  • new_text -text you want to replace old_text with.(Required)
  • instance_num -Specifies which occurrence of old_text you want to replace with new_text.(Optional)
SUBSTITUTE FUNCTION
SUBSTITUTE FUNCTION

FIND

Returns the starting position of one text string within another text string.FIND is case-sensitive.

= FIND(find_text,within_text,[start_num])

Note : start_num – Specifies the character at which to start the search.The first character in within_text is character number 1. If you omit start_num,it is assumed to be 1.

FIND FUNCTION
FIND FUNCTION

EXACT

Checks whether two strings are exactly same and returns true or false.EXACT is case-sensitive.

= EXACT(text1,text2)

EXACT FUNCTION
EXACT FUNCTION

SUMIF

Add the cells specified by a given condition or criteria.

= SUMIF(range,criteria,[sum_range])

  • range -range of cells you want to be evaluated by criteria.(Required)
  • criteria – criteria in the form of a number,text,a cell reference,expression or a function that defines which cells will be added.(Required)
  • sum_range – the cells to add together.If sum_range is omitted,the cells in range are added together instead(Optional)
SUM IF FUNCTION
SUM IF FUNCTION

AVERAGEIF

Finds average(arithmetic mean) for the cells specified by a given condition or criteria.

= AVERAGEIF(range,criteria,[average_range])

  • range – one or more cells to average,including numbers or names,arrays,or references that contain numbers.(Required)
  • criteria – criteria in the form of a number,text,a cell reference or expression that defines which cells are averaged.(Required)
  • average_range – the actual set of cells to average.If average_range is omitted,range is used.(Optional)
AVERAGE IF FUNCTION
AVERAGE IF FUNCTION

VLOOKUP

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 sorted in ascending order.

= VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

  • lookup_value – select the value to lookup
  • table_array – range containing the lookup value.
  • col_index_num – the column number in the range containing the return value
  • range_lookup- optionally specify TRUE for approximate match or FALSE for an exact match.
 VLOOKUP FUNCTION
VLOOKUP FUNCTION

LOWER & UPPER

LOWER() function converts all letters in a text string to lowercase.

In the end,UPPER() function converts a text string to all uppercase letters.

= LOWER(text)

= UPPER(text)

LOWER & UPPER FUNCTION
LOWER & UPPER FUNCTION

In short,I have listed mostly the formulas which are used in daily official work.

Please read below articles for reference.

29 thoughts on “Excel Formulas(Quick Ways to access excel formulas)”

  1. Pingback: How to learn more about excel? - Unique Ideas

  2. Pingback: Advanced Excel Tutorial(How to learn more about excel) - Unique Ideas

  3. Sundeep Ananth Dubey (rightpurchasing.com)

    This is very informative and written in a language that everyone can understand.
    I have used excel earlier so it was a little easy for me, but many formulas in this were new for me and I understood them with ease. Excellent work Pooja 🙂

  4. Pingback: How to earn money from home - Unique Ideas

Leave a Comment

Your email address will not be published. Required fields are marked *

%d bloggers like this: