How to use Google Sheets for accounting: Three formulas
Discover three essential Google Sheets formulas for accounting, and see how accounts receivable software can automate these tasks to make your work easier.
While many accounting teams still use Excel, more and more are switching to Google Sheets. With remote work becoming the norm, collaboration and cost savings (Google Sheets is free!) are big reasons why. Want to know how to use Google Sheets for bookkeeping? Here are three helpful formulas every accounting team should know.
VLOOKUP for accounting in Google Sheets
VLOOKUP, or “vertical lookup,” is helpful—especially when dealing with lots of data like accounting teams do. It helps you find specific information (like a customer name or email) and pull back related details (like an invoice number or amount due) from another sheet.
Here’s how to build a VLOOKUP formula in Google Sheets:
- Start with =VLOOKUP
- Add the value or cell you want to look up, like =VLOOKUP(A2, …
- Add the range you want to search, for example: =VLOOKUP(A2, A:I, …
- Add the column number with the value you want, like =VLOOKUP(A2, A:I, 4, …
- Finally, add FALSE and close the parentheses: =VLOOKUP(A2, A:I, 4, FALSE)
The result will show up in your main sheet. Using Google Sheets for accounting like this can help you create reports and spot key account details much faster.
Import data from multiple spreadsheets with Google Sheets
If you’re managing data across several disconnected spreadsheets, pulling everything together for reports or workflows can get tricky.
The IMPORTRANGE formula makes it easy to pull data from one spreadsheet into another—and keeps them synced automatically. It might look complicated because it includes a URL, but it’s simpler than VLOOKUP.
Here’s the formula format:
=IMPORTRANGE(“spreadsheet_url”, “range_string”)
- The spreadsheet_url is the link to the sheet you’re pulling data from.
- The range_string is the specific cells you want to bring in.
Since this pulls data from an online source, you’ll need internet access for it to work.
Adding comments in Google Sheets formulas for accounting
Sometimes, you need to add notes or comments to your data—whether it’s about a partial payment, a dispute, or mismatched invoices.
Google Sheets lets you add text comments inside formulas using the N function. For example, you can write:
=your_formula + N(“partial payment against invoice 07137”)
This adds a note without affecting your calculations, because N returns zero. It’s handy to include extra context for yourself or your team.
Are you tired of juggling Excel or Google Sheets for your accounting? Our accounts receivable software can streamline your payment process and reduce your Days Sales Outstanding (DSO) by 30%. Automate communications, offer better online payment options, and speed up cash application—all in one solution.
Get started with Sage today and take your accounts receivable to the next level!
