Microsoft Excel is a powerful spreadsheet program and is part of the Office suite.
This document is to describe a few of the features and tips in Excel, especially in regard to making into CSV files.
•To fill cells with the same or related values - Use the ‘Fill’ feature. There are several methods: To copy to the right, select the cell with the data in and drag the selection down to where you want it repeated and then press Ctrl+D (or Ctrl+R to copy across to the right). You can also use the Arrow options on the Home ribbon. Tips: To copy down to the end of the data you can highlight the cell with the value and then double click on the small square in the bottom right corner of the cell. If you want the same number or date rather than it adding 1 each line select the first 2 cells with the same value and then click the small square to fill down
•Insert, Copy and Paste - We find the quickest method to: Insert - Right click on the column header to the right (or Row indicator below) and select Insert. Move - Right click on the column header to the right (or Row indicator below) and select Cut, then right click on the column header to the right (or Row indicator below) and select Insert Cut Cells if you want to also create a new column, or Paste if you want to overwrite the selected column
•To change Formulas into Values – Create new column, copy the column with the formula, highlight the new column and select Edit / Paste Special and select Values
•To change the order of the spreadsheet - Select all fields (eg: click in the top left corner of the sheet) and select Data / Sort and select which columns you want to sort by.
•To split fields - If a field need to be split several time (eg: an address delimited with ; you can use the left() function to get the first bit and then the mid() function below to get the remaining and then repeat on the remaining to get the rest (see Working with Text below).
•To Join fields - You can add the cell co-ordinates together eg: in cell c2 you could enter =a1&" - "&b2 (Remember to copy and paste as values)
Working with Numbers
•Remove Tax - To calculate the Exclusive value from an Inclusive value (where f2 is the price and 20 is the tax rate) use: =f2-(f2*20/(20+100))
•No Decimal in Prices - If a price column has no decimal point (eg: 15.35 is entered as 1535) then use: =f2/100
Working with Text
•To get part of a cell after the 3rd digit use: =mid(f2,4,10)
•To change a cell by condition use: =if(f2>0.23,f2,0.23)
•To get part of a cell after a character (-) use: =mid(f2,find(“-“,f2)+1,10)
•To get part of a cell before a character (-) use: =Left(f2,find(“-“,f2)-1)
•To combine the above (eg: Swap) use: = mid(f2,find(“-“,f2)+1,10)+”,”+Left(f2,find(“-“,f2)-1)
Export to CSV checklist
•Make sure there are no currency symbols or thousand separators in the prices (Click on the , symbol and un-check the Thousands separater)
•Make sure there are no un-wanted characters in the data. Use the Find/Replace feature (Ctrl+h) to remove characters that can mess up the import or export. You would normally replace with nothing or a space. Examples of these include:
o, (Comma) - This is the field separator character in CSV files. In some areas of Evopos we can handle these if enclosed in double quotes, but we prefer to replace with a space or a ; (semi-colon)
o' (Single quote) - These can cause problems in some circumstances
o(Line Feed) - This is the character chr(13) that marks the end of the record (row). These are often found in the Long Description where HTML has been used. Because you cannot see this character you have to enter Ctrl+J in the Find What box
•To save as an CSV file select File, then select Save As and set the format to CSV
•If the data is shown as hashes (###) try expanding the width to fit. If this does not fix it try changing the format from Text to General (Numbers)
•If a column (eg: part number) is shown with an error (eg: 854545645445+eee) try changing format to 'Number' and reduce the decimal places to 0. If the column (eg: Part Number) has a dot in it or the number is too long you may still lose part of the part number. The better method is to change the file extension to txt, open Excel first, select File / Open to open the file and set the delimited options and when you get to the Column formats make sure the relevant columns are set to 'Text'. Warning: If you save the file as a CSV file and then open the CSV file it will loose the 'Text' setting and the numbers may be corrupted again if you save the CSV file again.
•If the data does not import correctly try removing un-wanted characters (see above)
•The keypress: Ctrl+` (single quote symbol) may have been used to activated the “Show Formulas” mode in Excel. To fix this error and get back the values (or results) just press Ctrl+` again.
•When you set the cell formatting to “Text”, Excel treats any formula in that cell as text and shows it instead of evaluating it.To fix this error, just select the cell, set its formatting to “General”. Now edit the formula and press enter. (Alternatively you can press F2 and then Enter after setting format to General).
•Check that the delimiter has not changed from a comma (,) in Windows / Control Panel / Regional settings / Listing separator (sometimes changed to create delimited with ‘|’ CSV files to get over issues with commas in text)