How to Use Formulas Across Sheets in Excel

There are tons of ways to use data from different sheets in Microsoft Excel. Depending on what kind of formulas you need to use and what you’re trying to accomplish, one method might be better than another.  

The two methods we discuss below are the two most straightforward ways to use data from different sheets in Microsoft Excel. There are far more powerful options that work better in different scenarios. If you want to learn more, take Simona Millham’s Excel course. Simona is an Excel guru who makes learning Excel easy! 

An Overview of Using Formulas in Excel

In this video, CBT Nuggets trainer Simona Millham walks you through how formulas can be used in Excel sheets. This short, nine-minute video will help you get started—or you can read on for more info. 

Point and Click Method

Using the point-and-click method in Excel is the easiest way to build formulas in Excel. Here’s how to do it:  

First, click and select the cell in Microsoft Excel where your formula will live. Then enter the equal (=) sign as the first value in that cell. Excel is smart enough to understand that you’re trying to create a formula in that cell. So, it will switch context accordingly. At this point, each cell you click will be added to your formula. 

Select a different sheet in your worksheet. Then click and select cell A1. Look at the formula bar in Excel. You should see ‘=sheetName!A1’ where ‘sheetName’ is the name of the sheet you selected. Take note of that syntax. We’ll discuss that syntax more below. 

Add the math operator you need between each cell you click and select. For example, if your Microsoft Excel worksheet has three sheets all using Microsoft’s standard naming schema, and you want to add the values from cell A1 together from sheets 2-4, your formula on sheet 1 will look like this: 

=sheet2!A1 + sheet3!A1 + sheet4!A1

That formula will add and display the value from cell A1 in sheets 2-4 in sheet 1. 

If you need to multiply the sum of cell A1 from sheet2 and sheet3 by the value of cell A1 in sheet4, your formula will look like this:

=(sheet2!A1+ sheet3!A1) * sheet4!A1

That formula looks a little different. Excel follows the order of operations. So, you need to surround the pointers for sheet2 and sheet3 in parentheses like a typical Algebra equation. This instructs Excel to sum the values from sheet2 and sheet3 before doing any multiplication. 

Of course, it may be easier to write that formula by hand instead of pointing and clicking values from different cells. Let’s discuss how to do that in the next section. 

Using Data From Different Sheets in Microsoft Excel By Hand

In the section above, I mentioned taking note of how Excel combines cells from different sheets into the formula bar. For example, pointers in Microsoft Excel that reference data from other sheets in formulas always start with the sheet name followed by an exclamation mark and the cell number. For example, if you have a sheet labeled ‘March’ in Excel and want to reference the data in cell B12, the pointer in your formula will look like this:

=March!B12

Easy enough, right? For practice, try writing a formula that sums the values of cell B13 from sheet 5, sheet 8, and sheet 12 (using the standard naming schema).

Once done, your formula should look like this:

=Sum(sheet5!B13,sheet8!B13,sheet12!B13)

Easy enough. What happens if one of your sheets has a space in the name, however? We may not always use one-word descriptors in our Microsoft Excel sheets. 

In this case, add apostrophes around each sheet name:

=Sum(‘hotdog sales’!A1,’hamburger sales’!A1,’soda sales’!A1)

Before diving into multi-sheet projects, consider using CBT Nuggets' IT Value Calculator to determine the ROI of developing advanced Excel skills for your organization.

Frequently Asked Questions

How Do I Reference a Cell From Another Sheet in Excel?

Use the sheet name followed by an exclamation mark and cell reference: =Sheet2!A1. If the sheet name has spaces in it, wrap it in apostrophes: ='Sheet Name'!A1.

What Is the Syntax for Excel Cross-Sheet Formulas?

The basic syntax is SheetName!CellReference. For ranges, use SheetName!A1:C10. Always include the exclamation mark between the sheet name and cell reference.

How Do I Sum Data From Multiple Sheets in Excel?

Use the SUM function with multiple sheet references: =SUM(Sheet1!A1,Sheet2!A1,Sheet3!A1) or for consecutive sheets: =SUM(Sheet1:Sheet3!A1).

Can I Use VLOOKUP Across Different Excel Sheets?

Yes, VLOOKUP works across sheets: =VLOOKUP(lookup_value,Sheet2!table_array,col_index_num,FALSE). The table array must include the sheet name reference.

How Do I Handle Errors in Cross-Sheet Formulas?

Use IFERROR to manage missing data: =IFERROR(Sheet2!A1,"Data Not Found"). This prevents #REF! errors when sheets are deleted or renamed.

Final Thoughts

Excel is a powerful tool. We’ve discussed two ways to use data from different sheets together in Microsoft Excel. 

There is far more to learn, however. For instance, if you routinely work with multiple sheets in Excel, the Consolidate feature is also handy. That feature can also calculate formulas using data across multiple sheets. However, the data it creates may not look like how data is displayed when entering a formula by hand.  

Another useful but often misunderstood feature is pivot tables. Pivot tables make calculating and organizing tons of data in Excel as easy as cake. They can be tricky to use, however. 

To learn more, sign up for CBT Nuggets and explore our Excel courses.