50+ Excel Tricks Accountants Love by Heart! – Episode 1

I often confront a question “what to know in Excel”. Answer to this is not easy. To me Excel is like a journey and it never ends unless you stop. Similarly more you use Excel more you learn about it better you become going around things easily using Excel’s almost endless combination of features, formulas and shortcuts. Even if you somehow conquer it you will have to learn many things new as the new version is floated.

This article is for novice to experts alike and I narrowed down the question to what accountants love the most in excel and this might give you a jump start to embark on excel. The following list is the result of years of experience being an accountant, using excel and above all having good people around me and that includes my friends and students who just never pause to make me learn something new. I specially like to thank one of my students Diana Levinets helping me in getting this list done.

This article will be based on Microsoft Excel 2010 version but as many things in 2010 Excel version are same as in 2007 so you might/can use these ideas in 2007 version of Excel as well.

Have you checked our handpicked list of Top 10 Excel Keyboard Shortcuts for Accountants yet? I’m sure you will not regret visiting this page.

Knowing these will definitely bridge the gulf between an excel rookie and excel expert accountant. In no particular order these tricks are a must have items in your skills back pack and knowing these simple tricks I assume you can safely tell anyone that you do have some knowledge of excel.

1 Freeze panes

freeze panes icon Consider you have a long list of items and every time you scroll up or right the first coloumn or row disappears. Such a mess right? What if first row and/or coloumn stays static and the rest moves? Read the tutorial to know how

Have a look at the following picture. Problem is that when excel sheet is moved up or right the important headings and column gets into the fold. To overcome this problem check out our tutorial on freeze panes

2 Sort

sort and filter icon Often you want to put the data in particular order ascending or descending order. Well this is as easy as 1-2-3 if you are using Excel. In few clicks you can make the data follow your order as your wish and command

Who doesn’t like things to be in order. Look at the following data all set to be put in order in different way. Psst! what if I tell you you can sort even using colour? Don’t believe? Read, watch and stand witness Excel’s sorting might in our tutorial

value sort

4 Split margins / Split Windows

split button small Freeze panes has its limitations. For example you want right side of the sheet static. Or what if you want even the static portion to move? In short freeze panes won’t help. From this point forward Split Margins or split screen or split windows can help.

Following figure shows how you can split your work space in four independent quadrants and observe them without moving the other part of the same worksheet. How to get this around. Check out our tutorial on Split view.

value sort

A5 Multiple Windows / side by side view

multi window button Sometime you wish that somehow you can see and access different sheets within same workbook without clicking sheet tab below. So its definitely not working and even split windows is not helping as it is stationed only at one sheet. Here multiple windows works its tango!

Sneak peak of what I am talking about is as follows:

value sort

6 Remove duplicates

multi window button One thing is universally true humans are prone to error and when it comes to data entry job sometime a record is made twice. Now finding these duplicates is a difficult job. But Excel handles that with one handy feature: Remove Duplicates

Have a look at the output when a data is checked for duplicates using this trick and excel responds in no time. Want to know how? Check out our tutorial

value sort

A7 Text to Coloumn

This feature is one tool for many problems. Its just up to you how to use it. Many a times you want only a specific chunk of data but its so hard to separate it from the whole bit. Like separate first name from last. Separate year from date etc. And as I said earlier its applications are endless and not limited just to splitting data.

A8 Tables

Yes Excel cells and coloumns and rows and you can apply colors and borders but problem with that formatting such way is kind of  static i.e. if your data expands or contracts it does not adjusts itself accordingly. Solution to this is Tables.

9 Printing

Excel’s print is not just a Ctrl + P. Its this and much more. One of the nuisance often encountered by users is to print same headings on each page of every print. Or what if your data has overflown from print area i.e. either columns are missing or rows are missing. What to do? Well all of these problems can be sorted with few clicks

A10 Excel’s Auto fill /correct / complete /autosum

Often in data related works we have to repeat the process. That will be so easy and error free if what we intend to write is automatically inserted for us. Or if there is a mistake then it gets corrected automatically. Who don’t like if things start happening automatically as we wanted. Well excel have you covered even for this.

This is the first episode of the series covering 50 treats Excel offer. Next episodes are soon on their way. In the mean while I hope you will practice and apply these things to make your work time efficient and productive.