Look Who it Was Random video

Excel - Table Formulas: Automate Your Data Analysis with this Game-Changing Feature! - Episode 423

382 views · Published 24 September 2009 · 2:52 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: Table Formulas: Automate Your Data Analysis with this Game-Changing Feature!

Welcome back to the MrExcel netcast! In just 14 days, Excel 2007 will be hitting store shelves. As we continue our 2007 Tuesday series, we're diving into the new table functionality. In our last two podcasts, we showed you how to set up a table and some formatting tricks. But today, we're going to talk about the real game-changer: table formulas.

One of the biggest headaches with formulas is when you have a large dataset and someone accidentally messes up a formula in one of the rows. With the new table functionality, this is no longer a problem. I have a table set up with data in columns A, B, C, and D. When I add a new column for Profit, the table automatically extends and the formula is automatically copied down to all the rows. No more worrying about errors in individual cells!

But what's really amazing is the new table nomenclature for building formulas. Instead of referencing specific cells, we use the table name and the column headings. For example, if I want to calculate Profit as Sales minus Cost of Goods Sold, the formula would be =Table2[[This Row],[Sales]]-Table2[[This Row],[COGS]]. And the best part? This formula is automatically copied down to all the rows in the table.

But what if you need a different formula for a specific row? No problem. You can use the AutoCorrect button or simply enter a different formula in that cell. And if you're working outside of the table, you can still use the traditional cell references. But let's be real, the new table formulas are a game-changer and will save you so much time and frustration. So why not take advantage of this amazing functionality?

Thanks for tuning in to another netcast from MrExcel. Don't forget to subscribe and hit that notification bell so you never miss an update. And if you found this video helpful, give it a thumbs up and leave a comment below. 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) Introduction to Excel 2007 table functionality
(00:22) Overview of new table features
(00:33) Automatic formula handling in tables
(01:00) Using the new table nomenclature for formulas
(01:34) How to customize formulas in tables
(01:44) Using the AutoCorrect button for calculated columns
(01:57) Traditional formulas still work outside of tables
(02:12) Automatic copying of formulas in tables
(02:32) 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:
AutoCorrect button in Excel tables
Automatic copying of formulas in Excel tables
Automatic formula copying in Excel tables
Benefits of using tables in Excel
Building normal formulas in Excel tables
Copying formulas in Excel tables
Creating new formulas in Excel tables
Excel 2007 table functionality
Formula handling in Excel tables
Table nomenclature for building formulas in Excel
Undoing calculated columns in Excel tables
Using formulas outside of Excel tables

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



One of the best features of the new Excel 2007 table functionality is the new table formulas that Excel can create. Once you enter a formula in the first row of a dataset, Excel 2007 will automatically copy the formula down to all of the rows. Episode 423 shows you the details.

This blog is the video netcast companion to the upcoming book, Excel 2007 Miracles Made Easy. Download a new two minute video every Tuesday and Thursday to learn one of the tips from the book!

More from this channel