Track Spending in Google Sheets | Dashboards | Personal or Business

This expense tracker solution is optimized to help you summarize your spending in Google Sheets and includes dashboardzs with several useful charts. This solution can be used for an indivual or by a team of people working together. When you are using it for a team, it involves several similar spreadsheets that are linked together in a master sheet. This walkthrough will show how the tracking works and how it flows into the dashboards.

Expense Tracker Template

Sheetgo templates
Choose the Expense Tracking template

Once you are signed into Sheetgo, you will be able to select the Expense tracker template from their template gallery.

Sheetgo is free to use for 30 days and there are several paid plans available. If you sign up for an annual plan, use the code OAKTREE_OFF for a 10% discount for your first year. This site will be compensated by Sheetgo if you sign up. Signing up with the link helps to support the development of this site. Thank you in advance!

Spreadsheet files in Google Drive
Spreadsheet files in Google Drive

Individual Files

After you choose the Expense tracker template, you will gain access to the expense tracking spreadsheets shown above.They will be saved in the path \My Drive\Sheetgo Templates\Expense Tracker\Team Expenses Spreadsheet. If this is just for you, you can just use one of the spreadsheets. If it’s for a team, you can use aditional spreadsheets for each person.

expense log
The spreadsheet that contains a log of your spending
Individual Dashboard
Dashboard showing individual spending

Filling out the log

Each individual file comes with a log for tracking expenses and its own dashboard. The log shown above has been pre-filled with spending for the last 90 days and this is the data that is flowing into the dashboard.

There is also a column for the the currency values that are going to flow into the dashboard. If you need to change this currency, go to the menus, then to Format, Number, More formats, More currencies. The spreadsheet that I used is defaulted to US dollars because I’m in the United States. Google sheets knows that. So yours may default to wherever you are. But, if you need to change it, that’s how you do it.

In column for the type of expense, there’s a more happening here and you will probably see that because of the drop-down arrow in the right hand side of each cell in this column. When you click on one, there’s a preset list of values that you can choose from for each expense. If the pre-filled values don’t work for you, you can just change them. Go to Data, Data validation, and here are the options that have.

There is also a column for a link to a receipt. If you’re putting your receipts in Google Drive, or you’re putting them in Dropbox, or someplace else, grab the link for that particular receipt and then just paste it in there. Once you paste the link, hover over it and left-click. It would bring you to the image of that receipt.

Individual dashboard

If you go to the Dashboard sheet in this same file, and you can view a breakdown of your expenses. These charts show you your expenses per day, expenses per month, and a break down by type. If you want to modify any of these, you can just double click on them. You’ll get the dialog box over to the right that gives you all the options to change your chart to whatever you want.

If you’re just one person, and that’s what you wanted to do, track your expenses in Google Sheets, you’re done at this point. You downloaded their template from Sheetgo, and you just used it for an individual.

Combining Files for Teams

Master sheet

workflow
Sheetgo’s illustration of the workflow

To link the individual spending spreadsheets, Sheetgo combines them using a workflow. Sheetgo provides an illustration of this workflow as shown above. There are three files in the team expense folder which is represented by the grey circle with a picture of a folder. They will all flow into the team expense tracker, which is just a name that they gave to the master sheet.

File names added to log in master sheet
File names added to log in the master sheet
Team dashboard
Dashboard showing spending for all team members

The master sheet combines the individual spreadsheets. In addition, Sheetgo adds the file name on the end of each record so you can see where they came from.

This dashboard in this spreadsheet uses consolidated information from the individual files and they added a few more views so you can see some other metrics. You want to see who the big spender is, right? You can compare the individuals.

Workflow

workflow setting
Customize the workflow settings

You can edit this settings of this workflow. These settings will show us the source is just the folder with the three spreadsheets inside of it. You can pick individual sheets, but you can also just put a folder in here, if you wanted to add something. It would just pull up a file picker on the right-hand side. Select what you want to add and it would put it in the main screen here. Now it’s putting it in the master file. You can also change the desintation file if needed.

Automatic Updates

You can also enable automatic updates. If you do this, you can choose how often to update it. This option is only available if you pay for Sheetgo. You can update it manually if you want. If you don’t choose the automatic updates, you can update the workflow manually as often as you would like.

Access Controls

warning
Warning shown if you edit the master sheet

There is protection on the worksheet to give you a warning if you try to change it because the data here are supposed to be coming in from the individual spreadsheets. So you shouldn’t be typing it right into here, and if you do it’s going to give you a warning. Heads up, please don’t do that accidentally. That is controlled by Data validation settings that you can edit if you would like.

So that covers it! This expense tracking solution from Sheetgo has all you need to track your expenses or your business’s expenses.

Google Sheets – Insert Sheet Names into Cells




Page Title

