Perfia's Blog

Microsoft Excel: An excellent tool for nurses towards excellent and quality services

Introduction to Excel

Excel is a spreadsheet program, bundled by Microsoft in their Office suite for Windows and Mac. Excel was coded, but it would be incorrect to call it an invention. The computer spreadsheet was invented by Dan Bricklin in 1978–79. It was originally supported on Apple II, under the program name VisiCalc. Present Day Microsoft Excel Flash forward to present day with the release of Excel 2013, Microsoft Excel is the most familiar, flexible, and widely used application in the world due to its capability to adapt to almost any process. Coupled with the use of other Microsoft Office applications, Word, Outlook, PowerPoint, etc., there is little that cannot be handled by this very powerful combination.

Excel features:

calculation
graphing tools
pivot tables
a macro programming language called Visual Basic for Applications

Excel User Interface:

Ribbon - Excel Ribbon is the technical term for the row of Tabs above Formula bar. Excel Ribbon is a strip of buttons and icons (refer below image) in Excel Workbook window where Excel commands are located. The buttons and icons of Excel Ribbon are grouped in to different tabs based on the category of their functionalities. Clicking a tab in the tab list changes the Tab of Excel Ribbon.

Ribbon Options - When you click Ribbon Options button, three different options related with the display of Ribbon is shown. Available options in Excel 2019 are "Auto-hide Ribbon", "Show Tabs", "Show Tabs and Commands".

Quick Access Tool Bar - Quick Access Tool Bar (QAT) is located above Excel Ribbon. It is used to hold commonly used commands. Quick Access Tool Bar (QAT) is always visible, regardless of which tab is selected. By default, the Quick Access Tool Bar (QAT) contains the Save, Undo and Redo button. The Quick Access Tool Bar (QAT) is customizable so that we can add commonly used commands there.

Formula bar - Excel Formula bar is located at the top of the Excel Worksheet window. Excel Formula bar has three parts. The left most is the "Cell Name Box", middle one "Formula Bar Buttons" and right one is the contents of the currently selected cell. The data or formula entered in the Active cell will appear in right-most section.

Excel Formula bar components:

Column Letters - Each column in Excel Worksheet is identified using letters from A to XFD. From A to XFD, there are total of 16384 columns available. Below image shows the last Column letter (XFD) of Excel 2019.

Row Numbers - Each Row in Excel Worksheet is identified using numbers from 1 to 1048576. From 1 to 1048576, there are total of 1048576 rows available. The image below shows the last Row number (1048576) of Excel 2019.

Active Cell - The thick border outline around the cell indicates that it is the Active cell inside Excel Worksheet. The Active cell inside Excel Worksheet is used to identify the cell which is currently active. The Active cell is where the focus is on and where the data will be entered. Below image shows the Actice Cell as C5, which is hi-lighted with a thick border.

File Button - File Button can be used to open a menu (called as Excel Backstage View) which contains many options to work with the Excel file. Important options are "New", "Open", "Save", "Save As" and "Print". When you click the File button, a menu (Excel Backstage View) similar to the image below is opened.

Cell Name Box - Cell Name box is where the Active Cell Name is displayed. Refer the image below to understand what is Cell Name.

Tab List : Tab List is where different Ribbons are displayed as Tabs in Excel Ribbon. Different Tabs contain commads with similar functionalities which are grouped together.

Window Controls - Window controls are used to control the main Excel window. Similar to any normal Windows application, three buttons are used for minimizing the window, maximizing / restoring the window, and closing the window.

Zoom Bar - Zoom Bar is used to zoom in or zoom out of Excel worksheet. Percentage of zoom is also shown.

Horizontal scrollbar and Vertical scrollbar - Similar to any normal Windows application, Horizontal scrollbar and Vertical scrollbar can be used to scroll the contents horizontally or vertically.

Application of Excel:

to manage name list of data records
to perform mathematical calculation easily in daily business
inventory management
create forms and consolidate results
analytical tools
corporate budgeting

Excel in the Medical and Nursing Field

Excel is a powerful tool for keeping track of patient appointments, scheduling doctors, and organizing other information, such as contact numbers or insurance data.
Many clinics, hospitals and practitioners use Excel for these functions. Anyone seeking a career in medical office administration must take Excel as part of their curriculum.
Excel can also be used to keep track of medications, prescriptions and other health information. It can serve as a remarkably useful and flexible program for healthcare professionals.

Three Essential Excel Skills for Healthcare IT

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.
Here are the three Excel skills for Healthcare IT that represent some of the top tasks that you will likely need to do:

A. 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:

B. 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:

C. 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: