8 Must Know Spreadsheet Tips for Managing Your Product Information

When it comes to managing product information, spreadsheets are a trusted companion for countless ecommerce businesses. Their versatility and widespread usage make them a go-to tool for organizing and managing product data. 

In this blog post, we'll discuss eight must-know spreadsheet tips that you can use to improve your product data management. While we delve into these tips, keep in mind that, although spreadsheets are an exceptional starting point, a dedicated Product Information Management (PIM) system can always help take your data management to the next level. 

For now, let's explore the world of spreadsheets and discover how these tips can optimize your product data management process.

1. Keep errors at bay with data validation

Tip number 1 is using ‘Data Validation’ to make sure that your data adheres to the specific criteria that you set. Data validation is your first line of defense against data entry errors. Using it ensures consistency and accuracy throughout your product information. 

How to do it

Select the cell or cell range you want to validate, go to the "Data" tab, and click "Data Validation." From there, you’ll need to define your criteria. You may want to check for specific values, such as date ranges or specific text.  

In this example, we’re using data validation to check that all the prices listed are set to the correct currency. By setting the “rules” to “text contains” “$,” we will get an automatic error message for any piece of data that doesn’t adhere to this criteria. 

2. Use conditional formatting to gain visual insights

Conditional formatting is like having your data speak to you. It allows you to visualize trends and anomalies instantly and helps to facilitate quick decision-making. With conditional formatting, you’re able to highlight cells based on specific conditions, making it easier to analyze the data visually. 

How to do it

Select the range you want to analyze, go to the "Format" tab, and click "Conditional Formatting." Then, you can select the rule and formatting options you wish to apply.

In this example, we’re using a rule that says if the selected cells are equal to less than 100, they’ll be highlighted orange. By doing this, we can gain a quick visual overview of how many of our products are selling for less than $100 should you want to assess or change your pricing. 

3. Use pivot tables to make data analysis simple

When dealing with vast amounts of data, PivotTables are your go-to for data summarization. They offer a quick way to derive valuable insights from extensive datasets.

How to do it

Select the data you want to analyze, go to the "Insert" tab, and click "PivotTable" and then “Create.” You can either add the table to your existing sheet or a new sheet, depending on your preferences. Google Sheets will recommend some data summaries for you, or you can customize your Pivot Table according to what information you want to see. You do this by choosing the fields and dragging them into the appropriate areas.

In this example, we’ve set the product’s price as the “row” and the count of the SKU’s that have that price as the “value.” In this instance, we’re able to get a quick snapshot of how many products fall under what price category.

4. Refine your data with filters

Data filtering is your trusty sidekick for narrowing down your product list. Whether you need to view products in a specific price range or find out-of-stock items, data filtering is the way to simplify the process. With data filtering, you can view and manipulate specific subsets of your data.

How to do it 

Select the data you want to apply a filter to, go to the "Data" tab, and click "Create a filter." Then, use the drop-down arrows to filter data based on your criteria.

In this example, we’ve applied a filter to the product description collum to filter out any products with missing descriptions. That way, they can easily be worked on and added to. 

5. Simplify data presentation with custom views

Custom Views are your ticket to quickly accessing specific views of your product data depending on your needs. You can set up different views for different tasks, like marketing enrichment or product specifications. Custom Views enable you to save and switch between different settings for filtering, sorting, and other view options. 

How to do it

Select the columns you wish to work with, go to the "Data" tab, and click "Filter views," and then “Create new filter view.” From here, you can make adjustments to your sheet, hide columns that aren’t relevant, or add other filters that are useful to you. Name your new filter and click the “X” when you’re ready to return to the regular sheet. Your view will be automatically saved and are viewable at any time. 

In this example, a filter has been applied to the product description column and saved as a view. This means that only the products with a missing description will be shown when using this view. A team might use a custom filter views like this for their marketing department to view and work on data that falls under their responsibility, like product descriptions. 

6. Seamlessly integrate your data with import and export

With Google Sheets, you can import and export data from various sources or other sheets. Google’s data import and export capabilities work well for integrating your product data with other systems. Making use of this feature simplifies data exchange, whether it's with suppliers, ecommerce platforms, or other critical systems.

How to do it

Go to the "File" tab, select "Import" to import data, and choose "Download" to export the data in the formats you need.

