How Do I Use Excel

When starting to use Excel, we use formulas to manipulate data from other cells in the worksheet. After a while, however, this is no longer enough, as we need to combine data from multiple sheets, or even from other Workbooks.

Six ways Excel users save time with Power BI. Excel is a powerful, flexible tool for every analytics activity. Combine it with Power BI to get broad data analytics and visualization capabilities. Easily gather, shape, analyze, and explore key business data in new ways—all in less time—using both apps together.

Use Pivot Tables to recognize and make sense of data. Pivot Tables are used to reorganize data in a. Charts that use certain kinds of data sources that are not supported for workbooks viewed in a browser window. If you are using Excel Services or Excel Web App on premises (not in the cloud), the ability to display certain content in a browser window depends on whether Excel Services or Excel Web App is used to display a workbook.

In this article, we’ll tackle this issue and see how we can access the contents of any worksheet.

Formulas for Linking Data – An Example

Imagine that your supervisor in the fictitious WYZ Widget Company needs you to calculate how much of the assigned budget was actually used (you can find the file here).

Let’s look at the budget sheet (it’s the first in the workbook):

And compare it to the Actual Expenses sheet (the next one in the file):

Well, comparing it by eye just won’t do. We need a new table in which the difference is computed.

We’ll start with an empty table; that is, a table with the same headings for the columns and rows, but no data (available as the third sheet in the file):

In each cell of this table, we want to have the difference between the allocated budget and the actual spending. To do that, we’ll create a formula that deducts the amount of the second sheet (Expenses) from the first sheet (Budget).

To do this , we do the following:

We select cell B5
Type in ‘=’ (to start off the formula)
Click on the Budget sheet tab to display the budget sheet; If you follow these steps you’ll note that the Formula Bar now contains =Budget! – this will be explained in a moment
We’ll now click cell B5. The Formula Bar should now display =Budget!B5
Type in – , which will be added to the Formula Bar: =Budget!B5-
Click on the Actual Expenses sheet’s tab; The sheet’s name will be added to the Formula Bar: =Budget!B5-‘Actual Expenses’!
Finally we’ll click on the B5 cell of this sheet and press the Enter key; the Difference sheet will be displayed again, and the B5 cell will display a zero amount

Here’s how it should look (check out the formula bar for the formula we created for cell B5):

Exclamation Points to Sheets

OK, what did we do here?

Let’s look at the formula we’ve created and explain the parts.

The first part of the formula we got from the Budget sheet:

Budget!B5

When we use a reference to a cell on the current sheet, we just type in the name (or select it using the mouse); when we want to refer to a cell in a different sheet in the same file, we preface it with the name of the sheet and an exclamation mark.

The second part, which we deduct from the first, is:

‘Actual Expenses’!B5

So, now we have single quotes around the name. Why? Because the name includes a space. Any sheet name that contains a non-alphanumeric character needs to be quoted, so Excel won’t get confused.

Filling better

Now we can copy this formula to the rest of the cells in the table by using the fill handle.

Grab the fill handle and drag it down to cover the whole column. Excel is smart enough to replace the row numbers in each cell, so we get the difference between the appropriate entries in the two sheets:

You can also select the whole column and go on to fill the whole table by dragging the fill handle to the right up to the December column.

Summary

In this article, we learned how to reference data in other sheets, and that Excel’s fill handle is smart enough to work with those references. Will this technique be useful for you? Would you like to learn more about linking data between sheets? How about linking data between workbooks? Please share your thoughts in the comments…

About the Author: Joseph Reese is the founder of Excel-Formulas.com an Excel consulting firm. Head over to his website if you want to improve your performance with Excel.

The VLOOKUP function is one of the most popular functions in Excel. This page contains many easy to follow VLOOKUP examples.

Exact Match

Most of the time you are looking for an exact match when you use the VLOOKUP function in Excel. Let's take a look at the arguments of the VLOOKUP function.

1. The VLOOKUP function below looks up the value 53 (first argument) in the leftmost column of the red table (second argument).

2. The value 4 (third argument) tells the VLOOKUP function to return the value in the same row from the fourth column of the red table.

Note: the Boolean FALSE (fourth argument) tells the VLOOKUP function to return an exact match. If the VLOOKUP function cannot find the value 53 in the first column, it will return a #N/A error.

