Excel - Table Functionality: Tips to Control Formulas & Avoid Unexpected Changes! - Episode 728

Microsoft Excel Tutorial: Table Functionality: Tips to Control Formulas & Avoid Unexpected Changes!

Welcome back to the MrExcel netcast! In today's episode, we're diving into Excel 2007 and taking a closer look at the "format as a table" feature. I recently received an email from Jim, who was having some trouble with this feature, so I thought it would be helpful to revisit it and provide some tips and tricks for using it effectively.

To format a table in Excel 2007, you can either hit Ctrl T or go to the insert tab and choose "table". This will define the area of your table and automatically apply some useful features, such as the green bar format and filter dropdowns at the top. You can also easily change the format of your table by selecting from 54 different patterns.

However, one thing that can be frustrating is when you add a new column to the table. For example, let's say we add a column for "GP percent", which calculates profit divided by revenue. The formula will automatically be copied down the entire column, which can be convenient but also problematic if you want to make changes to individual cells. If you change one cell, the entire column will be affected. To avoid this, keep an eye out for the lightning bolt symbol and use the "undo calculated column" option to revert back to the original formula.

Another solution is to convert the table back to a normal range by selecting the "convert to range" option under the table tools. This will remove the automatic formula extension and allow you to make changes to individual cells without affecting the entire column. However, you will lose the formatting and features of the table, so it's important to weigh the pros and cons before making this decision.

In conclusion, the table functionality in Excel 2007 can be a useful tool, but it's important to be aware of its potential pitfalls. Keep an eye out for the lightning bolt symbol and use the "undo calculated column" option to avoid unexpected changes to your formulas. And if you prefer the convenience of calculated columns but don't want to deal with the automatic extension, consider converting the table back to a normal range. Thanks for tuning in to this netcast from MrExcel, and I'll see you next time for more Excel 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/


Excel 2007's table functionality is cool, but viewer Jim is wondering why Excel is changing his formulas. In Episode 728, we will take a look at how to control this behavior,

Table of Contents:
(00:00) Introduction to Excel 2007 and email from Jim
(00:20) Format as a table in Excel 2007
(00:28) How to define an area as a table
(00:36) Features of a table: green bar format and filter dropdowns
(00:49) Changing the format of a table
(01:07) Automatic copying of formulas in a table
(01:30) How to undo a calculated column in a table
(01:56) Potential problems with calculated columns in a table
(02:05) Converting a table back to a normal range
(02:32) Tips for dealing with the new table functionality in Excel 2007
(02:36) 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:
Adding a new column to a table in Excel 2007
Changing the format of a table in Excel 2007
Converting a table back to a normal range in Excel 2007
Dealing with calculated columns in Excel 2007 table
Excel 2007 format as a table
Formula calculation in Excel 2007 table
How to use Ctrl T or insert tab to create a table in Excel 2007
Inconsistent calculated formula in Excel 2007 table
MrExcel netcast on Excel 2007 table features
Overriding formulas in an Excel 2007 table
Tips for using the table functionality in Excel 2007
Undoing calculated column changes in Excel 2007 table


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