Formatted Table Running Total Custom Function
I have shared a running total formula for a formatted table many years ago. Here is a better solution that I have converted into a custom function.
(more…)Format as Table Extends Data Validation
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. (more…)
Avoiding keyed in values in Excel formulas
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. (more…)
An Easier Step Chart
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. (more…)
Sequential numbers in a filtered list
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. (more…)
Find the first and last dates in a filtered list
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. (more…)
![Read more about the article Creating a table of all combinations in Excel [Video]](https://a4accounting.com.au/wp-content/uploads/2018/01/vimeo-250571093.jpg)
Creating a table of all combinations in Excel [Video]
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. (more…)
How to Handle a Formatted Table Limitation
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. (more…)
Automatically Populating a Column with Text in a Formatted Table
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. (more…)
Pasting Data to the Bottom of a Formatted Table
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. (more…)
Pivot Tables and Different Date Grouping
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. (more…)
Excel 2010 Search Filter Option
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. (more…)
Excel Format as Table Part 2 [VIDEO]
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.
(more…)Format As Table in Excel – Part 1
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. (more…)