Microsoft Excel is one of the foremost applications for an accountant or financial auditor. Becoming pro in excel for accountants is not an easy task. It takes time and effort. If you are an accountant, you must be familiar with the useful Excel formulas and functions to make the process easy.
Here, we are going to list some of the important useful, and most used Excel formulas for financial auditors and accountants.
Consider one single cell here as an example.
1. To double the width of all columns
Ctrl+ `
2. Displaying and highlighting formulas –
1) Highlight the cell that have formulas.
Click on single cell >> go to Home tab >> find and select >> formulas.
2) Highlight the cell that have no formulas.
Click on any single cell >> go to Home tab >> find and select >> go to special >> check the box for ‘Constant’ and ‘Numbers’ and uncheck the rest.
3. Auditing Tool
1) Find all dependencies of cell.
Jump to Formulas Tab >> Trace dependents
To find the dependent cell, just keep clicking repeatedly on trace dependents.
2) Highlight dependent cell
Without displaying arrows  Ctrl+shift+)
4. Entire row and column references –
1) Add data from two different rows  (use Alt= or =sum) >> =sum(select rows)
2) Add data from two different column  (use Alt= or =sum) >> =sum(select the column)
3) Calculate average of data of entire column (use Alt= or =sum) >> =average(select the column)
4) Calculate median of the data of entire column (use Alt= or =sum) >> =median(select the column)
5) Calculate years till date from the given date
=datedif(A1,today(),”Y”)
A1 is the column number
6) Update cell values without formulas –
1) Increment or decrement the value from all rows or column without using formulas.
If you want to increase the cell values by 4
Type 4 in any blank cell >> now select all the rows and columns where you want to add 4 >> go to “paste special” or Ctrl+Alt+V >> Check “add” >> ok.
If you want to increase value by 10%
Type multiplier of percentage value (Here 10% i.e 1.1) in any cell >> Copy that value >> now select all the rows and columns where it needs to modify >> go to “paste special” or Ctrl+Alt+V >> Check multiply >> ok
You can use the same process of “Paste Special” for subtracting or dividing values
Date & Time
YEARFRAC()

Purpose of this formula to get fraction of a year between two dates. It returns a decimal number.

DATEDIF() 
This function will return the number of days, weeks, or years between two dates.

DAYS360() 
This function is used in accounting calculations to get the number of days between two dates based on a 360 – day year (i.e 12  30 days months).

DAY(), MONTH(), YEAR() 
This function is used to returns the integer day, month, or year of a given date. 
Present Value & Rate of Return
XIRR() 
This is a financial function that returns the internal rate of return (IRR) that doesn’t have regular period cash flows. This function will help us to understand the rate of return earned on an investment. 
XNPV() 
It is also a financial function that is used to calculate the net present value (NPV) of an investment by using a discount rate and a series of cash flow that occur at irregular intervals. 
IPMT 
It returns the interest amount of loan payment in a given period, assuming the interest rate and the total amount of payment are constant in all periods. 
IRR() 
Internal Rate of Return (IRR) function is used to calculate the investment’s rate of return. 
ISPMT 
This function is used to calculate the interest paid during a specific period of investment 
ACCRINT 
This function is used to calculate the accrued interest for a security that pays interest on a periodic basis. 
ACCRINTM 
This function is used to calculate the accrued interest for a security that pays interest on maturity. 
CUMIPMT 
This function is used to calculate accumulative interest paid on a loan between any two payment periods. 
CUMPRINC 
This function is used to calculate accumulative principal amount paid on a loan between any two payment periods. 
DB 
This function will return the depreciation of an asset for a given time period based on the fixed declining method. 
DISC 
This function is used to get the discount rate for securities. It helps you to know the discount rate when you know the other detail of bond. 
DURATION 
The function calculates the duration of security (Macaulay Duration) that pays interest on a periodic basis with a par value of $100. 
EFFECT 
This function will calculate the annual interest rate with the number of compounding periods per year from nominal annual interest rate. 
FV 
This function is used to calculate the future value of an investment assuming periodic, constant payments with a constant interest rate. 
FVSCHEDULE 
This function will calculate the future value of an investment assuming a variable interest rate. 
INTRATE 
This function is used to calculate the interest rate for a fully invested security. 
MDURATION 
It returns the Macaulay modified duration for a security with par value of $100. 
MIRR 
It calculates the modified internal rate of return for a series of cash flows that occur at regular intervals. 
NOMINAL 
It will return the nominal annual interest rate with the given effective rate and number of compounding years. 
PMT() 
This function is used to calculate the total payment for a loan (principal + interest ) which is required to settle a loan or an investment with a constant interest rate over a specific time period. 
NPER() 
This function is used to get the payment periods for a loan based on amount, interest rate, and periodic payment amount. 
NPV 
This function is used to calculate the net present value (NPV) of an investment using a discount rate and a series of future cash flows. 
PDURATION 
It calculates the number of periods required for an investment to reach a specified future value. 
PPMT 
It is used to calculate the principal amount of investment or loan of any given period. 
PRICE 
It is used to calculate the price of security /bond per $100 face value that pays periodic interest. 
PRICEDISC 
It is used to calculate the price of discounted security /bond per $100 face value. 
PRICEMAT 
It is used to calculate the price of security /bond that pays interest at maturity per $100 face value. 
PV 
This function is used to calculate the present value of an investment or loan. 
RATE 
It is used to calculate the periodic interest rate i.e per period of annuity. 
RECEIVED 
It is used to get the amount received at maturity for a fully invested security. 
SLN 
It is used to calculate depreciation of an asset for one period based on initial asset cast, salvage value, and the number of periods over which the asset is depreciated. 
SYD 
It is used to calculate the depreciation of an asset, specifically the sumofyears’ digits depreciation for a specified period in the lifetime of an asset. 
TBILLPRICE 
It is used to calculate the fair market value of Treasury bill/ bond per $100 face value. 
TBILLEQ 
It is used to calculate the bondequivalent yield for a Treasury Bill. 
TBILLYIELD 
It is used to calculate the yield on a Treasury Bill. 
YIELD 
It is used to calculate the yield on a security that pays periodic interest that is bond yield. 
YIELDDISC 
It is used to calculate the annual yield for a discounted security like Treasury bill. 
YIELDMAT 
It is used to calculate the annual yield of a security that pays interest at maturity 
Lookup
vlookup/index/match 
To perform more advanced data lookups, these functions are most important. With the use of these functions, you can do horizontal and vertical lookups, left lookups, 2way lookups, and lookups based on multiple criteria. 
sumifs/countifs 
Based on single criteria with the use of these two functions, you can sum or count cells conditionally. 
If 
This function will allow you to make logical comparison and returns one value if true and another value if false. 
Other 
Pivot tables 
It is used to recognize, sort, summarise, group, count, total or average data store in a more extensive table. 