Naturally, as a PIM company, we don't think spreadsheets are the most efficient way of handling product information, but they can be useful to prepare the data for a PIM System. At OneTimePIM, we have experts who handle product data for all kinds of companies. So we thought it would be helpful to round up some of the best spreadsheet formulas they use and share them with you.
That way, if you are already using these formulas you know you're on the right track. Or if they're new to you, then you'll learn something.
So here we are going to explore 6 clever spreadsheet formulas you need to be using for managing product information.
The VLOOKUP function allows you to quickly find a piece of information in a spreadsheet. It instantly locates a data point which you would otherwise be scrolling for. An example that often comes up for staff managing product information, is needing to locate a price for a certain part code.
How to use VLOOKUP
- Select the cell you want the data point you're searching for to appear and type =VLOOKUP().
- Between the brackets, first type the cell which contains the data point you already know then put a comma. In the example below, that cell is J9.
- Then type in the two cells which you want to search between with a colon in the middle of them, then put another comma. In the example below, I selected A2 to F55, so I covered the whole list.
- Using that search area you have created, then type the column number you would like to search for the value, then insert another comma. I needed to find out the length, so that was the 4th column of the ones I had selected. So I entered the number 4.
- Then type in FALSE and press enter.
The system should now locate the data point you needed. In the example you can see the formula in cell J10. Using that formula it is displaying the length associated with the identifier Kettle005.
A very helpful tool if you have thousands of products in a spreadsheet!
The CONCAT function, previously CONCATENATE, allows you to combine the data from different cells. This is particularly useful if wanting to combine two pieces of information to generate an SKU number.
How to use CONCAT
- Select the cell you want the combined data to appear in and type =CONCAT().
- Between the brackets enter all the cells which contain the values you want to combine, with a comma in between. Then press enter. So it would look something like '=CONCAT(A4,A6)'.
Excel should now combine all the values in the cells you have highlighted.
UPPER / LOWER / PROPER
These 3 functions allow to you change the case of letters in a cell. UPPER will make all the text in a cell capitalised. LOWER will make it all lowercase. PROPER will capitalise the first letter of every word. This is useful when generating attribute or category titles in a data sheet.
How to use UPPER / LOWER / PROPER
- Select the cell you want the modified data to appear in and type either UPPER/LOWER/PROPER with an = before and () afterwards.
- Between the brackets, type the cell which you want to change the case of and hit enter.
The system should now insert the text of the cell selected, with the right capitalisation.
ISBLANK will check a certain cell of your choosing and inform you if it is blank with either a TRUE or FALSE message. It's often used as part of a larger nested formula, for example 'if a cell is blank, add a certain value to it'.
Cells will show up as not blank, if it contains a formula. Even if that formula has no data showing. It will also show as not blank if there is only a space in the cell, which can be misleading.
Besides, a good example of when the ISBLANK formula can be handy is alongside the CONCAT function. So Excel can look for blank cells and fill them with data from other cells to form a description of some sort.
How to use ISBLANK
- Select the cell you want the modified data to appear in and type =ISBLANK()
- Between the brackets, type the cell which you want to check is blank and hit enter.
Excel should now give you a TRUE or FALSE reading, depending on if the selected cell contains any value.
The LEN function allows you to count the number of characters in a chosen cell. Say for example you have a description to place on your website, it might be limited by the number of characters. So the LEN function allows you to quickly see what descriptions are within the character limits.
How to use LEN
- Select the cell you want the modified data to appear in and type =LEN().
- Between the brackets, type the cell which you want to count the characters in and hit enter.
The system should be displaying a number which indicates the amount of characters in the selected cell.
TRIM will remove excess spaces from text, leaving only one space between the words in a cell. Space characters at the start or the end of a cell are also removed. This is great for fixing errors in product data that are inevitable when managing thousands of SKU's.
How to use TRIM
- Select the cell you want the modified data to appear in and type =TRIM()
- Between the brackets, enter the cell which you wish to trim off the excess spaces.
The cell should now display the same text that was in the cell you were looking to correct, but with all the extra spaces removed.
LEFT / RIGHT / MID
The LEFT, RIGHT and MID functions allow you to extract characters from the left, right or middle of cells. For example, if you wanted to take a prefix of a product name to form an SKU number, then this function might be the quickest way to do it when you have thousands to generate.
How to use LEFT / RIGHT / MID
- Select the cell you want the modified data to appear in and type the relevant word with an = before and () afterward.
- Enter the cell which you would like to extract the data from, then a comma
3.1) If you are using the LEFT or RIGHT function, then enter the number of characters you would like to copy across, then hit enter.
3.2) If you are using the MID function, enter the character number you would like the copying to start from, then another comma. So it would look something like =MID(A1,2,)
4.2) Between the final comma and brackets, enter a number to state how many characters you would like to copy. Now will look something like =MID(A1,2,4). Then hit enter.
Excel should now display the copied digits in the required cell, to the conditions you laid out in the formula.
Hopefully, these formulas will speed up the management of product information at your company. They have been chosen by our expert data team, who certainly know a thing or two about Excel. They use it every day alongside our spreadsheet view in OneTimePIM to enjoy the powerful benefits of both systems.