Basic Excel Tips and Tricks

Working with large datasets can be tricky so we've compiled a few Excel tips and tricks that have helped us over the years. If you'd like to learn about more advanced Excel functions such as formulas and Pivot Tables, UCPEA members have access to free online Excel training classes through United Training. For login instructions, please contact UCPEA.

Tip #1: Freeze the Top Row to Make Scrolling More User Friendly

It is not uncommon for reports at UConn to include hundreds or even thousands of rows and dozens of columns of data. When working with datasets this large, it can be easy to lose track of the heading of each column as you scroll. To lock the heading row in place, use Freeze Top Row. Go to the View tab and expand the menu under "Freeze Panes" in the Window group, and click "Freeze Top Row":

Freeze Top Row

Tip #2: Add Filter Buttons to Your Columns for Easy Filtering and Sorting

Most of the time, you will want to focus on certain subsets of data within the larger data set. You can search in Excel the same way you can in other Office programs (CTRL+F), but filtering and sorting particular columns is usually a better approach.

Add Filter Buttons to Each Heading

On the Home tab of the ribbon, click the "Sort & Filter" button from the Editing group and select "Filter":

Filter

Doing this will add drop-down buttons to each heading with a number of filter and sort options:

filter options main

Sort Options

Sort options will vary depending on the type of field you're sorting with (e.g. "smallest to largest" for numbers, "A to Z" for text, "oldest to newest" for dates and times, etc.) and you can sort in either ascending or descending order. If you've used colors to organize your data, you can also sort by font or cell color:

sort by color

Filter Options

There are several ways to filter your results. The first is to simply check the items from the list that you wish to display by checking the box next to each item you want to see. In this example, I've selected only "DIS," "LAB," and "LEC":

filter checkbox

All filters in Excel are progressive, meaning that as you add filters, your results for each successive filter will only be pulled from the results you have already filtered. For example, if I wanted to filter the data set to which I just added a filter to include only classes that meet on Mondays, the results I see from the second filter would also have the first filter in place, so I'd only see DIS, LAB, and LEC classes that meet on Mondays.

Other Ways to Filter

If you have a very large dataset and don't want to select items individually, you can filter in different ways depending on the type of data in the column you're working with. For example, when filtering text fields, you can filter based on the content of cells:

text filters

Other data types have different options. For example, you can filter number fields to show only values greater than, less than, or between values that you specify.

Clear Filters

You can mix and match filters as much as you want and clear filters you no longer need using the same menu on a field by field basis:

clear filters

You can also clear all filters at once by clicking the same "Sort & Filter" menu you used to add the filter buttons to each heading.

Tip #3: Use Custom Sort for Multi-Level Sorting

While the drop-down menu in Tip #2 makes it easy to sort your data set by a single field, sometimes you need to sort by several fields at once. To do this, use "Custom Sort" from the Sort & Filter menu:

custom sort

In the example below, I've set my data set to sort first by component, then by whether the class meets on Monday, and then by the minimum units:

custom sort example

Use sorting methods in combination with filtering to drill down even further into your data.

 

Tip #4: Use Conditional Formatting to Highlight Certain Values

Conditional Formatting allows you to highlight cells with color so that you can easily find those that fit your criteria. For example, there is a built in function that allows you to highlight duplicate values. Here's how it works.

Step 1: Click the Letter Above the Heading to Select an Entire Column

Here, I've moved my cursor to the "B" above my second column and clicked it when my cursor changed to a down arrow:

select column

On the Home tab, go to Conditional Formatting > Highlight Cells Rules > Duplicate Values...

conditional formatting

...and then choose the format you want to apply to the duplicate cells from either the built-in formats or your own custom format:

format options

Once you make your selection, Excel will automatically change the formatting of all duplicate cells:

duplicates