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 while bulk editing
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’
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.
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,”-”)
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 in bulk edit
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])
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<>””))
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
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,” “,”-“))
Step 2: Remove all the special characters from the product name
Syntax: =REGEXREPLACE(B2,”[^A-Za-z1-9-]+”,””)
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.
