Excel Data Validation Blind Spot

Macro to fix it

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.

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.

 

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.

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.

Download materials

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.

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.

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.

Download webinar materials

 

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.

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.

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.

https://sive.rs/book