Excel Tutorial: An In-Depth Guide to Working with your Data, Building Formulas, and Using Functions

Understanding Microsoft Excel is a skill that is both in high demand for most employers and valuable in most professions. It’s a must-have tool for virtually any small business and a necessity in research- and statistics-related professions.

Long story short, no matter what you’re doing, you can most likely benefit enough from the program that it is worth your time investment to understand it well. And you can easily undertake learning it, front to back, with convenient online resources. This guide will take you through the basics of manipulating data and constructing formulas and functions in the program. It will offer tips along the way about how to get the most mileage from the program.

You may find that some guided instruction will also be very helpful in getting to know the software, and you can easily get what you need in a comprehensive online course on Microsoft Excel.

With that, let’s jump right in and get to understand Excel more closely. This guide will use the 2007 edition of the program. So while most of the content covered will translate to other versions, keep in mind that some features may work just a little differently in the program you’re using.  This will be particularly true for older versions.

Basic components of Excel

Before learning how to use Excel features, let’s take a moment to get on the same page with some terminology that will be used throughout each section of this guide.  Depending on your current level of Excel knowledge, this may not be new to you, but its important to make sure the language is clear, to avoid confusion. Here are the basic terms you will encounter throughout:

Spreadsheet: When you open a new Excel file, the rows and columns that take up most of the program’s view comprise the spreadsheet. This, of course is where your data will go and where you will plug in your formulas.

Workbook: A workbook is made up of multiple spreadsheets. at the bottom left of the Excel window, you will see multiple tabs indicating the different worksheets, as well as a button for adding new tabs.

Ribbon: The strip at the top of the program, containing the menu items is referred to as the ribbon.

Tabs: The different tabs at the very top (file, home, insert, etc…) will change the content of the ribbon to contain different menu options.

Groups: On the ribbon, menu items are categorized into different groups, labeled according to their functions. For example, this image shows the font group accessed from the home tab.

Contextual menus and dialog boxes: clicking on some items from the ribbon will bring up additional windows with additional options. These are referred to as contextual menus or dialog boxes, depending on their function.

If you are not familiar with any of the resources described above, you might benefit from a beginning course in Excel to get more acquainted.

Additional terms will be defined throughout this guide.

Cell and range operations

Cells are the basic units for entering data in excel, and an area containing multiple cells is called a range. When working with cells in the program, you will reference them by their column letter and row number. And when referring to ranges, you will use that range’s first cell and its last cell, intersected by a colon (e.g. A1:B15).

One of the first areas of proficiency to develop with Excel is how to easily manipulate cells and data in the program.  This is generally a component in a introductory course on Excel.  However, even practiced users may find some new shortcuts and ideas by revisiting some of the operations described here.

Formatting

You can save yourself a lot of time by using formatting features to make data appear the way you want it to in Excel.

For instance, if you are plugging different dollar amounts in to a range, you can format that range as currency, all at once. That way you can save yourself a lot of keystrokes by simply entering the numbers, rather than entering a dollar sign and decimal each time.

Here’s how you do it:

1. Highlight the range you want to format by clicking on the first cell and dragging your mouse pointer to the last cell.

2. Click on the home tab.

3. In the Number group you will see options for formatting your numbers, including a dropdown box with different formatting categories.

In the dropdown box, click the downward arrow to pull down your list of options.

4. Select Currency from this list

You now have your entire list of numbers in a currency format

You can access many other formatting options for your data directly from the home tab, which you can apply in the same fashion. You may want to take a little bit of time to look at through the different options on the ribbon. If you are unsure about what one of them does, hover over it with your mouse, and a brief description will pop up.

Moving and filling

In addition to changing the format of data in your cells, you can easily change the location of data in the spreadsheet and automate filling in new cells. Try out these step-by-step instructions to learn a few examples:

A. Moving a range

Instead of having to cut and paste a range of cells, you can use a simple feature of excel to move the information.

1. Highlight the range you want to move.

2. Hover your mouse pointer over the right side of the range, where you see the black border.

3. hold down the left mouse button and drag the range to the new area. Here we will simply move the data one column over and one cell down.

As you can see, Excel will show a frame around the new range where the data will go.  It also notes the beginning and ending cells (B2:B7 in this example).

4. When you have moved the data where you want it to go, let go of the left mouse button. you should now have the range in its new location.

B. Filling in cells

With Excel, there are instances where you can quickly fill in a range with data . One of the most useful tools for doing this is the fill handle. This easy-to-use tool will let you complete a range of cells based on the values you put in to one or more initial cells.  Here are a few examples:

