If you have data that has blanks in it you may be able to combine columns using Paste Special – Skip Blanks.
Power Query Zoom
I used this today in a live webinar.
I zoomed into the Power Query window in Excel to make it easier to see.
Ctrl + Shift + + (plus)
20 years ago I had an article published on Excel shortcuts keys – let’s revisit it.
When you are working with text numbers in tables sometimes you need to convert real numbers into text numbers to do look ups.
There are at least two ways to do this.
Let’s see how to convert real numbers into test numbers, real fast.
In Australia our financial year starts in July. Excel is set up to work with calendar years and we need to do some date gymnastics to have our reports start in July. Here is a hack for Custom Lists that can make some things better in Excel.
On the weekend Sunday the 5th of September 2021 is a big day for Excel.
In Excel the date system starts on 1/1/1900 – that was day 1, and each day since has its own sequential number.
On Saturday September 5, 2021 (Father’s day in Australia) that number is – wait for it – see the image below.
May the fours be with you!
If you remove the formatting from a cell with a date you will see the underlying number.
When you import numbers from other systems they sometimes come in as text and are left aligned.
There are a couple of ways to fix them and they can both be done in less than a minute.