The code for the regular expression is =IF(TODAY()=TODAY(), REGEXREPLACE(CELL("address",'Detail 1'!A1),"'?([^']+)'?!.*","$1"), ""). The text in the box below is meant to be copy and pasted for the custom functions that were used in this sheet. The text in this Doc is the same, grab it from here if it’s easier.

//Return the current sheet name.
function SheetName() {
return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
}

//Return all current sheet names.
function SheetNames() {
var out = new Array()
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
for (var i=0 ; i<sheets.length ; i++) out.push( [ sheets[i].getName() ] )
return out 
}

//Return specified sheet name
function SheetNumber(idx) {
if (!idx)
return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
else {
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
var idx = parseInt(idx);
if (isNaN(idx) || idx < 1 || sheets.length < idx)
throw “Invalid parameter (it should be a number from 0 to “+sheets.length+”)”;
return sheets[idx-1].getName();
}
}

Text that accompanies this YouTube video.


Drive home page showing link to Admin page

G Suite vs. Free Google Apps | What is the difference?

If you’re a company or organization using the free Google Apps, and you’re looking for something that gives you more control over the way that your users utilize them as well as some great additional features, G Suite might be just what you need. Let’s take a quick look at Google’s premium option and what it can offer for you and your organization compared to the free standard version.

If you contact me here or email me at po@prolificoaktree.com or submit , you will receive a code for 20% off of your first year of G-Suite. If you sign up for G Suite using this link and apply the code during checkout, the discount will be applied after your trial period expires.

Admin Console

The Admin Console, which is an addition to your usual Google Apps menu, contains a lot of features that give you control over your users and how they access and share data and use the Google Apps within your organization.

Drive home page showing link to Admin page

If you click on the Admin app, you’ll be presented with a number of useful features. On the left, the Users feature allows you to add or remove users in your organization, as well as assign them to groups, for example when scheduling different meetings on your Calendar.

G Suite admin console with Users selected

The Apps feature gives you control over which apps your users can use and see. For example, you can turn off Gmail if you’d like everything done through Outlook.

G Suite admin console with Apps option selected

The Device Management feature gives you control over access through different devices, for example, if you’d like to prevent access to company data from a phone. We’ll take a closer look at that in the Mobile Device Management section below.

G Suite admin console with Device management option selected

Video explanation

Custom Email

Perhaps the most important difference between the free Google Apps and G Suite, and something which is often the sole reason why people upgrade is the ability to customize your email address. In the free version, your email always ends with ‘@gmail.com’. In G Suite, you can put your domain name in there instead. You must own the domain before you can use it in your email address.

Gmail showing a custom email

Data Migration

Another useful feature offered in G-Suite is the ability to migrate data from a different email service, such as Outlook or an IMAP service such as Yahoo, to your Gmail account. You can migrate emails, contacts and calendar data.

G Suite data migration

G Suite Editions

G Suite is a premium service, and you can pay monthly or annually. The annual plan has a small discount, but a flexible plan may be better for you if you’re adding and removing users regularly.

G Suite Plan types

The monthly payment depends on which Edition of G Suite you use: Basic is $5/month, Business is $10/month, and Enterprise is $25/month. In most cases, if you’re a small organization, Basic will do just fine.

Comparison of flexible plan and annual plan

If we look at a comparison of the different G Suite Editions, there are a few differences to take note of. One of the main limitations on Basic is the 30GB file storage limit per user. In the Business edition, storage is unlimited, with the caveat that if you have fewer than 5 people in your organization they cannot have more than a 1TB of storage each. In Enterprise, storage is completely unlimited.

Comparison of G Suite plans with storage highlighted

The Business and Enterprise Editions offer a couple of unique features. When you move up to Business, you get eDiscovery which can be useful if you’re searching for documents for a legal case. Enterprise offers much more granular control and customization of your Gmail. None of the G Suite Editions feature ads.

Comparison of G Suite plans with enterprise features highlighted

24/7 Support

G Suite offers 24/7 support, which can come in useful if you’re doing complex tasks with Google apps in a company.

New Calendar Features

If you have G Suite and you left-click on your calendar to create an event, there are a couple more options. The first one is the option to put in an Out of Office notification. The second one is Appointment Slots which allows other people to go into your calendar and set up appointments in the time slots you have designated to be available.

Extra options on calendar frin having G Suite

In the following image, you’ll see the public-facing side of your calendar, as it appears to someone who has come in to make an appointment.

Google Calendar showing appointment slots

Google Docs Sharing Options

If you have G Suite and you go to Google Docs to create a new Google Document, and click Change in the Sharing Settings, you’ll find a range of new sharing options that give you more control over how information is shared within your organization and who can see the document contents.

Google Docs link sharing options

Mobile Device Management

