Now that we’re more familiar with tabs, watch Excel Video 249 to write formulas that reference data on other tabs. As you watch how to write formulas to reference data on other tabs, one of the biggest takeaways is that I don’t get too worked up about Excel syntax for referencing other tabs and spreadsheets. I start a formula with = and then click on the cell I want to reference. Excel will fill in the brackets, exclamation marks, and single quotes as needed.
The syntax is that Excel references data on another tab in the same workbook by putting an exclamation mark between the tab name and the cell reference. If the data is in another workbook, the name of the workbook goes in brackets, followed by the tab name, followed by an exclamation mark, followed by the cell reference. If either the workbook name or the tab name has a space (or some other characters), Excel puts the name in single quotes. All the more reason to just click on the cell you want to reference and let Excel do the work.
Writing these formulas is a great way to manage your data. You can put all of the summary information, critical data, and highlights on one tab, all supported by data in other tabs and worksheets. By using these formulas, you only have to update the data tabs and the summary tab can update automatically.
Finally, watch for my warning about linking to data in other workbooks. If the location of your spreadsheet or the outside workbook changes, your links can break. If you have outside data you’re referencing consistently, you might choose to put the outside data on a tab in your workbook so you don’t have to deal with broken links, especially if that outside data doesn’t change very often.
