Kristi

Meditating yogi, spreadsheet alchemist, recovering economist.

  • How to Create Pivot Tables

    Let’s talk pivot tables.

    Pivot tables make the process of summarizing data super simple: select your data, drag and drop a few variables, and BOOM! Seriously, they are really that straight forward. What used to take time and thought to create now takes mere seconds!

    My friends, pivot tables are a game-changer. If you aren’t using them, you are missing out.

    Why some people consider them to be an “advanced” Excel skill is beyond me.

    In my opinion, pivot tables only require a few things:

    1. Well-structured data (for more about this, click here)
    2. Knowledge of what you want your data to tell you
    3. Basic pivot table mechanics

    Let’s dive into each.

    Well-structured data

    First, your data MUST be structured so that observations are in rows, and variables are in columns.

    For example, if you want to track your income and expenses, you should have columns for:

    • Transaction date
    • Transaction amount
    • Transaction description
    • Transaction category
    • Etc.

    Each row beneath your column headers will contain the above information for a single transaction.

    Knowledge of what you want your data to tell you

    Do you want to know how much you spent on each category?

    Do you want to know how much you spent in each month?

    Do you want to know how much you spent on each category each month?

    Do you want to know how much you earned?

    Understanding what it is you want your data to tell you is essential when it comes to actually creating your pivot table. But, once you understand this, creating a pivot table is easy!

    Basic pivot table mechanics

    Both Excel and Google Sheets make building pivot tables simple, although they differ slightly in their approach.

    We’re going to walk through the mechanics (and tie the first to points together) using an example:

    Suppose you sell spreadsheets through your website (sorry, couldn’t help it!). Of course, you track your expenses and income using the Profit & Loss Tracker, which already comes with a few nifty pivot tables. Score! 

    You love seeing how much money your website brings in each month, which the pivot tables make easy to do.

    And then, your arch nemesis hacks into your computer and – just to mess with you – deletes all of your pivot tables. 

    NO!

    How on earth will you rebuild them?

    Exel

    1. Select your data => Insert => Pivot Table => OK
    1. Then this happens…
    1. Now, go to the box on the right called “Field Names” and drag items from it into one of the four boxes below using the following overly general guidelines:
      • Dates often work best as columns. If you just want to see a simple list of items, e.g., expense categories and their totals, leave the columns box blank.
      • For basic data analysis, categories or other variables of interest typically go in the rows box.
      • Variables for which you want to exclude certain values can go in the filter box.
      • Variables that represent amounts often go in the values box.

    I can already conceptualize a bazillion exceptions to these generalizations. So, the best way to determine what goes where is to just start dragging and dropping things – keeping in mind the question(s) you are asking of your data.

    In this case, I’m going to drag the:

    • Month variable to the column box
    • Expense Category variable to the row box
    • Amount variable to the values box
    1. This ^ pivot table gives looks a little rough, but it contains everything it needs. You can:
      • Change its formatting in the Design tab on the ribbon. 
      • Change how the variables are displayed by clicking inside the boxes in the Pivot Table Fields section.
      • Change the look of other elements (check out this post for some ideas).

    Here’s what my pivot table looks like after a little formatting:

    Google Sheets

    1. Select your data => Data => Pivot Table => New Sheet => Create
    1. Then this happens…
    1. [This commentary was copied from the Excel section, since you probably didn’t read it if you’re reading the Google Sheets tutorial] Now, go to the Pivot Table Editor box on the right => click the “Add” button next to Rows, Columns, Values, and Filters options using the following overly general guidelines:
      1. Dates often work best as columns. If you just want to see a simple list of items, e.g., expense categories and their totals, leave the columns box blank.
      2. For basic data analysis, categories or other variables of interest typically go in the rows box.
      3. Variables for which you want to exclude certain values can go in the filter box.
      4. Variables that represent amounts often go in the values box.

    I can already conceptualize a bazillion exceptions to these generalizations. So, the best way to determine what goes where is to just start adding things and then drag and drop them as desired – keeping in mind the question(s) you are asking of your data.

    1. This ^ pivot table gives looks a little rough, but it contains everything it needs. You can:
    1. Change its formatting in the Design tab on the ribbon. 
    2. Change how the variables are displayed by clicking inside the boxes in the Pivot Table Fields section.
    3. Change the look of other elements (check out this post for some ideas).

    Here’s what my pivot table looks like after a little formatting:

    Want to learn more about pivot tables (and pivot charts)?

    Subscribe to my low-volume newsletter!

    Continue reading

  • Ditch Paycheck-to-Paycheck Overwhelm

    Stop the Madness! Ditch Paycheck-to-Paycheck Overwhelm

    This post is going to usher in a new era for posts to TSA.

    No more in-depth tutorials (mostly), unless you specifically request them.

    Instead, I’m going to focus on how you can use spreadsheets to help you manage some area of your life.

    Today, let’s talk about paycheck-to-paycheck budgeting, a topic that’s very close to my…pocketbook.

    As an economist by education, I’m supposed to act intelligently with my finances because, for starters, I know how compounding works.

    As a present-moment-embracing gal, however, I must admit that I’ve failed miserably in this area.

    While I’m working hard to change this with side hustling and habit-changing, I’m now bearing the fruits of both my old behaviors and some unfortunate new circumstances.

    Friends, I’m living paycheck-to-paycheck.

    While I’m fabulous when it comes to crunching numbers and creating wicked spreadsheets, I don’t always apply this fabulousness to my own life.

    But there are times when I create spreadsheets for myself, tweak them over the months as my needs evolve, and come to rely on them wholeheartedly.

    That is the case with a spreadsheet I’ve taken to calling Stop the Madness! (STM for short).

    If you want to buy it, you are awesome and I hope that you love it as much as I do. But if you just want to understand the gist of how it works so that you can create something similar, read on! This post is for you.

    Let’s talk about the challenges of paycheck-to-paycheck budgeting.

    First, I’m an economist by education and not a finance guru. Please do not construe anything in this post as being financial advice. I’m going to share with you only what I do to manage my own finances using spreadsheets. And, when I say “manage”, what I really mean is to make sure that I can always pay my bills without resorting to weird shenanigans that my former self has been known to do, e.g., using credit and juggling bills.

    Second, If you’d like to read about paycheck-to-paycheck budgeting from an actual financial blogger who is seriously amazing, go to The Budget Mom. I promise you that Kumiko’s posts won’t disappoint!

    Disclaimers and suggestions aside, let’s talk about the challenges of paycheck-to-paycheck budgeting. Ready?

    The challenge of paycheck-to-paycheck budgeting is that you must plan ahead. It’s as simple as that.

    Seriously.

    It’s not rocket science, but it can become complex when you are:

    • An under-earner
    • Someone who lives beyond her/his means
    • A compulsive spender
    • A snob when it comes to brands, stores, second-hand purchases, etc.
    • A person with a high discount rate (econ jargon for someone who greatly prefers spending money today over saving for tomorrow)

    Consider the following scenario:

    You get paid bi-weekly, which means two months out of the year you receive three paychecks. This month happens to be one of them. After barely scraping together rent that was due on the 1st (and living on the dried rice and beans in the back of your pantry because you couldn’t afford to go grocery shopping), you got paid on the 2nd. Yay! Now you are loaded. Your rent is paid, most of your bills aren’t due until mid-month – after you receive your 2nd of three paychecks – and you basically have $1,500 to spend on whatever.

    Maybe you consider renewing your website hosting three months early since you’ll be struggling to find $350 when it’s due, but then you don’t. Same with your vehicle registration renewal, Christmas shopping, and the emergency car repairs you sense are lurking in the shadows.

    So then you think about all of the things you’ve been absolutely needing to buy but have been too broke to get. You know, that new Erin Condren planner, Lucky handbag, and a replacement bottle of something or other from Sephora. It’s also been a while since you’ve swung by Starbucks, snagged a $15 bottle of wine (hey, that boxed stuff isn’t so bad…), or bought the $35 shampoo your stylist recommends instead of the cheap 365 stuff.

    And what happens next?

    That extra paycheck disappears, and you are in the same bind – if not worse – as you were in before. Except that now your hair smells divine (thanks, Aveda!).

    Your end-of-month paycheck covers next month’s rent, but you have a couple of bills due before your first paycheck hits on the 12th. And then you’ll need to buy groceries. Oh wait – rent is $1,300! That means that only $200 remains to pay $350 worth of bills AND buy groceries.

    Fuck.

    So, you evaluate your bills to determine which you’re going to have to pay late. Again. You live on beans and rice for five days straight as you tell your daughter that no, you can’t swing by the grocery store to buy bananas until Friday. And you feel like the biggest loser.

    Not that I would know…

    Are you ready to try something different? Good.

    Here’s how you can create your own stop-the-madness! paycheck-to-paycheck system.

    Step 1: Find out how much money you actually have available (hint: you must do more than check your balance online).

    This is both an extremely important and yet an often overlooked step. YOU. MUST. RECONCILE. YOUR. ACCOUNTS.

    Doing so will allow you to know exactly to the penny how much money you actually have…after the check you wrote to your kiddo’s school three months ago clears, the power bill hits, etc. Old-schoolers like me will remember cross-checking their checkbook registers against their bank statements each month. Seriously.

    These days, you can track all of your transactions in a simple spreadsheet. STM has a snazzy way of simplifying the process, but all you really need to do is:

    1. Enter down your starting balance; then
    2. Enter every transaction you’ve made that is not accounted for in this starting balance. This should be a small list if you use your debit card for everything. Also from here on out, record every transaction you ever make; then
    3. Make a note when your transactions clear your account; and
    4. Subtract your cleared transactions from your starting balance to get your available balance. This should match your bank account’s available balance; then
    5. Subtract ALL of your transactions – cleared or not – from your starting balance to find out how much money you have left over.

    If you don’t like using spreadsheets or are feeling overwhelmed, guess what? There’s an app for that! My favorites are YNAB (software that includes an app) and Balance My Checkbook (iOS only).

    While I’ve used both and love them each for their own reasons, they fall short for paycheck-to-paycheck money management because they can only show you your present day balances. They cannot show you how much money you will have available in your account on a future date. When it comes to paycheck-to-paycheck budgeting, knowing how much money remains available after future transactions clear the bank is crucial!

    Step 2: Put your economist hat on and forecast next month’s financial snapshot…by date!

    “Normal”, e.g., monthly budgets are great for showing you how much net income you’ll have to play with during any given month.

    But, as paycheck-to-paycheck budgeters know all too well if you don’t have a reserve to hold you over and your bills are due before payday, you can’t pay them. So, you do what I used to do – you do the time-consuming and tedious math on scrap pieces of paper to see how much money you can/can’t spend on necessities in order to make your bill payments. Maybe you even map out several different scenarios when you can’t get the numbers to work…yep, I’ve been guilty of doing this.

    Step 2 makes it easier for you to figure out how much of your current spending money you’ll need to not spend so that you can pay your bills and budget for necessities as far out into the future as you like. No paper involved. Note that manually forecasting your expenses this way can get very time-consuming. Sorry, but I need to plug STM one more time, as it does this heavy-lifting for you.

    Here is how to forecast your future financial snapshot:

    1. Make sure that you’ve completed Step 1; then
    2. Enter all of your expenses – bills, groceries, etc. – for the next full month; then
    3. Enter all of your expected income for the next full month; then
    4. Sort your entries by date; then
    5. Subtract the first expense from the starting balance to get your new balance…then subtract the second expense from the first’s balance…rinse and repeat, but make sure you add (don’t subtract!) your income; then
    6. Evaluate this column. Are any of these balances negative? If so, you don’t have enough money to meet your needs and will have to get creative, e.g., spend less than you planned or bring in additional income.

    That’s it! Of course, it would behoove you to build up a reserve so that you can escape this paycheck-to-paycheck mentality. So, view this only as a Band-Aid sort of fix to the troubles inherent when funds are tight.

    Final thoughts

    Are you a paycheck-to-paycheck budgeter? Do you have an alternative system or app recommendation that works well for you? If you’ve escaped this style of budgeting or continue to use it despite growing an ample reserve, I’d love to hear from you!

    Also, if you would like to become an affiliate for The Spreadsheet Alchemist, I’d love to have you on board! Please create a Gumroad account (it’s free, and if you’ve bought any of my spreadsheets, you probably already have one) and then drop me a line. Also, make sure to tell me what email address you used to sign up with Gumroad. Thanks!

    SaveSave

    SaveSave

    SaveSave

    SaveSave

    Continue reading

  • Why I’m Breaking Up with Google Sheets

    Why I'm breaking up with Google Sheets.

    [UPDATE: Google Sheets and I have reunited. My new 9-5 has me living in the program and barely touching Excel, so I’ve been forced to change my tune. A little.]

    There, I said it.

    I’ve adored Google Sheets for so many reasons.

    It’s free.

    It has a mobile app that’s free.

    It’s easier than Excel to navigate.

    For a spreadsheet program, it’s simple.

    But, here’s the thing…

    Google Sheets can’t handle my spreadsheets.

    Literally. It crashes on me or denies me access when I’m in the middle of a project.

    WTF?!

    Late last year, I created what I believed was a glorious paycheck-to-paycheck budgeting program using Google Sheets. All but one beta tester gave it glowing feedback.

    But, then the app started crashing on me – a deal-breaker, as it’s most important feature was the ability to enter transactions on the go.

    Boo, Google Sheets!

    So, I scrapped the damn thing.

    In hindsight, this is probably a good thing, as the spreadsheets were too complex even for my taste.

    But that’s not the point.

    I’m a slow learner, and so the I’m-breaking-up-with-you moment took another seven months to materialize.

    That moment is now.

    I bit the bullet and subscribed to Office 365, which means I pay $6.99/month to get Excel on my Mac. I’d share an affiliate link, but they declined my application. I think it was because I talked about having a ménage-à-trois with Google Sheets and Excel. But I digress…

    Anyway, I did this because I needed to have a spreadsheet app on my phone so that I could track my spending, and Google kept crashing on me.

    Not long after, I got really crazy and created a seriously wicked Profit & Loss Tracker. [It’s a premium spreadsheet, which will be available for purchase soon, if you are so inclined.]

    Just like all of my spreadsheets to date, I decided to create a Google Sheets version of it because I know that many of you use that platform.

    *Insert a round of some serious expletives!*

    The damn thing kept telling me that my access was denied…mid-entry! And I’d lose a lot of work. Now, push repeat a bazillion times. [UPDATE: the Profit & Loss Tracker now has a Google Sheets version.]

    Yep. No joke…

    And then I just said fuck it.

    It’s a lot of work creating dual versions of each spreadsheet I make, given that I use things like drop-down lists, pivot tables, and some formulas that aren’t compatible between platforms. [UPDATE: some data validation features like drop-down lists have become at least partially compatible as of late 2019.]

    Anyway, maybe Google and I will kiss and make up at some point, but for now, I want a divorce.


    So, dear readers, what are your thoughts? Which spreadsheet platform do you dig? Do you have any horror stories you’re dying to get off your chest? That’s what the comments are for 🙂

    SaveSave

    SaveSave

    SaveSave

    SaveSave

    Continue reading