Tips for using ExcelTips - March 9, 2016
Your mapping project requires hundred of markers? Don’t panic. A simple import process exists in the Click2Map editor to add several markers in one single operation.
It’s possible to import .csv, .kml and .xml files. For the record, a Microsoft Excel document can be converted with a few clicks into a .csv file.
These different files have to contain Click2Map data fields (more information here). We can never say it often enough: for a successful and efficient import, your original file must be organized completely.
In this blog post, we give you some tips to gain time in the use of Microsoft Excel and create a file ready to import.
The following shortcuts are the most common and useful to update and edit an Excel file.
Create a new workbook: Ctrl + N
Copy: Ctrl + C
Paste: Ctrl + V
Cut: Ctrl + X
Cancel: Ctrl + Z
Select the entire worksheet: Ctrl + A
Save: Ctrl + S
Save as: F12
Close Excel: Alt + F4
Select an entire column in a worksheet: Ctrl + Spacebar
select an entire row in a worksheet: Shift + Spacebar
Insert blank cells: Ctrl + Plus (+)
Delete the selected cells: Ctrl + Minus (–)
Close the selected workbook window: Ctrl + F4
Move to the beginning of a worksheet: Ctrl + Home
Select the cells to the first cell on the worksheet: Ctrl + Shift + Home
Move to the last cell on a worksheet: Ctrl + End
Select the cells to the last used cell on the worksheet: Ctrl + Shift + End
These functions will be useful to format your information. They will save your time for updating each markers manually in the editor after the import.
- Combine the contents of multiple cells
You can use the ampersand (&) operator in a formula to combine text from multiple cells into one cell. This function is necessary when you need to merge two addresses fields in one cell for instance.
In the D2 cell, you enter =A2&" - "&B2&" "&C2
Using quotation marks " - " allows you to insert a space, a comma or any special caracter between the two addresses.
- Format your text
The list of your contacts is mixed up in your Excel file. Use the following functions to format them.
Uppercase the first letter of the text: =proper(text or cell)
Uppercase the content of the cell: =upper(text or cell)
Lowercase the content of the cell: =lower(text or cell)
Let’s take an example:
In the C2 cell, =proper(A2)&" "&upper(A2). It suffices to extend the formula to the next cells.
Tips & Tricks: If you need to delete some columns and avoid the error message #REF!, select first the new column, copy (Ctrl + C). Place your mouse on a new column, in the Home tab, click on the arrow under the Paste button, choose Paste the Values. You will notice the formula is gone. You can remove safely the old columns.