Love it or hate it, if you work in front of a computer, chances are you have had to read or edit a Microsoft Excel workbook. For many people, creating an Excel spreadsheet is nothing more than copying a bunch of steps and complicated formulas. Get one character wrong or use the wrong formatting and the entire table breaks. So we send it to that one coworker who is the Excel guru. If you can get past this way of thinking and learn to think of Excel more intuitively, you can begin to instinctively understand how to use data to tell the story you need to tell. Then it all comes down to a quick search of the exact formulas.
But how do you get there? One excellent way to is to begin by practicing with data that you already understand that translates into a story you also already understand—and what is more personal than our wallets? Our purchases are the data that tell a story that we already know, for example, I love food, and that story shows when you look at my transaction history. Our wallets reflect trends in our own lives, like our increasing addiction to that morning stop for coffee. Our wallets can be used to forecast, for example, how much we should budget for living expenses for next month.
Let’s use our understanding of both the data and the stories behind our wallets to understand how to translate one from the other. This is simple if you have an online banking login and some established transaction history online. Then we can get started.
- Export your transaction history xls file and open it up in Excel. Most major banks offer this option in their online banking portals.
- Clean up the data, but not too clean. If you have columns for date, amount, and description, respectively, you have what you need to get started. Just make sure the dates are formatted as dates, the amounts are formatted as currency, etc. Remember, the more changes you make here the less automated your future reporting will be, so keep the manual changes simple.
- Add another column for category, for example, clothing, entertainment, travel, pets, etc. Use this step to research new functions in Excel, like the filter capabilities—if you always shop at Raley’s, use the filter on the descriptions column and quickly fill out the category for all grocery purchase. Some banks assign this information, but I like to use my own custom labels.
- Pivot! Insert a pivot chart. This is where many proficient Excel users get stuck, and instead waste hours manually creating tables. Use the pivot table builder to drag and drop fields until you see something meaningful.
- Interpret. Try viewing your transactions by month, category, and purchase amount, then look for answers and trends to represent what you already know to be true. How much did you spend on sandwiches last month? How much more was that than the month before? How many times did you go to happy hour during fiscal close week at work? If you were to make a movie ticket piggy bank, how much should you put it in for next month? Did your new love interest cause your restaurant purchases to spike recently compared to an average month? You may not know the exact formulas, but if you know what you’re trying to accomplish, you will know how to ask Google the right questions to get the formula you need.
Once you’re comfortable with this simple interpretation of your purchases, you are on your way to an intuitive grasp of data manipulation. An understanding of what would be the column value vs. the row value, when to use sums vs. counts, and total change vs. percent change is now easily translated from your own purchases to your company’s shipment data, or your team’s inquiry ticket history, for example. Understanding how to tell a story using data will tell you what to look for so that you won’t need to worry about memorizing the formulas or following confusing how-to steps.
