Reetu | Nov 6, 2023 |
Use of Advance Excel in Daily CA Practice
Excel is a valuable and in-demand skill in a wide range of industries. Advance Excel skills include the ability to create graphs and tables, work efficiently with spreadsheets, and perform calculations and automation to process massive amounts of data. With the rise of big data and data analytics, Advance Excel abilities are a valuable asset in both the job search and day-to-day professional activities.
In this article, we discussed about the Index of the Formulas and Quick Briefing of Excel. Let’s dive into it.
1. How MS Excel Is helpful:
2. Basic Points to be Kept in Mind while Working on Excel:
In Excel, an absolute reference is a cell reference in which the column and row coordinates stay constant while copying a formula from one cell to the other. A dollar symbol ($) is used before the coordinates to correct them. For instance, $D$2 is an absolute reference to cell D2.
This reference changes when the formula is copied to any other cell or any other worksheet. Relative cell references are used whenever calculations need to be repeated.
This function allows you to join the data in two different cells into one single cell.
Sumif / Sumifs
The Microsoft Excel SUMIFS function adds all numbers in a range of cells, based on a single or multiple criteria.
The INDIRECT function in Excel is used to create a reference specified by a text string. It enables us to create references from cell values. This function is one of the lookup and reference functions in Excel. It is most used for building dynamic references for calculations and chart ranges.
The AND function is a premade function in Excel, which returns TRUE or FALSE based on two or more conditions. It is typed =AND and takes two or more conditions.
The Excel OR function returns TRUE if any given argument evaluates to TRUE, and returns FALSE if all supplied arguments evaluate to FALSE.
The IF function in Excel performs a logical comparison between two values. The result of the IF function is either TRUE or FALSE. And gives resultant value based on the TRUE or FALSE.
The IFERROR function in Excel is designed to trap and manage errors in formulas and calculations. More specifically, IFERROR checks a formula, and if it evaluates to an error, returns another value you specify; otherwise, returns the result of the formula.
Type the formula =ABS( ) into the cell. The ABS function tells Excel to calculate the absolute value of the number or cell reference that you specify.
This works exactly like Autofilter / Filters in Excel. Just the difference is that it allows to apply filter on single click option.
To keep an area of a worksheet visible while you scroll to another area of the worksheet.
The Excel SORT function sorts the contents of a range or array. Values can be sorted by one or more columns. SORT returns a dynamic array of results.
Text To Column
To separate the contents of one Excel cell into separate columns, you can use the ‘Convert Text to Columns Wizard’.
There are endless shortcuts in the MS Excel. Few of them are listed here which are used commonly in our daily usage. If anyone can not remember that, simply just to separate the contents of one Excel cell into separate columns, you can use the ‘Convert Text to Columns Wizardpress “Alt” and see the further shortcuts visible on the top of the screen.
This function is used to Change the text to Uppercase.
This function is used to Change the text to Lowercase.
This function is used to Change the first letter of each word Uppercase and rest to Lower case.
To remove the Additional and unwanted Spaces (“ “) from the cell.
This Function can be used in multiple ways like extracting month name form date, Weekday and many more, or to convert any cell to text format.
The Excel TODAY function returns the current date, updated continuously when a worksheet is changed or opened. The TODAY function takes no arguments. If along with date time is also required, “Now” Can be used.
Value – The Excel VALUE function converts text that appears in a recognized format (i.e. a number, date, or time format) into a numeric value. Normally, the VALUE function is needed in Excel, because Excel automatically converts text to numeric values.
These functions of Excel are used to extract the specific number of characters from left/right/mid of the cell.
The Excel EOMONTH function returns the last day of the month, n months in the past or future. Use a positive value for months to get a date in the future, and a negative value to get a date in the past.
This feature of excel is used to check the errors in the entire sheet, Specifically Circular reference errors.
Using this function all the duplicate rows can be removed at once.
The Excel SUBTOTAL function returns an aggregate result for supplied values.
SUBTOTAL can return a SUM, AVERAGE, COUNT, MAX, and others (see table below), and SUBTOTAL function can either include or exclude values in hidden rows.
This feature groups the multiple columns or rows so that the same can be expanded or Hide at single click.
To protect the specified sheet so that no one can change and data in the cell and allowing specific range to be marked as editable. Also formulas in that sheet can also be hided.
The Excel HYPERLINK function returns a hyperlink from a given destination and “friendly name”. You can use HYPERLINK to construct a clickable hyperlink with a formula.
The HYPERLINK function can build links to workbook locations, pages on the files on network servers.
The Excel DATEDIF function returns the difference between two date values in years, months, or days. Syntax for this Function is not visible in excel as it is only documented in Excel 2000, but you can use it in your formulas in all Excel versions since that time.
This function is used to Change the format of the date so that the excel can read that in Date format as sometimes excel format and given format of dates are different.
This tool of excel allow us to give a name for a range and then same can be used in difference formula with indirect Function.
The Excel XLOOKUP function is a modern and flexible replacement for older functions like VLOOKUP, HLOOKUP, and LOOKUP. XLOOKUP supports approximate and exact matching, wildcards (* ?) for partial matches, and lookups in vertical or horizontal ranges.
This tool of Excel Records our repetitive job to be done on single click or pressing specified key combination.
You can reach the author CA Akshay Garg at [email protected] or at 7988355606.
In case of any Doubt regarding Membership you can mail us at [email protected]
Join Studycafe's WhatsApp Group or Telegram Channel for Latest Updates on Government Job, Sarkari Naukri, Private Jobs, Income Tax, GST, Companies Act, Judgements and CA, CS, ICWA, and MUCH MORE!"