How To Easily and Quickly Update Prices in Magento

Updated: May 29, 2021
Step by step-guide on how to compare prices with cobby, calculate new sales prices using the supplier list and import prices to Magento with one click.

You would like to update multiple product prices in a Magento store? To do this manually for each product is very time consuming and cumbersome. Mass processing would be the faster way here.
In Magento 1 or Magento 2, bulk editing usually works by importing and exporting csv files. Especially if your store has a very large product catalog, it is much more time-saving and clearer to export the data in a spreadsheet program like Excel, update the prices and then import them back into the store. However, importing and exporting data to and from Magento requires a deeper understanding of the store data structure and the CSV file format.

Sounds complicated? It is indeed. That’s why cobby offers you a simpler and faster solution for updating product prices in Magento. Watch the video or the step-by-step tutorial below to learn more.

Video: How to bulk update prices with cobby in Magento

Scenario

You sell different items from different suppliers through a Magento online store. Your supplier for bags sends you a price list with new prices. You would like to transfer these purchase prices to your Magento shop with a certain price surcharge (sales prices).

Step by step

You need Excel and the supplier list with the new prices (in digital form).

  1. Open files and load data

    First open your supplier list with Excel.
    Then click on cobby (1) → Load products (2).
    The product data from your Magento store will now be loaded into new spreadsheets. The supplier list remains in a separate sheet in Excel.

    Supplier List with cobby

  2. Add new columns for the price matching

    First you create another column.
    To do this, first select the correct product category via the spreadsheets: In this case “Bag”, because only the prices for bags should be updated.
    Click with the right mouse button to the right of the price column (1).
    Add a new column via “Insert” (2).
    The new column “Column1” is created (3).

    Insert column

    The column serves only as a temporary addition and is not taken into account when saving in the Magento backend.
    Please also note that Excel uses existing filters from the previous column. This can lead to errors when creating formulas. You can find more information about this and detailed instructions on how to disable it here: Deaktivieren der Datenüberprüfung für Excel Zellen

  3. Initiate formula for price matching (INDEX)

    In the newly created column, enter the beginning of the formula for the INDEX =INDEX( .
    Insert Excel formula INDEX

    The Excel formula INDEX is composed of four parts:
    1. What is to be matched?
    In our example New price from the supplier list: NewPrices → Column C
    2. What should be matched in the “old” list?
    Here it is the SKU, because this is the same in both lists and is unique, i.e. from the sheet Bag → Column SKU
    3. Which section is it in the “new” list?
    Here it is the SKU in the supplier list: NewPrices → Column A
    4. At the end the “0” still follows for the exact price
    Formula: =INDEX(NewPrices!C:C;MATCH([@SKU];NewPrices!A:A; 0))

  4. First formula part: select section which should be adjusted

    While you are still in the formula input, click into the spreadsheet with the supplier list NewPrices (1), select the column header “C” (2) and close this first part of the formula with a semicolon ; (3).

    Insert Excel formula Index

  5. Second formula part: select matching value in existing list

    Now switch to the spreadsheet Bag, write “MATCH” after the semicolon (click on a cell in the SKU column and close this part again with a semicolon ( ; ).
    The SKU must be in the same line as the newly created formula (in this example line 3).

    Insert Excel formula INDEX

  6. Third formula part: select matching value in the new list

    In the next step, you add the column SKU from the supplier list. In order to do this, switch back to the supplier list NewPrices, select the header column with the SKU (in this example A) and close this part with a semicolon ( ; ).

    Insert Excel formula INDEX

  7. INDEX Finalize formula and apply to the whole column

    With another semicolon ( ; ) you then indicate that an exact match must be found. The constant for this is .
    With two closed brackets )) you end the input of the INDEX formula. To apply the formula to the whole column and thus to each article, you can now copy and paste it or double-click on the small square at the bottom right of the cell to copy it for all cells.

    Excel copy Row

  8. Calculate new sales prices with a formula

    Since these are the purchase prices, you increase the new prices by your margin, in this case by 10% as an example.
    To do this, you must store the following function in the first cell of the original price column (1) (3). Formula: =ROUND([@Column1]*110%;0)
    With this the new price is automatically rounded.
    By dragging down via the small square at the bottom right, you copy the formula again for all cells. Your new sales price is fixed.

    cobby in Excel

  9. Transfer (Import) data to Magento

    At the end you click on “Save products” in the cobby section (no. 5 of the upper image).
    Your new prices will now be updated in real-time in your Magento store and are immediately online.

Related:  Set Up Special Prices for Magento Products

Conclusion

In order to easily and quickly update many prices in Magento, you require neither Magento knowledge nor a complicated csv import-export solution. All you need is cobby and Excel.

Product
Store Systems