Simplify the creation and readability of certain formulas by using cell references that refer to an entire row or entire column. Not only are the formulas easier to understand, they also work ideally with lists of data that are frequently changing, because there's no need to re-write the formulas.
- [Voiceover] We're looking at a worksheet called Row…References and just to the right of it…is another worksheet called Column References.…And what we're about to show you in both of these sheets…is quick ways to write formulas…that deal with entire rows or entire columns.…In the Row References worksheet we've got some data…from rows one through 14 categorized by city…for the various items that were being sold.…And we want to know how many cartons have been sold…in the East.…Now the two Eastern cities are Boston and New York.…There's no other data in rows two and three,…except the numbers that we see…and the actual labels in cells A2 and A3.…
So I'm about to add up these numbers.…I'll simply press the auto-sum button…either on the right side of the home tab…or left side of the formulas tab.…Click auto-sum ignore what excel is trying to do…and drag across rows two and three.…Using the left mouse button, just dragging down those two.…And look at the notation…equal sum left parenthesis two colon three.…That refers to row numbers two and three.…
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: Use entire row/column references in formulas