Excel formulas & tips

Here are some Excel functions and tips, which might help you to format the data in your import file.

Gotcha’s to watch out for

Any leading zero’s will be lost when opening a csv type file. Eg, 0021 will revert to just 21. If that is a category number, then that will fine, as we have enough smarts to translate that when importing.

Price values like 204 and 204.00 are the same thing when importing into Tradevine.

Excel will convert large numbers like barcodes into scientific notation. Eg. 1234567890123 gets converted to 1.23457E+12. The workaround is to either open the file in a simple text editor like Notepad etc or deslect the barcode field when you import the file back into Tradevine.

When copying and pasting the cell data created by a formula, make sure you paste the data as a value, not a formula. In later versions of Excel, right clicking where you are pasting into, will offer a 123 paste values option.

 

Handy hints

You can copy the formula in the top cell of a column quickly down to the bottom by mousing over the bottom right corner of the cell with the formula and when you see the bold plus symbol, double click it. It should automatically copy the data down to the last row unless your column has a mix of empty cells and non-empty cells.

Formatting your pricing type data

In most of these examples, we take a number from cell A2 and convert the format of the number.

Cell A2 = 123.45

Mission Formula Outcome
Add a 65% markup =A2*1.65 203.6925
Round to 2 decimal places =ROUND(A2*1.65,2) 203.69
Round up to the nearest whole dollar =ROUNDUP(A2*1.65,0) 204
Round down to the nearest whole dollar =ROUNDDOWN(A2*1.65,0) 203
Substitute the cents for 95c in the dollar =TEXT(A2*1.65,"0.95") 204.95
NB: this has rounded the dollar value and then applied the 95c
Substitute the cents for 95c in the dollar, but first round down to the nearest whole dollar =TEXT(ROUNDDOWN(A2*1.65,0),"0.95") 203.95

Formatting your text data

In these examples, we will build up some text using other text.

Cell A2 contains the word Gizmo. Cell B2 contains the value 123.45

 

Mission Build up some text from 4 different components
Formula =CONCATENATE("This is the best ",A2," on the planet at just $",B2)
Outcome This is the best Gizmo on the planet at just $123.45
   
Mission Build up some text, but format some of it onto a new line using CHAR(10)
Formula =CONCATENATE("Model S-01725",CHAR(10),"This is the best ",A2," on the planet at just $",B2)
Outcome Model S-01725
This is the best Gizmo on the planet at just $123.45
   
Mission Create some text to display over a few lines in a web page using HTML
Formula =CONCATENATE(“S1416-“,A2,”<p>The one and only <b>Gizmo</b>.</p><p>Rated as the best around by the world’s most renown <i>obsessive compulsive</i> product buyers.</p> “)
Outcome   S1416-GIZMO
The one and only Gizmo.
Rated as the best around by the world’s most renown obsessive compulsive product buyers.
   
Mission Replace the copyright symbol with HTML entity formatted text.
Here our cell A3 contains the text:  Protected by © 2013
Formula =REPLACE(A3,SEARCH("©",A3),1,"&copy;")
Here the Search function is looking for the position of the copyright symbol. The Replace function then only replaces that one character with our new HTML entity text of &copy;
Outcome   Protected by &copy; 2013

 

Common symbols

Always wanted to know how to insert those special symbols, like the symbol for copyright? These can be created by holding down the ALT key and punching in a three or four long sequence of numbers.

NB: if you intend to use these symbols and import the data, then you will need to save your import file in UTF-8 format. Alternatively, if the HTML Entity text exists for a symbol, then you can use this in your import file to create the symbol.

Name Example Key Combination HTML Entity
Bullet point ALT + 0149 &bull;
Degrees 45° or 17°C ALT + 248 &#248;
Diameter Ø ALT + 0216 &#216;
Copyright © ALT + 0169 &copy;
Registered ® ALT + 0174 &reg;
Trademark ALT + 0153 &trade;
Quarter ¼ ALT + 172 &#0188;
Half ½ ALT + 171 &#0189;
Three Quarters ¾ ALT + 0190 &#0190;
Infinity   &#8734;
Cents ¢ ALT + 0162 &cent;
Micro µ ALT + 0181 &micro;

Tips

Here are some general Excel tips, which you may find useful.

To quickly enter a date use the keyboard shortcut CTRL with a semicolon ( CTRL + ; )

To enter the current time use the keyboard shortcut CTRL with SHIFT and a semicolon ( CTRL + SHIFT + ; )

To automatically size the width of all your columns, select the gap in the top left corner (above row 1 and left of column A), this should highlight the entire spreadsheet. Then double click the gap between any two column headers.

Alternatively, if you are a fan of customising your Quick Access Toolbar, then you can add the AutoFit Column Width command to your toolbar.

Again, you will need to select that top left space and then you can select the green AutoFit Column Width icon to auto size all the columns.

There is also an AutoFit Row Height if that appeals.