Microsoft Excel is an effective tool for Accounting and Finance Professionals. Individuals can extract lots of benefits by using Microsoft Excel. T tables of Microsoft Excel are well suited for performing various individual tasks. The formulas, functions and graphs are a great way to reduce time and efforts. There are ot of tricks and short cuts available in Microsoft Excel. Here are some useful tips and tricks in Excel which you may find helpful for your daily job activity.

Short cut for making total of the required number of rows: Assuming that you have values ranging from the cells A1 to A20 and you want to get the total in Cell A21. This activity can be performed in two ways: The first method is very common and used mostly by many accountants. The first method requires you to insert this formula in Cell A21 “=Sum(A1:A20)”.

Another effective and short way is that you need to press simply the Alt key + = by placing the cursor in the same cell as mentioned above. You will notice that the said formula appears in Cell A21 automatically. Now you need to press enter key. In this way, you will have got same results with two different methods for making total of the required number of rows.

Making Grand total and Subtotals: Very often, accountants are required to make grand total of subtotals in rows or columns.

Whether it may be the tasks relating to preparing schedules or segregating the expenses to be charged to various cost centers, there is a great need for making subtotals as well as grand totals. This activity can also be performed in two ways. Let’s take a simple example for the first method. Supposing that you have values in a particular worksheet ranging from cells A1 to A20; every fifth row has a subtotal, such as A1 to A4 with subtotal in A5 and so on. And you want to make grand total in Cell A21. In order to make a grand total of subtotals, you simply need to type this formula in cell A21, “=Sum(A1:A20)/2”- without quotation marks. . This will give you the grand total of subtotals in cell A21. If you have the value 1 in each cell, with all subtotals 4, then, the grand total should be 16.

As to the other method for making grand total of subtotals, it may be said that it’s a bit tricky. It’s not as simple as the above mentioned method. In order to make grand total, you need to assign this formula in the cells in which you want subtotals. The formula is: “=Subtotal(9,A1:A4)” , “=Subtotal(9,A6:A9)” and so on. Finally, for your grand total, this formula will be required for inserting, “=Subtotal(9,A1:A20)”. This will provide the same result, which is 16. Yet again, by applying two methods you will have made subtotals and their grand totals.

Right/Left Function: If there is a need for using some limited number of letters from a text existing in particular cells, you can make use of these functions. For instance, there is a text in Cell A1 “Commonsense” and you want to display only 6 letters of the word in Cell B1 starting from left. The function goes in this way: “=Left(A1,6)”. The word ‘Common’ will appear in B1. Similarly, by using this function in C1 this way, “=Right(A1,5), you will get the word ‘sense’. Simply speaking, the right and left functions are useful when you need to pick limited letters from a text starting either from left to right or right to left.

Cell References: There may be times when you need to make cell references for particular cells of the worksheet you are working in. For Example, you want the value in Cell A1 to appear in F1. The simple trick to do that is that you need to give this reference in F1, “=A$1”. This absolute reference is used with a dollar sign ($). Using cell references is a good way to keep a row or column constant in your worksheet.

FAREED SIDDIQUI

I am an Individual Member of Institute of Management Consultants of India. I am a Professional with a high creative capability based on Business, Management, Financial Accounting, Management Accounting, Finance and Management Consulting Field. I impart part time training and development programs. I write professional articles on business & management, business English and Software Engineering.

Related Articles

0

1.1K

The Basic Components Of Business Planning Function

Business Planning is an important aspect of every business organization. The following article discusses about the basic components required for planning function of management..

0

1.22K

How To Prepare A Ledger Using Microsoft Excel

More often than not, as an accounting professional you need to prepare Ledgers. You may be using the application software which might have been set up for your organization specifically.

1

302

Revive Indian Economy By Reviving Private Investments

There is sudden panic in the media and political circles about the Indian Economy. Private investment is not picking up in India.