Excel is one of the most powerful software tools in the world for collecting, analyzing, and summarizing data. But its incredible power comes at a cost: Excel is a massive program, and it can take beginners months or even years to master it. Many first-time Excel users don't take advantage of all of the program's functionality; we often find new learners manually entering data when they could be using formulas and functions to speed up and expedite their work.

With that in mind, we've compiled a list of the 10 most important places to start when you're learning how to use Excel.

**The goal of this guide is to familiarize you with Excel's most useful functions** — not to provide an in-depth overview of all of them. If you find yourself struggling to follow along, use the

Without further ado: here's our beginner's primer on how to use Excel — from worksheet basics to formulas and functions to Pivot Tables.

## Cell and workbook basics

Before getting started with Excel, it's important to learn the program's basic structure so that you can navigate it effectively. Each Excel file is called a

Pressing

Within each workbook, Excel contains multiple

Each worksheet contains a grid of

We can also refer to cells using

When you select a cell and start typing, you can insert

**Numbers**, which are numerical values like5 or78.34 . Numbers can be used as part of calculations like addition, subtraction, and multiplication.**Strings**, which are bits of text like"Boston" or"SnackWorld" . Strings can be manipulated in various ways, like finding pieces of text within them or chopping them in half.**Dates**, which represent days in our standard calendar, like5/19/2019 .**Cell references**, which point Excel to other cells in your workbook, likeC9 . We'll learn more about cell references further on in this tutorial.**Functions**, which are formulas that allow you to quickly and easily perform calculations that would be difficult to do by hand. These functions are often based on the contents of other cells within your workbook, and can output numbers, strings, or dates. And if you enter a function into Excel that doesn't make sense, it'll give you anerror , like#N/A . Whenever you see an error like this, it means that there's a problem with your function and you need to revise it.

Those are the basics of workbooks and cells, but there's lots more to learn. Check out the more detailed articles below for further reading.

### Further reading

- What is Excel? A beginner's overview
- Excel workbook and worksheet basics
- Working with rows, columns, and cells

## Save three hours of work a day with these 5 simple Excel hacks

Work smarter, not harder. Sign up for our 5-day mini-course to receive **must-learn lessons** on how to automate your work using Excel.

- Why the
**TRUE and FALSE**functions are so important... - Why to
**rethink the way you do VLOOKUPs**... - Plus, we'll reveal
**why you shouldn't use PivotTables**and what to use instead...

## Sorting and filtering data

One of the simplest and most immediately useful features of Excel is that it can

Take, for example, the below list, which contains a number of rows representing items sold by customer:

The above is a useful list, but it would be easier to read if we could sort by customer name to group all sales to the same customer together. To sort a list, position your cursor in the header cell of the column you'd like to sort, head to the

We can also sort data based on more advanced parameters by highlighting our entire data set and pressing the

Pressing this button will open the

In addition to sorting, we can also filter our data. Filtering means cutting down a large data set to a smaller subset of rows that satisfy certain specified criteria. To test filtering, we'll place our cursor within our data table, then head to the

Notice that after filtering our data, small dropdown arrows appear next to the headings of each of our columns. We can click these arrows to open up the

When we press the

The beauty of filtering is that no rows have actually been deleted; our entire original data set is preserved. We've just temporarily filtered out rows in which the

Now that you know how to use basic sorting and filtering, check out our more in-depth articles below. They'll show you how to perform advanced custom sorts and add multiple filters based on dates, values, and text.

### Further reading

- How to alphabetize in Excel: A complete guide to sorting
- How to filter data with autofilters in Excel

## How formulas work

=5 +4

Output:9

Since Excel sees the

Formulas often contain more than just numbers and operators like the

- First, a
function name , which describes what the function does — like the name of a recipe. Function names are built into Excel. For example, there is aSUM function that adds together a series of values, and aAVERAGE function that averages together multiple values. We'll explore the various functions available to you throughout these tutorials. - Second,
arguments , which combine within the function to output something bigger — like the ingredients of a recipe. For example, if we wanted to take theAVERAGE of4 and6 , the arguments to the function would be the numbers four and six. In a function, arguments are separated by commas within parentheses. - Third,
output , which is what comes out of the function when it's finished calculating — like the final product of a cooked recipe. For example, if we took theAVERAGE of4 and6 , the output would be5 .

Take a look at the examples of completed formulae below. Can you identify the function names, arguments, and output?

=AVERAGE (4 ,6 )

Output:5

=MAX (4 ,6 ,2 ,34 )

Output:34

Within formulae, we can also

=SUM (AVERAGE (4 ,6 ),10 )

Notice that we've included the

=SUM (AVERAGE (4 ,6 ),10 )

Step 1: =SUM (6 ,10 )

