Excel - Cntl+T Table Relative or Absolute?: Episode 1635
2,351 views · Published 31 January 2013 · 4:38 · Indexed 29 October 2026
Channel: MrExcel.com · 2013 · Education
Microsoft Excel Tutorial: Ctrl+T Tables: Are the Formulas Relative or Absolute? | Excel Tips & Tricks. Welcome to episode 1635 of the MrExcel netcast, where we dive into all things Excel. In this episode, we'll be discussing a common question that came up during my recent sessions at Trainertage in Lucerne, Switzerland. The question is, when using Ctrl + T tables, are the formulas relative or absolute? First off, let me just say that Trainertage was an amazing experience. I had the opportunity to meet some incredible people and learn some new Excel tricks that I can't wait to share with you all. If you want to see some of the fun we had, check out the video at vimeo.com/58590184. And yes, I may have been speaking in "slow English" during the video, but hey, it was all in good fun. Now, back to the topic at hand. During one of the sessions, Dietmar Gieringer brought up a great point about the relative nature of formulas in tables. As you may know, when using a regular range, the formula will automatically adjust when dragged to a different column. But what about when using a table? Dietmar showed us that even though the table nomenclature may seem like it's hard-coding the formula, it actually still works as a relative formula. But what if we need to call out specific months in our formula? This is where the INDIRECT function comes in. By using the new Excel 2013 formula text =FORMULATEXT(), we can see the formula in a different cell while still being able to build a formula in another cell. This allows us to use the INDIRECT function to create a table reference that points to a specific column. And the best part? It still works as a relative formula. I was so impressed by this trick that I had to share it with all of you. And while we're on the topic of sharing, I also wanted to mention Dietmar's new book, "Microsoft Excel 2010". I may not be able to read German, but just from the screenshots in the book, I can tell that it's full of great ideas and tips that I can't wait to try out. So if you're looking for some new Excel resources, be sure to check it out on Amazon.de. And finally, I just want to say a big thank you to Dietmar and everyone at Trainertage for such a fantastic experience. If you ever have the chance to attend Trainertage in Lucerne, Switzerland, I highly recommend it. And as always, thank you for tuning in to the MrExcel netcast. Be sure to subscribe and stay tuned for more Excel tips and tricks. See you next time! Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/ You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/ Table of Contents: (00:00) Demonstrating a trick learned at Trainertage (01:10) Using relative formulas in a table (02:00) Using INDIRECT to call out specific months in a table (03:08) Building an INDIRECT formula using table nomenclature (03:54) Recommendation for Dietmar Gieringer's new book on Excel (04:04) Clicking Like really helps the algorithm #excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial This video answers these common search terms: AVERAGE function with table nomenclature Building formulas with INDIRECT Ctrl + T table relative or absolute? Excel book on amazon.de Excel tricks at Trainertage New book by Dietmar Gieringer Relative nature of formulas Trainertage in Lucerne, Switzerland Using Ctrl + T tables Using INDIRECT function in Excel 2013 Using tables and formulas Vimeo video: 58590184 Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153450/ Today Bill brings us the second Podcast in the "Things that I Learned at Trainertage" Series. [To see the first episode in this new series: "Dynamic Range Without OFFSET" Podcast #1632] "Ctrl+T Tables; Relative or Absolute" Today is another trick from Trainertage 2013: Dietmar Gieringer was showing how to make a Table Reference Absolute using INDIRECT. I was surprised that a Table Formula is Absolute when copied across the Columns. Follow along With Episode #1635 as Bill shows us both styles. ...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! http://www.mrexcel.com/learn2010/LE2010.html "The Learn Excel from MrExcel Podcast Series" Visit us: MrExcel.com for all of your Microsoft Excel Needs!
More from this channel
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920