10 things a Finance Professional should know about Microsoft Excel
From small daily expenses to top-level profit and loss, everything is accommodated in this spreadsheet.
Every department in an organization needs this tool to get things done but the most effective use of Excel can be done in the Finance department.Now when we talk about Finance, it is all about payment, invoicing,numbers, calculations, and no other tool can be sharp enough to play with numbers as our very own Excel.
PivotTable: Imagine a huge chunk of data given by your Boss which needs to be converted into an attractive dashboard.Here what you need is the Pivot table. Pivot table looks complicated but believe me, there are no formulas required to use this tool, it works on a simple technique of Drag and Drop. Pivot table is a tool that summarizes a huge data in an organized manner and u have the ease to slice and dice the data in different ways as per your requirement.
Location: Insert – Pivot Table
Conditional Formatting:As the name suggests, it is a tool used to apply conditions on a cell or a range of cells to get the desired formatting. There are various conditional formulas that can be applied depending on the requirement. For example, we can use conditional formatting to change the colour of the cells having a value greater than 200 or less than 500. This is very useful when you need a glimpse of some data where the Filter tool is not serving the need.
Location: Home– Conditional Formatting
Lookup Functions: Another most popular function of excel is the Lookup and reference function. The main use of this function is to lookup a value in a large data table and give u a corresponding value back residing in the same row. The commonly used LOOKUP functions are VLOOUP and HLOOKUP.VLOOUP and HLOOKUP work on the same technique, the only difference is that V stands for Vertical and H for Horizontal Lookup. If yourdata has column headers then you will need VLOOKUP and if it has Row headers then HLOOKUP will serve the purpose. In VLOOKUP, it is important for the value you are looking up to be at the left-most column and the value that’s being returned to be in the adjacent column and similar positioning logic is to be applied for HLOOKUP.
Location: Formula – LOOKUP and reference.
Macros: In technical terms, Macros is VBA (Visual Basic Application) which is a written code that executes commands to do things in Excel. By using Macros, you can automate your task which you do repeatedly. Macros allows you to perform multiple operations just by clicking a simple button. It is considered as the most complicated function of Excel, butit’s the most interesting function and is fun to work withonce you are equipped with the tricks. To explain in simple terms, it’s just like recording an activity on any digital device, but the difference here is next time when u want to perform the same act, you don’t need to repeat your actions, Macros will do it for you because the steps are recorded in its memory.
Location: Developer – Record Macro (If you don’t have Developer Tab installed on your Menu bar then just right click on any tab and go to Customize the Ribbon, you just have to check the Developer button and it will appear on the menu bar)
Scenario Manager: This is a great function that can be used to analyze different scenariosin any business and compare the results. It gives you the option of creating different conditions that you forecast for your business and the expected outcome from the same. It alsoprovides an option to summarize all the conditions on a single dashboard.
Location: Data – What-if-analysis – Scenario Manager
Conditional Logic: The conditional Logics available in Excel are IF, AND & OR, however IF remains an important functionused in most of theapplications. Whenever u need a formula that’s based on a condition, IF functionhelps you get the required output. Sometimes you need an IF condition inside an IF, the easy way to recognizesuch a situation is to understand when you need to applya ‘but’ in your sentence.For eg, IFvalue is <10 and >5 then OK but IF >=10 then GOOD.
AND is used when there are 2 or more conditions to be tested on a value. For an AND statement to be TRUE, both the conditions must be True whereas in OR statement we need only one condition to be TRUE for the value becorrect.
Location: Formulas – Logical
COUNTIF: Another useful function that uses IF condition is COUNTIF. This one is straight forward as the name suggests, it counts things IF a condition is met.
SUMIF: This function is like COUNTIF except for the fact that it adds two or more arguments.
The better version of IF condition is IFS which is used when you want to put condition on exceptions especially when these exceptions are more than one, for instance; you have to sum up the payment received from customers whose sales revenue was more than 1 Lac along with an exceptional condition of those customer belonging only to a particular city or country.
EOMONTH and EDATE: EOMONTH is used where calculations are based on the Month end criteria as it helps get the End of Month date for an input.Similarly,EDATE is a tool that helps get an exact datewhich can be useful to schedule Customer payments.
EOMONTH and EDATE
EOMONTH and EDATE
Location: Formulas – Date & Time
Absolute cell reference: There are 2 main types of cell reference in excel named Relative and Absolute. Relative is where when excel sees the location of the cells relative to the location of the formula. But in some cases, you don’t want the cell reference to be changed and that’s whereAbsolute cell reference is used. It is basically freezing a particular value in a cell to apply it in required formulas.
Absolute cell reference
Absolute cell reference
In addition to above, there are many excel functions which can ease down the job of a Finance specialist. Excel is an Endless learning journey where you will keep on inventing new things in every pace.
So, don’t stop your journey in the mid and keep learning and rising in Life.