The purpose of this article is to teach you five tricks that can make you more productive using MS Excel.

  1. Generate a unique list of entries in a column. When you have a large spreadsheet that contains duplicate entries, generating a unique list is easy:

    1. Higlight the column containing the data to be filtered.

    2. Choose the Data tab, then choose Advanced Filter.

      Excel with Data tab and Advanced Filter circled.

    3. Select the Copy To Another Location option, choose the location (e.g. C:C), and select the Unique Records Only check box.

      Advanced Filter Menu with copy to another location, copy to and unique records only checkbox circled.

    4. Click OK, and Excel will copy the unique entries from the source column into the specified new location.

      Spreadsheet showing records copied into column(s).

  2. Apply conditional formatting. Conditional formatting provides a way to see patterns in large data sets. In this example, condirional formatting will be used for values over $1,000.

    1. Select the cells to which formatting will be applied.

    2. On the Home tab, choose the Conditional Formatting command. Select Highlight Cells Rules, then choose Greater Than.

      Conditional Formatting command with Highlight Cells Rules and Greater Than selected.

    3. Enter the criteria and formatting to be applied. Click OK.

      Format Cells Window to apply criteria for greater than.

    4. In this example values greater than $1,000 appear in cells with light red fill.

      Sample spreadsheet with values populated for greater than 1000.

  3. Jump to the beginning or end of a massive spreadsheet. Scrolling is fine in sheets with small data sets, but for sheets that are hundreds or thousands of rows, there is a simple keyboard shortcut. Simply type CTRL + ↑ to jump to the top cell, or CTRL + ↓ to drop to the last cell before an empty cell.

  4. Let Excel quickly calculate subtotals of your data.

    1. It's a good practice to make a copy of your original worksheet first because a mistake can make a mess of your data that may be hard to fix!

    2. Using your working copy, sort your data on the column by which you want to subtotal. This is an important step, because the Subtotals feature will subtotal records in the order they appear. (Sort is located on the Data tab.) In the image below, the data has already been sorted.

      Spreadsheet showing sorted data.

    3. Make sure that your columns have header rows. Click anywhere in your source data and go to Data | Subtotals. The Subtotal dialog box will appear and Excel will take its best guess as to the column on which you want to subtotal and the function you want to use for those subtotals. In this example, a subtotal is added at each change in college.

      Subtotal window with Amount selected for Add subtotal to option.

    4. Click OK to generate the subtotals.

      Spreadsheet with subtotals generated

  5. See headers as you scroll with the Freeze Panes function.

    1. To activate the freeze function, choose the View tab, then click Freeze Top Row as shown below. (You can freeze other panes by choosing the row below the section to be frozen, then choose Freeze Panes.)

      View tab with Freeze Top Row selected for Freeze Panes function.

EdTech CC-By-NC-SA License