Lesson 2 - Formatting Your Data
A Process and Workflow Design Training Series...
Mastering Microsoft Excel and Google Sheets
If you’re an accountant or bookkeeper and you don’t feel like you’ve mastered Excel and Google Sheets... this course is for you!
NOTE: In Lesson 2 you will learn how to start with a blank spreadsheet and build a simple cash flow analysis that looks gorgeous!
Further down in this lesson you can access the finished templates. Or you can scroll down and access them now! Or you can check your inbox, because by now I may have emailed them to you as well.
You're going to learn that by shutting off the gridlines, and using custom fill colors and borders, you can make your data pop. And it isn't hard to do, once you see how it's done.
I will even show you how to use yours or your client's logo to get the hex codes for their colors and use them in your spreadsheet design.
Have you ever seen a spreadsheet someone else worked on and at first glance you are instantly overwhelmed, have no idea what you are looking at, or where to start?
Usually these are the ones that no one bothered to take the time to format, and make it look nice which means easy to read.
That’s what this lesson is all about. It’s about how to present financial information in a spreadsheet that looks attractive to readers.
It will draw them in and engage them with the data so you can discuss what they need to know efficiently and intelligently.
Turn off the gridlines!
The first thing I do any time I am working in a spreadsheet where I am planning to present important data is turn off the gridlines.
Now you may be wondering how this works in terms of giving readers’ eyes a guide to follow, and the simple answer is with borders, but we’ll get to that later.
A Simple Cash Flow Analysis
Let’s say we want to put together a simple cash flow analysis that will be easy for a client to use on their own. Something that shows all inflows and outflows in one place.
We’ll break it down as follows:
INFLOWS
- Cash flows from sales / income
- Owner Contributions
- Loans Received
OUTFLOWS
- Expenses
- Loan Payments
- Credit Card payments
- Owner Distributions
These descriptions will go in a column on the left. We will leave room for the specific categories to be added, and we will set up subtotals and totals.
In either format, Excel or Google Sheets, set column A to be nice and wide and give it a shade of grey:
Then drop the categories in, and fill in some of the details:
Now let’s add a nice header.
We’ll make that first row nice and big, and a different color.
Let’s say we want to use QuickBooks Green in our header. You can search the internet for QuickBooks branding and you can find the Hex Codes. There are different shades of colors, but for our purposes lets use this one: QB Green: #108000
Here’s how to apply it…
IN MICROSOFT EXCEL
IN GOOGLE SHEETS
Doesn’t this already look 1000x better than most spreadsheets you see?
And we haven’t even got any numbers in there yet.
Now let’s put a timeline in there. Notice I indicated in the header that we are looking at Jan 2022 - Dec 2022.
Enter a date of Jan 1, 2022 in cell B2. I know you normally think of putting the last day of the month, but the key here is that every month has a 1st, but not all months have the same last day, and we want to use a formula here to make this more dynamic.
Then in cell C2 enter the following formula:
=EDATE(B2,1)
Copy that to the right until you get to Dec 2022.
Then format your dates - since I covered this in the previous lesson I am going to assume you know how to navigate to these options.
BORDERS
Now let’s do some borders! Yay!
Select the range in the white space, from cell B5 to the bottom of your range of data…
Then we’re going to apply light grey dotted borders. They are subtle, they do the job, without over powering the actual data which will be the numbers we’re presenting.
IN MICROSOFT EXCEL
IN GOOGLE SHEETS
Now let’s fill in some numbers and write the formulas we need for our totals.
Then at the bottom, add the following 3 rows:
- Net Cash Flow
- Beginning Balance
- Ending Balance
The net cash flow is your total inflows - total outflows.
The beginning balance is last month’s ending balance. For the first month, enter whatever beginning balance you wish to start with, or leave it at 0.
The ending balance is the sum of the two.
Download the templates here:
Branding Your Spreadsheets
Branded Forecasts
Last year I was working with my friend Meghan who hired me initially to help her with her pro forma. Long story short, I decided to completely redo them (in Google Sheets).
Wanting to go the extra distance on this, I grabbed her logo and dropped it into this tool, to get the hex codes for the colors from her logo:
Of course there are different shades, but here’s what I went with:
Pink: #f02c74
Grey Scale: #4d4d50
To illustrate this I am going to take the Balance Sheet and P&L from the QBO Test Drive Company, and format it according to Meghan’s branding.
You’ve already learned how to use these hex codes for the custom colors.
This exercise is about learning a very practical use case for this. Use your own logo with the tool above to get your own branding into a spreadsheet template, for practice. It is tempting to us your own branding for your clients’ templates, but your clients will be much more blown away when they see their own branding in your work.
As a matter of practice, you should always ask your clients for a copy of their logo, and keep it in your files. This way you have a good hi res file, but it’s also easy enough to steal off of their website if you need to get it quickly 😉.
GET INSPIRED!
Browse Templates for Both Excel and Sheets:
Open EXCEL:
Click File 👉🏽 New
Then browse the templates or search for something.
For Google Sheets:
Lesson 2 Complete!
What's Next?
Lesson 3 - Data Analysis, Filtering, Pivot Tables and Pivot Charts
In lesson 3 you will get an exercise file with a LOT of data in it from varying sources (bank accounts, sales data, contact lists, and more).
This is the lesson where you will learn how to slice and dice data so you can get ANY kind of information out of it and move it, format it, and ultimately present it to clients and their stakeholders in really powerful ways.
And you’ll be able to do this lightening fast!
After we finish this lesson you will not believe what you are able to do, and how quickly you can get it done!
Copyright and Usage Disclaimer: We know you are viewing this workbook because you purchased this product from Nerd Enterprises, Inc. And by the way congratulations on being part of the elite group of people who actually read disclaimer fine print. If you are viewing this material and have not purchased the product directly from Nerd Enterprises, Inc., then you are viewing a pirated, unauthorized copy. Please close this page immediately. If you do not, Russians will infiltrate your social media channels and cause you to get into political arguments with people who have nothing better to do than argue with strangers online. We take copyright abuse very seriously and so do our attorneys. Please report any abuse to [email protected]. In addition, you can purchase any of our products including the companion video series to this workbook by visiting our Course Catalog. You can also purchase shoes on Zappos. Just throwing that out there. Are you still reading this? Seriously? You’ll never get this time back. So what’s your favorite show to stream these days?
