Excel Basics

I always worry about offending my intelligent readers by writing about a subject that is so basic they resent that I would presume they are not already very familiar with it. But then again, maybe I presume too much as I have spent years perfecting some tasks that might benefit others.

Take spreadsheets for instance. While I do not do a lot of macros or even pivot tables, I do have several intensely detailed and complex spreadsheets that I maintain 5-10 times a day. The most complex is for my budget, combining business and personal in 17 color-coded tabs. It is kind of massive in tracking business and personal expenses with columns for multiple bank accounts, credit cards, employee payroll, business expenses, grocery expenses, Amazon spending (3 tabs!), current credit card balance, MTD (month to date) and YTD (Year)). But also I track utilities (KHz used and bill amount), Charity donations, medical expenses, estimated federal & state taxes, other deductibles, car payments, etc. Many include charts. Other tabs are for Business (with similar columns), budget projections, tax calculations, medical expense breakdown, project expenses, reports and charts, stocks, credit cards, and more. Yeah, it is HUGE and I probably need a life as I spend way too much time on it. But keep in mind while others are playing video games or music or movies, I am playing with spreadsheets in my spare time!

I do not use QuickBooks given the depth of my spreadsheets and the reports I can garner from it. My accountant is happy with it, so why change and deny myself those blissful hours of maintenance? Other people play games on their computers in their spare time. I work spreadsheets. My daughter's favorite line to me is "Did you put that on a spreadsheet, Dad?" Yeah, I guess I am predictable in my addiction.

Other spreadsheets I maintain are grocery shopping list (tracker for way too much detail on past shopping habits), baseball (tracking my favorite team and league stats), NFL (same stats as the MLB), client projects (one for each client), Features proposal for potential new work (which is a great basis for an SOW (Statement of Work)) and a bunch of ones I just do now && then for fun (like Lottery What If).

Here are some basic methodology tips for Excel:

Create, rename and color code a sheet: Click on Insert > Worksheet, Then right click on the new tab called "Sheet" and select Rename. Right click again and select Color.

Lock a reference: Put a $ sign in front of the column or row, i.e. $A4 locks the column when you copy & paste it elsewhere on your sheet. A$4 will lock only the row. Similarly, $C$7 locks both column and row. Without locking, the paste moves the col/row reference as you paste. i.e. pasting B16 with the formula (IF(B17>0),B16-B17,B16) into cell B17 and further down (hold down the shift key and highlight the column range you want to past to) will always refer to the cell directly above the one your in. Handy for keeping a balance.

Fill in values: When entering a series of months, entire dates, numbers or days of the week, etc, you can save time by right clicking on the bottom right of the cell (a small square appears in the corner) and dragging down the range to be filled in will cause the entries to fill in properly according to the source. i.e. months will advance by one over the entire range.

So some basic spreadsheet 101 here might help you create a simple budget. Here is how I built mine.

Fig 1

Looking at figure 1, the columns are as follows:

• Date
• Code/Def
• Check number or note
• Description
• B for which Bank account
• Credit
• Debit
• Checking balance
• Credit card balance
• E-account balance
• Cap Checking balance
• Cap Savings balance
• Other savings balance
• Grocery and cash MTD
• Amazon card balance
• Amazon MTD
• Amazon YTD
• Church (God) tithes
• Church income
• Medical expenditures YTD
• Charity YTD
• Car Pmt
• Etc.

Note my use of color coding on both the columns and in the description. This helps me rapidly find what I spent on that regular expense in the past.

The first column - A, is a simple date showing month-day (you should know what year it is, but click on it to view if necessary). Every year, archive anything older than 2 years and trim your sheet to minimize file size. Then B is a code column for noting Deductible (D), medical (M), employee (E), Amazon (A), etc. We'll reference these in other columns and worksheets.

Next C is a column for check number or credit card ((You can't mix this with the code column as what if you use your credit card for a medical expense?).

Colum D is for Description. E refers to which account the payment\deposit references. F & G are for crediting or debiting from (I know, the opposite of wacky accounting, I always was confused in accounting class).

The other columns all take their input from the above columns. Checking (H) obviously calculates from the debit\credit added to the balance after checking which account it impacts. The formula is an easy one: =IF(E983="m",H982+F983+G983,H982). It checks the account = m (main), if so it performs the calc, if not, it merely takes the previous balance forward.

The following columns are similar in their formulas, checking to see if there balance is impacted (credit card, Amazon, etc.). This is slightly tedious, but not difficult. All are simple If statements checking a column’s value then performing a calculation If-then or else. Rather than try to calculate daily interest, I add it as a line item. It makes calculations less complex (the KISS principle is my motto). Note that I often have the code in the column header (frozen view) to aid my poor overwhelmed memory.

Now you can reference that data in another spreadsheet. Just be sure that you are not going to sort or you can mess up your references. This is good for month end or year end when you are probably done inserting entries prior to that date. A simple calculation referencing the cell such as for medical will pull from column T and look like ='Personal Ckg'!T508. This simply means tab “Personal Ckg” (that’s the exclamation point), column T, row 508. 

Copying a value down is easy and the cell references will advance appropriately. If you want to lock in a column for another calculation, you use the $ sign (i.e. $T508 makes the calculation always use column T even if when copying the formula to another column).

For example, when creating a new column for the next year as follows:

Copying the formulas from the 2015 column to 2016 requires that the source column be locked in, i.e. $T<row>. So the formula in AB 3 would look like: ='Personal Ckg'!$W982 while AC4 (next column and next row) also refers to the same data ='Personal Ckg'!$W983 for the 2016 column.

The details in this spreadsheet could fill a 1,000 page book. But hopefully this can get you started. Try a simple budget and add the other columns as you grow. Tracking your checkbook (in lieu of a check register – which I do not use as it is all in the spreadsheet – AND BACK IT UP, like nightly!). Try these basics:

Date

Code

Description

Account

Credit

Debit (separating these makes accounting easier at year end)

Checking balance

Savings balance

Use my guide above to set it. Get a book on basic Excel, including formulas. Later you can add tabs, macros, locked fields, pull-down choices, etc.

I hope I have not confused you with my shallow explanation. There is so much to say about this subject that it would take a LARGE book to cover it all. If you have questions, let me know. Like most nerds, I LOVE to talk about & explain this stuff. Hopefully this gives you an idea of how to begin your own spreadsheet for budgeting, shopping or other things you want to track.