3. Here's another example. Instead of returning the salary, the VLOOKUP function below returns the last name (third argument is set to 3) of ID 79.

Approximate Match

Let's take a look at an example of the VLOOKUP function in approximate match mode (fourth argument set to TRUE).

1. The VLOOKUP function below looks up the value 85 (first argument) in the leftmost column of the red table (second argument). There's just one problem. There's no value 85 in the first column.

2. Fortunately, the Boolean TRUE (fourth argument) tells the VLOOKUP function to return an approximate match. If the VLOOKUP function cannot find the value 85 in the first column, it will return the largest value smaller than 85. In this example, this will be the value 80.

3. The value 2 (third argument) tells the VLOOKUP function to return the value in the same row from the second column of the red table.

Note: always sort the leftmost column of the red table in ascending order if you use the VLOOKUP function in approximate match mode (fourth argument set to TRUE).

Vlookup Looks Right

The VLOOKUP function always looks up a value in the leftmost column of a table and returns the corresponding value from a column to the right.

1. For example, the VLOOKUP function below looks up the first name and returns the last name.

2. If you change the column index number (third argument) to 3, the VLOOKUP function looks up the first name and returns the salary.

Note: in this example, the VLOOKUP function cannot lookup the first name and return the ID. The VLOOKUP function only looks to the right. No worries, you can use INDEX and MATCH in Excel to perform a left lookup.

First Match

If the leftmost column of the table contains duplicates, the VLOOKUP function matches the first instance. For example, take a look at the VLOOKUP function below.

Explanation: the VLOOKUP function returns the salary of Mia Clark, not Mia Reed.

Vlookup is Case-insensitive

The VLOOKUP function in Excel performs a case-insensitive lookup. For example, the VLOOKUP function below looks up MIA (cell G2) in the leftmost column of the table.

Explanation: the VLOOKUP function is case-insensitive so it looks up MIA or Mia or mia or miA, etc. As a result, the VLOOKUP function returns the salary of Mia Clark (first instance). Use INDEX, MATCH and EXACT in Excel to perform a case-sensitive lookup.

Multiple Criteria

Do you want to look up a value based on multiple criteria? Use INDEX and MATCH in Excel to perform a two-column lookup.

Note: the array formula above looks up the salary of James Clark, not James Smith, not James Anderson.

#N/A error

If the VLOOKUP function cannot find a match, it returns a #N/A error.

1. For example, the VLOOKUP function below cannot find the value 28 in the leftmost column.

2. If you like, you can use the IFNA function to replace the #N/A error with a friendly message.

Note: the IFNA function was introduced in Excel 2013. If you're using Excel 2010 or Excel 2007, simply replace IFNA with IFERROR. Remember, the IFERROR function catches other errors as well. For example, the #NAME? error if you accidentally misspell the word VLOOKUP.

Multiple Lookup Tables

When using the VLOOKUP function in Excel, you can have multiple lookup tables. You can use the IF function to check whether a condition is met, and return one lookup table if TRUE and another lookup table if FALSE.

1. Create two named ranges: Table1 and Table2.

2. Select cell E4 and enter the VLOOKUP function shown below.

Explanation: the bonus depends on the market (UK or USA) and the sales amount. The second argument of the VLOOKUP function does the trick. If UK, the VLOOKUP function uses Table1, if USA, the VLOOKUP function uses Table2. Set the fourth argument of the VLOOKUP function to TRUE to return an approximate match.

3. Press Enter.

4. Select cell E4, click on the lower right corner of cell E4 and drag it down to cell E10.

Note: for example, Walker receives a bonus of $1,500. Because we're using named ranges, we can easily copy this VLOOKUP function to the other cells without worrying about cell references.

Index and Match

Instead of using VLOOKUP, use INDEX and MATCH. To perform advanced lookups, you'll need INDEX and MATCH. Maybe this is one step too far for you at this stage, but it shows you one of the many other powerful formulas Excel has to offer.

How Do I Use Excel In Canvas

Xlookup

How Do I Use Excel 2019

If you have Excel 365, use XLOOKUP instead of VLOOKUP. The XLOOKUP function is easier to use and has some additional advantages.