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.
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).
You need Excel and the supplier list with the new prices (in digital form).
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.
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
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))
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).
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).
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 ( ; ).
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.
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.
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.
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.