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

Watch on YouTube

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