Output:16

Nesting isn't just limited to two layers — you can nest as many functions within each other as you'd like. Take a look at the following formula — which we haven't broken down step-by-step — and see if you can figure out why it outputs what it does.

=SUM (SUM (AVERAGE (1 ,3 ),AVERAGE (5 ,7 )),SUM (12 ,10 ))

Step 1: =SUM (SUM (2 ,6 ),SUM (12 ,10 ))

Step 2: =SUM (8 ,22 )

Output:30

You now know the basics of how to use formulas and nested functions in Excel. Check out the tutorials below for more detailed information!

### Further reading

## Save three hours of work a day with these 5 simple Excel hacks

Work smarter, not harder. Sign up for our 5-day mini-course to receive **must-learn lessons** on how to automate your work using Excel.

- Why the
**TRUE and FALSE**functions are so important... - Why to
**rethink the way you do VLOOKUPs**... - Plus, we'll reveal
**why you shouldn't use PivotTables**and what to use instead...

## Cell references

Excel really starts to get insteresting when you start using

Take a look at the following formula, which contains the most basic type of cell reference:

=C4

In this piece of code, we're telling Excel to go to cell

=E6

Cell references are useful because they can be included within functions. This enables you to perform complex calculations based on sets of cells that would otherwise be difficult to perform manually. For example, take the following

=SUM (C3:C7 )

This function will automatically

By default, cell references are

