How to Split a Comma-Separated List of URLs From a Column Into Separate Rows in Google Sheets

If you have a spreadsheet that has a column with a comma-separated list of values (like URLs), you can easily combine them and create a separate row for each one in Google Sheets using a formula like this:

=TRANSPOSE(SPLIT(JOIN(",", Sheet1!B2:B), ","))

The formula above references column B of Sheet1, where each row's Column B is a comma-separated list of URLs for each product on a Shopify store.

Here's what the formula does:

  1. JOIN(",", Sheet1!B2:B): This part of the formula combines all …

Read more

How to Import a Range From Another Sheet Within the Same Spreadsheet in Google Sheets

Google Sheets has an IMPORTRANGE function that allows you to import a range of cells from another spreadsheet and requires you to enter the spreadsheet's URL.

But what if you just want to reference data from another sheet within the same spreadsheet?

A simpler approach is to use the QUERY function instead.

A Quick Example

We can use this formula to pull the values from columns A and B of Sheet1:


Here's the data in Sheet1:

We'll enter the formula in …

Read more

How to Reference the Current Row in Google Sheets Formulas

Google Sheets has an INDIRECT function that lets users use relative row references in formulas.

For example, instead of using a formula like =A2+B2, you can replace the row numbers with a variable referencing the current row.  This way, you don't have to worry about whether the references for the current row are correct if your formula only deals with data in the current row.

This is particularly useful if you're pushing the formula to …

Read more

How to Change CSV File Encoding to UTF-8 with Google Sheets

When opening a file in Excel or LibreOffice, you may notice some characters don't display properly.  This can be frustrating, and feel overwhelming but we've found a pretty easy fix that we think can help you get this figured out. 

One way to solve this problem is to ensure your file is encoding in UTF-8.  This can get tricky as tools like Excel and LibreOffice have many different versions, so figuring out where the settings are can be confusing.

We've …

Read more

How to Create a Basic Pivot Table in Google Sheets

Google Sheets has a "Pivot table" option which allows you to create summary reports from your existing data.

In this tutorial, we'll show you how to create a basic pivot table from a list of individual order data.

The pivot table will group the data by the "Created At" column and will display the following:

  1. Number of orders per day.
  2. Total quantity of all line items ordered per day.
  3. Average order value per day.

Step 1: Select the range of data to summarize. …

Read more