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.
Tag Archives: data
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.
Improved Data Structure
Tables rule
I ran a webinar in January 2019 where I presented and explained a budget challenge file I had submitted in November 2018. I mentioned during the session that I didn’t like the layout of the Data tab. Well someone asked how should it look? So here is how I would have arranged it.
Free Excel Webinar Recording – Budget Challenge
Feedback score 93%
In January 2019 I presented a webinar that examined a solution to a 4 dimension budget challenge. Download the materials using the button below and watch the video.
NOTE: This is not a beginner’s session.
This webinar is based on a budget scenario which you need to read before the session starts. It is only 3 pages long and included in the materials.
Topics covered include
- using INDEX-MATCH (better alternative to VLOOKUP)
- 3-D formulas and techniques to make using them easy
- using a reporting template
- validations
- extracting sheet names
- automating reports
As always there will are a few more tips and tricks shared in the session.
Data Validation Shortcut
Two options
The Data Validation dialog allows you to limit or control what a user enters into a cell. There are two keyboard shortcuts to open it.
Extracting entries from a slicer
A simple hack
There is a complicated way to extract the entries from a slicer but there is also an easy way to do it.
Sort by value ignoring the sign
An absolutely simple function to the rescue
I recently received an unusual request about sorting. They wanted to sort in ascending order but they wanted to ignore the sign of the values. So -44 would be next to 44.
Distinct Count in Excel
The Data Model to the rescue
Counting is the poor cousin to summing in Excel. Not many people count things, but everyone adds up things. There is a special sort of count that can be useful. A distinct count counts unique entries and is hard to do with a formula. If you have Excel 2013 or a later version you can use a PivotTable to perform a distinct count.
Updates to Pivot Tables
Time-saving improvements
If you have the latest Excel version or the subscription version, you may have noticed some refreshing improvements to PivotTables.
Running Total in a Formatted Table
Using the SUM function
Formatted Tables allow you to create formulas that automatically copy down as the table expands. To create a running total in a column you have a couple of options.
Related Tables and PivotTables
Don't Panic!
When you are building a PivotTable based on two related tables you may see unusual layouts that don’t make sense. Don’t worry, when you add values to the table all will be fixed.
Selecting All with Ctrl + A
Some other useful techniques
Many people know that you can select the whole sheet with Ctrl + A but there are lots of other selections it can perform.
An Easier Step Chart
A Line chart solution
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.
Sequential numbers in a filtered list
A formula solution
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.
Inserting a Blank Row Between Entries
Manual technique
Over the years I have had many requests to help people insert blank rows between entries is a list. Apparently it is for an input routine that requires blanks. My normal solution is a macro because it automates the process, but there is a manual technique that is quick and easy.
Entering Headings via a Macro in Excel
Handle a range in one go
Let’s say you are creating a new table in a new sheet using a macro and you need to create the headings in row 1. There is a reasonably easy way to do it.
PivotTable Combinations Trick
How to get a list of combinations
I did a recent post on using Power Query to create an all combinations list. I found another technique on chandoo.org to create that list using a PivotTable.
PivotTable Grouping Doesn’t Work
Rogue data the reason
Grouping is a powerful feature in PivotTable reports but sometimes Excel won’t let you apply grouping. There are a few reasons for this.
Identify Milestone Years in Excel
MOD function to the rescue
Recently a client wanted help in summarising a large data list of employees. They wanted to identify the years of service in terms of 5, 10, 15 years and other milestone years based on a start date.