It is fairly common to need to use Microsoft Excel as a pharmacy student, pharmacy resident, and pharmacist. In this article an experienced pharmacist provides practical tips to help make using Microsoft Excel easier.
Authored By: Timothy Gauthier, Pharm.D., BCPS-AQ ID
[Last updated 10 December 2017]
Some pharmacists hate using Microsoft Excel, but not me. I love spending hours running reports, manipulating data sheets, and “torturing” data until it tells me what I need to know.
In nearly 10 years as a pharmacist most of my experience with Microsoft Excel comes from working on practice-based research projects and medication utilization evaluation (MUE) aka drug utilization evaluation (DUE) projects. In these endeavors I have collaborated with a large number of pharmacy students and pharmacists, many of whom taught me a thing or two about how to use Microsoft Excel better.
I do not consider myself an expert at Microsoft Excel, but I do feel there are some super helpful Microsoft Excel functions many pharmacy students and pharmacists are unaware of. With this in mind the following is provided. Hopefully you will pick up a few pearls to use in your next project in this list of tips for using Microsoft Excel as a pharmacy student, pharmacy resident, and pharmacist.
Know and use common quick keys
This is by far the most helpful thing for when using Microsoft Excel or almost any other computer program. If you are someone that highlights a row and then uses the right-click button on your mouse to copy and paste, it is time to stop wasting precious time with that nonsense and start using quick keys. Mouse-less commands = less time using the mouse = less time wasted.
Here are some common quick keys to know about. Notice how the letters tend to go with the action, like to bold something it’s ctrl+b. They are different if you are on a Mac (usually replace ctrl with command, and another note for Mac users the quick key for screen shot is command+shift+4, which is super useful).
Ctrl+tab = navigate your open programs
Ctrl+a = select all
Ctrl+c = copy
Ctrl+x = cut
Ctrl+v = paste
Ctrl+z = undo
Ctrl+n = open new blank file
Ctrl+b = bold
Ctrl+u = underline
Ctrl+i = italicize
Ctrl+f = find
Ctrl+n = open a new document
Hold shift + [direction] = will highlight cells as you keep hitting the [direction] button
Hold shift + hold ctrl + [direction] = select all cells in [direction]
Hold shift + click on two distant cells in the same row or column = select all cells in that line
Hold ctrl and click on multiple cells = highlight random cells
Put that status bar to work
On the bottom of the Excel window you will find the status bar. By right-clicking on it you can select which information will show there (e.g., sum, count, min, max). If you select the options you are likely to use and then highlight a row you want that information for, the status bar does the work for you and avoids you having to use an equation to sum, count, or get other basic information from a group of cells.
Standardize how data is recorded (i.e., 0=no, 1=yes)
To make it easier to analyze your data set try to standardize how data is recorded. For example, do not use na, n/a, and not applicable all in the same Excel file. When you go to sort it, it will not work out. Try to be consistent in how data is recorded.
I suggest standardizing it to 0 for no and 1 for yes when possible. That way you can just highlight a whole column during data analysis, then look at the count and sum results on your status bar to see positive and negative results. This is much faster than the steps you would need to take had your data not been standardized.
Standardization becomes extremely important when more than one person is doing data collection.
Be smart about how, where, and when you save your files
Naming files: I recommend saving your file with a specific name and in reverse date order so when you sort it will sort in the order you want. For example: “Penicillin_Project_draft 2017_12_01.” This way when you share your file with someone or go back to your files to look for your data, you can identify the project and see which file is the most recent. If you are looking at your files in a folder and have it in “details” view, it will usually display a “modified date” column, which is another way to verify which is the most recent version of the project data. Beware however the “modified date” column may be inaccurate if you opened and closed a less recent version.
Saving your work: Using the above example, when you open the file on 12/01/2017 select “save as”, then save it to the same folder but under the name “Penicillin_Project_draft_2017_12_02.” Keep doing this as you progress through your project, saving a new copy each time you go back to work on it. At the end of your project you will have numerous files, but if you totally mess up your data by accident you will be able to go to an earlier version rather than starting from the beginning.
It is suggested to use underscore rather than periods or spaces within file names do to compatibility issues that may arise. Here is an article on best practices for naming files if you want to learn more about this topic.
Be sure to save your work frequently. Hit that save quick button up on the top of your screen at least every 30 minutes. You never know when disaster could strike and your computer could fail.
Storing your files: Do your best to keep everything in one place using folders with intentional names. Saving things in multiple places or with nondescript names can create confusion later.
Consider using a cloud storage option like Dropbox or iCould. If you save something in the cloud and your computer randomly dies or has a major issue, your work will not be lost. Beware however that patient data may not be able to be stored on some cloud storage services due to encryption issues, so refer to institutional policies before saving patient data to a cloud storage service.
If you don’t need it, don’t collect it
This one is not really about making Excel easier to use, it is about being smarter in how you design data collection sheets. Remember that the less data you collect, the faster you can get the job done. Think about whether or not the data point will be meaningful at the end of your project. If the answer is that the data will not be useful, then do not collect it. Ask yourself this repeatedly when developing your data collection sheet.
Related to this, if your project requires Institutional Review Board approval, only collect the data points you have permission to collect.
Be smart about creating and labeling new tabs
Do not overlook the value of using multiple tabs within one file. I like to have the first tab as “original data”, the second tab for “data collection”, then the third tab for “data analysis.” Use multiple tabs to help keep things organized.
When you create a new tab in your Excel file give it a fitting name so that if you come back to it a year later it will still make sense to you or anyone else who needs to use the data sheet.
Use the insert comments function
If you right-click on a cell there is an option for “insert comment.” After creating a comment the cell will display a small red triangle in the upper right corner so you can identify which cells have comments. Scroll over the cell to see the comment (as shown above).
This function is really useful for (1) the headers of columns to help with standardizing data input and (2) for making a note to come back to later when you are not sure about how to input data into a particular cell. I generally try to avoid highlighting cells with all different colors (it just looks messy). The insert comments helps to keep coloring cells to a minimum.
Beware merging cells and hiding data
Merging data can make sorting problematic. Hiding data can cause you to overlook data later on that you had previously hidden. In general, I recommend avoiding use of the merge cell or hide data functions. I have seen them cause problems too many times and it is not worth it. If you decide to use the merge or hide cells functions, do so with caution.
Collect specific dates and times when possible
Rather than collecting someone’s age, collect their date of birth. You can then use formulas to calculate their age later, which will give you confidence that the age you use is in your analysis accurate.
In addition, using dates allows you to use formulas to see the time between events, such as when a drug was started and stopped. Excel allows you to save both date and time in one cell if you want to get that detailed for assessing things down to the minute later (e.g., 12/1/2017 @ 12:30:00).
Freeze the top row to make scrolling through your data easier
Keep the top row with your headings on the page while scrolling down by using the “freeze top row” feature. You can also use this feature to freeze sections of your data sheet as you scroll, but the freeze top row option is generally the most helpful row or column-freezing function.
Use the sorting and filtering features
Use the “insert filters” button to make it easy to sort your data. You can sort from A to Z, Z to A, smallest to largest, largest to smallest, by color, and more.
You can also use the sort button (shown above), which is good if you want to sort by one column then by another column too after that.
Beware that when sorting if only part of your Excel sheet is highlighted, it may only sort that part of the data. This could be disastrous, so sort with care.
Get familiar with some basic formulas
Getting to know some of the basic formulas can be helpful even if you are using the status bar.
For example, if you want to know the sum of the data in cell 1 through 5 for each row in column A through F, put the equation “=SUM(A1:A5)” in cell A6. The A6 cell will now show the sum of cells A1 through A5. Next, copy (ctrl+c) and paste (ctrl+v) cell A6 into cell B6. Now cell B6 will show the sum of cells B1 through B5. You can keep copying and pasting down the sixth row down to cell F6. When this is done you will have the totals you wanted displayed. This method can be more efficient than using the status bar if you are working with large data sets and want to sum cells.
You can replace SUM with MIN, MAX, COUNT, and many other options to take advantage of the formulas function.
Use the little square in the bottom of the highlighted cell(s)
When you select a single cell a small square appears in the bottom right of that square. If you select a group of cells the same small square appears in the bottom right of the lower-right cell. When you put your cursor over the square your cursor will turn into a “+” sign. By clicking on this square you can then drag data into subsequent boxes.
For example if you want to have numbers 1-50 in row A, put “1” in A1, then “2” in A2. Highlight cells A1 and A2, then click on the little square and drag it downwards. It will automatically populate cell A3 with “3”, A4, with “4” and so on. This also works with patterns. If you want to repeat a pattern over and over again, put the pattern in the cells you want then use this feature to drag the pattern into subsequent cells.
You can also just double-click on the little square rather than dragging it down and this can work as well. It usually will only add data in the rows which you already have data.
Format cells accordingly
This one is pretty simple, but try to format your cells so that they are appropriate. For example, set dates to show with the month, year, and day if you need it to that level. Just use month and year if that is all you need. Format cells by right-clicking on them and selecting “format cell.”
I like to center justify most columns, because I find it easier to visualize when viewing a large data set. Enabling or disabling the “wrap text” function can also assist with visulazing data.
In general, try to avoid using more decimals than necessary too. Always be on the look out to limit the amount of clutter in your data.
Try answering your questions using Google rather than searching through menus
Use search engines like Google when you run into an issue you cannot resolve with creating a graph, enabling a feature, disabling a feature, or something else that is new.
It is kind of embarrassing, but on more than one occasion I have spent a solid 15 minutes trying to figure things out on my own only to eventually go to Google and find an answer in about 30 seconds. Do not waste time searching through menus and playing with features. If you cannot find the answer to your question on your own in a minute or two, go right to Google and save yourself the trouble.
Consider learning about pivot tables
This is a great feature for data analysis, but pivot tables are not that easy to learn how to create and there is a learning curve. If you need to do some serious work with Microsoft Excel though, put learning pivot tables on your to-do list. If you need to do serious work with statistical analysis, consider leaving Excel behind and start using SPSS. In addition, the word on Twitter is that R is also a great resource for statistical computing.
Closing thoughts
There are many other little things that can make a big difference when it comes to using Microsoft Excel as a pharmacy student, pharmacy resident, and pharmacist so keep an ear and an eye out for additional tricks.
Hopefully these will help make your life easier and your project more manageable!
Bonus points from social media comments…
- Use column A to number the rows in your data set. This way if you are sorting your data and make a mistake, you can sort by column A to get it back to how it was previously.
- Make all attempts to limit text. Less text means your data will be easier to visualize. Try to use abbreviations instead of words and lowercase rather than uppercase lettering.
- Pate the visible output from one cell into another, not shifting fomulas/calculations that creates the cell output –> Paste special – select values and number formats, OK.
RECOMMENDED TO YOU 
