11 Ways to Improve Efficiency with Magento Catalog Work Using Excel

The user experience for merchandisers within the Magento 2 platform has improved substantially from Magento 1. It makes a lot of things that must be done manually in the back end much easier and faster, thereby allowing for improved efficiency. For example, you can now filter by name, SKU, attribute set, etc.

Although Magento has added a lot of improvements to its new platform, you will still need to do a lot of work in Excel, and we are here to make your life easier. With that in mind, we have compiled a list of tips to help you maximize your efficiency with Excel, and, in turn, catalog work.

1. Sort and Filter

This can easily be said to be the most important way to improve your efficiency in Excel. If you aren’t utilizing this functionality, you need to start implementing it.

To take advantage of sorting and filtering, first select everything in your workbook. You can do this by clicking any cell and pressing Ctrl + A or clicking the arrow in the top left corner. Go to Data > Filter. Then, click the arrow for any column you want to filter or sort and choose the options you’d like.

This will allow you to view only certain products at a time. For example, let’s say you need to add colors to your “additional_attributes” column and currently the color of an item can only be found in its description. You can go to your “description” column, click the arrow, and type in a color name, such as “red.” Then, you would see all the items that are “red,” and, in turn, you will know which items you can input as red in your “additional_attributes” column. You could repeat the process for each color.

2. Freeze Panes

When working in a spreadsheet, it’s nice to be able to see the first row and column, regardless of how far you scroll. It’s especially helpful to have your first row frozen, because it gives you the ability to change filter and sorting options without having to scroll to the top of the spreadsheet. It will save a lot of time and make things more efficient, especially if your spreadsheet contains a high number of products.

Freezing the first column is great for referencing SKU and name. By doing this, you know what product the row you’re working on and the SKU/name in which it corresponds.

How to freeze your first row and column at the same time: Select cell B2 and then click Freeze Panes in the View tab.

3. Narrow Down Your Export Columns

Okay, so you probably won’t be able to narrow down your export to include only the products you need to update, but there are ways to narrow things down nonetheless. For example, if you have some disabled products and you don’t want to enable them, select “Enabled” from the dropdown for Enable Status (on the Export page). Contrary to what you might expect, this will not exclude the enabled products but rather include them in your report.

Often when you’re working on adding or updating products, you focus on a few key things. You rarely mess with columns for things like “related_tgtr_position_limit”. So, go ahead and exclude some of what you don’t need from your export by checking the corresponding boxes before clicking “Continue” to download your CSV file. If you have a lot of products, this will speed up the export process. Plus, it helps prevent Magento from leaving out information, something which happens more often than you may think.

4. Hide/Delete Columns You Didn’t Exclude in the Export, but Don’t Need

If you aren’t adding or changing anything in certain columns, you don’t need them for a Magento import and, thus, can delete them without repercussions.

If you aren’t working on specific columns, but will need them later, you can simply hide them. Do this by selecting the columns and right clicking to select “Hide”. Alternatively, you can use the shortcut Ctrl + 0 to hide your selected columns. To unhide them, find the two visible columns the hidden ones are in between. Then, right click and select “Unhide”.

Hidden columns can be found where there is extra spacing among the letters for visible columns. Also, you can find them by looking for missing letters in your columns.

5. Consider Working in a .xlsx File First, Before Making a Final .csv

Every time you save and close a .csv file, any filtering, sorting, color coding, comments, etc. you may have done will not get saved. Also, .csv files can’t contain multiple worksheets. With that said, you may want to consider working in an .xlsx file until you’re finished. Before you import into Magento, just save each .xlsx worksheet as a .csv file and they will be good to import individually.

6. Utilize Ctrl + Shift

To select all cells in one direction, press Ctrl + Shift + the arrow in the direction you want to go. Or, if you want to select a range of cells, rather than dragging down to select them*, you can select the first cell in the range, hold Ctrl + Shift, and then click the last cell in the range. These shortcuts come in handy when you want to count items to ensure you have the right amount. In addition, using Ctrl + Shift commands prove useful for copying and pasting.

*Note that if a blank cell exists in the area you want to select, Ctrl + Shift will not select everything you want. In that case, you must drag down to select the cells. Also, know that the count will only include cells in which values exist.

7. Use the Bottom Right Corner of a Cell to Fill Cells Below It

If you want to fill all the cells in a certain column, place your cursor over the bottom right corner of your starting cell and double click where the plus sign shows up. Excel fills the blank cells as a series, but if you want the cells to have the same value as the first one, you can click the little dropdown that pops up and select “Copy Cells”.

Much like how things work with Ctrl + Shift, blank cells will affect the result of double clicking to fill cells. Only the consecutive blank cells below the one you double click the bottom right corner of will be filled. To fill cells with existing values, or apply a formula downward, you must click the plus sign and drag down.

8. Quickly Add Text to Beginning or End of Cells

You can do this with a formula, but if you do, you must add and delete columns, as well as remember to select “Paste Values”.  Code can provide a bit of a shortcut. The code you’ll want to use is as follows:

