10 Excel Tips for your trade

Excel is literally the greatest analytical tool ever designed. Yes, some platforms do some things better, faster, encompass more, and provide more directly useful outputs but no platform does it all so consistently well compared to Excel. Every professional accountant, actuary, or analyst uses Excel in some way. I personally have worked with some of the highest tech accounting systems in contention and yet always use Excel for the dirty tasks. Why shouldn’t the creative mind use Excel for helpful procedures? Who is to say that the creative mind should shun away from Excel for their project management tool, time tracking instrument, or financial modeling tool? I can save you 100 dollars per month by eliminating the need to purchase other third party management systems by incorporating Excel into your life. Excel is creative. It is the most creative tool for individuals and organizations with analytical needs.

 1) Bank Data output, copy paste

It is amazing how many people avoid Excel because they do not want to enter data manually into a spreadsheet. Why not just take advantage of the data you already have and build around the data.  I know for a fact that all established banking institutions have a “Download Activity Statement “into Excel or CSV file. A CSV is simply a temporary instance of an Excel spreadsheet. Go ahead and download that bank statement onto your desktop and utilize the next two steps below to never enter data manually again.

2) Quick navigation around large data

So you have your bank statement activity for 12 months into a CSV or Excel file and you are annoyed at having to scroll through the data to get to the bottom of the file.  There is a really quick way to navigate around a big table of data, its called “Command + arrow key”. Hold command (or Ctrl on a PC) and click the arrow in the direction you would like to navigate. You are directly brought to the last cell in that table.

3) Quick highlighting for copy and pasting

In order for you to move data from one spreadsheet to another, or one CSV spreadsheet to an Excel spreadsheet, there is a quick way to highlight an entire table of data in 4 clicks.  If you hold the Command key + Shift + down-arrow + right-arrow, you will highlight the entire table.

Then from here we just copy and paste to move the entire table to the new spreadsheet. This is very helpful when dealing with a large amount of data.

Also another quick tip is extracting just specific columns of data from a spreadsheet in order to avoid useless columns of data for when you copy and paste into another spreadsheet.         Simply click on the header of the column, then press Command + Shift + down-arrow. This will highlight only the column you need to extract.

4) Wrap Text

Have you ever been typing a long description into a cell but want that description to show up without having to expand manually the column and row of for that cell? Select the cell and click “Wrap Text”. The spreadsheet will automatically fit dialogue into a single cell with no overlap. You can do this to individual cells or you can do this to entire tables of cells for faster manipulation.

5) Filter Data

Now that you have your data into a manageable table, you may want to choose only lines from a single day, or sort lines from highest to lowest, alphabetical, or simply display lines with a specific word. There is a catchall sorting function called “Filter” that will give you all of these options within one hot-cell. Simply highlight your entire table of data and select “Filter”. This function will provide you with a heavy amount of data sorting options.

6) Sum, Average, and Count

These three formulas can save you hours of time and headache when navigating data. Simply type =word(“select cells”).

For example:

You can count the number of cells, take an average of the cells, or sum them all up. It’s really that easy.

7) IF statements

Let’s not go running to the hills when we discuss “IF statements”, they are actually extremely easy to learn and implement. We just need to talk about the three key variables that make up an “IF statement.”

“IF statements” are made up three key parts.

=IF(test, True, False)

Think about it like a sentence, IF this TEST is TRUE than this result is displayed unless of course the test is FALSE therefore this other result will be displayed.

So in the above example: IF this cell is greater than 99 than the result is displayed as a YES unless of course the cell is less than 99 in which the result is displayed as NO.

Now copy and paste this cell into all of the below cells and you get a useful output that you can use to analyze larger amounts of data to a set test.

8) SumIF statement

This formula tricks a lot of professional accountants for no good reason other than the fact that no one has been able to break it down well enough into a logical sentence. The formula is used to sum a group of items based upon a very specific set of standards. Lets look at it as a formula, then a sentence.

=SumIF(data table, criteria, numbers group)

In a sentence it looks like this: SUM only IF we find a match within this DATA TABLE to our specific CRITERIA item while taking into consideration this NUMBERS GROUP.

Here is how the formula looks within Excel. Please take notice of the color specific cells and where they go within the formula.

9) Print Selection

Such a simple feature, you would assume that everyone on the planet would know how to use it; you are incorrect in that assumption. I have seen some of the ugliest reports given to me by professional accountants because they don’t understand the value of print selection. Simply highlight the cells you would like to print and print away. Print to PDF, print to paper, print to anything. Just make sure to select “print selection” and look, you can avoid massive amounts of useless data on your investor presentation. It is hilarious to me anytime I am in a meeting and someone passes around a financial projection and there are so many worthless variables and titles listed on the handout that it becomes hard to pick out the important data.

10) Pivot Tables

They are amazing things, pivot tables are. They are also the easiest tools to use within Excel. Seriously I personally think that =sum formulas are more annoying to prepare than pivot tables.

Simply start with a table of data.

Have a cell selected. Click on the pivot table shortcut, it looks like this.

Then you should see a chart appear in a new spreadsheet that looks like this.

Now the key to pivot tables, make sure your “field names’ are unselected to start. It helps clean the system and presents a blank canvas. The “areas” labeled “row labels” and “values” are your most important tools. Simply select and drag your “field names into the two “areas”.

As you can see, by doing this you can instantly create summaries of large tables of data without any effort on your part whatsoever.

Conclusion

Be lazy and consistent when it comes to self-budgeting, use simple tactics on a routine basis in order to drive important lifestyle habits. Import you banking data into a .CSV file, be selective and “copy and paste” specific columns to a new spreadsheet. Use “filter” to sort and maneuver around large data. Use “pivot tables” to summarize quickly based on useful criteria. Make life easy for yourself. Know how much you spent at certain places during the year and make your business, personal, and family budgets clean and organized. It is totally within your control and you now know how to do it.

And when all else fails, obtain quality guide material, such as our Bank Statement Management guide for Entrepreneurs here: