While most accounting teams use Excel for their spreadsheet needs, a growing number of teams and organizations are moving to Google Sheets. At time where remote teams are ubiquitous, collaboration and cost (Google Sheets is a free tool), are driving greater adoption of Google applications. Here are three helpful formulas in Google Sheets that all accounting teams should know.
V-lookup in Google Sheets
“V-lookups”, or “vertical lookups” are extremely useful, especially when working with vast amounts of data, like accounting teams do. V-lookups are used to locate a set data point (i.e. a customer name or email address) and return a defined detail for that data point such as invoice number or amount due in a second sheet (tab).
To locate these details, build your formula using these parameters:
- Start with “=VLOOKUP”
- Then add in the value or cell you want to sort by “=VLOOKUP(A2 . . .”
- Next add in the range you want to pull from, i.e. “=VLOOKUP(A2,A:I, . . .”
- Next, you add in the column that contains value you are looking for, i.e. “=VLOOKUP(A2,A:I,4, . . .”
- Finally add in “false” and close parentheses: “=VLOOKUP(A2,A:I,4,FALSE)”
The results will appear in your primary sheet / tab. This will help you build and develop reports and identify key outstandings in accounts much faster.
Importing Data from Multiple Spreadsheets
If your department is like many using manual processes today, you are managing data across several disconnected spreadsheets. But, how do you pull in relevant data for reporting and manage activity workflows?
Using “IMPORTRANGE” easily imports data from one spreadsheet into another, keeping the two spreadsheets in sync at all times. While “IMPORTRANGE” can look long and unwieldy, that’s usually because it includes a URL within the formula, which can make it look far more complicated than it is.
In reality, the formula is easier than VLOOKUP, “=(IMPORTRANGE(, [range_string])”. The Spreadsheet URL is simply the URL link from the spreadsheet you are pulling from. Because this does pull from a URL, Sheets using this function requires online access to pull the data. “Range_string” simply means the specific cells from the pulled spreadsheet you want to sync into your new spreadsheet.
Comments in Google Sheets Formulas
Working with customers and internal partners often requires additional notes and comments to prepare yourself or colleagues for the next interaction.
Within Google Sheets, you can add in an “N” function to add in text or context to a number. A customer makes a partial payment, has a dispute or invoices don’t match up, you can include a note like this “+ N (“partial payment against invoice 07137”) and then can reference these notes in your future conversations. In Sheets, “N” makes a return of “0”, so adding this to a function or amount will not impact any additional formulas you may have in that area.
Tired of using Excel or Google Sheets to manage your accounting workflows? Lockstep Receivables elevates your payments strategy and reduces DSO by 30%. Leverage automated communications, enhanced online payment capabilities, and automated cash application to modernize your collections.