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 this is the sheer number of built-in tools, and certain Excel functions that make our work much easier.
Excel is great for small business budgeting, preparing profit and loss statements, general goal setting, high-level data analysis, and more. Here are some of our favorite tips and tricks we think you’ll find useful if you are trying to master Excel.
1. 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 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)
2. CONCATENATE
Using CONCATENATE, you can combine the values of multiple cells into a single cell:
=CONCATENATE(F3,G3,H3,I3,J3)
Here’s the result:
Alternatively, you can concatenate using just ampersands: =F3&G3&H3&I3&J3.
3. 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 use Conditional Formatting via the Home ribbon.
Hover over Icon Sets and click More rules.
Create the rules that determine which icon is used when.
In the example below, I’ve used different bar charts to reflect the amount of time spent on a given subject.
4. Excel Wildcard 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 wildcards 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 ~*).
5. “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")
6. 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’re done with data entry 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.
7. Lock References Using the Dollar Sign
As you know, Excel refers to 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.
8. Log-Log Graph
A log-log graph has both the X axis and the Y axis on a logarithmic scale. In this graph, the grid line for both the axes will rise exponentially and unevenly.
- Once you have your X and Y axis data ready, select the entire table.
- In the Insert Menu, click on Charts.
- Choose Insert Scatter (X, Y).
- Alternatively, you can also choose a Bubble Chart.
- Then click on Scatter with Smooth Lines and Markers.
- Select the Chart.
- On the Y axis, double click.
- On the right, find the Format Axis panel.
- Click on Axis Options.
- Check the Logarithmic Scale box.
You will notice that this time, the grid line is not a straight line but rather uneven. This is because the X axis value is exponential.
- Select the Chart.
- On the X axis, double click.
- On the right, find the Format Axis panel.
- Click on Axis Options.
- Check the Logarithmic Scale box.
Finally, you’ll be able to see that your graph is logarithmic and it is a straight line.
9. PivotTables
If you have a large amount of data, and you want to get a good “big picture” view of it, a good solution is creating a PivotTable. This is the best way to summarize vast Excel data.
To create your table, go to Data > Summarize with Pivot Tables. A dialog box will appear, where you’ll be asked to choose the excel data you want to analyze and where you want to place the data 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.
10. 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.”
11. Remove Duplicates
Excel has a quick way 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 the feature, simply select the data you want to be cleaned and go to Data > Remove Duplicates.
12. ROUND Function
While making calculations, it is easier to round up the numbers in Excel. It makes the data seem straightforward and gives you a better overview of the numbers. You can round up numbers in Excel using three functions:
- ROUND: The ROUND function uses the general mathematics rule. Numbers above five are rounded up to the closest digit while the number below five is rounded down to the closest digit.
- ROUNDUP: The ROUNDUP function by default rounds up all the numbers.
- ROUNDDOWN: The ROUNDDOWN function by default rounds down all the numbers.
In the formulas tab, scroll down to Math & Trig, and then select ROUND, ROUNDUP, or ROUNDDOWN.
13. Semi-Log Graph
A semi-log graph has one of the two axes (usually the Y axis) on a logarithmic scale. In this case, the Y-axis values will increase or decrease exponentially while the X axis values will remain constant.
- Once you have your X and Y axis data ready, select the entire table.
- In the Insert Menu, right click on Charts.
- Choose Insert Scatter (X, Y).
- Alternatively, you can also choose a Bubble Chart.
- Then click on Scatter with Smooth Lines and Markers.
- Select the Chart.
- On the Y axis, double click.
- On the right, find the Format Axis panel.
- Click on Axis Options.
- Check the Logarithmic Scale box.
This is how you can have a semi-log graph with a straight line.
14. Sparklines
Sparklines are charts that provide simple visualization of your data. You can embed virtually 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.
15. 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))
16. Waterfall Chart
A waterfall chart is a standard Excel chart showing the addition or subtraction of the initial value and the final value in the rectangle. The values appear in different colors depending on whether they are positive or negative. In addition, the size of the rectangle showing the final value reflects the size of the value.
- Once your data table is ready, select the table.
- In the Insert panel, click on Charts.
- Find the Insert Waterfall Chart icon and click on Waterfall.
At this point the chart is ready but the total values are not correctly represented.
- Double click on the rectangle which shows the initial value.
- The Format Data Point Settings table will appear.
- In the Series Option, check the Set as a Total box.
- Again, double click the final value in the chart.
- Check the Set as a Total box.
