MS Excel Tricks for CSV Product Uploads to Shopify

MS Excel tricks can automatically generate hundreds of entries for a CSV product file upload to Shopify. Among other things, these MS Excel tricks are great for generating fields like the title, SEO description, handle and so on. Goodbye tedium! Hello SEO friendliness.

--

--

Title

A good title is comprised of different product details (its brand name, product type and so on). Here is a formula for combining its component details from other cells.

• Click on a cell within the 'Title' column

• Enter the formula as pictured immediately below. To clarify, a formula always starts with '='. Then enter the cell address of the 1st component, C3 in this case. Then enter the following to grab its content and then another '&' if another cell will follow.

=C3&""&E3

NB: If you want a space after the cell's contents leave a space between quotation marks as follows.

=C3&" "&E3

• Continue adding cells this way. When there are no further cells, do not end '&' after the last set of quotation marks.

=C3&" "&E3&" "&D3&" "

Handle

Shopify requires merchants to remove all spaces in the handle field. An automatic way of creating SEO-friendly titles and then removing the spaces with dashes '-' is as follows.

-- --

• As shown immediately below, paste ONLY the values from the contents of the cell for the 'Title'. To get this dialog box, right click on the cell to which you will paste content and select 'Paste Special ...'. 

Above we illustrated with only one line. However, you are likely to have hundreds or even thousands of lines that can be done all at once. Immediately below, I have created 2 additional lines to illustrate how this may be done for multiple 'Handle' cells at once. In fact, you may select the entire column.

• Select the entire 'Handle' column or cells whose spaces will be replaced
• Click 'Ctrl H' in Windows or 'Command H' in Apple to get the the 'Find and Replace' dialog box
• Click the 'Replace' tab
• NB create a space in the 'Find what' field by placing the cursor into that field and pressing the space bar once. Obviously, you will not see anything. However, you will know you have accomplished this task if the cursor advanced. If you do not do this, this function will not work.
• As shown immediately below, in the next field, 'Replace with', enter a dash '-'
• Click the 'Replace all' button

• If you executed the steps above, you should see a confirmation like the one pictured immediately below.

--

--

Google Product Category

Use the Google Product Category attribute to indicate the category of your item based on the Google product taxonomy. Categorizing your product helps ensure that your ad is shown with the right search results.

• Go to 'Google Products Taxonomy' of download the MS Excel file direct from Google
• Copy your category code. In the example immediately below, the product is a rug. The code '571' is therefore highlighted.

• Paste the code into the CSV file field 'Google Shopping / Google Product Category'

SEO Description

Many people neglect this cell, allowing the body content to become the default. However, taking the time to create a formula can increase conversions. Among other things, the formula can include a product-specific value propositions and benefits like 'free shipping' and benefits'. To achieve this, I create temporary columns in my CSV file for 'value' and 'benefits'. Before uploading the file to Shopify, I copy and paste the result as values (and not formulas) and then delete the temporary columns.

In the following formula example, we combine filler words (highlighted below) with the content of cells that describe the product type and USP.

="Show off "&E3&" Get "&F3&""

If you have numerous products, some of which will not have benefits in one of the cells, it will be best to leave out filler words since the filler words will not be followed by an attribute.

=E5&""&F5&""

RELATED CONTENT

• How to create product variants in the CSV file for upload to Shopify

How to Create Product Variants For Shopify in the CSV file

Why Use Variants
From an SEO and Google ranking perspective, 'minor' product variants should be listed as subordinate variant listings rather than individual standalone products. If Google spots the same information for multiple individual standalone products with only minor differences like color or price, your website could receive a strike against it. Conversely, variant differences that are considered to be sufficiently significant to justify multiple individual standalone listings are those that involve the following variables.

• Title
• Body (HTML)
• Vendor
• Tags

--

--

The 2 Methods
This post will illustrate 2 ways to use a CSV file to create listings for color variants in a Shopify store. Below is what customers will see on an individual product page (for either method).

In the pictured example (above), a user will simply need to click the 'Color' drop down menu to view the variants 'blue', 'red' and so on. When customers select a different color, they will see unique stock keeping Unit (SKU) codes and prices for each variant.

The 2 methods are:

1) Shopify-generated CSV

2) Manual Way

1) Shopify-generated CSV

I prefer this method because provides a more reliable template (than the other method). It will not set you wrong, even if Shopify changes its settings and renders the details of method 2 outdated. 

This method involves first manually setting up variants in Shopify before downloading Shopify's automatically generated CSV file of those variants. 

• Within Shopify, go to 'Products'
• Select the 'Add a new product' button (in the top right corner of the screen)
• Create a page as usual for only the front / collection page display variant 
• Scroll to the bottom of the screen to see the options for creating variants. Name your variant, in this example 'color'. List the variants, along with their corresponding prices.
• Save that page. NB If you forget this part, you will be unable to add variant-specific images.
• After saving the page, continue by clicking the image icon that should now appear next to each variant to add corresponding variant images
• Save

2) Manual Method (in the CSV file)

2a - Collection Page Display Variant

In the CSV file, create the front / collection page display variant as any normal simple product without a variant with one exception.

• In column 'Option 1 Value', change 'Default Title' to 'Color' (your variant type)

2b - Each Variant Line

Make the following entries.

• Handle:      copy and paste from the collection page display variant above
• Title:      LEAVE BLANK
• Body (HTML):      LEAVE BLANK
• Vendor:      LEAVE BLANK
• Tags:      LEAVE BLANK
• Published:      LEAVE BLANK
• Option 1 Value:      Blue
• Variant SKU:      Create a variant form of the collection page display variant. In this case, the word 'blue' was used at the end of basic SKU (optional)

 

• Variant Grams:      Enter the appropriate data
• Inventory Variant Qty:      Enter the appropriate data
• Image Src:      Enter the URL of the image for the variant. You will be able to copy the URL from Shopify after having already uploaded the image file on the same Shopify page (via 'Settings', 'Files'). As above, this variant image will appear as a small thumbnail below the large image of the main option.
• ... all others:      Enter the appropriate data

RELATED TOPICS

• Formulas that make it easy to create CSV product files for Shopify