Ask the NIH Help Desk

Working with Microsoft Excel

Q: I’m working with a long list in Excel but there are duplicate entries. How do I get rid of the duplicate entries without searching for each one?

A:The Filter feature will work perfectly for you. Just follow the steps below:

1. Select the column or click a cell in the range or list you want to filter.
2. On the Data menu, point to Filter-Advanced Filter.
3. Do one of the following:
1. To filter the range or list inside your current worksheet, click Filter the list, in-place.
2. To copy the results to another location, click Copy to another location. Then, in the Copy To box, enter a cell reference.
1. To select a cell, click Collapse Dialog to temporarily hide the dialog box. Select the cell on the worksheet, and then press Expand Dialog.
2. Select the Unique records only check box.

Q: I have to format my worksheets so all my columns look the same. Is there an easy way to do this?

A: Yes! The Macro feature is what you need. If you perform a task repeatedly in Microsoft Excel, you can automate the task with a macro. When you record a macro, Excel stores information about each step you take as you perform a series of commands. You then run the macro to repeat, or "play back," the commands. There are two ways to record a macro: manually or by using Microsoft Visual Basic. Recording it manually is the simplest way:

1. On the Tools menu, point to Macro, and then click Record New Macro.
2. In the Macro name box, enter a name for the macro.
3. If you want to run the macro by pressing a keyboard shortcut key, enter a letter in the Shortcut key box. You can use CTRL+ letter (for lowercase letters) or CTRL+SHIFT+ letter (for uppercase letters), where letter is any letter key on the keyboard. The shortcut key letter you use cannot be a number or special character such as @ or #.
   Note: The shortcut key will override any equivalent default Microsoft Excel shortcut keys while the workbook that contains the macro is open.
4. In the Store macro in box, click the location where you want to store the macro.
   If you want a macro to be available whenever you use Excel, select Personal Macro Workbook.
5. If you want to include a description of the macro, type it in the Description box.
6. Click OK.
7. If you want the macro to run relative to the position of the active cell, record it using relative cell references. On the Stop Recording toolbar, click Relative Reference so that it is selected. Excel will continue to record macros with relative references until you quit Excel or until you click Relative Reference again, so that it is not selected.
8. Carry out the actions you want to record.
9. On the Stop Recording toolbar, click Stop Recording.

Q: Can I use Excel features in any other programs?

A: You can create an Excel table in Microsoft Word, PowerPoint, or Outlook and still use the same features you would use if you were actually working in Excel. Just go to Insert-Object and select Microsoft Excel Worksheet.

Q: I want to know the totals of the data in my worksheet. How do I show that in Excel?

A: That’s a great question and very simple to achieve!

1. Make sure the data you want to subtotal is in the following format: each column has a label in the first row and contains similar facts, and there are no blank rows or columns within the range.
2. Click a cell in the column to subtotal.
3. On the Data menu, click Sort Ascending or Sort Descending.
4. On the Data menu, click Subtotals.
5. In the At each change in box, click the column to subtotal.
6. In the Use function box, click the summary function you want to use to calculate the subtotals.
7. In the Add subtotal to box, select the check box for each column that contains values you want to subtotal.
8. If you want an automatic page break after each subtotal, select the Page break between groups check box.
9. If you want the subtotals to appear above the subtotaled rows instead of below, clear the Summary below data check box.
10. Click OK.

Have more questions?

If you need any assistance, or have additional Excel questions, just give us a call at the NIH Help Desk at 301-496-4357. We have agents who are Microsoft Certified Specialists in Excel that are available to you!

NIH HELP DESK
(301) 496-4357
(866) 319-4357 (Toll Free)
(301) 496-8294 (TTY)