The following spreadsheet contains sales data for three product categories (

=SUM (C4:C6 )

Output:$16,000,000

Our total is set for `Ctrl` + `C` on Windows or `⌘` + `C` on a Mac. Then, we'll click and drag to select cells `Ctrl` + `V` on Windows or `⌘` + `V` on a Mac.

Notice what happens to the formula in cell

=SUM (D4:D6 )

Output:$24,000,000

Relative cell references are convenient, but there are some times during which we don't want our cell references to shift when we copy and paste. Consider the following sheet, in which we've created a static value in cell

=C7 *H4

Output:$4,800,000

Our formula works in cell

=D7 *I4

Output:0

Fortunately, there's a good solution to this problem: absolute cell references. Absolute cell references allow us to lock the row or column of a reference — or both! — so that they do not change when we copy and paste from one cell to another.

The

Here's an updated version of our formula, with an absolute reference used to lock the column letter on the reference to our tax rate cell,

=C7 *$H4

Output:$4,800,000

Notice what happens when we copy and paste this formula to the right. Our

=D7 *$H4

Output:$4,800,000

When working with absolute and relative cell references, it's important to remember that you can use them on the row, column, or both. Here's a handy table to help understand what that

Format | Meaning | Explanation |
---|---|---|

$A$1 | Row and column locked | Cell reference will not change at all as cell is copied and pasted. |

$A1 | Column locked | Only row reference will change as cell is copied and pasted. |

A$1 | Row locked | Only column reference will change as cell is copied and pasted. |

A1 | Nothing locked | Both row and column will change as cell is copied and pasted. |

Remember, rows and columns in a cell range can also be locked. For example, the range

Those are the basics of cell references — both relative and absolute. Check out the article below for more detailed information.

### Further reading

## Mathematical functions

Now that we've covered the basics of Excel, it's time to get into the program's more advanced features — starting with mathematical functions themselves. Excel has hundreds of functions available for use, and it would be impossible to cover all of them in this short tutorial. So, to start, we'll provide a brief overview of some of Excel's most common mathematical functions.

### SUM

Here's a basic use of the

=SUM (3 ,7 )

Output:10

Here's a slightly more complicated use of the

=SUM (C3 ,C4 ,C5 )

Output:$18,000,000

And, finally, here's a better use of the

=SUM (C3:C5 )

Output:$18,000,000

Check out our detailed

### AVERAGE

The

Here's a basic use of

=AVERAGE (10 ,20 )

Output:15

And here's

=AVERAGE (C3:C5 )

Output:$6,000,000

Head over to our overview of the

### MAX

The

Take a look at the following formula, in which we've used

=MAX (4 ,5 ,6 )

Output:6

Of course, like

=MAX (C3:C6 )

Output:$5,000,000

### COUNT

=COUNT (5 ,9 ,10 )

Output:3

The above formula outputs

=COUNT (5 ,"Seven" ,10 ,#N/A ,45 )

Output:3

=COUNT (B2:B8 )

Output:5

How is this useful in practice? Use the

### Further reading

- Excel's SUM function
- AVERAGE formula in Excel
- Using Excel's MAX Formula
- Minimum Values in Excel Using MIN
- How To Use Excel's COUNT Function
- Using Excel's COUNTA Function

## Lookup functions

Now that we've got a handle on some of Excel's basic mathematical functions, it's time to move on to a more complicated class of tools: lookups. Lookup functions allow us to look up a given value on a table with multiple rows and columns dynamically. That's quite a mouthful, so let's take a look at a real-world example to give you an idea what we mean.

Take a look at the below spreadsheet, which lists SnackWorld unit sales and profit for various item categories in a given month.

Let's say that a SnackWorld analyst wants to pull the

### VLOOKUP

We can accomplish the above using the

=VLOOKUP (lookup_value ,table_array ,col_index_num )

Given a set

=VLOOKUP ("Lollipops" ,B2:D7 ,2 )

Output:35,000,000

In the example above, we provide

How is this useful in practice?

=VLOOKUP (C9 ,B2:D7 ,3 )

Output:$24,000,000

Notice that our

Check out our in-depth

### INDEX MATCH

Once you've mastered

**Counting isn't necessary.**VLOOKUP forces you to count columns, providing it with acol_index_num to indicate which column to pull data from.INDEX MATCH allows you to skip this step, instead selecting the column in question directly.**Inserting columns is easy.**SinceVLOOKUP uses acol_index_num for its lookup, inserting columns into your lookup table can often break your formulas.INDEX MATCH prevents these problems by eliminating thecol_index_num argument.**Backwards lookups are possible.**INDEX MATCH allows you to look up values that are to the left of your item or category list, rather than only those that are to the right.**Even more functionality.**INDEX MATCH allows you to take advantage of a plethora of more advanced functionality — including both vertical and horizontal lookups; two-dimensional lookups; and lookups with more than one criteria.

Covering how

### Further reading

- How to do a VLOOKUP
- Using INDEX MATCH
- Using INDEX MATCH MATCH (look up in two dimensions)
- INDEX MATCH with multiple criteria

## Save three hours of work a day with these 5 simple Excel hacks

Work smarter, not harder. Sign up for our 5-day mini-course to receive **must-learn lessons** on how to automate your work using Excel.

- Why the
**TRUE and FALSE**functions are so important... - Why to
**rethink the way you do VLOOKUPs**... - Plus, we'll reveal
**why you shouldn't use PivotTables**and what to use instead...

## Logical functions

Logical functions are some of the most advanced formulas you can create in Excel. They allow us to tell Excel to choose one of various possible outputs depending on a

### IF statements

=IF (logical_expression ,value_if_true ,value_if_false )

When an

Let's take a look at a very basic

=IF (7 <3 ,"Correct answer!" ,"Incorrect answer" )

Output:"Incorrect answer"

In the formula above,

Of course, the above example isn't particularly useful, because we don't need a complicated formula to tell us that

In the following example, we use

=IF (SUM (C3:C5 )>=F2 ,F3 ,F4 )

Step 1: =IF ($22,000,000 >=$20,000,000 ,F3 ,F4 )

Step 2: =IF (TRUE ,F3 ,F4 )

Output:Great job!

Let's break down what's happening in the above formula. First, our

Those are the basics of

Operator | Meaning | Explanation |
---|---|---|

= | Equals | Returns |

> | Greater than | Returns |

< | Less than | Returns |

>= | Greater than or equal to | Returns |

<= | Less than or equal to | Returns |

<> | Not equal | Returns |

For more information, stop on by our tutorial on

### SUMIF

Once you've got a handle on the

Let's take a look at a basic example of

We already know how to use

We can do this using the

=SUMIF (range ,criteria ,sum_range (optional) )

Given an input

=SUMIF (C3:C7 ,"=" &G4 ,D3:D7 )

Output:$11,000,000

In the above example,

After examining each cell in the range *if and only if* our

The best part about this formula is that it's dynamic. Try changing the string in cell

The above may take a little time to digest, but walk through it a couple times and you'll get a handle on how

Once you've mastered

It's also worth noting that

### Further reading

- The TRUE and FALSE Excel functions
- Using IF statements in Excel
- Excel's SUMIF function
- Excel SUMIF with multiple criteria: SUMIFS
- Excel's COUNTIF function

## Creating charts

Formulas and numbers aren't all that Excel has to offer. In addition to displaying data numerically, it's also excellent at displaying data visually using charts and graphs. In this section, we'll give you a brief overview of Excel's charting capabilities, and show you how to generate graphs based off of your data.

The most important first step to creating a chart is preparing your data. To generate a chart, you'll need to create a data summary table that shows the exact data points that you'd like to appear on your chart. Oftentimes, we create summary table for use with charts and graphs by leveraging the Excel functions we discussed above, including lookups,

Once your data is ready for charting, select your entire table by clicking and dragging your mouse, or using hotkeys (`Ctrl` + `A` using Windows or `⌘` + `A` on a Mac). Then, navigate to the

Our chart is created! With just a few simple clicks, we've taken a data summary table and made it visually clear and accessible for our users.

There are many different chart types available in Excel, so it's important to think carefully about which visual will most clearly communicate your data. In the example below, we've used the same data to create a

Of course, the chart above is lacking some crucial details — for example, it doesn't have a title, so we have no idea what year our data comes from (or, for that matter, what company's sales data it represents). Once you've created your chart, ensure that your users have all the data they need by adding a title, axis labels, and other formatting, like a legend and data labels. All of these options are available when you select your chart and go to the

There are many complex charting options in Excel — including advanced features like manually-set axis labels, statistical analysis, and customized styling. Play around with the options in the

### Further reading

## Pivot Tables

Once you've mastered Excel's basic formulas — like

Fortunately, there's an easy alternative to complex formulas:

The spreadsheet below contains sales data for a number of SnackWorld orders in the first quarter of the year 2019.

The data is in what we call

Once we've got our data in order, we can make our Pivot Table. Start by selecting any cell within the input data table; then, head to the

A

A new sheet will appear that contains our Pivot Table. Notice that Excel has inserted a blank Pivot Table graphic on the left-hand side of the screen, and opened a new

The PivotTable Field List contains a listing of all of the column headings within the data table we used as an input. We can add any of these column headings to the

Report Filter allows us to filter our output based on the values in one or more fields.Column labels lets us summarize by a particular field across the top of the screen (columns).Row Labels lets us summarize by a particular field down the side of the screen (rows).Values Specifies the column data that we'd like to summarize — for example,Total price .

That's all a lot to keep track of — so let's take a look at a real-life example to get a better handle on what this all means. Suppose that a SnackWorld analyst wants to summarize total sales by customer over the entire period contained within our data table. She could do so by dragging the

Without any formulas or functions, Excel has automatically added up the

There are lots more things we can do with Pivot Tables by moving our columns between the four boxes at the bottom of the screen. For example, by dragging the

We can also drag another field — like

Or, we can drag a field to the

The power of Pivot Tables is practically limitless — they can be used in a massive variety of situations to segment and summarize data. They're particularly valuable when calculating a

### Further reading

## Advanced formatting

Now that you've got a handle on data manipulation in Excel, it's time to learn some advanced formatting techniques to polish your spreadsheets — making them both beautiful to look at and easy to interpret.

The most useful of these features is called

Let's take a look at a common example of conditional formatting in practice to get a first-hand view of just how useful it is. Take a look at the below spreadsheet, prepared by a SnackWorld analyst. It shows the company's total monthly sales by product category for the year of 2019:

The SnackWorld CEO, upon seeing this data, had an important comment: the information is all there, but it's very hard to read the chart and identify trends without spending a significant amount of time looking at the numbers. Our analyst could create a chart or graph representing the data, but she wants to preserve it in table form so that exact sales numbers for each category and month can quickly be pulled. Is there an easy way that we could make this data easier to look at while preserving its integrity?

The answer is conditional formatting. Let's test it out by highlighting our data set — excluding the dates on the left-hand side of the chart, because we do not want to format those — and clicking the

A large formatting menu opens up giving us several options:

**Highlight Cells Rules**allow us to highlight particular cells based on the values that they contain. We can specify criteria telling Excel to highlight cells that contain values greater than or less than a certain threshold; between two numbers; equal to a certain value or string; containing a given substring; and more.**Top / Bottom Rules**allow us to highlight particular cells that are important relative to other values in our data set. For example, we could highlight the ten largest or smallest cells by value; the top or bottom 10% of cells by value; or cells containing values greater than or less than the data set average.**Data Bars**show values relative to the whole data set using a graph-type visual overlay.**Color Scales**change the background color of cells based on the values that those cells contain on a scale. Color scales use color gradients, with the highest-value cells appearing at one end of the gradient spectrum and the lowest-value cells appearing at the other end.

For our analyst's purposes, the

When we select this color scale, Excel tints our cells on a spectrum, with the highest values in green and the lowest values in red. Note that the program automatically uses a gradient, making it very easy to tell where our data values fall in the range.

To clear conditional formatting, select all formatted cells and look at the

Those are the basics of conditional formatting, but there's much more to learn. Check out our full conditional formatting tutorial for more information!

### Further reading

There you have it: the top ten places to start when you're learning how to use Excel. We hope that this tutorial has been helpful as you start off with this amazing and powerful program. Our website features dozens of free, easy-to-follow courses on how to use Excel's formulas, functions, and other features, so be sure to check it out for more information as you learn. And if you have any questions or comments for us, we'd love to hear them — sound off in the Comments section below. Thanks for coming by!