Be Super CA with these Excel tricks and Tips

---

Chartered Accountants are always tied up so saving on time is their big priority. Also they spend a lot of time on excel crunching numbers. Excel is an inevitable part of CA job. Its essential for them to be well versed with excel functions. So we collated these simple tricks and tips which will make their day to day job fast and easy.

Start learning and using these tips and also encourage your colleagues and staff to use it to imporve the productivity of your team.

This is part of 1 of series we will release on excel tricks and tips.

1. Pivot Tables

Accountants normally deal with huge financial data. There may be something like sales registers where you want to make reports like areawise sales, productwise sales. A pivot table allows you to take the systems data dump and quickly organize it for meaningful analysis.

To create a PivotTable, simply select Insert on the ribbon and then select Pivot Table. You will be presented with a box asking you to define the area of your PivotTable. Simply highlight your table, including the field row.

Data in a PivotTable can be further manipulated with filters.

2. Conditional Formatting

Here excel applies formatting only if a certain condition has been met.

There are many options like a) Highlight Cells Rules, b) Top/Bottom Rules, 3) Data Bars, 4) Colors Scales, 5) Icon Sets, etc.

You can remove it too. Click on Conditional Formatting and move almost to the bottom of the menu where you will see Clear Rules.

3. Faster Cell Selection / Navigation

Following are various options to select data cells quickly in excel.

Shift + Arrow Keys – Expands the selected range in the direction of the arrow key.

Shift + Spacebar – Selects the entire row or rows of the selected range.

Ctrl + Spacebar – Selects the entire column or columns of the selected range.

F8 – Enters the extend selection mode. Press F8 again to exit the mode.

Shift + F8 – Enters the add selection mode. Press Shift + F8 again to exit the mode.

Ctrl + G – Opens the Go To window to select a range or range name.

Ctrl + A – Selects the entire worksheet.

4. Ease of Copying Formatting of Cell

By using F4 key, you can copy the formatting from one cell to another. Apply formatting to one cell and click the cell you wish copy the formatting and just click F4 and thats it.

5. Use of Short-cut Keys

To move to the next worksheet: CTRL+PageUp

To move back to a previous worksheet: CTRL+PageDown

To toggle between workbooks: ALT+Tab

Formatting with two decimal places: CTRL+Shift+1

Format as Time: CTRL+Shift+2

Format as Date: CTRL+Shift+3

Format as Currency Value: CTRL+Shift+4

Format as Percentage: CTRL+Shift+5

Format in Exponential Form: CTRL+Shift+6

6. Count-If

Count-If function will count data cells, which satisfy a particular condition.

COUNTIF can be used to count cells that contain dates, numbers, and text. The criteria used in COUNTIF supports logical operators like >, <, = and wildcards (*,?) for partial matching.

Above tricks will surely save lot of time of yours and make you a super-accountant.