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.
Category Archives: Excel Blog
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.
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.
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.
Excel Formula Week Starting with a Monday
The WEEKDAY function has options
Let’s say you have a weekly roster. You have a date and you need to identify the Monday start date for the week that contains that date. The formula is fairly easy.
Reduce typing with variables in Excel VBA
Use a Constant variable
Sometimes when writing code you use the same text string in multiple places in your code. To make things easier you should capture that text string in a variable.
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.
Counting Entries in Excel
SUMPRODUCT solution
Excel has a few counting functions. But when it comes to counting entries in a cell it can be difficult if you are using formulas that return a blank cell. This is where the SUMPRODUCT function can come to the rescue.
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.
First and Last Cell Address in a Range
Following on from my last two VBA posts here is how you can identify the cell addresses of the first and last cells in a range.
VBA to Find the Row and Column Number Limits of a Range
First and last row and column numbers
In my last blog post I found the last used row and column numbers on the active sheet. This post lets us find the row and column extremities of a specific range.
Excel VBA to Find the Last Used Row or Column
UsedRange used wisely
When writing VBA code it is common to need to know the last used row and/or the last used column. These values provide the edges of the sheet contents.
Controlling a Conditional Format with a Checkbox
Turn it off and on
Here is a technique that allows you to turn off and turn on the conditional format without actually removing the conditional format. You may want to do this to print a sheet without the conditional formats being applied.
Change in Ctrl Key Selections
Latest versions
Do you use the Ctrl key and the mouse to select multiple ranges? Well you may want to watch this short video.
Excel VBA to Remove Filters
Its a one liner
When creating macros that work with filters it is a good idea to remove filters at the beginning of the macro code. Here is how you do that.
Missing Sheet Gridlines in Excel
Its a format
Ever seen a sheet with what looks like missing gridlines? Wondered what happened? The issue is a format.
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.
Filling a large range in Excel with a series [Video]
Keyboard and mouse shortcuts
Let’s assume you need to fill a vertical range with all the whole numbers from 1 to 100. There are lots of ways to do this, but I think the keyboard could be the quickest.