Excel - Table Schmable, I Want Results!: Episode 1482

1,261 views · Published 1 December 2011 · 1:52 · Indexed 4 November 2026

Channel: MrExcel.com · 2011 · Education

Watch on YouTube

Microsoft Excel Tutorial: Copying Formulas in Excel Tables: How to Solve the Common Issue | MrExcel Podcast.

Welcome to another episode of the MrExcel Podcast. In today's episode, we will be discussing a common issue that many Excel users face when working with tables. Our question comes from Ari, who has encountered some difficulties while trying to copy formulas across columns in a table.

Tables, introduced in Excel 2007, offer great formatting and filter options. However, when it comes to copying formulas, the table nomenclature can be quite frustrating. As Ari has discovered, simply copying the formula across columns will result in an error. So, what's the solution?

Well, the first option is to manually enter the formula without letting Excel build the reference for you. This means typing out the entire formula, without using the mouse or arrow keys. While this may seem like a hassle, it is a reliable way to ensure that the formula will copy correctly.

Another option is to convert the table back to a normal range. This can be done by disabling Table Design and then selecting "Convert to Range". This will keep the formatting intact, but allow you to enter formulas without any issues. Simply enter the formula as you normally would, and it will copy across columns without any problems.

So, why does Excel automatically convert data into a table if it causes these issues? If the data does not require the features of a table, it is best to convert it back to a range. This will save you time and frustration in the long run. Thank you for tuning in to this episode of the MrExcel Podcast. Be sure to join us next time for more helpful tips and tricks.

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) Table Schmable
(00:11) Issue with Table Formatting in Excel 2007
(00:21) Annoyance with Table Nomenclature in Formulas
(00:33) Solution: Manually Build Formula References
(00:43) Alternative Solution: Convert Table to Range
(01:03) Hassle of Manually Building Formulas
(01:14) Benefits of Converting Table to Range
(01:24) 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:
Converting Excel table back to a range
Converting Excel table to a range
Copying formulas in Excel tables
Disabling Table Design in Excel
Excel 2007 table formatting
Excel filter dropdowns
Excel formula copying
Excel statistical analysis
Excel table nomenclature
Formula referencing in Excel


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153160/


Today Yari is frustrated that his Formulas pointing into a Table (with a capital T) will not copy across the Columns. Yari wants totals and he isn't getting them. In Episode #1482, Bill explains why Yari isn't getting his totals and then shows us two methods that will display totals for us.

...This episode 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!

"The Learn Excel from MrExcel Podcast Series"

MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!

More from this channel