Yes, you can create a cell drop down without Data Validation. It uses a built-in technique and is flexible.
One Minute To Excel #5 – Extracting Unique Entries
Three techniques
In previous videos I worked with duplicated entries.
In this video I demonstrate how to extract unique entries from a list.
Three different ways in 60 seconds.
Off we go!
Related Posts
One Minute to Excel #4 – Copy Anything
Mouse and keyboard combined
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.
Let’s go.
How to Refer to a Column in a Spill Range
INDEX rules
A spill range is the result of a dynamic array formula. At the moment that requires the subscription version of Excel.
One Minute to Excel #3 – Filling in the blanks
No blank looks
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!
Handling Formula-Based Blanks
The N function to the rescue
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.
One Minute to Excel #2 – Identify Duplicates
Short, sharp video tips
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.
Single formula for a Column
It can done
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.)
James Clear
One Minute to Excel #1 – Remove Duplicates
Working against the clock
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.
XLOOKUP Doesn’t Always Spill
But it can
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.
Removing Greyed-Out Slicer Options in Excel
How to tell the Slicer to forget
Sometimes Slicers seem to have a long memory and list entries that are no longer in the current data set. There is a setting to fix this.
Greyed Out AutoSum Icon In Excel
Get around a sheet protection issue
When you protect a sheet in Excel many icons are turned off (greyed out), including the ever popular AutoSum icon. That’s when it pays to know keyboard shortcuts.
Possible Dynamic Array Bug
Values issue
Recently I have been using more dynamic array formulas and have come across an unusual situation where a date is not treated like a date.
Remember dynamic arrays are currently only available in the subscription version of Excel.
Have a look at the image below.
You can download the example file at the button at the bottom of the post if you want to see it in practice.
Cell B1 is an input cell to set the first date of the sequence.
Cell E1 creates sequential numbers using the SEQUENCE function.
Row 2 has the dates and is formatted as a date.
Row 3 is the problem. If I try calculate the number of calendar days in the month using DAY and EOMONTH functions it gives a value error as if the entry in E3 is not a date.
If I multiply the E2# reference by 1 it fixes the issue as per row 4, but I shouldn’t have to do that.
I have had this is other cases where a number is not treated a number until you multiply it by 1 or perform a calculation with it.
Not sure if anyone else has seen this?
If you want to learn more about dynamic arrays I have a free Webinar Recording plus a pdf manual at the link below.
Free Introduction to Dynamic Arrays Course
Related Posts
Treating text as zero in Excel
Let’s say you are getting inputs you can’t control and in some cases you get text and others you get numbers. You want the numbers, but you need to treat text as zero. Here’s the easy way to do that.
On Writing Well (Harper Colslns)
This is a classic that I have just discovered. The book was written many years ago, but has been updated.
It focuses on non-fiction writing and has lots of great guidelines plus great advice.
My favorite piece of advice was – if you are having difficulty with re-writing a sentence, try deleting it and see what the effect is.
The basic tenet of the book is that writing is re-writing. No one gets it right first time, so be prepared for re-writing and re-writing.
I know from personal experience that I can improve existing writing – but a blank page is hard to edit.
It has specialist sections on most popular types of non-fiction writing. I skipped a few that I don’t write about e.g. Sport.
Highly recommend.
If you are interest in fiction writing check out Stephen King’s (yes THE Stephen King) classic – On Writing.
Sequences of a Repeating Series of Numbers in Excel
MOD and SEQUENCE used together
In budgets, forecasts, financial models and even reporting models repeating the numbers 1 to 12 can be useful. The SEQUENCE and MOD functions can make it easy and scalable.
Talking To Strangers (Penguin Books, 2020)
Written in his typical storytelling style, Malcolm Gladwell examines a police incident to see why and how we struggle to understand and relate to strangers.
Its not as straight forward as you think.
The book covers a lot of ground in many different areas. It is interesting and confronting.
If you liked his previous books then this one is worth reading.
Stop Power Query from automatically setting Data Types
It is a setting
When you transform data using Power Query it will often automatically promote the headers (use first rows headers) and guess the data types for each column. You can stop it doing this.
Your Music and People (Hit Media, 2020)
I first heard about Derek Sivers on Tim Ferris’s podcast and now have three of his books.
His back story is amazing and covered in a previous book.
This book is written about music and musicians, but it really applies to any creative endeavor.
Good ideas to help you get your music (or creative work) out there.
Short read – recommend.
Available from his website – link below.
Hell Yeah or No (Hit Media, 2020)
I first heard about Derek Sivers on Tim Ferris’s podcast and now have three of his books.
His back story is amazing and covered in a previous book.
This book is about identifying and doing what’s important.
Lots of short ideas to help kick start your important work.
It is a short read and I think only available from his website – link below.
Power Query and large data sets
Saving time
When working with some large data sets in Power Query it may be useful to default your Close and Load option to Connection Only rather than a Table. That way if you do strike an issue it won’t take ages to load to data.
Entering today’s date in Excel
It works in lots of places
You can use a keyboard shortcut to enter today’s date in a cell, but you can also use it in lots of other places in Excel.
New Task Pane Icons
Seems new icons have been added in the right of screen to allow you to switch easily between Task Panes in a recent upgrade.
This is in the subscription version of Excel.
Power Query shortcut for Adults
The shortcut to display the Queries & Connections Task Pane is easy to remember. It is Adults Only!
To display or hide the Queries & Connections Task Pane you use. These keys are pressed in sequence, not held down.
So only adults allowed in Power Query.
Related Posts
The Practice (Penguin, 2020)
Another great book by Seth.
It is written in bullet point form and has lots of pearls of wisdom to help you get your work out there into the world.
Seth looks at things a bit differently and that can help you focus on what you need to do and get it done.
Summing Text Numbers in Excel Updated
Dynamic arrays to the rescue
I wrote a blog post a few years back showing how to add up numbers formatted as text. If you have the subscription version of Excel you have another solution.
Get the Sign Right in Excel
Get the ABS you deserve
I was recently working with some data that had some issues with the sign on the quantities. The quantities should have had the same sign as the associated dollar amount, but they didn’t. Here is how I fixed it.
IFERROR in Power QUERY
Keep trying
Power Query does not have an IFERROR function. It does however have another function that performs the same type of calculation.
Prefix Numbers Conditionally in Excel
Conditional Format to the rescue
Let’s say we need to put a prefix in front of a number to identify the period being used. Whether that be year, month or week.