Write Excel formulas that refer to multiple worksheets

Learn how to create formulas in Excel that link to cells in multiple worksheets – or other workbooks. Then, if a value in one of your linked cells changes, your formula updates to reflect that change!

In this Excel video lesson we started by creating a template which we copied to create multiple worksheets (for Jan, Feb, Mar, etc.)

Let’s write a formula to SUM the sales from the Jan, Feb & Mar worksheets to create our 1st Quarter Summary Report. We will also review the Data, Consolidation method to accomplish the same goal.

Here are the steps to follow in this Excel Video Lesson:

For Summary or Consolidation reports it is best to start with a template.
In Cell D4 of the “1st Quarter…” worksheet we type “=sum(” and then click the worksheet tab for “January” and select cell D4.
Hold down the “Shift” key and click the March worksheet tab.
Notice that in the Formula Bar, Excel has selected the range from January:March for cell D4.
After you complete the formula with a “)” you can copy the formula to the other cells in the report.
Data, Consolidation is easy to calculate if you have organized your data based on a Template. You are consolidating “by Position.”