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.
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
*, use the tilde operator
~ in front (e.g.
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.
The 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:
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))
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
- If you want to lock the column reference, enter
- If you want to lock the row and column references, enter
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.
Using Concatenate, you can combine the values of multiple cells into a single cell:
Here’s the result:
Alternatively, you can concatenate using just ampersands:
“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:
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
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:
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.
Create the rules that determine which icon is used when.
In the below example, I’ve used different bar charts to reflect the amount of time spent on a given subject.
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)
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.
We want to know how much we need to sell in April to meet our $1,500 goal.
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.
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.
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.
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 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.