One Minute to Excel #21 – Hiding Zeroes
If you want to display a blank cell instead of a zero there are two ways to do it. See both in this short video. https://vimeo.com/601506805
Power Query Shortcut
Have you tried right clicking a formatted table recently? There is a new option to Get Data from Table/Range - which means to import the table into Power Query so you can data cleanse the table.
Time differences in Excel
Here’s a technique to calculate the time differences when you aren’t sure which time is first or last. Note with standard Excel settings you cannot report negative time.
(more…)One Minute to Excel #20 – Custom Lists
Do you have a list or lists that you use all the time? Would you like to write the first entry and then drag it like January to get the rest of the list? Here’s how.
(more…)Back when text was just text
20 years ago my last article for the Accountants Weekly magazine was published. They spelled my name wrong after getting it right for all the other articles, maybe that’s why I stopped.
(more…)One Minute to Excel #19 – Skip Blanks
If you have data that has blanks in it you may be able to combine columns using Paste Special – Skip Blanks.
(more…)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)
One Minute to Excel #18 – Convert real numbers to text numbers
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. https://vimeo.com/594604405
Excel Custom List Override
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.
(more…)May the fours be with you
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…
One Minute to Excel #17 – Convert text numbers to real numbers
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.
(more…)