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?
- Use VLOOKUP to show data from one spreadsheet in another
- How to add Y or N to the VLOOKUP result
- How to import DBF data into Excel and join it together
- Join separate columns of data and show in one cell
- Separate data held in one cell
- Get Age from Date of Birth
- Remove SPACES from cell
- How to extract specific text from a cell full of text
- Remove zero values from cells
- Convert Numbers into Time
- Convert Text Number into Actual Number
- Date Formula's
- Keep 1st Word in Cell
Vision exports data in multiple files, which depends on each entity you add to your search. There will always be a PATIENT file and then additional data files depending on your search.
You can join these data files using either tab separated or DBF...
Exporting as DBF creates very large files which takes a while to compile after the search has run, doesn't include free text and must include all data items to show in Excel. For example if your exported data includes BP's and Smoking and addresses; if there are some patients that only have a smoking and no BP, or No BP and only a smoking record etc... they will not show in the Excel spreadsheet when you merge the data.
However DBF files are easy to join up in Excel.
So if your exported files are going to be small amounts of data and you can guarantee all items will be present, then DBF is 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:
Microsoft Visual FoxPro ODBC Driver needs to of been installed per workstation
it is run on.
Download and run: English VFPODBC.MSI
- 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 Export
This by far the quickest way to export data as it creates small txt files, so you can export very large amounts of data, its 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 disclose patient demographic data, this is perfect as you simply open the txt file in Excel.
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 data, it will simply show #N/A
I personally use Tab Separated in the majority of exports from Vision.
This is probably the most popular formula I use to link up tables of data.
99% of the exported Vision data will always show the PAT_ID in the first column. This is the link that joins all the data together.
The formula would look something like this:
- A2 would be the patient ID to use as the lookup in the other table of data. Therefore the PAT_ID must exist in both spreadsheets and it is the starting point of the search.
- patient! is the other spreadsheet name that holds the data you want
- A:S is the table of data on this spreadsheet. The table must start from PAT_ID (i.e. A) and include the column of data you want to show - in this case anything between column A and column S I will be able to show.
- 19 is the 19th column starting from column A that holds the data I want displayed
- FALSE has to find the exact PAT_ID to be a match. Whereas TRUE gives an approximate match, so if the value you're looking up is not found in the list it uses the nearest it can find without going over.
Notice that the majority of the formula will remain the same because the forename and surname columns are within A:S, which means I only need to change the column number in the formula.
Or if you just want a TRUE or FALSE reference you could use:
- After exporting the DBF files from your Vision search
- Open Microsoft Excel 2010
- Data tab > From Other Sources >
From Microsoft Query
- Select Visual FoxPro Database > click OK
Make sure "Use the Query Wizard..." is selected
- Click the Browse button and locate the Vision DBF files
folder, (i.e. The folder you chose when you ran your Vision search) then
- You should now see the DBF databases in this folder.
- Expand the tables to see the columns of data you can choose to show in Excel
- Double-click on the column names from any of the tables, so that they
show in the right window area or use the arrows located on the middle bar
to add/remove these column names
Note: The Options... button allows you to sort the column names
- Click Next when you have all you need
- Click OK when the Query Wizard shows the following
This message is basically saying it doesn't know what column of data to use, to join the tables together.
- Locate pat_id in both tables as shown below
- Then drag one pat_id onto the other to create a join
line between them
- Now click the File menu and Return the data to Microsoft Excel
You can either use the Concatenate formula or join cells using the & symbol.
If you want to include some text elements between your joins use " "
The formula would look like this:
=CONCATENATE(D2&" "&E2&" "&F2&" "&G2&" "&H2&" "&I2&" "&J2)
=D2&" "&E2&" "&F2&" "&G2&" "&H2&" "&I2&" "&J2
If you have a string of text in one cell that you need to separate, for example and address or the whole patient name, you can separate each block of text into separate columns.
You just need to insert the correct amount of columns before you start. For example if you want to separate Title, Forename and Surname you will need 2 additional columns inserted to the right side of you initial column of text, while an address may need 5 additional columns and so on...
Use "Text to Columns" to divide up the data.
- Highlight the column you want to divide up
- Select Text to Columns located on the Data tab
- In the example below commas are used in the column of text to divide
it up, so we will use the delimited option
- Click Next
- Make sure you select Comma as the delimited text character
- Click Finish as the next screen doesn't provide anything that is needed.
- Press OK to move the text from the original column into the new ones
To work out the age when data was recorded, in the same formula above, just replace TODAY() with the event date cell reference.
Using the formula above means creating a new column, whereas using REPLACE would remove spaces in the actual cells.
Highlight the column (or cell) > Edit menu > Replace > Find What: (just type a space) and leave Replace with: empty > Click Replace All > OK
Highlight the column (or cell) > Home tab > Find & Select > Replace > Find What: (just type a space) and leave Replace with: empty > Click Replace All > OK
If you just want to remove all the text above and just leave the contents within the brackets (inverse delete) you could use the REPLACE feature as follows:
- Highlight column A (or cell) > Edit menu > Replace > Find What:
- Replace with leave blank > Click Replace All > OK
- Find What: ]*
- Replace with leave blank > Click Replace All > OK
This will now look like this:
There are a few methods to achieve this, but the simplest way to hide all zero values in a sheet is to switch them off for the whole worksheet. Just remember, you must set it for each sheet in the workbook, as follows:
- Click the File menu and then choose Options (under Help).
In Excel 2007, click the Office button and then click Excel Options.
In Excel 2003, choose Options from the Tools menu.
- Choose Advanced in the left pane. In Excel 2003, click the View tab.
- In the Display Options For This Worksheet section, uncheck the Show A Zero In Cells That Have Zero Value.
- Click OK.
The sheet setting will hide all zero values in the sheet. If you want to hide specific zero values, but not all, you can use a numeric format instead:
- Select the cells that contain the zero values that you want to hide.
- Click the Home tab and click the Number group's dialog launcher (the
small arrow in the bottom-right corner.
In Excel 2003, choose Cells from the Format menu.
- Click the Number tab (if necessary).
- Choose Custom from the Category list.
- Enter 0;-0;;@ in the Type field.
- Click OK
If you edit in the cell hidden 0s will still be visible in the Formula bar or in the cell. To undo this format, simply choose an alternate numeric format for the cells.
This format is easy to forget and may cause problems later on, so choose this method as a last resort.
The numeric format shown above will hide literal zeroes and most returned by a formula. When you run into an exception, you can use a conditional format as follows:
- Select the cells that contain the 0s you want to hide.
- Click the Home tab and then click the Conditional Formatting option
in the Styles group. Choose New Rule.
In Excel 2003, choose Conditional Formatting from the Format menu, and skip to step 4.
- In the top pane, select the Format Only Cells That Contain option.
- From the second dropdown, choose Equal To.
- Enter 0 in the third control.
- Click Format.
- From the Color dropdown, choose White (or the color that matches the sheet's background).
- Click OK twice.
You will also need to Format Cells using either Time or a custom Category like: hh:mm
When importing data from an external database into Excel you may find that some numbers are actually defined as text and therefore your formulas won't work.
To resolve this;
- Copy a 1 (actual number) from a cell
- Highlight your column of numbers
- Use Paste Special, then select Multiply.
- This will multiply all the values in the cells by 1
Thus converting them to actual numbers Excel can recognize.
Below are a set of formula's that gather information from the today date value in cell B2.
These are useful when using Pivot tables to count numbers in a given month or year, etc...
With regards to the WEEKDAY formula (serial_number, return_type)
Serial_num is any valid date
Return_type is a number (between 1 and 3) that determines the type of return value.
1, or omitted = 1 (Sunday) through 7 (Saturday).
2 = 1 (Monday) through 7 (Sunday).
3 = 0 (Monday) through 6 (Sunday).
With regards to the WEEKNUM formula (serial_num, return_type)
Serial_num is any valid date
The default for Return_type is 1, or it can be omitted and the week begins on a Sunday.
If Return_type is 2, the week begins on a Monday.
This formula will keep the left most word before the first space in a cell:
- =LEFT(A2,FIND(" ",A2)-1)
To keep just the first few characters from the left use =LEFT(A2, 3)