Another feature of G Suite is Mobile Device Management, which gives you control over if and when to activate your user’s mobile device, as well as the option to perform a remote wipe.

G Suite admin device management options

Additional Security Features

In G Suite’s Admin Console, there’s a Security feature that gives you a greater range of security options for your organization.

G Suite admin console with Security highlighted

Inside, you’ll find options for password monitoring, login challenges, and Single Sign-On (SSO) for your users. The following image shows the options available in the Basic edition, but you will find much more granular security options in Business and Enterprise.

G Suite security options

Conclusion

That’s a brief overview of the key differences between the free Google Apps and G Suite, Google’s premium apps service. You will now have a better idea of the benefits to you and your organization if you go ahead and choose G Suite. Hope this has been helpful to you!

G-Suite Discount Code Request

After conditional formatting

Google Sheets | Conditional Formatting an Entire Row

Highlighting Just One Cell

Right now, our custom formula that we built in the previous post is =B1="Joan" and we were applying that formula to column A by using A2:A for the range.

After conditional formatting
Before formatting the entire row
Custom formula
Custom formula

However, we want to highlight each row, in its entirety instead of just one cell.

If you want to learn more about the complex subject of conditional formatting, I have created a course about it over at Datacamp. This is an affiliate link and if you use it to make a purchase I will receive a portion of the proceeds. Thank you for supporting my channel!

Only one cell
Only part of row highlighted

Expanding the Selection

Now, we are going to expand the range used in the “Apply to range” box all the way to column G by entering A2:G into the Apply to range input box. Specifying the range using this syntax will start the range at A2 and expand it down to the end of the spreadsheet and to the right through column G.

New range
Extending the range
Watch the video

This site has a companion YouTube channel that has pretty much, well almost exactly, the same content. If you like this, you'll like that.

Fixing the Formula

If you stop now, it doesn’t change anything. You would think the formatting would extend across the entire row, but it doesn’t. What you need to do is change the formula.

Custom formula with fixed column

Custom formula with fixed column

Before this change, the formula was incrementing one cell to right each time it calculated, just like any other spreadsheet formula when it is copied to another cell. Now, we have changed the formula from =B1="Joan" to =$B1="Joan". The dollar sign prevents the formula from moving to the right each time it decides if the conditional formatting criteria is being met. You have told your formula to continue looking at the same column for the criteria as it formats each cell. Just like you would if you were inside a spreadsheet cell, you used a dollar sign to indicate that value shouldn’t move when the formula moves.

The Entire Row is Highlighted

It’s working now since we fixed the column reference. Every row that was a sale by Joan has been highlighted.

Entire Rows are Highligthed
Custom formula with fixed column

I hope that was helpful. You can take this into your next presentation and wow everyone. You’ll just be amazing;) Have fun with it.

docs share icon

keep in gmail

Add an Email from Gmail into Google Keep – No Extensions

If you have a Gmail email and like to take notes in Google Keep, there have probably been times where you would like to add an email to Google Keep so that you can refer to it later. There’s a way to do this now without extensions or plugins, in just a few easy steps.

First, go to your Gmail and find the email that you’d like to store in Google Keep, and open it. On the far right-hand side of the screen you’ll find a panel with a Google Keep icon. Left-click on this icon to open Google Keep.

keep in gmail

With Google Keep open, you’ll find on the far right at the top there’s a button with a yellow plus sign which gives you the option to create a new note. Left-click on this button to create a new note for the email.

take note in keep

This brings up a window where we can add a title and text to the note. You’ll see that the note already contains a link to the email.

email link note

Add a description to the note, in this example we’ll clarify that it’s an email about time management software. Then click ‘Done’ to add the note to Google Keep.

keep note description


Now, let’s go to Google Keep. Our note is here!

note in keep

We might want to color all our Gmail notes the same color to make them easier to spot. Click the Color palette icon at the bottom of the note and select a color for this note. We’ll make it red.

keep note color

We can click on the link inside the note to bring up the email in Gmail.

keep note link email

It’s important to remember not to delete the email, as our note does not make a copy of anything – it just acts as a link-back to the original email. However, you can archive the email in Gmail to get it out of your inbox, and the link will still work fine.

gmail archive email

You can also have Google Keep remind you about the email later, for example next week. Left-click the Reminder icon at the bottom of the note in Google Keep, and select when you would like to be reminded.

keep note reminder

You can then archive the note in Google Keep as well so it doesn’t clutter the page, and you’ll still get a reminder notification, so you can deal with it at the right time. To do this, left-click the Archive icon at the bottom of the note.

archive note keep

That’s it! We’ve added our email to Google Keep. The email is archived (out of our Gmail inbox), the Google Keep note is archived (out of our Google Keep home page) and we’ll be getting a reminder about the email next week.

Hope this tutorial has been useful for you and your business!

Tutorial Video