For a more detailed guide on how to combine data from multiple documents, see this blog post, “How to merge product information from multiple sheets.”

7. Find data with ease using VLOOKUP

VLOOKUP is a lifesaver when you need to find specific information in a sea of data. VLOOKUP is a powerful function in Google Sheets (and other spreadsheet programs) that allows you to search for a specific value in a range or table and retrieve related information from that table. It's commonly used for tasks like looking up specific product data or any other dataset where you need to find and retrieve particular details.

How to do it

The syntax for the VLOOKUP function is as follows: VLOOKUP(search_key, range, index, [is_sorted]

Let's break down each part of this function:

• search_key: This is the value you want to look up. In your case, it could be a product name, ID, or any unique identifier.
• range: This is the range or table where you want to search for the search_key. Make sure the search_key is in the first column of this range and the data you want to retrieve is in subsequent columns. In a product data example, the range might include product names in the first column and product details (e.g., price, description) in adjacent columns.
• index: This is the column number (1, 2, 3, etc.) from which you want to retrieve data when a match is found. For example, if you want to get the price of a product, and the price is in the third column of your range, you would use 3 as the index.
• [is_sorted]: This is an optional argument. If you set it to TRUE (or omit it), VLOOKUP assumes that the first column of the range is sorted in ascending order. If set to FALSE, it assumes an exact match is required. For most product data scenarios, you would use FALSE because you want to find an exact match.

Here's an example of how to use VLOOKUP in Google Sheets with product data:

Imagine you have two different sheets, one with your barcode and description and the other with your SKU and barcode, and you want to combine the data so that it’s all in one place and matching to each product correctly.

Your first sheet might look something like this: 

“Description and Barcode”

And your second sheet might look something like this: 

“SKU and Barcode”

In order to combine the two sets of data so that the SKU, Barcode and Description are all together for each product, you’d need to complete the following action;

=VLOOKUP(A2,'Description and Barcode'!A:B,2,False). 

• "A2", is the search_key.
• "Description and Barcode'!A:B" is the range to search in
• "2" is the index to retrieve the description.
• "FALSE" indicates that you want an exact match.

The formula will return the matching descriptions for each product. 

8. Eliminate Duplicates

Keeping your data clean and organized is crucial. Eliminating duplicates helps to maintain data integrity by removing repeated entries so that your product information remains accurate.

How to do it

Choose the columns you wish to check for duplicates, go to the "Data" tab, and click "Data clean up," followed by “Remove duplicate data.” Doing this will automatically delete any repeated data throughout your selected area.

Consider a PIM tool

Google Sheets is undoubtedly a powerful tool for managing your product information. As we’ve seen throughout these eight tips, it’s versatile and efficient. However, as your business and product catalog grows, you may start to find limitations and increasing errors. 

This is where a dedicated system for Product Information Management (PIM) comes into play. PIM elevates your data management capabilities to a new level, offering advanced features designed for  handling complex product data. 

With PIM, you can 

• Have customizable views: Whereas spreadsheets allow only for “filter views,” a PIM allows you to save completely customizable views for each team or colleague as needed. They can be used to ensure that only relevant information can be touched or changed by the appropriate teams, eliminating errors and making for a smoother workflow. 
  
  
• Edit in bulk: With PIM, you can search, edit, and replace old data in just a few clicks. You simply use the bulk edit function to make changes to thousands of products simultaneously. 
  
  
• Visually mark your products as ‘complete’ or not:  With Plytix, you can use a feature called ‘completeness attributes,’ which works like a checklist to visually highlight what product information is complete and what still has data missing. You use this to ensure that only product information that’s 100% ready can be sent to your sales channels.
  
  
• Manage your digital assets: Managing product images with a PIM can be seamless. You won’t need any other digital asset management system (like Dropbox or Google Photos) because, with most PIMs, you can upload, organize, and store all your digital assets right from within the platform.

While you apply these tips to your daily routine using spreadsheets, keep PIM in mind as the ultimate solution to master the art of product data management. PIM ensures data accuracy, consistency, and scalability, and it provides a user-friendly interface to help your product information management reach new heights.

To discover how PIM adopters are outperforming their competitors, grab your free copy of the ebook How PIM Adopters Outperform their Competition.