We've all been there when people give a judgemental look when you use a mouse in excel or lag behind in remembering when to use quick formulas for your analysis. Well, here are Excel Tips and Tricks that all CAs must know.
PIVOT TABLE
A pivot table is a saviour when it comes to analysing bulky data with a field list, which can be customised as required as per your own convenience
Shortcut: Alt → N → V
SUMIF FUNCTION
The best alternative to the Pivot table is SUMIF Function. It is used when a huge number of line items are there in a worksheet and is very useful for large data in terms of Category and Field sections. Where we need the sum of values for only one criteria or category, the SUMIF function is more used.
Formula: =SUMIF (range, criteria, sum_range)
VLOOKUP FUNCTION
The VLOOKUP function performs a vertical lookup by searching for a value in the first column of a table and returning the value in the same row in the index_number position.
Formula: =VLOOKUP( value, table, index_number, [approximate_match] )
XLOOKUP FUNCTION
Using the functionalities of both VLOOKUP and HLOOKUP, Xlookup is the new generation lookup function. It performs either a vertical lookup or horizontal lookup by searching for a value in a row or column of a table and returning a corresponding value in a table.
Formula: =XLOOKUP( value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode] )
CONCATENATE FUNCTION
Often in excel, one might not find text in a well structured manner and for that, you might want to split the content of one cell into individual cells, or do the opposite - combine data from two or more columns into a single column. For example, one might want to bring the name of a person and address together in a cell that is otherwise contained in two different cells.
Formula: =CONCATENATE(text1, [text2], …)
INDEX MATCH
Another alternate to the XLOOKUP function is Index Match. It is a very powerful combination of excel formulas that takes analysis and modelling in Financial terms to the next level.
Formula: =INDEX (array, row_num, [col_num], [area_num])
Hope these formulas minimise your manual efforts when it comes to analysing bulking data and the anxiety it brings with it.
ONLINE EXCEL COURSE - EXCEL MASTERY PROGRAM BY CACLUBINDIA
Do you want to learn to make your work stand out? Then CAclubindia's Excel Mastery Program is the right fit for you. The unique course structure combined with CA Rishabh Pugalia's education, job experience, training experience, and passion will make this course a journey worth taking.
In this Mastery Program, you will learn those topics and methods which others don't teach. These include:
- Use of complex Lookup Formulas such as OFFSET, INDIRECT in a simplified fashion with multiple application
- Hidden Tricks & Settings – Compare 2 Excel files, Split Text to Rows, Unpivot Columns
- Projects for Finance, and Sales
- Use of Cell Referencing ($) in formulas
- Fill intermittent black cells using Go To special
- The common mistake of Vlookup users
- Using SUMIFS to find the sum of values between two dates
- A hidden trick of COUNTIFS to do VLOOKUP for duplicate values
- A hidden trick of Consolidation using SUM for multiple sheets
- Fuzzy Lookup can do what VLOOKUP can't
- Hide a sheet so that no one can unhide easily?
- Why does a Date in Excel look like a random number?
- Rectify dates format using Text to Columns
- TRIM formula to remove excess spaces in a sentence
- Use SUMIFS & COUNTIFS with 3 criteria
- Basics of Macro etc.
So, what are you waiting for? Take the road towards EXCELlence with CAclubindia now!
Click here to view / enroll the course
CAclubindia
