Reports
      Back to home
      1. Knowledge Base and Tutorials
      2. Reports

      How to open or format a CSV in Excel

      Some data may be missing or formatted incorrectly when opening a CSV file in Excel e.g. leading zeros in telephone numbers. Below is a guide on how to display the correct information

      Excel will display data when double clicking or right-clicking a file and choosing open but can remove some data from the file as it doesn't know the format of the cells. 

      More options on how Excel displays this data are available when using the built in Text Import Wizard which automatically launches when following the below steps.

      Open a CSV in Excel

      1. Go to File > Open and browse to the location that contains the CSV file 

      2. Select All Files in the file type dropdown list in the Open dialog box

      3. Locate and double-click the text file that you want to open

        • If the file is a CSV file, Excel starts the Import Text Wizard

      4. The Text Import Wizard examines the text file that you are importing and helps you ensure that the data is imported in the way that you want
        1. Step 1 of 3 is to check Delimited as the file is separated by commas
        2.  Step 2 of 3 is to check Comma as this is what identifies separate columns, you will see a preview of the data in the window
        3. Step 3 of 3 is to specify which format to apply to each column. eg. choose the column related to a phone number and change the format from General to Text, this will tell Excel to display the cells and retain the leading zero

      How  to separate Date and Time

      1. Select the cell or column that contains the text you want to split
      2. Select Data > Text to Columns
      3. In the Convert Text to Columns Wizard, select Delimited > Next
      4. Select the Delimiters for your data. For example Space if you are seperating the time from the date in a Groop CSV. You can see a preview of your data in the Data preview window
      5. Select Next
      6. Select the Column data format or use what Excel chose for you
      7. Select the Destination, which is where you want the split data to appear on your worksheet
      8. Select Finish

      How to format cells as Date

      Follow these steps:

      1. Select the cells you want to format
      2. Press CTRL+1
      3. In the Format Cells box, click the Number tab
      4. In the Category list, click Date
        Pick Date in the Category list
      5. Under Type, pick a date format. Your format will preview in the Sample box with the first date in your data

      Note: Date formats that begin with an asterisk (*) will change if you change the regional date and time settings in Control Panel. Formats without an asterisk won’t change.

      If you want to use a date format according to how another language displays dates, choose the language in Locale (location).