There are various 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. Here are two options.

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 might work better in different scenarios. If you want to learn more, take a look at Simona Millham’s Excel course.

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.

Want to Learn Excel Today?

If you’re ready to learn how to use Excel as a powerful no-code app platform, you need to watch Simona Millham’s Microsoft Excel training at CBT Nuggets. Simona is an Excel guru with years of experience. She makes learning Excel easy. Sign up for a free 7-day trial now and get started! 

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 parenthesis 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 said to take notice of how Excel adds 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)

Final Thoughts

Excel is a very 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 easy as cake. They can be tricky to use, however.