You can create formulas to gather data from multiple worksheets, either with a lot of painstaking worksheet and cell selection steps or with techniques that let you select multiple sheets efficiently. 3D formulas are powerful, easy to set up, and are easily expandable as you add more worksheets.
- [Voiceover] In this movie, we're looking at a…different workbook, 02-Regional Sales.…It's got four sheets named after states,…the California sheet is visible at first.…As I click the Texas sheet,…notice that the layout is identical.…Numbers are different, same thing with Florida and New York.…And on sheet one here we might want to get…some totals from those sheets.…So for cell B2, I want to get the California total.…If you've not worked with formulas…that gather data from different sheets,…either from the same workbook or different workbooks,…keep in mind you can write these very easily…and nearly always you do not want to be typing sheet names.…
I'm going to type equal and click the California sheet,…and then click cell F6, that has…the total in it that I want and Return.…The formula as I double-click cell B2 looks like this.…Sheet names are always followed by exclamation points…but don't type these, it's just an opportunity for a…typing mistake and it's much easier to click.…I'll do one for Texas, equal, click the Texas sheet,…
Author
Dennis Taylor
Released
5/26/2016- Understanding how the hierarchy of operators affects formula results
- Knowing when to use absolute vs. relative references
- Using Formula Builder for unfamiliar functions
- Displaying a worksheet's formulas and highlighting formula cells
- Converting formulas to values
- Creating 3D formulas to gather data from multiple sheets
- Creating and expanding nested IF functions; using AND, OR, and NOT with IF
- Looking up information with VLOOKUP, HLOOKUP, MATCH, and INDEX
- Analyzing data with the statistical functions: MEDIAN, MODE, etc.
- Using the power functions: COUNTIF, SUMIF, COUNTIFS, AVERAGEIFS, and more
- Calculating financial data such as payments
- Performing basic math
- Calculating dates and times
- Editing text with functions
- Using array formulas and functions
- Referencing data in other cells and files
- Extracting information from Excel worksheets
Skill Level Intermediate
Duration
Views
-
Introduction
-
Welcome50s
-
Exercise files25s
-
-
1. Formula and Function Tools
-
2. Formula and Function Tips and Shortcuts
-
3. IF and Related Functions
-
4. Lookup and Reference Functions
-
Use MATCH and INDEX together4m 40s
-
5. Statistical Functions
-
6. Power Functions
-
7. Selected Financial Functions
-
8. Math Functions
-
9. Date and Time Functions
-
10. Text Functions
-
11. Array Formulas and Functions
-
12. Reference Functions
-
13. Information Functions
-
Conclusion
-
Next steps25s
-
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: Create 3D formulas that gather data from multiple sheets