To add text to the beginning of cells, enter =”text “&A2 To add text to the end of cells, enter =A2&” text”

Replace “text” with whatever your desired text is. As for A2, make that whatever cell you wish to add text to. Also, remember to include a space before or after your specified text. Then, use the bottom right corner trick discussed in #7 to add the text to several cell values.

Now, if you’d speed up the process a bit, you can use Microsoft Visual Basic for Applications (VBA). Simply complete the following steps:

Step 1: Select the cells you want to add text to

Step 2: Hold Alt + F11 to open Microsoft Visual Basic for Applications (VBA)

Step 3: Click Insert > Module and paste the following code (again, change “text” to whatever text you’d like to add)

For adding text at the beginning:

Sub AppendToExistingOnLeft()
Dim c As Range
For Each c In Selection
If c.Value <> "" Then c.Value = "text " & c.Value
Next
End Sub

For adding text at the end:

Sub AppendToExistingOnRight()
Dim c as range
For each c in Selection
If c.value <> "" Then c.value = c.value & " text"
Next
End Sub

Step 4: Hit F5 to apply the code

9. Be Careful with Commas

Know that commas serve as separators for values. For example, in Magento 2, all the attributes go into one column under “additional_attributes”. So say you have “color=red,size=M” in a cell under that column. Assuming you have those attributes and options in your back end, there shouldn’t be a problem. However, if you were to input “color=red, size=M”, it would not import correctly because of the space. And if you didn’t put the comma at all, that would be problematic as well.

10. Take Advantage of VLOOKUP

VLOOKUP is extremely useful when you wish to compare two sets of data. Sometimes, Magento doesn’t import everything it’s supposed to, even when it doesn’t specify any import errors. To double-check data, you can compare a new export sheet with the one you imported. To learn more, check out LexisClick’s tutorial over how to use VLOOKUP for catalog work.

11. Fix Errors Resulting from Invalid Characters

One unfortunate thing about Magento 2 is can - on occasion - spit back a “General system exception happened” error when something is wrong with a .csv file. Sometimes, the problem is one or more invalid characters.

Step 1: Confirm the problem does indeed deal with invalid characters

Create a new sheet and copy and paste everything into your new sheet. Then, select all the cells in your new sheet and use the following VBA code (use Alt + F11 then Insert > Module again) from Allen Wyatt to remove invalid Unicode characters. Remember, you must have the cells you want to apply the code to selected or it won’t work.

Sub Remove8Bit2()
    Dim rngCell As Range
    Dim intChar As Integer
    Dim strCheckString As String
    Dim strCheckChar As String
    Dim intCheckChar As Integer
    Dim strClean As String

    For Each rngCell In Selection
        strCheckString = rngCell.Value
        strClean = ""
        For intChar = 1 To Len(strCheckString)
            strCheckChar = Mid(strCheckString, intChar, 1)
            intCheckChar = Ascw(strCheckChar)
            If intCheckChar < 128 Then
                strClean = strClean & strCheckChar
            End If
        Next intChar
        rngCell.Value = strClean
    Next rngCell
End Sub

After hitting F5 to run the code, save the file and then try importing it into Magento. If you don’t receive the error message, your problem was in fact invalid characters and you should move along to Step #2.

Step 2: Find and replace with Ctrl + F

Try searching for common invalid characters like the degree symbol, left and right quotation marks, the copyright and trademark symbols, etc. Often, depending on what you’re selling, you’ll only use a few of these characters regularly, so they will be easier to find. Once you do find them, replace them with the corresponding HTML code (for example, the code for the degree symbol is °).

Step 3: Once you’ve found and replaced the common invalid characters you can think of, try importing again

If there’s still a problem, you will need to continue to Step #4.

Step 4: Find and replace remaining invalid characters using either VLOOKUP or partial imports depending on your amount of SKUs and preference

If you have a large number of SKUs, you’ll want to perform a VLOOKUP to compare your first sheet to your duplicate with the removed characters. You shouldn’t have invalid characters anywhere outside of the name, description, short_description, and meta_description columns, so those are the only ones you’ll need to check with VLOOKUP. Once you’ve found where the invalid characters were this way, you can add the correct HTML code.

If you’re not working with that many SKUs, rather than doing a VLOOKUP to find where the invalid characters are, if you prefer, you can try breaking up your first sheet into smaller imports to narrow down where the problem lies.

Our application specialists are well-versed in Excel, especially when it comes to what needs to be done for Magento catalog work. Even when utilizing these tips though, catalog work takes time – time you may want to spend on other things. Let us help you with our Catalog Services so you can focus on your business goals and other tasks.

Even with our help, you’ll still have access to the Magento platform so you can complete some work on your site on your own. Plus, Razoyo clients have staging and lab environments to experiment with, so you can test things before they go live. One thing you can do when importing new products into Magento is start by uploading a single product. Then, you can do a product export and see how Magento changes the spreadsheet for clues on what didn’t go through, as well as ‘undocumented’ formatting requirements. That way, you can make changes before importing your entire spreadsheet.