If you utilize Google Sheets to manage production and management, you can access various formulas to make your work easier. Many functions enable stakeholders to arrange, edit, and look up and analyze different kinds of information.
Every business owner ought to know these Google Sheets formulas
The majority of business owners do not keep track of their sales performance. Because of this, it’s nearly impossible to forecast their sales for the week or month ahead, making them nervous. The adage goes, “If you can’t measure it, you can’t control it.” The analysis of sales metrics is essential for improving your sales performance.
1 Separate data into multiple cells using the Split() function
Sometimes, you’ll have to extract data from one cell and divide it into several cells. This can be accomplished through this split formula.
SPLIT(text, delimiter, [split_by_each], [remove_empty_text])
The text is the same as the one in which the parameters will split. The delimiter character(s) will be used to divide the text.
Make use of this feature to use this function if you have an alphabetical list of names; however, the first and last names aren’t separated. The split formula puts every first name and last name in appropriate columns.
2 Import information using IMPORTRANGE()
Sometimes, we must transfer data from one document to another. For example, the IMPORTRANGE formula lets users transfer a particular set of cells from different worksheets into a new one.
IMPORTRANGE(spreadsheet_url, range_string)
The formula could appear complex, mainly when you see the lengthy spreadsheet URL, but it’s pretty simple. All you require is the spreadsheet’s URL and cells of information you wish to import.
This is an illustration:
IMPORTRANGE(“https://docs.google.com/spreadsheets/x/ 1ewe0OPLHUAlo-RIOLS829sa/edit#gid=1023456789”, “Sheet3!B1:D15”)
Note that quotes contain both the spreadsheet URL and range. It happens since Google Sheets treat both arguments as strings. There is also an exclamation mark between the sheet’s name (Sheet3) and the cell range (B1:D15). Therefore, it is necessary to include this mark to distinguish the two.
3 Add numbers using SUMIF() in a range of cells
For a single criterion, use this: SUMIF(range, criterion, [sum_range])
For multiple criteria, use this: SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, …])
This formula can be used to calculate the total of cells based on the conditions in the range.
The best way to use the formula would be to multiply the costs in certain areas while excluding other categories.
One scenario that could be used for this formula is an examination of your budget. There may be an inventory of items under categories such as Electronics, Movies, Books. If you need to know how much you’ve paid for movies, you could use this formula:
=SUMIF(D1:D10, movies, E1:E100)
The D1:D10 spectrum represents all different categories (Electronics, Movies, Books), and the E1:E100 range covers all prices. When we include the term movies in the formula, we will pull only the movie prices from both columns.
4 Use advanced functionality to find and highlight duplicates in Google Sheets
When we make a long list or spreadsheet, it is possible that we accidentally have duplicate data in a few cells. To locate any text that has been repeated, you can apply the formula to highlight duplicates.
If you are often forced to be concerned about duplicate content in your spreadsheet, read more about highlighting duplicates in Google Sheets to tidy up your spreadsheet.
5 Search for data in columns using VLOOKUP
VLOOKUP(search_key, range, index, [is_sorted])
VLOOKUP is a term used to describe vertical lookup. You might have realized this feature searches for information in columns (vertical field).
In the simplest terms, this feature allows users to find data in an excel spreadsheet that meets the criteria they’re seeking. A few examples of how this function could be beneficial in the business world include:
- Finding items based on the item number
- Finding receipts and identifying them using a docket or spending numbers
- The process of determining sales figures for specific items or groups of products
- Importing information from other sheets
Utilizing data from other sheets and incorporating it into an entirely new document is one of the most significant features of this VLOOKUP formula. It is possible to save time by not moving the entire data set into a new document since it is possible to connect the relevant data using VLOOKUP.
6 Create and use Pivot Tables
Do you have enormous sets of data that make it difficult to view all at once? That’s where a pivot table can help.
Do you have a lot of data that is difficult to see in a single go? That’s why the pivot table could come in handy.
Imagine the pivot table as an overview of your vast information set.
In their fundamentals, spreadsheets store data arranged on an x-axis and y-axis, rows and columns.
A pivot table is the third dimension (pivot) to spreadsheets by aggregating information.
If you own four stores, and a spreadsheet that contains sales from every store throughout the year in one sheet. This makes it hard to track sales by date or store, so you might use a pivot table to take the data from each of these variables and then put them into a smaller table.
Business owners need to learn spreadsheet formulas
A spreadsheet is a powerful tool with numerous options and formulas that can help improve workflows for business. This list is only the bare minimum of the best formulas you must know.
Once you are proficient in these formulas, you’ll be amazed at the improvements you can make to the overall efficiency of your business. For example, learning how to use Google Sheets or Excel properly can aid your business tremendously.
