I am passionately in love with math. I, capital L, Love long division. When I was a kid (and maybe even a few months ago when I was home for the holidays), I’d make my mom give me strings of large numbers to divide by other strings of numbers – for fun! I got a 5 on my high school AP Calculus test and owned a graphing calculator. I also had great friends and a date to the prom, so don’t worry, this math nerd story has a happy ending. I love that with math, you get a straight answer. I find calm and relaxation in its square-ness. As an adult, most answers to questions come in shades of gray; but with math, there really is a black and white, a right and wrong. Two plus two will equal four, no matter what your hair looks like that day or whether you’re a Republican or Democrat. Math feels like a strong hand to hold on the craziest of days.
Now, I know not everybody feels this way about my beloved. Math makes many people sweat and break out with anxious hives. And that’s okay, you don’t have to love it, but you shouldn’t be afraid anymore. Vow today to take control of your checkbook, to get a handle on your monthly budget and to call your accountant with confidence at tax time knowing that your deductions are itemized and in order. The secret? A simple Excel spreadsheet. With just a few simple steps, the Excel spreadsheet will happily and easily do most of the work for you. Sure, call in the experts for the big stuff (even I don’t do my own taxes), but if you arm yourself with the knowledge to take care of the small stuff along the way, April 15th will be much more pleasant and you’ll have the ability to create a financial plan for yourself based on truth about your spending, not just a vague idea. Do you know how much you spend on food each year or shoes or supplies for your small business? You should. Having this knowledge will bring peace and calm to a subject that usually creates anything but, giving you the power to change what’s not working and to celebrate what is.
I am going to take you through creating a basic Excel spreadsheet step by step. Be warned, things are going to get scary at about Step 5 (or Step 1 for some of you) – just breathe. Use this opportunity to embrace your fears and to commit yourself to sticking with it. You will be so happy you did! Here we go:
- Open Excel, it’s that big green “X” in your dock (if you’re a Mac user, PC’s you’re on your own!) Do this now and then come back for Step 2, seriously go ahead, there is no time like the present to learn something new! I’ll wait . . .
- Name your columns going across the top of your spreadsheet. The header columns are labeled A, B, C, etc. Let’s say we want to track our basic expenses for the year. We would name our columns: Rent, Utilities, Food and Entertainment (yes I’m simplifying, but wouldn’t life be great if these really were our only expenses!)
- Be sure to leave the first column empty, so we have room to name our rows in Step 3.
- Now, go type those into your sample spreadsheet, starting with “Rent” in column B, “Utilities” in column C, etc.
- Also, you can adjust the size of any of the rows or columns by hovering over the grid line, clicking and dragging. Easy as pie (mmmm. . . pie!)
- Now, save your document. Give it a fun name that will make you smile. Part of getting over our fear of math is re-framing how we see it. If you give your spreadsheet a name that makes you smile and laugh, you’ll be more apt to actually open it and use it. Seems silly, but it works! I’m a big fan of using song lyrics.
- Next up, name your rows going down the left hand side. These are labeled 1, 2, 3, etc. For our sample expenses spreadsheet, they would be the months of the year.
- Start with row 2, so January doesn’t get mixed up with our column names. For those of you who remember playing the game Battleship – you’d be typing “January” into A2 (it’s a hit!), “February” into A3, etc.
- We also want to name the row after December “Total”, so we have a place where it all adds up!
- This is where the magic happens. We are now going to create our math functions. We want to add up all the months in each column, so we know how much we’ve spent in each category for the year. Put away your calculator, you don’t need it. Seriously, let it collect dust. Excel has got you covered.
- Put your cursor in the Total box for our Rent column (That would be B14 for those Battleship lovers)
- While in B14, click on this symbol in the toolbar: ∑. Here is what will magically appear in your Total box: =Sum( ). In the parentheses, type “B2:B13”.
- I know these numbers and symbols are scaring your right now, but hang in, keep reading. Take it step by step. You got this! Congratulations, you have just told Excel to add your Rent from January to December.
- So when you enter your rent into this spreadsheet each month, Excel will do the math. Easy! Just for fun, try it. Type “500” into the January and February Rent boxes. DO IT! Did it total automatically? Do you feel giddy and awesome? Yes and yes!
- Now, repeat Step 5 for the other boxes in Row 14, remembering to use the correct letter for the column. B was for “Rent”, C will be for “Utilities” and so on.
- Okay, we are almost there. Take a quick stretch and sit back down. Now you may be asking, what do we do for the categories that will have multiple monetary entries throughout the month? Good question! Most people have one rent payment, so that’s easy to enter, but what about our Food column? To add things as you go within a box is even easier than what we did in Step 5. Let’s add up some food purchases in January.
- Click into the D2 box and type this: =2.99+3.55 and then press enter. Voila! Excel added it for you!
- Any time you have an expense to add for Food in January, click on that box and add it on (+expense amount, press enter). Excel will add it up there, as well as updating the yearly total down in row 14. Two equations for the price of one!
- Play! This spreadsheet isn’t real. We are making up the numbers. So, save it now and then go wild. Click around and add numbers and play, play, play! This is the best way to get comfortable with something new.
- You can undo any “mistake” (Command Z for the cool kids) and mistakes are golden when you’re starting out. Make them here, so you’ll know how to handle them when you make your real life spreadsheet.
- Save as you go and make it fun. Put on some music and get down with your math nerd self!
- Pour yourself a glass of wine or ice tea or chocolate milk and pat yourself on the back. Feel proud of yourself for taking the risk to learn something new. Rest easy tonight knowing that you have a new tool in your life skills toolbox to better manage your finances and therefore your life! And if you have any questions, give me a call at BSLM, I’ll be happy to talk you through it.
Post authored by Erin Jerozal