Tips and tricks for managing product data by spreadsheet

When you dreamt of running an online store, you probably never imagined you’d spend so much time looking at spreadsheets. 

Updating spreadsheets and validating data might win the prize for the most unglamorous tasks in ecommerce. Yet, managing product data is one of the most important things you can master to grow your business.

Fortunately, it’s easy to improve your spreadsheet knowledge and avoid a few of the major pitfalls. Follow along for the top tricks on overcoming the challenges of managing product data by spreadsheet.

Pros and cons of managing products by spreadsheet

First of all, how did ecommerce land in this position in the first place? Each supplier, merchant, channel, and app uses different platforms to manage their data. These parties needed a way to share data with each other. Short of an API, the easiest way to do that was by exporting it to a spreadsheet. 

The good news

Spreadsheets are portable, meaning you can export and share a spreadsheet with anyone. Everyone has either Microsoft Excel or Google Sheets and has a basic understanding of these tools. A universal spreadsheet format like CSV (comma-separated values) can be imported into virtually any program. Here are the main benefits: 

• Familiarity and versatility. You don’t need specialized software or account access to view the data.
• Search and replace functions. Find and change the text to set up wildcards, update a vendor name, or perform other tasks.
• Collaborate with suppliers in real time using Google Sheets, or even set up an inventory sync to automatically update your store’s product inventory. 

The challenges

Despite the ease and affordability of sharing data by spreadsheet, there are some distinct challenges. Let’s take a look. 

1. Data fragmentation. It’s difficult to understand and analyze your data when it exists in many different spreadsheets, which aren’t all in the same format. 
2. Version control issues. When you receive multiple versions of the same report (for instance, a weekly inventory report from a supplier), it’s easy to accidentally open and manipulate the wrong one. You can’t collaborate in real time on a local file and could accidentally duplicate efforts.  
3. Formatting issues. Spreadsheets can exist in different forms: Excel, CSV, Google Sheets, or even Apple Numbers. If you encode rich data in one format, e.g. Excel, that data may be lost or changed when converted to another format. Strict formatting requirements for platforms like Shopify may cause you to manually reformat each file before importing. 
4. Unintended store edits. What happens when you import your data and it messes with live products on your storefront? Something as simple as leaving a cell blank can override data on your product or change the price. 
5. Limited edit visibility. When you update your products with a spreadsheet, it’s difficult or impossible to see your update status or history. 
6. Scalability. Particularly when you reach multichannel distribution, managing products with a spreadsheet can become too cumbersome and time-consuming. 

The top 4 mistakes when managing products by spreadsheet

Despite these challenges, the popularity of data sharing by spreadsheet means you have to learn to live with it. Let’s examine four common pitfalls and how to overcome them to eliminate at least a few of your spreadsheet headaches. 

1. Defaulting to scientific notation

Have you ever witnessed Excel converting your long numbers to a form of equation, like 2.25E+10? To save space, Excel converts long numbers to scientific notation by default. While helpful for mathematicians and scientists, it’s a huge pain for everyone else. In product spreadsheets, this skews common numbers like SKUs and barcodes. 

How to avoid scientific notation in product spreadsheets

There are a few tricks to remove scientific notation in an individual Excel sheet, but no way to permanently disable the feature. To avoid the conversion, it’s best to simply not use the full version of Excel to manage product data. 

Instead, use the CSV for Excel format to view and manipulate your product data. This keeps your data in the universal CSV format and allows you to make changes without any formatting conversion issues. 

To do this, open a blank Excel file, then import your CSV data from text (instructions here!). 

2. Not using ChatGPT to learn formulas

Learning to write Excel formulas feels a little like learning to code sometimes. To save time and hassle, try asking ChatGPT to write the formula for you. “If I’m trying to do something manually in Excel, I’ll briefly describe it to ChatGPT and it’ll give me the right formulas to use (in almost all cases),” advised Daniel Beck, Ablestar founder. 

First, enter a prompt describing your starting point such as the information you want to analyze and which column it’s in. Then, describe what you want to accomplish. Here’s an example of a formula request to transcribe the prices from one spreadsheet into another one. 

3. Leaving blank rows in Excel files

Large datasets can be slow, but they shouldn’t be extremely slow. Excel reads blank rows as data, so it analyzes every blank row each time you import or export a spreadsheet. That translates to an inflated file size and slow imports and exports. But since these rows don’t contain any real data, you can simply delete them. 

How to remove blank rows in Excel

First, check whether you have blank rows in your Excel file. To do this, simply scroll to the bottom of the spreadsheet to see how far down the scroll bar goes. If it scrolls far beyond your data, you have blank rows. 

Next, follow these instructions to remove the blanks:

1. Select the row just below the visible data in your spreadsheet.
2. Hold the ‘Shift’ key and click on the last row of the file to select all rows in between.
3. Open the ‘Edit’ menu in Excel.
4. From the dropdown menu, select ‘Delete’.
5. Re-save your file and you’re good to go!

Bonus tip: In addition to blank rows, keep an eye out for individual blank cells in product spreadsheets. Platforms like Shopify may read blank cells as data and override existing product data on your site. 

4. Manually reformatting each spreadsheet

With spreadsheets, you will always need to open and validate your data to some extent before sending it to a partner or platform. Depending on your ecommerce platform, you may have to completely reformat your spreadsheets to meet its requirements. Shopify, for example, requires columns to be in the exact right order and named exactly to its specifications. Otherwise, your import will fail. 

With some ingenuity and the right tools, you can eliminate much of this manual work. 

How to avoid (most) failed product imports

There are many reasons a product import could fail when you add or edit products. One big one is mismapped product data. Shopify’s strict formatting requirements make this a common occurrence, but no one wants to spend hours reformatting spreadsheets from suppliers. 

A third-party data management app can eliminate a lot of the work for you. Ablestar Bulk Product Editor, for instance, takes multiple file formats and leads you through an import wizard to quickly map different columns to your Shopify product fields— no reformatting needed. 

The app also eliminates one of the headaches of spreadsheet updates— edit visibility. In the app you can see your update status, cancel an edit, view your version history, and undo edits with a click when an unintended change happens. You can also select the option to skip unchanged rows, significantly speeding up minor edits to your product catalog. 

Simplify product edits with Bulk Product Editor

Managing product data by spreadsheet is a common but challenging task in ecommerce. While spreadsheets are portable and easy to share, they bring drawbacks like data fragmentation, version control issues, and formatting challenges.

By mastering these spreadsheet techniques, you’ll not only save time and reduce errors but ensure that your store runs smoothly, allowing you to focus on the more exciting aspects of growing your business.

To simplify spreadsheet imports or eliminate spreadsheets altogether for product management, try Ablestar’s Bulk Product Manager for Shopify stores.