1. A sequence

Let’s say we want to assign a series of entry numbers for the dollar values we used in the previous example.  The entries will simply be numbered in order from 1 through 6. Rather than putting in each number in that series, you can shorten the task using the fill handle. Here’s how:

a. Enter the numbers 1 and 2 in cells A2 and A3, respectively

b. Select those cells by clicking on cell A2 and dragging down to A3.

c. Move your mouse pointer to the bottom right corner of the range, where you see a small black box. You will see that your pointer changes to look like a black plus sign.

d. Hold down the left mouse button and drag to the cell where you want the series of numbers to end.

Based on the sequence of the initial two cells, Excel will assume you want to increase the value by 1 for each new cell in your range, so it should show the number six in a pop-up next to the final cell.

e. Release the left mouse button. You should now have the numbers 1-6 appearing cells A1-A7.

The fill handle will work this same way with any interval.

You can also use it to repeat formulas that need to be used for a range of values. For instance, let’s say you want to keep a running total for the numbers included in column B.  For that, you can enter one simple formula and repeat it.

1. In cell C2, plug in the the formula =C1+B2. This will give you the value $25.00

2. Highlight cell C2, and use the fill handle to apply the same formula. to the rest of the applicable cells.

If you click through each of the cells you just populated, you will see that Excel automatically changed the cell references by one row each time. So, for instance, the formula in C2 (=C1+B2) becomes =C2+B3 in cell C3.

Absolute references

In working with the fill handle and some other operations in Excel, it’s important to define cells that you don’t want to change in your formulas. And you can do that easily using absolute references.  In short, you indicate that you want the column or row to stay the same by placing a $ sign before it in your cell reference.

Again, let’s use an example to make that easy to understand. Say that, instead of creating a running balance, you want to add a specific dollar amount to each of your dollar values in column B. That dollar value will be placed in cell C1. For our example, let’s set that value at $25.00

You may have realized already that the formula we want to use to get our total is the same as the formula we used in the previous example: =C1+B2. However, this time we want to make sure that the reference to cell C1 doesn’t change when we fill in our cells. To do this, we will change our formula to contain an absolute reference. In this example, since we are only copying the formula down one column, we only need to make sure the row doesn’t change, so we will simply need to place the $ sign in front of the row (C$1). However, if we wanted to make certain that neither the row or column change, we can place a $ sign before the column as well ($C$1).

So, ultimately, the formula in C2 needs to look like this: =C$1+B2. Type this in and use the fill handle to copy the formula down to the other cells. You should see that $25 dollars is added to each of the values in column B.

Data validation

It’s all well and good to have some shortcuts to fill in and format cells, but you might be wondering about cases where you will need to prepare your spreadsheets to work well with other contributors. You might wonder how you can make sure they are entering the right kind of information and not changing things so that your spreadsheet stops working correctly.

Well, there are some great features in Excel to help you with this, and one of the major ones you’ll want to understand is called data validation. Put simply, data validation will ensure only a certain type of data can be placed in to a cell. This helps to keep things consistent between multiple parties working with a spreadsheet.

Data validation is accessed from the ribbon, in the data tools group on the data tab.

Let’s walk through an example for using data validation. We will start with a list of dates and use data validation to ensure that only dates can be entered in additional cells for the list. Returning to the running example used so far in this guide, we can place a list of dates in column A.

Let’s say you want to allow users to add additional entries in to this list.  However, you will be using the date to organize your information, so you want to make sure that information is entered correctly, in the format above. For that purpose, you can put data validation on column A. Here are the steps.

1. Select column A by clicking on its header.

2. Click on data validation in the data tools group. This will bring up the data validation menu.

The settings tab of this menu is where you will specify the type of entry that you will allow in this column of your spreadsheet. By default, it is currently set to Any value, so you will want to switch that to Date.

3. In the “Allow” selection box, press the downward arrow to see your options. Then select Date from the list.

If you will allow any dates in the entry, this is all you will need to do to make sure the correct data form is entered.  However, once you select the date format, the menu will change, giving you options to limit the allowable range of dates. If you wish you can select a start and end date to specify the range.

Notice, also, that there are two additional tabs in the data validation menu. The tab titled input message will allow you to configure a notification to users letting them know what type of information they need to put in.  The error alert allows you to configure the message that appears when a user attempts to enter the wrong data type.

You can, of course, learn plenty more about data validation features in an online Excel course.

