If you are trying to get into Healthcare IT, or any other tech job for that matter; it’s important to develop core technology skills that will carry you through the challenges ahead. An essential core skill is a decent understanding of Excel, which you will use to manage spreadsheets of data items. The data could be lists of patients, medications, departments, users, or just about anything else you can imagine. We’ll look at three Excel skills for Healthcare IT that represent some of the top tasks that you will likely need to do.
There are countless resources available to learn Excel, with many of them getting into voluminous details. With that, I’m not going to try to provide comprehensive training here, but will focus my examples on Healthcare data. So here we go…
Find Duplicate Values In Excel
Let’s say you have two lists of patient IDs, and need to know the differences between the lists:
 |
Here is one of the fastest ways to compare values between two columns.
1. Highlight the data on both columns, excluding the header, if you have one:
 |
2. With the Home tab selected, click on Conditional Formatting, then Highlight Cell Rules, then Duplicate Values:
 |
3. Next, you are presented with a box that shows you a color scheme to call out duplicates. You can change the color, but for here, we’ll just leave it. Click OK:
 |
4. Now you are presented with the results. Let’s break down what we have. The items highlighted in red represent numbers that are indeed found in both columns. The items in white indicate the numbers that are only found in their respective columns. For example, 1337961 is found in column A, but not column B. 1432297 is found in column B but not column A:
 |
Here is how you can group the duplicate or unique values together.
1. Highlight one of the columns below the header:
 |
2. Go to the Data tab, then select Sort
 |
3. Select the column to sort by and make sure to check the box to indicate you have headers. Then select the Sort On option of Cell Color:
 |
4. Next, select the Order to be the color listed. Then click OK:
 |
5. The column is now sorted:
 |
Excel Data Formatting
When analyzing healthcare related data, you will come across times when you need to reformat or otherwise change the way data is displayed. One common example relates to medical record numbers like the ones in the previous example. Some organizations have a policy to set all MRNs at 10 digits, meaning that we’d need to pad the above values with leading zeros. Here is how we do that.
1. Highlight the column or values you need to work with, then right-click, then select Format Cells:
 |
2. The Format Cells box will show:
 |
3. Next, select Custom and type in exactly 10 zeros. Notice that now your numbers have been padded to fill in any variable lengths to 10 digits:
 |
4. Click OK to see that your MRNs are now padded to 10 digits:
 |
Open A Text File in Excel
In Healthcare IT, it’s common to have some kind of data in the form of a text file that needs to be imported into an Excel spreadsheet. Let’s look at this list of patient info in Notepad:
 |
We will need to open it in Excel and place each data item into its own column. This is also called a CSV (comma separated variable) file.
1. In Excel, select File, Open.
 |
2. At the Open File window, select file type of text files in the lower left:
 |
3. Next, open the text file you need to work with. Then you will be presented with the first of several screens that will allow you to separate the items in each row. Sometimes data items are separated by a fixed width; but in this case, the data is separated by a Tab character. Click Next to continue.
 |
4. You are now presented with the next screen where Excel already detected that a Tab separates the items. The vertical black lines between each data item indicate where the columns will be designated in the spreadsheet:
 |
5. Now, click on the Space delimiter to add it to the parsing. Notice that the street name is now divided up further, which will translate to additional columns:
 |
Depending on the situation, you may need to experiment with different parsing characters. If you mess up, you can always start over. For now, I’ll uncheck the Space character and proceed with the next step.
6. Now we have the option to format the text or even skip columns that we don’t want to import:
 |
7. For this example, we decide to not import the Address column:
 |
8. Finally, the data is opened in a spreadsheet that can take advantage of all the features of Excel:
 |
As I mentioned at the beginning of this post, the things you can learn and do with Excel are practically endless. If this has sparked your interest in learning more, here are some topics with resources that you can look at:
