12 Excel Tricks You Need to Know

If you are a frequent Excel user, here are 12 useful tricks to help you manipulate your data to get the information you want.

Katie Horne
Last Updated on November 16, 2018
Disclosure: Your support helps keep the site running! We earn a referral fee for some of the services we recommend on this page. Learn more

Despite the wide availability of spreadsheet tools, data analysis suites, and other powerful programs that do a lot of what Microsoft Excel does (and probably better), we still return quite frequently to Excel itself. One of the reasons we do is the sheer number of built-in tools and functions that make our work much easier. There’s certainly a time and place for specialized software, but humble Excel is still great for budgeting, general goal setting, high-level data analysis, and more. Here are some of our favorite Excel tricks we think you’ll find useful.

Remove Duplicates

Excel makes it easy to clean and organize your data. The Remove Duplicates feature is easy to use and, as its name suggests, removes duplicate values from your data set. To use Remove Duplicates, simply select the data you want to be cleaned and go to Data > Remove Duplicates.

Excel Wild Card Searches

You’re probably familiar with launching the search function using CTRL + F or ⌘ + F. You can extend the search functionality with wildcards. This is useful when you are searching for inexact matches that meet specific criteria.

There are two wild cards you can use:

  • ? stands for one character
  • * stands for one or more characters

If the result you’re looking for includes either ? or *, use the tilde operator ~ in front (e.g. ~? or ~*).

Using Index and Match to Search Your Spreadsheet

The Index function returns a value based on the intersection of a row and column.

The following example shows us the index function which includes:

  • The array we want to be searched. [A1:B2 represents the section of the table we want to query, namely the area between A1 cell (upper left) and B2 cell (lower right). This area covers the first 2 rows and first 2 columns in the table.]
  • The values we want returned. In this example, we are asking for the values in the first column, first row (1,1) within the array.

excel tricksThe Match function searches for a value in an array and returns its relative position. For example, let’s say we want the relative position of “blue” in the array consisting of cells A1 and A2:

excel tricks

By combining the Index and Match functions, you can search your entire spreadsheet for values:

=INDEX(your-values, MATCH(value-to-lookup, lookup-column, sorting-identifier))

Proper

The Proper function is useful if your data set contains oddly-formatted text values. For example, let’s say that cell A1 reads, “i LoVe tO sInG.” Using PROPER(A1), you’ll see I Love To Sing.

Lock References Using the Dollar Sign

As you know, Excel represents cells using row/column pairs such as A1. When referencing a particular cell, you can lock in either the column or row value so that the reference doesn’t change unexpectedly.

  • If you want to lock the row reference, enter A$1.
  • If you want to lock the column reference, enter $A1.
  • If you want to lock the row and column references, enter $A$1.

This is especially handy when working with large databases or when you want to perform row-by-row (or column-by-column) calculations since you can ensure that the formula references the correct cells.

Concatenate

Using Concatenate, you can combine the values of multiple cells into a single cell:

=CONCATENATE(F3,G3,H3,I3,J3)

excel tricks concantenate

Here’s the result:

excel tricks concantenate

Alternatively, you can concatenate using just ampersands: =F3&G3&H3&I3&J3.

If Statements

“If” statements allow you to output the value of your choice as valid (or, conversely, false). For example, let’s say you’ve entered the following:

=IF(B2>C2,"Pass", "Fail")

This is akin to saying, “If the value of cell B2 is greater than the value of cell C3, populate this cell with “Pass.” If not, populate this cell with “Fail.”

You can build on the simple “If” statements using SUMIF, COUNTIF, and AVERAGEIF. For example, if you had a set of survey results, you could use COUNTIF to count the number of rows where the respondent is female using:

=COUNTIF(B17:B19, "Female")

Conditional Formatting

You’re probably well acquainted with using conditional formatting to color-code your cells, but you can use the built-in icon sets to denote particular points of interest. You can get to the Conditional Formatting area via the Home ribbon.

Hover over Icon Sets and click More rules.

excel tricks icons

Create the rules that determine which icon is used when.

excel tricks icons

In the below example, I’ve used different bar charts to reflect the amount of time spent on a given subject.

excel tricks activity

3-D Sum

If you have multiple spreadsheets that are identical in format, you can easily calculate the sum of values spread out across the spreadsheets.

Let’s say that you have a budgeting spreadsheet to keep track of your weekly expenses. For each week, you have a separate tab within the spreadsheet (the tab for the first week is Week 1, the tab for the second week is Week 2, and so on). Using 3-D sum, you can add up your total expenditures — for example, you might want to know the total amount you spent on gas over the course of several weeks. To do this, you’d enter:

=SUM('Week 1:Week 7'!B10)

excel tricks

Goal Seek

The Goal Seek function is useful for determining the conditions needed for a specific outcome. Some examples include:

  • Given my sales in January through March, what do I need to sell in April to meet a certain threshold?
  • If I have saved and earned interest on my account for the past ten years, how much more do I need to save so that I’ll have $2 million in the bank when I retire at 67?

Once you’ve populated your data in Excel, you can launch the Goal Seek function by going to Tools > Goal Seek.

In the example below, you can see that we’ve provided earnings details based on the price we charge in a given month.

excel tricks

We want to know how much we need to sell in April to meet our $1,500 goal.

excel tricks

While this example was simple (and somewhat trivial), the Goal Seek is frequently used by consultants, sales professionals, and anyone interested in optimizing an unknown value.

Pivot Tables

If you have a large amount of data, and you want to get a good “big picture” view of your data, you can use pivot tables.

To create your pivot table, go to Data > Summarize with Pivot Tables. You’ll be asked to choose the data you want to analyze and where you want to place the pivot table.

excel tricks

Next, you’ll see the PivotTable Builder. You include as many (or as few) columns as you’d like, and you can choose to filter your data, add new columns or rows, and create new summary variables.

excel tricks

Sparklines

Sparklines are charts that provide simple visualization of your data. You can embed pretty much an entire chart into one cell, which allows you to see what your data looks like at a very high-level. You can choose to create three types of Sparklines: Line, Column, and Win/Loss.

You can create your own Sparklines chart by selecting the data you want to be plotted and then going to Insert > Sparklines > [Type of Sparkline].

In the example below, we’ve plotted the results of three 100 yard freestyle swim trials using Column Sparklines.

excel tricks

Excel Tricks Takeaways

Despite the proliferation of specialized tools, we find Excel highly useful and therefore use it often. In this article, we covered ten of our favorite tips and tricks for Excel, and we hope you’ll find them helpful as well.

Read previous post:
social media tools
9 Social Media Tools To Include In Your Marketing Arsenal This Year

There's no question about it -- social media is important if you're a small business owner. Not only does an...

Close