Excel Hints & Tips

Microsoft Excel Hints & Tips

Here are a few popular Excel formulas and processes I have used with Vision data.

Please note that all names and demographic details shown on this page and throughout this website are fictitious, any resemblance to real persons, living or deceased are purely coincidental.

Export as Tab Separated or DBF?

Vision searches export data in multiple files based on the entities you add to your search.
There will always be a PATIENT file, and then additional data files when using either 
tab separated or DBF exports.

You can join up this data depending on which method you have chosen to use,
by following the instructions below

DBF or Tab Separated Exporting

DBF Exports...

  • Using DBF creates very large files, which takes a while to compile after the search has run.
  • Also note that it doesn't include free text
  • When using Microsoft Query in Excel it uses a fixed type of join that will only show data that exists in each table for a patient.
    For example: if you export smoking and alcohol data. Only patients that have both a smoking         and alcohol status will appear after you have created the join process.
  • However, DBF files are easy to join up in Excel.


If your exported files are going to have small amounts of data, and you can guarantee all items will be present, then DBF might be the way to go.


Importing Vision DBF data into Microsoft Excel requires two elements to of been performed per workstation.

If you are on the Vision remote server (VES/WES) the following is automatically installed and you may skip these steps:

  1. A Microsoft Visual FoxPro ODBC Driver needs to be installed on the workstation.
    Download and run this file: English         VFPODBC.MSI
  2. Microsoft Query needs to of been installed, which is part of the Microsoft Office installation. If it hasn't been installed, you will be prompted and may need the original CD to add this feature.

Tab Separated Exports...

  • This by far the quickest way to export data as it creates small txt files, so you can export very large amounts of data. It's around 10 times quicker in exporting the data out of Vision and exports everything including free text.


  • If you are only interested in the data and don't want to include patient demographic data, this is perfect as you simply open the relevant txt file in Excel, and you have all your data based on the unique patient identifier (PAT_ID)


  • If you want to join data, you will have to use the VLOOKUP formula explained below. The beauty of VLOOKUP is that it won't remove any patients that do not have any data, it will simply show #N/A


  • You can use Microsoft Excel without having to install anything else to merge the data


I personally use Tab Separated in the majority of exports from Vision.



Click the button below for step-by-step instructions on how to join the patient data from the tab separated txt files in Microsoft Excel


Share by: