Do you know how your biz or blog’s income is trending?
How about its expenses?
Creating charts that track your income, expenses, and net income (profit!) is stupid easy.
No joke – let’s do this!
Step 1: Get your data ready.
If you want to create a chart to track something, you’ve got to have something to track.
To keep things simple, I’m going to use one of the budgeting spreadsheets that accompanies my FREE guide “From Spreadsheet Virgin to Goddess in 30 Minutes”.
First things first – your table cannot have any spacer rows or columns. If it does, you must delete them.
Here’s what spacer rows and columns look like (everything in turquoise is a spacer):
Spacer columns and rows make your tables look prettier, but they make life difficult if you want to actually analyze your data.
Your table can only have one header row “carry over” to your chart. If your table has more than that, you will have to condense those rows into one.
Also, EVERY column needs to have a heading. In the above image, column B doesn’t. This confuses Excel and – trust me – you definitely don’t want to confuse a piece of software.
All this just means that it would really behoove you to make your table look something like this:
Step 2: Insert a chart.
There is more than one way to insert a chart.
Here, we’ll just insert a basic line chart. Why? Because they are pretty rad and easy to interpret.
I’m going to show you how to do this in both Excel and Google Sheets. Please note that I’m working on a Mac. If you are on a pc, your Excel options will differ slightly (if you can’t figure something out, just ask!).
Insert a line chart in Excel:
Select the whole table except the ‘Total’ row and column (A8:M8 in this case) => click the ‘Charts’ tab in the ribbon => click the line icon => select the ‘Line’ option.
Behold the result:
Yes, it’s ugly. Let’s fix it.
First, we’re going to get rid of the ‘Adjustments’ and ‘Other’ lines because they don’t give us much information.
- Right-click inside your chart => ‘Select Data…’ => click ‘Other’ in the ‘Series’ list => click ‘Remove’.
- Click ‘Adjustments’ in the ‘Series’ list => click ‘Remove’.
Next, we’re going to add a title to our chart.
- Click the ‘Chart Layout’ tab in the ribbon => click the ‘Chart Title’ icon => click the ‘Title Above Chart’ option.
- Click inside the new title text that appears on your chart => delete ‘Title’ => type ‘2016 INCOME’ (or whatever).
Now, our chart looks like this:
Still ugly, but we’re getting there.
Finally, we’re going to angle the x-axis text (e.g. the months of the year) so that you don’t have to lay down on your computer desk to read it. We’re also going to change the color of the lines.
- Right-click on the x-axis (where the months of the year are) => select ‘Format Axis…’ => select ‘Text Box’ => under the ‘Text Layout’ section, change ‘Angle’ from ‘0°’ to ’45°’ => click ‘OK’.
- Right-click on the top line (in this case, the red one for ‘Products’) => select ‘Format Data Series…’ => select ‘Line’ => select ‘Color’ => choose your preferred color => click ‘OK’ => click ‘OK’. Repeat for each line if you’d like.
Not gorgeous, but not bad. You get the gist of it at least, no?
Now, play around with it! Be sure to check out the options in the ‘Chart Design’ and ‘Format’ tabs in the Ribbon.
Insert a line chart in Google Sheets:
Select the whole table except the ‘Total’ row and column (A8:M8 in this case) => click the ‘Insert’ menu option => click ‘Chart…’ => select the ‘Chart Types’ tab => select the ‘Line’ icon => click all but the first check box => click ‘Insert’.
Ugh…let’s clean it up a bit.
First, let’s get rid of the ‘Adjustments’ and ‘Other’ lines.
Right-click inside your chart => click ‘Advanced edit…’ => click on the ‘Chart Types’ tab => click on the little grid box near the top that (on hover, says ‘Select data range’) => change ‘A1:H8‘ to ‘A1:H6‘ => click ‘Update’.
Now, let’s add a title to our chart.
Click on the current title that says ‘Jan, Feb, …’ and replace it with ‘2016 INCOME’ => hit ‘Enter’. I’m sorry to say that there is simply no way to center a title in a Google Sheets chart without dabbling into code. *sigh*.
Here’s what our chart looks like now:
Better, but let’s fix it up a bit more.
Finally, we’re going to angle the x-axis text (e.g. the months of the year) like we did with Excel, and we’re also going to change the color of the lines.
- Right-click on the chart => select ‘Format Axis…’ => select ‘Advanced edit…’ => make sure that you are in the ‘Customization’ tab => Under the ‘Axis’ section, delete the title ‘Source’ => change the drop-down option for ‘Slant labels’ from ‘Auto’ to ’30°’ (45° isn’t an option in Google Sheets) => click ‘Update’.
- Right-click on the chart => select ‘Advanced edit…’ => make sure that you are in the ‘Customization’ tab => Under the ‘Series’ section, change ‘All lines’ in the drop-down menu to ‘Affiliates’ (or whatever) => change the color => repeat with the rest of the options => click ‘Update’.
Sweet. You can tweak your chart to your heart’s content if you’d like. Here’s the finished product:
Step 3: Interpret these lovely charts.
Wait! We’re not quite done yet. We need to interpret our charts.
Some are more savvy than others when it comes to interpreting charts and all that jazz.
Lucky for you, I’m an economist. Woo-hoo! (Okay, so interpreting these puppies really doesn’t require a degree or two in economics to understand).
- Your x-axis (otherwise known as the horizontal line littered with months) is pretty self-explanatory. The further right you head, the more current of a time period you are looking at.
- Your y-axis (otherwise known as the vertical line littered with $ amounts) is also pretty self-explanatory. The further north you head, the more money you are looking at.
- For each time period (Jan, Feb, …, Dec), there is a corresponding income amount. Plotting these amounts for each product yields the trend line for that product.
- If the trend line (read: the line) is – over time – slanting upward, all is well. It means your income is increasing.
- If the trend line slants downward, we’re in trouble and may need a government (read: taxpayer) bailout. Ouch.
Okay, so we barely scratched to surface of what you can do with charts. Seriously, there is so much ground to cover! What is it that you’d like to learn?
Also, you may find this post on creating a basic profit & loss statement (with free download) relevant and helpful.
Finally, if you’d like to play around with your chart’s formatting or functionality more and can’t figure something out, just pose your question in the comments section and I’ll try to answer it or even write a new post about it. Also, if you are a reader who can answer another reader’s question, please feel free to jump in. Thanks!