How To Easily and Quickly Update Prices in Magento

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.
   
   

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).
   
   
   
   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( .
   
   
   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).
   
   

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).
   
   

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 ( ; ).
   
   

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 0.
   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.
   
   

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.
   
   

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.