How to optimize your data collection with Excel

27 Sep 2022

Are you aware that the combination of data entry via Excel and our web application atrify publishing can be a real turbo for successful data exchange?
And are you now wondering how you can use Excel to spruce up your data and optimize it for atrify publishing ?

In this post, I'll show you two simple tricks that can help you collect data with Excel:

  1. Tip: New article installations with Excel
  2. Tip: Little help for attributes with character limit


1st tip: New article installations with Excel

For data entry with Excel, depending on the stock, I use a current matching base article and an outer packaging, which I export from the atrify publishing to Excel. For this I use the setting "All item data on one page".

atrify publishing Export product data

In order to be able to work optimally with the Excel file, I remove all columns without content. However, note that columns are not deleted for which there may be data in the new articles. I use this method for large amounts of data. For smaller acquisitions, I use a macro that hides empty columns. This has the advantage of being able to show the columns again if needed. Of course, this procedure can also be used for large quantities.
In addition, I delete the "Publishing" tab at the bottom of the Excel file.

If "single-variety" hierarchies are created, I copy the two columns GTIN and number of the next lower article unit in the tab "Article data" between the column "GTIN of the article unit" and "Data owner: GLN".
This way I can enter the link to the lower VPE when I create a new item.

Excel data acquisition for atrify publishing

After completing the entry, the two columns must then be transferred back to the "Components" tab and the "GTIN of item unit" column.

Next, set "Valid-from date (time)" as desired.

atrify publishing Set validity via Excel

Hide the columns between "Number of next lower item unit" and "Item short description" and fix the table in cell "J12" (FMCG Publishing).

atrify publishing: Data acquisition via Ecxel

With this, the table is well prepared and the editing of the master data can begin.

Another small hint: If you want to create large amounts of data, I recommend importing a copy of the table including the restoration of the "Components" tab as a test in atrify publishing before further editing. This way you can correct errors in Excel if necessary and then create and edit a new hierarchy row by row with "Copy/Paste".

atrify publishing Correct data entry with Excel

2nd tip: Little help for attributes with character limit

For some attributes, such as the attribute "Article short description", only 35 characters are provided. To revise and capture the texts for this attribute, I temporarily insert another column next to the attribute. I then provide this newly inserted "Hillf column" (yellow) with a suitable formula that is used to check the number of characters entered. So I can see immediately when I come over the character limit and can optimize the text accordingly.
The formula for the first data line in the example is "=length(I12)". I can copy or "drag down" this formula as a default into other cells for the attribute. Excel automatically inserts the correct variables. Excel also does this job when copying rows.

Excel: Attributes with character limit

And do not forget: Before importing the file to atrify publishing , the newly inserted "auxiliary column" must of course still be deleted in the Excel spreadsheet.

I hope you enjoyed my tips and now I wish you continued fun with data collection using Excel and atrify publishing.

Do you want to have your data professionally recorded or do you need support in processing your data with Excel?
If you have general questions about working with Excel, my colleagues from Support (👉🏽 will be happy to help you. For questions about the tips listed here or about our data entry service, simply send us an e-mail to 👉🏽

Uwe Pieper

Uwe Pieper

Uwe Pieper has been involved in the capture of master data from the SINFOS data pool to atrify for over 20 years. As a Data Capturing Specialist for atrify, Uwe supports our customers in the area of data capture.

More articles in the atrify info hub