Copying is a common task in Excel. This technique applies to most things in Excel form cells and range to charts, images and sheets.
It also works in Word and PowerPoint.
Have you used the mouse and keyboard together? It is time to start.
A spill range is the result of a dynamic array formula. At the moment that requires the subscription version of Excel.
Imported data often has missing entries you need to populate.
You can use Power Query, but that duplicates the table.
This technique works on the existing table and is quick and easy to apply once mastered.
Start the clock!
It is common to display a blank cell using the IF function and “”. A problem can arise when you want to use that IF formula in a calculation. Here is an easy way to cope.
In the previous video I removed duplicates, in this video we identify duplicates using Conditional Formatting.
I identify the duplicates twice in a minute in this video.
The clock is ticking.
In Excel your goal should be to have a single formula in a table column that can be copied down the whole column.
Aikido instructor George Leonard on mastery:
”How long will it take me to master Aikido?” a prospective student asks.
“How long do you expect to live?” is the only respectable response.
From James Clear newsletter.
(I think you can replace (Ctrl + H) Aikido with Excel.)
I have started a new series of short videos showcasing Excel’s features that work in less than a minute.
There is a countdown to see if I can do it.
In this first one I remove duplicates in less than a minute.
The new XLOOKUP function has the ability to spill when you select multiple columns to extract. Even when you do, it doesn’t always spill across.