One of the problems with Excel’s Data Validation is that it is possible to have an invalid entry in a data validation cell. This can be caused by Paste Special Values or linked drop downs that don’t update if an earlier drop down is changed. To easily identify invalid cells you can use a macro.
UNIQUE Function and Blank Cells in Excel
Zero in on a problem
The UNIQUE function has a bit of an issue with blank cells, formulas that return blank cells and zeroes.
Identifying if a List has Unique Entries in Excel
A MODE solution
If you need a logical test to determine if a list is unique you can use the MODE function with the ISNA function.
Data Entry Formats in Excel [Video]
Format as you go
When you enter data into Excel you can format as you type. See how in this short video.
Related Posts
Adding up Text Numbers In Excel Another Technique
If you have a list of numbers that are a text numbers or a combination of text numbers with real numbers there is a technique I covered in this blog post to add them up. But if the range also contains text then the technique won’t work. There is the work around. The solutions below work in the subscription version of Excel. Check the comments section below for a solution for all versions.
The Magical N Function in Excel
Who you calling short?
One reason I like the N function is because it is Excel’s shortest function name. But it has quite a few useful features as well.
Last used column number in a row in Excel
AGGREGATE to the rescue
A while back I posted a formula to find the row number of the last used cell in a column. I revisit the solution to provide the last used column number in a row.
Excel Power Query and Multiple Files 2022
Webinar recording from April 12, 2022
This is the recording of the second free Power Query webinar I ran in 2022.
You can watch the first one at this link.
In this session we see how to import multiple files in one Power Query. We look at importing CSV and Excel files.
You can download the materials, including a detailed pdf manual using the button below.
Removing Outliers in Excel
Dynamic array solution
I wrote a blog post a while back about outliers and Excel and I thought I would revisit it thanks to dynamic arrays.
One Minute to Excel #25 – Find the breakeven point
Goal Seek solution
If we have a simple Profit and Loss and we want to figure out a breakeven point, we can use Goal Seek to find it.
We can also use it to see sales required to meet a certain profit.
All in less than in minute.
Capture an AutoCalculate Value in Excel
A new feature
A recent update to Excel has made a slight change to the AutoCalculate feature. You can copy a value by simply clicking on it.
One Minute to Excel #24 – 1,000 random dates
A RANDARRAY solution
Let’s say we need to do some testing and we need 1,000 random dates in 2022.
We can use a new function to make this easy to create and easy to change.
RANDARRAY usually works with numbers but in Excel dates are numbers, so we get it to create random dates for us.
I set myself a challenge to do this in less than minute – see how I went in the video below.
Weird Excel
Strange stuff happening with a range name
Some weird stuff happening when using a range name in the same sheet. Check out the video.
Never seen this in my over 25 years of using Excel. The file may have issues but definitely some weird stuff.
Found the problem – or more accurately Jan Karel Pieterse Excel MVP did on LinkedIn.
It was as setting in the Options => Advanced => Lotus compatibility settings.
The Transition formula entry option was ticked – it needs to be unticked – see below.
That was a new one on me – big thanks to Jan Karel Pieterse.
Excel Variance Formula
IF function to the rescue
When calculating variances between actuals and budget, you typically have a positive value representing a favourable (good) variance and a negative value for an unfavourable (bad) variance. When looking at revenue and expenses together this poses a problem for the variance calculation. The calculation needs to be different for revenue and costs. Here’s a way to use a single formula for both.
The Excel CONVERT function
Most conversions are easily done
If you need to convert between different measurement systems Excel has just the function for you, called CONVERT.
Introduction to Excel Power Query
A free webinar recording
Earlier this month I ran a free webinar on Excel Power Query.
This is the recording of the session with no editing, no time limit and no sign up required.
Power Query is the best way to import data into Excel. It is also the data importation system used in Power BI, so everything you learn in Excel can be applied to Power BI.
You can download the materials, including a detailed pdf manual at the button below.
Please enjoy, learn and share.
Related Posts
The Big Switch (2022)
A must read for everyone.
We will all need to get involved to make change happen. The politicians from the two major parties in Australia are not interested, so we must make them interested.
Electrify is the call cry.
We need all levels of government to take the lead and start to support and subsidise the industries we will need moving forward.
It’s a little embarrassing that after 45 years of research and study, the best advice I can give people is to be a little kinder to each other.
Aldous Huxley
Working with a Different Working Week in Excel
International functions to the rescue
Let’s say you are transitioning to retirement (lucky you) and you only work four days a week. You have Wednesdays off to play golf. You may still do projects and you need to figure out completion dates based on a start date and working days. Excel can help you.
Hyperlink to a Specific Cell in Excel
Range name to the rescue
When you create a hyperlink manually in Excel it captures the cell reference. If you insert rows the cell reference in the hyperlink doesn’t update. If you need it to update, this is what you need to do.
Think of many things. Do one.
Portuguese proverb
Switching Reports from Rows to Columns in Excel
TRANSPOSE and OFFSET solution
I was recently helping someone with a budget which they had built vertically, with the months going down the sheet. They then asked to display it horizontally, with the months going across the page. In the latest version of Excel this is straightforward.
Excel VBA to Navigate to Sheets
A shortcut menu makes it easy
If you right click the arrows on the left of the sheet tabs at the bottom, left of the Excel screen you can see a list of all the sheets in the file. You can use VBA to show this list anywhere.
Clear the Filter in One Column Only in Excel
Keyboard shortcut
When you have multiple filters across columns you may want to clear just the filter in one column. There is a keyboard technique to do that.
Selecting the Cell in the First Row in a Column in Excel
Two shortcuts
There is a keyboard shortcut to select the first column in a row, but as far as I know there is no shortcut for the first row in a column.
Dynamic Arrays and a Book Index
Another solution
Years back when I wrote my Excel book, I had to create an index for the book. I shared the file I used including the macro in this post. Recently I thought dynamic arrays could do much of the work for this.
Financial Year Month in a Pivot Table
Create new column
I wrote an article years ago explaining how to use a related table to handle financial years in Excel Pivot Tables. You can read the article here. If you only want the months in financial year order you can just add an extra column to your table.
Good reading resource
If you are after a good book to read, this is a great resource with over 300 books listed, and ranked.
Derek Sivers has had an amazing life and I am a fan of his own books.
He has included ratings and notes of the books he has read.
You can sort the list by Title, Newest or Best.
Extracting Time from Date and Time in Excel
Another MOD function solution
I had a recent query regarding checking time in a column that had both date and time. There is an easy way to extract time from a date-time combination.
Excel Chart Label Line Break
When adding labels to a chart sometimes you need a line break. Well it is possible.