Excel’s Formatted Tables [Video]

Free webinar recording

Learn about Excel’s powerful Table feature that treats tables like little databases.

I ran this free live webinar in March 2021.

You can access the pdf manual and example file by clicking the button below the video.

Download Materials

One Minute to Excel #8 – Incrementing a Long List of Dates

It is easy when you know how

In this short video I cover how to increment dates in long ranges.

It uses a little know dialog.

One Minute to Excel #7 – Copy Date down a Long Range

Quick and easy technique

In this short video I cover how to insert dates in long ranges.

Its simple and quick.

One Minute to Excel #6 – Add Subtotals Automatically

Built-in feature

In this short video I cover how to insert automated subtotals.

Its a built-in feature, that is easy to use.

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!

 

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.

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!

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.

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.

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

Download Example file

 

 

 

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.

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.

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.

https://sive.rs/

 

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.

https://sive.rs/