<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Sander DiAngelis on Medium]]></title>
        <description><![CDATA[Stories by Sander DiAngelis on Medium]]></description>
        <link>https://medium.com/@sanderdiangelis?source=rss-c5ce817ed806------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*jm_Z-HYiYvgC3U_7b7h_Ew.jpeg</url>
            <title>Stories by Sander DiAngelis on Medium</title>
            <link>https://medium.com/@sanderdiangelis?source=rss-c5ce817ed806------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sun, 22 Nov 2026 09:15:36 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@sanderdiangelis/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[How to Build a Budget and Transaction Tracker with Google Sheets (Get a Free Template )]]></title>
            <link>https://sanderdiangelis.medium.com/how-to-build-a-budget-and-transaction-tracker-with-google-sheets-get-a-free-template-f854b7bd0ea8?source=rss-c5ce817ed806------2</link>
            <guid isPermaLink="false">https://medium.com/p/f854b7bd0ea8</guid>
            <category><![CDATA[tools]]></category>
            <category><![CDATA[budget]]></category>
            <category><![CDATA[personal-development]]></category>
            <category><![CDATA[google-docs]]></category>
            <category><![CDATA[personal-finance]]></category>
            <dc:creator><![CDATA[Sander DiAngelis]]></dc:creator>
            <pubDate>Mon, 15 Jun 2020 02:13:47 GMT</pubDate>
            <atom:updated>2021-01-22T19:03:17.231Z</atom:updated>
            <content:encoded><![CDATA[<h3>How to Build a Budget and Transaction Tracker with Google Sheets (Free Template 🙌)</h3><p>The first step to taking control of your finances is to have a complete understanding of where your money goes. Then, after a review of transactional history, you can develop a healthy budget and build toward financial freedom.</p><p>In this post I detail how I built my own budget and transaction tracker using Google Sheets. Use the information I provide to create your own budget and transaction tracker from scratch. Or, save yourself the time and simply use my template below.</p><p><a href="https://docs.google.com/spreadsheets/d/1TAXeBDKJO1keYwKWjP0fdf8K2LdPd00hxdw_MifA4WQ/edit?usp=sharing"><strong>Open my template here</strong></a><strong>. Follow the instructions presented to create your own budget and gain valuable insights!</strong></p><figure><img alt="Budget template and transaction tracking built with Google Sheets, by Sander DiAngelis and Greenshift" src="https://cdn-images-1.medium.com/max/1024/1*YjaucO1Xwt2N_5TzZBcaNQ.png" /><figcaption>Screenshot of the linked budget</figcaption></figure><h4>Step 1: Log Transactions</h4><p>Gather a full month’s worth of transactions across your card and banking accounts. We’ll begin by logging all income and expenses.</p><p>For this exercise, ignore transfers between accounts (e.g. moving money from one bank account to another; from a bank account to a credit card or investment account; etc.).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*4n4Vryp4hA4doMUOBB05jg.png" /></figure><p>In Google Sheets, log income on one half of the sheet and expenses on the other. It’s important to accurately record both the <strong>amount</strong> and <strong>category</strong> of each item in separate columns.</p><p>For my budget and tracker, I first listed all categories on a separate tab, titled <strong>Insights </strong>(see Step 3 for a visual). I then created a dropdown on the <strong>Transactions </strong>tab (shown above) by selecting the transaction category cells and clicking <strong>Data &gt; Data validation</strong>. This allows you to select a range and create a dropdown list from values included within that criteria.</p><p>You don’t have to incorporate the dropdown, but I find simply clicking your desired category from a list to be easier (and less error-prone) than typing the category every time.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*M1mnf7vYAdqI7uw_rKvg7Q.png" /></figure><p>It’s important that <strong>all categories are consistently titled</strong>, because we’ll be tallying the amounts of each category in Step 3. In other words, don’t alternate between <em>grocery </em>and <em>groceries</em> for your category selection; pick a consistent spelling for each category and stick to it!</p><p>(Rather than building this out yourself, you might try working with a copy of my template, which has most of the categories you could need. It also provides the ability for you to create custom categories.)</p><p><a href="https://docs.google.com/spreadsheets/d/1TAXeBDKJO1keYwKWjP0fdf8K2LdPd00hxdw_MifA4WQ/edit?usp=sharing"><strong>Open my budget and transaction tracking template here</strong></a><strong>.</strong></p><p>I also suggest titling and dating the transactions. This will train you to be more mindful of your spending (and it will help you identify the occasional instance of fraud).</p><h4>Step 2: Determine Monthly Gain or Loss</h4><p>Next, calculate your total income, total expenses, and determine the difference between the two.</p><p>I suggest running your calculations on a separate tab in your sheet. You’ll see in my template a tab titled <strong>Insights</strong>.</p><p>First, sum your income with the formula:</p><p>=sum([beginning cell]:[ending cell])</p><p>The beginning cell<em> </em>will be the first value associated with your income transaction(s); the ending cell will be the last value associated with your income. Both of these cells will fall within the same column.</p><p>Then, do the same for your expenses. The difference between your income and expenses represents your monthly gain or loss.</p><h4>Step 3: Gain Insights with Categorized Expenses</h4><p>It’s now time to unravel where you can better optimize for savings. This will also provide a baseline of past spending across categories so that you can determine realistic budget targets.</p><p>In separate tab, create columns to help you organize the following metrics: <strong>income</strong> <strong>category, planned income</strong>, <strong>actual income</strong>, <strong>income difference, expenses category</strong>, <strong>planned expenses</strong>, <strong>actual expenses, and expenses difference</strong>. You might have already created the <strong>categories</strong> column and values if you followed my instructions in Step 1 regarding category dropdown lists.</p><p>See the picture below to visualize the recommended formatting.</p><p>To tally actual income or expenses across each category, you’ll need to run a formula that requests: <em>for all instances where the </em><strong><em>category</em></strong><em> = [your category to be calculated], sum the </em><strong><em>amount</em>. </strong>This can be accomplished with a <em>SUMIF </em>statement, such as:</p><p>=SUMIF(‘Transactions [Start Here 🚀]’!L:L,H38,’Transactions [Start Here 🚀]’!I:I)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*XaWD2Fq7Ncm1jVZeRRdD3w.png" /></figure><p>The statement above translates as: <em>if the data in column L of the tab titled ‘Transactions [Start Here 🚀]’ is equal to cell H38, then sum the corresponding cell(s) in column I.</em></p><p>Looking at my <strong>Transactions</strong> tab (pictured below), you’ll see why I selected columns L and I. Column L reflects the expense <strong>category</strong> and column I the expense <strong>amount</strong>.</p><p>So, if column L on the <strong>Transactions</strong> tab is equal to cell H38 on the <strong>Insights</strong> tab (which reads 🥝 <strong>Grocery</strong>), then the corresponding amount in column I of the <strong>Transactions </strong>tab will be added.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*EZ1dQE35IBj47MxOiKQDqQ.png" /></figure><p>Copy this formula for all rows of expense categories.</p><p>You should now get a nice overview of your spending habits. Do any numbers surprise you? Does anything seem amiss? Follow the same steps for your income categories.</p><p>With your actual income and expense metrics as a baseline, you can now input your own metrics into the <strong>planned income</strong> and <strong>planned expenses </strong>columns.</p><p>Now calculate the difference between your planned metrics and actual metrics using the following formula:</p><p>=[<strong>planned</strong> cell #]-[<strong>actual</strong> cell #]</p><p>Take note of any large differences and consider how you might adjust your habits.</p><h4>Step 4 (Bonus): Chart Your Income and Expenses 🚀</h4><p>Take your understanding one step further by creating a chart of your results.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*GuwvtRyHFukEFOZDDCVRhg.png" /></figure><p>I opted for a pie chart to break down each type of expense, and sparkline charts to show simple visuals of income vs expenses and planned metrics vs actual.</p><p>The pie chart can be created by selecting <strong>Insert &gt; Chart</strong> and editing the variables to be similar as pictured above.</p><p>The sparklines require a bit more formulaic work. For a nice overview, take a look at the article <a href="https://www.howtogeek.com/450155/how-to-use-sparklines-in-google-sheets/"><em>How to Use Sparklines in Google Sheets</em></a>.</p><p>To create my large dark green <strong>Income </strong>sparkline chart pictured above, I first merged a few cells to be the maximum length of the chart. I then used the formula below:</p><p>=SPARKLINE($E$35,{“charttype”,”bar”;”max”,max($E$35,$K$35);”color1&quot;,”#337A6B”})</p><p>Note that cell E35 is my <strong>total</strong> <strong>actual income</strong> and cell K35 is my <strong>total actual expenses</strong>. So, the sparkline formula reads: <em>create a sparkline bar chart for the amount in cell E35, where the maximum length of the bar is the maximum of either cell E35 or K35. Make the primary color in the chart equal to the hex value 337A6B.</em></p><p>I used nearly the same formula for the <strong>Expenses </strong>sparkline chart. The only modifications to the formula are that I changed the cell to be charted to K35 (<strong>total actual expenses</strong>) and I altered the color:</p><p>=SPARKLINE($K$35,{“charttype”,”bar”;”max”,max($E$35,$K$35);”color1&quot;,”#B78055&quot;})</p><p>Similar logic was also used for the planned vs actual sparkline charts.</p><p>You might find it fun to experiment with =if formulas, as I did to display various messages and emojis based on your performance. I suggest clicking into those cells on my template and seeing for yourself if you can decipher the logic. Note that the first comma in an if statement reads <em>then</em> and the second comma in an if statement translate as <em>otherwise</em>.</p><p>Using a simple example, take a look at the formula pictured below:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/662/1*R4DYS3frRYZO4iORDOTZMg.png" /></figure><p>This formula reads: <em>if cell E35 (</em><strong><em>actual total income</em></strong><em>) is greater than cell D35 (</em><strong><em>planned total income</em></strong><em>), then display </em>🙌 <em>; otherwise, display </em>🙁<em>.</em></p><p>Add your own messages, craft your own insights, and have fun!</p><p><strong>Thanks for reading and please leave a note below if you have any comments or questions.</strong></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f854b7bd0ea8" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>