Importing and Formatting Raw Data in Excel

Data can be put into Excel using a number of methods.

  • Manually entering the data
  • Copy and paste
  • Opening a file from within Excel
  • Importing using External data tools (Data tab)
  • PowerQuery

The method you choose will depend on the type of data source you are using, and what you wish to do with it.  If you wish to update the data from the source on a regular basis, the best option is to import using the External data tools as this allows you to specify the data types of all your columns, and it can help you create a connection to the source that will update when you need it to. There are are large variety of file types that Excel can import including SQL Server, text/csv files, Amazon Azure and websites.

Data tab

If you are doing a quick, one-off bit of analysis, you can use any of the above methods, but if you know that you will want to re-import the data, setting up a connection to that data source will save a lot of time later.

Once your data is in Excel you may need to perform some formatting before you can use it.  Particular attention needs to be paid to data types.  For example:

  • Importing student ID numbers without changing the field to text can remove the leading 0, as Excel tends to convert it to a number
  • If you have a column with numbers in it, but you want Excel to treat it like a text field, you can change this manually, but Excel will raise it as a possible error:

Data type error

Check all your columns to make sure they are as you expect.  You can adjust the type by using the drop down menu (Home menu -> Number section) – this is particularly useful for changing numbers into currencies or percentages.

Once you have imported the data, it is good practise to ensure that it is also in a table format.

Leave a Reply

Your email address will not be published. Required fields are marked *