Lists

A very useful feature included in data validation is the ability to limit input based on a list. This, again, is done using the data validation menu. However, before selecting your form of validation, you need to create the list of values you will allow.

Let’s work on a new column in the spreadsheet example we’ve been creating. We can use column D to allow entry of certain categories. However rather than starting right in on column D, we’ll want to make a list elsewhere in our workbook.  For this example, we’ll put it right on the same spreadsheet, in column F.

And now, we can use these entries to make our data validation list, following these steps.

1. Highlight column D by clicking on the column heading. (Note that if you do not want to put data validation on the entire column, you can alternatively highlight the range you want to apply it to by clicking and dragging.

2. Navigate to the data validation menu by clicking data validation in the data tools group on the data tab.

3. From the “Allow” drop-down menu, select “List”

4. New menu options will appear for the validation criteria.  In most cases you will want both checkboxes to remain checked. This will give users a drop-down menu they can select their inputs from, and it will ensure that blank cells are not interpreted as invalid.

The source text box is where you want to identify the range containing your list.  You can either type in your cell range, using absolute references (=$F$2:$F$4) or you can select the range from your spreadsheet.  For the latter option, follow these steps:

a. Click the range-selection button on the right side of the Source text box

b. Select the range containing your list.

c. Click the range selection button again to bring back the menu

5.  Once you have identified the range, click OK.

Now, when you click on a cell in column D, you should see a drop-down list containing the valid options.

Data protection

If you want to make extra sure that no collaborators are putting in or deleting values where they shouldn’t, you can use data protection options to assist you.  Essentially, these options will allow you to lock down your spreadsheet entirely, so that it is effectively a read only document, or open only certain cells for input and editing.

Locking and unlocking cells

Before placing protection on a spreadsheet, you will want to understand how to indicate which cells should be locked and unlocked. Basically, if a cell is locked, this means a user can’t edit it once the spreadsheet has been protected. By default, all of the cells in your spreadsheet are locked, so once you protect the workbook, none of them will be editable.

You can change the status of a range as locked or unlocked in the formatting menu. Here is an example:

In column D of the sheet we’ve been developing, we want users to be able to enter categories.  But let’s say that’s the only change we want made to this workbook. That being the case, we’ll want to unlock just those cells.

1. select the range to unlock.  Here it will be D2:D7.

2. Right-click anywhere within the selected range and select format cells from the drop-down menu.

3. The formatting menu will come up.  Select the Protection tab (this should be the final option on the tabs list.)

You’ll see that the cells in the range are marked Locked by default.

4. Uncheck the box marked Locked to unlock the range.

Protecting a sheet

Having indicated which cells you want locked and unlocked, you are prepared to protect your spreadsheet to prevent any unwanted changes.  The process for doing this is simple:

1. Select the review tab.

You will see review options on the ribbon

2. Select Protect Sheet from the Changes group. This will bring up a new menu with multiple options for you to select and a place to enter your password.

3. Enter a password you will remember

4. Check the boxes next to the operations you want users to be able to perform. Typically you will want other users to be able to select cells. The other options may be useful in certain circumstances.

5. Click OK

Your spreadsheet is now locked. Notice that the Protect Sheet option in the Changes group has changed to Unprotect Sheet.

Formulas and functions

So far we have covered some important basics on manipulating data in Excel.  Hopefully this will help you work efficiently with the cells and ranges in your spreadsheets.  Next, we will move on to formulas and functions that will help you automate your spreadsheets, perform calculations, and solve real-life problems with ease.  If you want to learn a lot more about these tools, you can take an intermediate level course in Excel that will teach you all about them.

To start, let’s get clear on what formulas and functions are and how they differ.

Understanding formulas

Formulas are calculations in Excel. These can be purely mathematical or they can be complex and involve one or more functions. Earlier in this guide, we used an example of a simple formula, =C$1+B2.  This formula uses basic addition and cell references to solve a mathematical equation. Note that formulas do not necessarily need to contain cell references but can be used to process straightforward mathematical equations as well.

The key to telling whether a formula is being used in a cell or range is the inclusion of the equals sign (=) at the beginning of a statement.

Understanding functions

Functions in Excel can be thought of as mini programs to help you process data. They are short lines of code that perform different tasks with your data. Once you get to know the available functions, you will find that they are among the most helpful components of the program.

You can spot a function in an Excel spreadsheet by its syntax, which includes the function name, followed by an parentheses and the data inputs needed to perform the functions, e.g SUM(A1:B2).  They are always part of a formula, even if the formula consists of a single function.

Categories of functions

Now that you see what functions are, let’s take a deeper dive into how they operate and how to use them. The following sections will outline the different categories of functions and, in the process, get you used to working with them.

Logical Functions

One of the best ways to automate your data crunching in Excel is to use logical functions. For general users, they are some of the most widely used, and therefore a good set to begin with.  These decision-making tools apply logical arguments to the data in your cells to help you solve complex problems, evaluate data, etc.

This category is small but mighty, containing only a few functions. The three you will find most useful are:

• IF – returns a true or false result based on a logical test

• AND – Returns true or false if a set of conditions are met

• OR – Returns true or false if any condition is met.

Let’s return to the spreadsheet example to work with an IF function.  We’ll add this function in column G to quickly determine whether the dollar values in column C are greater than $80.00. Of course, since we are working with a short list, you may contest that a function isn’t necessary.  But imagine if you are working with a spreadsheet that includes thousands of entries!

Here is how you’ll complete the function:

1. Click on the cell where you want the function to go.  In this example, we will ultimately copy it to multiple cells.  However the first instance will go in cell G2.

2. Click the function button to the left of the formula bar

3. The Insert Function menu will appear. This will give you the option to search for a function or choose a category.  In the menu labeled “Or select a category” click the downward arrow to see the list of categories and choose the Logical category by clicking it.

4. The Select a function menu changes to contain only logical functions. Choose IF from this list and click OK.

5. You now have a dialog box for entering your function. You will see that the IF function gets three inputs.  The logical test will define the condition that you want to confirm. For this example the condition we want to test is C2>80. So this is the entry for the Logical_test box. In the other two boxes, you define the value you want Excel to return if the condition is met and the value to return if it is not. If the value is true, we will ask Excel to return “OK”. So this is the entry for the Value_if_true box. And if the condition is not met, we will have Excel return the text “Low”

6. Click OK, and you will see the result “Low” entered in to cell G2. Notice how the formula appears in the formula bar: =IF(C2>80,”OK”,”Low”). You can type functions directly in to the formula bar, rather than using the function dialog box, and this is how an IF function will look.  Its syntax is =IF(Logical_Test,Value_if_true,Value_if_false).

7. Using the fill handle, copy the IF function in G2 down to the remaining cells in G2:G7

This of course is only one example of a logical function.  And if you want some guided experience on learning to use lots of similar functions, you might want to take a course specifically on formulas and functions in Excel.

Database Functions

Excel can be used to house a very large number of records and function as a basic database. And while it has not been designed with the same level of querying power as Microsoft Access, the program offers a set of great functions that make it easy for users to maintain and analyze records in the program. In essence, these functions take many of the most useful functions for data analysis and adapt them for large databases.  If you are planning on using Excel for this purpose, you can learn the essentials you will need to know as part of an online course in Excel.

If you would like to familiarize yourself with the database function capabilities, you might take some time to read the available functions’ descriptions. If you click on the insert function button at the left of the formula bar, select the Database category, and you take a look through them.

Text

Functions are not limited to working on numeric input. In fact, if you are working with text strings in your Excel spreadsheets, you have a very useful set of text functions to assist you. There are many options in the text category, so we can focus on a short list of the most helpful ones to get you started:

• FIND: this function returns the starting position of a sub-string (part of the text) within a text string. It is very commonly used in combination with other functions to automate problem-solving tasks.

• CONCATENATE: combines text from multiple cells in addition to text you specify. This function has a shorthand method for its use.  Rather than typing out its full form, you can use an ampersand (&) to invoke it.

For instance, if you wanted to combine text entered into cell A1 along with the word “units” you can simply enter the formula =A1 & “units”. In its long form, this would be written =CONCATENATE(A1, “ units”)

• Left, Mid, and Right: These functions return a sub-string (of a specified length) from the beginning, middle, and end of a text input.  Again they are very useful in problem-solving, particularly when combined with other functions. Visit this sub-string functions tutorial to get a handle on how to use them in your spreadsheets.

Statistical

Excel has a very powerful set of statistical tools and additional add-ins that provide even more advanced functionality to the program.  If you are training to use Excel for statistical data processing, you may want to consider a course designed to teach you Excel for statistics.

Some of the most generally useful functions in this category are:

• Average, Median and Mode: As their names imply, these functions provide a quick and easy method for determining the mean median and mode, respectively, for a range of numeric data.

• Averageif: Averages numbers within a range that meet a designated criteria. As its name indicates, it combines the Average and IF functions

• CountIf: Returns the number of records that meet a given criteria within a specified range. This can be very useful for purposes such as reporting dashboards.

If you would like some quick training on more specialized functions in this category, you can access a tutorial on TTEST, a tutorial on the STDEV standard deviation function, and a tutorial on the ANOVA advanced function that will start you out on the analysis toolpak add-in.

Math

As you have learned, you can use Excel formulas to operate the program essentially as a calculator.  And there are a number of functions that operate like the programs available on an advanced calculator.  For example, the SQRT function will return the square root of designated inputs; you can perform logarithms using the LOG function.

There are also some helpful time savers in this category.  One very widely used example is the SUM function, which calculates the sum of an entire range that you specify.  The SUMIF function combines this with the IF function to add together only the numbers that meet a given logical test.

Lookup and Reference

When you are working with data tables in Excel, using lookup and reference functions will provide automated and simplified ways of sifting through the data to find the information you need.  They can also give you quick, ad-hoc tools for finding data results from reports.  Some of the top functions in this category that you will want to get to know are:

INDIRECT: returns a refence that you specify by a text string.  This function can be a bit confusing, but it can be very helpful if you get used to it. Take a look at this brief Excel indirect tutorial to get a handle on it.

VLOOKUP: One of the most often used functions for analyzing data in tables, the VLOOKUP function searches one column to find a value you specify, and it returns a value from a related column. Thus, it is fundamental for working with arrays in Excel.  It’s highly recommended that you come to understand this function well.  But rather than launch in to its details here, take a look at this guide to using the function.

Financial

Calculations in finance can be complicated due to continuously compounding interest, etc.  Thus, Excel provides a quick and easy way to get answers in finance without putting in a lot of time. Especially helpful for general users are the functions that calculate present value, future value, payment, etc. across the term of an investment or loan. Try the functions PMT, FV, and PV.

Date and time

Dates and times are stored as numeric values, so if you need to work with them in your formulas, Excel prvides a range of functions that will assist you. These are very often used in combination with other functions, typically for converting a date to a numeric value. They can also be used to dynamically update the date and time on a spreadsheet.

Cube & Engineering: Two other categories in the Excel functions list, cube and engineering are highly specialized.  Cube provides a way to work with data cubes and engineering provides a range of functions custom fit for the purposes of engineers.  These will generally be learned in specific application to a discipline or profession

Combining functions

As this guide has mentioned, you can solve complex problems in Excel by learning to combine functions. Let’s look at an example that introduces how this is done. Returning to our evolving spreadsheet, we can write a function that will give multiple outputs depending on the average dollar value in column C. Here is that spreadsheet, once again, for reference.

Let’s say that if the average dollar amount is between 150 and 200, we want to return the value OK.  If the average is above or below this, we will return “Outside”. This requires the If, AND, and AVERAGE functions to complete in a single formula. We can enter the formula in cell A9.

The AVERAGE function will give us our average dollar amount, so let’ start by writing this.  We want to take the average of cells C2:C7, so the function should read:

AVERAGE(C2:C7)

Now, we want to determine if the result of that function is within the range 150-200.  We will use the AND function, then to determine whether it is more than 150 and less than 200. That means the AVERAGE function will be place inside of the AND function.  We will need to write it twice to cover both of the criteria.  It will look like this:

AND(AVERAGE(C2:C7)>150,  AVERAGE(C2:C7)<200)

On it’s own, this will return a true or false result.  However, we want to know instead how much lower than the range’s minimum or higher than its maximum the outcome is.  This is where we will employ the IF function.  Since the AND function makes up the logical test, we will use it as the first input for that function.  It should look like this:

IF(AND(AVERAGE(C2:C7)>150, AVERAGE(C2:C7)<200), “OK”, “Outside”)

Once you have that finished, you simply need to place an = at the beginning to indicate to Excel that it is a formula.  You should then see a result returned in cell A9.

Summing up

The goal of this guide has been to get you started on using Excel to efficiently manipulate data and run formulas and functions to make your experience more fluent and simple.  There is of course much more to learn.  Macros, Dashboarding and reporting, or statistical data testing are just a few examples of next steps you can take once you have these basics down. If you would like to get a great start on Excel, make sure to check out some of the resources mentioned above.

Keep in mind that if you dedicate a little time up front, you will be flying through the data and spreadsheets and making a great return on your investment in learning Excel.