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.

Index of formulas to be Discussed

• Absolute Referencing
• Relative Referencing
• Concatenate
• Sumif
• Indirect
• And/Or
• If
• Iferror
• ABS
• Compilation of all formulas to make automated template for ease of our work
• How such formulas can be used in preparation of financial statements

Quick Briefing of Excel

1. How MS Excel Is helpful:

• Accuracy
• Quickresults
• Tailor made conditions as per our requirements
• Chart Analysis
• All data is Linked
• Formatting options, Etc.

2. Basic Points to be Kept in Mind while Working on Excel:

• Common Formula in all the Rows/Columns as per our format
• Using absolute or relative reference while using references in Formulas
• Use of tables where ever possible
• Use of Macros, Queries & Connections, Power Pivot where ever required (Advanced Part)

Quick Briefing of Formulas (Basics)

Absolute Referencing

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.

Relative Referencing

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.

Concatenate

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.

Indirect

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.

And/ Or

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.

If

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.

Iferror

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.

Abs

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.

Slicer

This works exactly like Autofilter / Filters in Excel. Just the difference is that it allows to apply filter on single click option.

Freeze

To keep an area of a worksheet visible while you scroll to another area of the worksheet.

Sorting

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’.

Shortcuts

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.

Basic Shortcuts

Upper

This function is used to Change the text to Uppercase.

Lower

This function is used to Change the text to Lowercase.

Proper

This function is used to Change the first letter of each word Uppercase and rest to Lower case.

Trim

To remove the Additional and unwanted Spaces (“ “) from the cell.

Text

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.

Today/Now

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.

Left/Mid/Right

These functions of Excel are used to extract the specific number of characters from left/right/mid of the cell.

EOMonth

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.

Error Checking

This feature of excel is used to check the errors in the entire sheet, Specifically Circular reference errors.

Remove Duplicates

Using this function all the duplicate rows can be removed at once.

Subtotal

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.

Grouping

This feature groups the multiple columns or rows so that the same can be expanded or Hide at single click.

Sheet Protection

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.

Hyperlinks

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.

Datedif

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.

Date Formatting

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.

Range Naming

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.

X-Lookup

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.

Macro Recording

This tool of Excel Records our repetitive job to be done on single click or pressing specified key combination.

Why to prepare FS using trial as a base:

• Accuracy
• Avoiding casting errors
• Quick and accurate effect of MOCs or error corrections
• Record of Multiple versions
• Time Saving
• No need to remember what has been considered in particular figure
• Easy to map opening balances in next period audit

You can reach the author CA Akshay Garg at [email protected] or at 7988355606.