Using Excel

(Okay, this picture has nothing to do with today’s topic – it’s a copout picture. I’m still trying to set up this website and I am encountering a few problems due to learning to use this tool. And maybe I am being a little too ambitious on what I want to accomplish. Besides, sourcing pictures for these topics is going to be a challenge.)

Since I mentioned in prior posts that I use Excel to help manage the financials of 150 projects as well as a way of presenting streamlined information, I need to point out a couple of things that helps when using Excel. On the internet, you can find references to instances of high profile Excel errors (the two that pops to mind are the “London Whale” trader error and the Carmen Reinhart/Kenneth Rogoff error) so Excel errors are a real concern.

Unfortunately, sometimes there are no other alternatives to using Excel. I will first try to use a more robust software and if it doesn’t give me what I need, then I resort to Excel.

I basically rely very heavily on 5 or 6 functions in Excel:

1. Vlookups although I’m starting to use a combo of Index and Match

2. Nested if statements

3. Sumproducts

4. Conditional formatting

5. Sumifs and countifs

6. and lastly, pivot tables.

These don’t prevent errors; they are just tools in my arsenal to automate activities or to conduct analyses. But I wanted to point out that I really don’t use a whole lot of fancy functions in my day-to-day work.

To aid in reducing errors, I do the following things:

1. Eliminate as much as I can any manual entries. If I have to do a manual entry, I try to have a certain area be dedicated to manual entries so I know what entries need to be updated each time I use the spreadsheet. Whenever I take over a new job function that has spreadsheets, the first thing I do is find out what manual entries are made and try to eliminate them either through formulas or use of pivot tables.

2. I set aside a tab for downloaded data. Sometimes I may have to source data from different sources so there will be multiple tabs dedicated to each downloaded source.

3. When creating formulas, I set aside certain columns in the beginning to be dedicated to formulas and highlight the fonts in red. A lot of times with downloaded data, I like to create categories of expenses or revenues so I insert a couple of columns, say A through C, to be dedicated to formulas. The formulas are consolidated in one area so I don’t have to search for the cells with formulas.

4. Also, again, when creating formulas, I set them up so that I can easily copy them downward as the dataset gets larger. The formulas have to be replicable and changeable in the correct way. They also have to be set up in a fashion such that when I insert a row or column, there will be no adverse impacts. But I must say, I prefer that I don’t have to insert rows or columns.

5. I avoid embedding fixed constants in my formulas if those fixed constants might (and will) change in the future. I don’t want to have to go searching for them. In the same area where I have manual entries set aside (see number 1), I add the constants and variables here. The idea is to have one easy place to find the variables for updating.

6. Use pivot tables rather than setting up a table of formulas referencing the cells to generate information. Once you create your report or table using sums and averages and counts, you can’t easily change the table or report to add new categories or to restructure the information layout. Pivot tables, on the other hand, can be changed very easily. It’s such a powerful tool for slicing and dicing.

7. Once I have my report or calculations set up, I double check my numbers. I do the 50,000 feet view to see if the numbers make sense. Throughout the spreadsheet, there will be multiple cross checks to see that the numbers add up correctly. Included in the cross checks are the totals comparison between the pivot table and the downloaded data. There are many avenues of double checking the numbers.

The aforementioned tips work fine when it is just you using the file. Once you start creating files that have to be used by multiple people, then other steps need to be taken. It may be that a database tool such as Access is needed for holding large amount of data. Or you might have to lock and protect certain cells so that the formulas are not inadvertently overwritten. You will probably need somebody doing the 50,000 feet view to cross check the numbers.

This approach to spreadsheet creation has helped me create reliable reports and tools that I have been able to use for at least a year, some for multiple years.