Most Commonly Used Excel Functions

Here, I have mentioned some of the Excel functions which are commonly used in the organizations and in our day to day work.

1.SUM: To get the total of a group of cells.

2. SUM IF: To get the totals with some criteria or condition (Ex- from a given list of products and their sales volume you want the total of one specific product)


3.SUM IFS: To get the totals with more than one criteria. (Ex-In the previous example let add one more criterion, we want to check the sales of a particular product in particular month.)








4. SUM PRODUCT: To get the sum by multiplying two or more sets of arrays.
     














5. IF ERROR: To show the alternate results in case of formula error. Generally clubbed with some other formula to show the alternate results.

6. V lookup: To find the data in the large datasheet by using one unique field. This is the most commonly used excel function. (Ex-Suppose in one sheet we have Material code and quantity and other sheets their cost, here material code is the unique field)















7.Index: To get the value at a given position in a specific range. Index function mostly used to Match function where the match function used to find the location.
8.Index Match: In Index function, we have to give the row number and column number. Using a match we can give the criteria to get the value.



Comments

Post a Comment

Popular posts from this blog

FINANCIAL PLANNING & ANALYSIS Part - 1

Image
Financial Planning and Analysis is carried out to ascertain the financial strength and weakness of an enterprise. The main tools are Ratio Analysis and Cash flow and Funds Flow analysis. Ratio Analysis: Ratio Analysis is the comparison of different numbers from the balance sheet, Income Statement and Cash Flow statement against the figures of previous years, other companies, the industry or even the economy in general for the purpose of financial analysis. Ratio Analysis can be classified into four broad categories Liquidity Ratio: It derives the liquidity or short term solvency means ability of the business to pay its short term liabilities. Leverage Ratio: This is also known as Capital Structure. This may be defined as the ratio which measures the long term stability and structure of the firm. This can be further derived in two parts known as: (I)             Capital Structure Ratios (II)           Coverage Ratios Activity Ratios : It evaluates
Read more

Basic Shortcuts In Excel

Excel Shortcuts: Here are some excel shortcut I used for my everyday works. 1. Ctrl+PageUp/PageDown : To move through various pages within the excel sheet. 2. Ctrl+(Plus): To Insert new line(row/column) in excel sheet.(select the entire row/column if you want to insert a new row or column) 3. Ctrl+(Minus) : To delete lines(row/column) in excel sheet.(first, select the row/column) 4. Ctrl+(arrows) : To move to the last cell value(left/right/up/down) 5. Shift+(F11) : To add a new sheet/tabs in excel 6. Ctrl+Shift+(L) : To put the filter in the excel sheet (same shortcut press again to remove the filter) 7. Alt+ (Equal to”=”) : To get the total of a column(If the filter is there it will calculate “Subtotal” or else “Sum”) 8. Alt + AEF : To convert Text numbers into number 9. Alt+HOI : To Auto fit the column width in the excel sheet. 10. Alt+ NVT - To create Pivot in the excel ( It will ask for the table range for the Pivot) 11. Alt+ JTID -
Read more