Excel’s Format as Table feature on the Home ribbon has many advantages. One advantage that isn’t mentioned much is the automatic extension of Data Validations.
One of the cardinal rules of Excel is don’t key-in a value into a formula if that value could change. Tracking the value down could be problematic if you need to change the value. Tables can be the solution to avoiding keyed-in values.
Here’s another way to create a Step Chart. This one is quicker. I wrote previously about using a scatter plot and error bars but it required a lot of chart changes. This one hacks a line chart and requires no chart changes.
Let’s say you have a filtered list and in each of the filtered cells you want to enter a sequential number, but in the hidden rows you don’t want to enter anything. There is a way, but it takes a few steps.
Let’s assume you have a large table that you are filtering. Based on the current filter you want to work out the earliest date and the latest date. You may be surprised to learn the SUBTOTAL function can help you.
Let’s assume you have three state codes and four department codes and you want to create a table of all the possible 12 combinations (3 x 4). How do you do it so that it is flexible? i.e. if you add a new state or department it must be easy to update the combination table.
Formatted Tables are great but there is an issue when it comes to copying formula that use the table names (Structured References). There are two techniques that cope with this limitation.
Here’s the problem, we have four separate tables with the same layout. They hold four different metrics: Actuals, Budget, Forecast and Last Year. A column called Type is used to hold the metric name. We need to populate the Type column.
Formatted tables are a great feature in Excel. They were expanded and renamed in Excel 2007 (formerly called Lists) and their use offers many advantages.
When you use date grouping (by months) in one Pivot Table report it affects other Pivot Table’s date reporting from the same data. There is a work around to allow you to have daily, monthly and quarterly Pivot Table reports.
When filtering in Excel you will be shown all the entries in a field. Unfortunately, if there are hundreds of unique items this can mean a lot scrolling to select the correct item to filter by.
The Format As Table feature has many useful features that are worth taking advantage of. The previous post listed them. The video of this blog is shown at the bottom of the post.
Excel 2007 updated a little used feature of Excel 2003 called Lists to create the functionality behind the Format As Table icon on the Home Ribbon tab.