Excel’s Conditional Formatting feature has a built-in unique option. Its unique option only identifies entries that are not repeated. This is different to the Advanced Filter Unique option which lists each unique item from a range once. To filter by entries only appearing once you can use Conditional Formatting with filtering. No formulas required.
Monthly Archives: February 2019
Free Excel Webinar Recording – Format As Table Features
Feedback score 93%
In February 2019 I demonstrated how to use the Format as Table feature in Excel, including some advanced techniques.
Many of Excel’s features and functions work seamlessly with formatted tables. They can help you improve the structure and reliability of your spreadsheet files.
Formatted tables can allow you to create powerful reports like those in a relational databases.
Topics covered
- advantages and limitations of formatted tables
- keyboard shortcuts
- using formatted tables with formulas
- solutions to some of the limitations of formatted tables
- using range names with formatted tables
- using formatted tables with data validations
- creating a running total
- using PivotTables
- Relationships (Data tab)
As always there will are a few more tips and tricks shared in the session.
PivotTable Grouping
Create your own categories
Grouping of dates in Excel’s PivotTables is fairly common and in the most recent versions of Excel, automatic. Many people don’t realise that you can perform other types of grouping in Excel.
Allocate based on start date and number of months
EDATE to the rescue
Let’s say you want to allocate a value across multiple months based on a start date and how many months you want to allocate. The monthly allocation will be averaged based on the number of months. The solution isn’t that hard.