10 Formulas to Edit Bulk Product Data in Excel

by | Nov 17, 2021

Top 6 E-commerce platforms to build a website with in 2021


Anyone who has ever used an Excel spreadsheet to revise product data, especially eCommerce managers, knows how quickly the work can become overwhelming for bulk editing products. With large volumes of products, it is difficult to know where to start. It’s just too easy to miss a cell value or type in the incorrect content.

However, with the right formulas for the respective issues, things could become much easier to manage and edit in Excel Spreadsheet.

In this blog, we will be discussing some magical formulas that you can use to edit bulk product data in excel.

Well then, let’s get started.

Formula 1: Remove leading zeros for Product Name and Description

If you use leading zeros for product numbers in excel, then you need to remove them. Unfortunately, Excel does not provide an easy way to remove leading zeros from names and descriptions of products.

For example, You have a list of products as 00123450123456 and want to remove the first two zeros and make it 123450123456. So how do we do this?

Removing the existing formatting from the cells is all it takes to get rid of the leading zeros.

First Step: Select the cells that contain integers that begin with a zero.

Second Step: Click the ‘home’ tab.

Third Step: Click the Number Format dropdown in the Numbers group.

Fourth Step: Click ‘General’

Edit product name and Descriptions excel product data

Formula 2: Calculate Unit Price (Total Price/ Quantity) and Unit Cost (Total Cost/ Quantity)

You are running a business, you have products of different prices, sizes, and quantities. You want to calculate the total cost of your products under this scenario.

Excel provides an option to calculate the unit cost and unit price for each product. Unit price is the total cost per unit of measure. This can be calculated by dividing the total cost by the quantity of that product. This calculates the unit price. The unit cost is the unit price multiplied by the quantity of that product.

Unit Cost Formula = Total Cost / Total Units Produced or Bought

Unit Price Formula = Total Price/ Number of Units Produced.

edit price quantity and unit price

Check out how to setup Pricing for Retailers and Vendors

Formula 3: Split a cell value by comma, colon, slash, dash or other delimiter

When you are trying to work with data in Excel, your data is often stored in one column, but you want to make it available in multiple columns. For example, I have a list of customer names stored in column A, but I need their phone numbers in columns B and C.

Or let’s say, I have a string of words and numbers in an Excel worksheet that I would like to split into two columns. The goal is to have the first half of the string separated by a dash and split them into separate cells.

For example, if you had the following string of words and numbers: Shoes-123. You would end up with two columns: ‘Shoes’ and ‘123’

Syntax: =SPLIT(A2,”-”)

splitting cells using formula in excel

However, the problem arises when the string is attached with multiple dashes, e.g. Red-long-frog. In this case, using a simple split function would not work. Instead, you would have to use right, left, or middle split functions, listed below:

=LEFT(B1,2) 

OR

=RIGHT(B2,4)

OR
=MIDDLE(B2,5,2)

Formula 4: Using VLOOKUP Function to Find Values

Have this ever happened to you that you want to look up for all the attribute with one attribute. For say, I want someone’s email address, home address, age, and gender. I can do that with one VLOOKUP formula. I can use the VLOOKUP function to retrieve the customer’s phone number based on their name. This is great for when you have a lot of information that you need to access quickly.

VLOOKUP is an abbreviation for ‘Vertical Lookup.’ It’s a function that tells Excel to look for a specific value in a column (the so-called table array’) in order to return a value from another column in the same row.

A VLOOKUP function is made up of four parts:

  • The value you’re looking for;
  • The value you’re looking for and the range in which you’re looking for it;
  • The number of the column that carries the return value inside your selected range;
  • For an exact match with the value you’re looking for, use 0 or FALSE; For a close match, use 1 or TRUE.

Syntax: VLOOKUP([value], [range], [column number], [false or true])

VLookup function in excel

Formula 5: Using formulas to filter out blank values

One of the most time-consuming tasks is to clean up product data in your eCommerce store. Sometimes, if not done properly, it can even lead to losing sales.

In this section, you will learn how to use formulas to filter out blank values in bulk product data

The “formula” column calculates a simple formula that checks for the existence of a value. If a value exists, then it returns it. If not, then it returns a blank. This way, you can use the formula column as a filter where any blank values are converted to zeros.

The “formula” column calculates a simple formula that checks for the existence of a value. If a value exists, then it returns it. If not, then it returns a zero.

Syntax: =FILTER(data,(rng1<>””)*(rng2<>””)*(rng3<>””))

Find and filter blank cells in Excel CSV

Formula 6: Using formulas to select only rows of interest

Selecting only rows of interest in excel for bulk product data is one of the most common excel tasks. I will show you how to use formulas to select only rows of interest in excel.

Let’s say you have a list of products, and you need to know how many of each product you have in stock. You do not want to add a new column for each product. Instead, you can use a formula to select all your products except for those out of stock.

Begin by drawing a cell around all your products using the mouse or Ctrl + Shift + Down Arrow key combination.

You can then apply an IF function.

 =IF(AND(A2=”Score”,A2>=90),1,””)

 – where A2 is a column containing a list of scores and =AND(A2=”Score”,A2>=90) – does a check on a cell value and returns true if the pattern ‘Score’ appears in the cell, and if the cell value is more than 90. 

Formula 7: Using formulas to change dates into text or numbers

If you are using Excel for any kind of work that involves bulk data entry, then you need to know how to use formulas to change dates into text or numbers. The most common scenario where you will need this is when importing product data from a CSV file.

For example, when you import the product data, there are often some cells containing dates. What will happen is that Excel will give you an error saying something like “Cannot convert this type of value”. To avoid this, just use one of the following formulas to change dates into text or numbers, so that Excel will not have any issues importing those CSV files:

Syntax: =VALUE(text)

Syntax: =DATEVALUE(date_text)

Formula 8: Using formulas to find duplicates in bulk product data

It’s not uncommon for business data to contain duplicate records, which can be an issue when it comes to reporting. To check if there are any duplicate records in your excel sheet, you can use the formula tool. You can also learn how to remove duplicates using the filter function in Microsoft excel.


However, it’s always easier to find duplicates in bulk product data, using formulas. You can use a COUNTIF formula in the excel spreadsheet to get ‘TRUE’ or ‘FALSE’ response to detect the duplicate values, where:


True = Duplicate Values

False = Unique Values

Just change the ‘A’ in the formula to the letter of the cell column you’re looking for duplicates in.

Syntax: =COUNTIF(A:A,A1)>1

Find and Fix duplicate values in Excel

Formula 9: Using formulas to count the number of products with certain attributes

I want to collect and display the number of products that have certain product attributes. For example, I want to one attribute “has discount” and output the number of products that have this attribute.

This Excel formula helps you to calculate the total number of products that match certain attributes.

To count the total number of products that match certain attributes, you can use the following formula in Excel.

For example, suppose you want to know the total number of products that are blue with a price between $10 and $20. In this case, your table would look like follows:

Select the column of ‘Colors’ and apply

=COUNTIF(B2:B8,”Blue”,C2:C8,”)

Formula 10: Using Formula to convert Product Name to Product URL

You have a list of products in excel. Now you want to make each product name a product URL. Is it possible? Yes, it is! this is known as dynamic cell referencing/referencing, which can be achieved using the INDEX and MATCH functions.

In Excel, you can use a simple formula to produce a URL from a product name. Usually, this approach is useful when you want to produce a link to a product using a product name as the link text, and you don’t want to have to keep track of any links that have been created manually.

This is only a two-step process, requiring two formulas:

Step 1: Remove all capital letters from the name and substitute all spaces with “-”

Syntax: =LOWER(SUBSTITUTE(B2,” “,”-“))

convert product name to url in excel

Step 2: Remove all the special characters from the product name

Syntax: =REGEXREPLACE(B2,”[^A-Za-z1-9-]+”,””)

product name value to product web address in excel csv

Using PIM to bulk edit product data easily

Have you ever had the following situation?

1. You have a lot of products data in an Excel sheet.

2. But, your data is not complete and needs to be updated with some information from other sources.

3. You need to edit a lot of data one by one, which will take a long time. Or you forget some products and need to find them out again and fill in all the missing information.

4. When you update the product data, you need to update the Excel file by using VBA code, but it’s hard to do it quickly.

In Shopify, when you add a product from an Excel Spreadsheet, e.g. from a CSV file, the product data that you add manually will be lost in translation if it is not in the right format. For example, if you add a product and use the wrong field name or structure, your product information will be lost and you will have to begin again.


What if I tell you that you can rid of this irritating and threatening issue, once and for all? What if I tell you that you will never have to face the above-listed situations? Well, there is a way.

Apimio is a product information management solution, that helps eCommerce managers edit and manage products in bulk and helps solve the issues of formatting and data loss.

In fact, there are a whole bunch of reasons to leave a spreadsheet for a PIM. Luckily, we have covered that already. Feel free to read more about that over here.

Checkout our blog on 7 reasons to leave Spreadsheets for a PIM 

And why would not there be? After all, PIM prevails to replace spreadsheets. It exists to make all the information management easier.

Conclusion

Are you currently in charge of a Shopify business or working as an eCommerce manager in an eCommerce company? Perhaps you’ve considered adopting a product information management system to influence the direction of your company. Perhaps you’re considering switching to Shopify as a way to ease into the eCommerce world.

It makes no difference what the case is. When the surf grows choppy, a PIM may be a reliable and extremely useful tool for keeping you in control. Make the most of the PIM-Shopify integration’s powerful superpower.

I hope that this post was helpful in showing you some quick and easy ways to edit bulk data in excel. If you have any tips of your own, please leave them in the comment section below.

Recent Articles

What is an SKU? The Ultimate Guide To Unique Product Identifiers.

What is an SKU? The Ultimate Guide To Unique Product Identifiers.

The last thing a brand wants is to be associated with a product recall. But in today’s world, it’s not just about the quality of the product itself. It’s also about how you label and identify your products. Luckily, SKU solves this issue for you.

In this blog, we will be discussing SKU in-depth and going through its definition, purpose, working, benefits, and finally, the difference between an SKU and UPC.

read more
Product Content Syndication: Why You Should Be Doing It.

Product Content Syndication: Why You Should Be Doing It.

Your product content is the lifeblood of your business. If you’re selling products online, then you know that you want to direct your customers back to your eCommerce channel when they need to buy something. You can do that with product content syndication. Product content syndication is the process of taking your product content and distributing it in relevant eCommerce platforms on the web. It’s a great way to get in front of your customers when they’re ready for conversion.

read more
11 Key Responsibilities of the Marketing Department

11 Key Responsibilities of the Marketing Department

Marketing professionals have many responsibilities that include building the company’s image, developing strategies to promote products or services, and providing information about those products or services to all interested parties. 

In this blog, we will be talking about the key roles and responsibilities of the marketing department. Well then, let’s get started.

read more

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

Share This