Pasting a Filtered List in a Formatted Table

I have been recently working with some very large (500,000+ rows) tables. As part of the process I had to filter one Formatted Table, copy it and then paste it in another Formatted Table. Excel would sit there processing for a long time – but I found a technique to speed up the process.

After you copy the filtered list, simply paste it in a blank sheet. This is virtually instantaneous. Then copy that interim list and paste in the other Formatted Table – again almost instantaneous. Two quick pastes is a lot quicker than paste and wait.

In case you didn’t know, when you copy a filtered list, you only copy the visible cells – the filtered ones. The hidden cells are omitted from the copy.

So if you are experiencing delays in the pasting of a filtered list, just use an interim paste and then another copy to speed up your copy and paste.

For more information on Formatted Tables, check out the links below.

Format As Table in Excel Part 1

Excel Format as Table Part 2 [VIDEO]

 

Inserting Data into Formatted Tables

I was recently working with a large Formatted Table in excess of 100,000 rows with Power Query.

I was copying in new data to a temporary workings table and then manipulating it with Power Query to get the required output. The data was varying lengths. I found that if you pasted data into a Formatted Table that was a lot longer than the Formatted Table it can take a long time for Excel to process the paste (I am talking tens of thousands of extra rows).

To get around this delay I found that if you first expanded the Formatted Table using Insert Rows, the paste was virtually instantaneous. Inserting the extra rows was also very quick.

So if your Table has sufficient rows the paste is quick, if Excel needs to expand the table to fit the new data, it can be slow for large data sets. Make sure you insert sufficient blank rows to speed up the paste.

You can learn more about Formatted Tables at the two blog posts below. I have also covered the topic in numerous free webinars.

Format As Table in Excel Part 1

Excel Format as Table Part 2 [VIDEO]

How to get CREATIVE! by John Cleese

Brilliant talk!

No slides needed, this was way back in 1991!

This is especially relevant these days because we get so little “think” and “alone” time.

 

 

Where it all began

A great talk by Dan Bricklin the co-developer of Visicalc – the first desktop spreadsheet.

He talks about how game changing spreadsheets were for the desktop computer.

Discover where he was when the idea came to him and how he visualised it.

Decisions he made back then are still with us today.

Sheet Protection – No password

When you protect a sheet with a password, you must make a note of the password, otherwise you create problems for yourself.

Did you know you don’t have to supply a password?

To apply sheet protection, right click the sheet tab and choose Protect Sheet.

When the Protect Sheet dialog displays just press Enter or click OK.

This protects the sheet with no password.

This stops accidental changes and you can easily unprotect the sheet to make changes.

You can right click the sheet tab to unprotect as well.

Tools of Titans (2016)

I am over half way through the book and it has lots of great ideas, concepts, tricks and tips. Highly recommend.

Tim is famous for the 4 hour work week book, but more recently his podcasts have become legendary.

The book is based on the people Tim has interviewed and had on the Podcast (not all the podcasts are interviews). I am a big fan of the podcast and the book delivers.

Great to have so much information in one book.

In the introduction Tim says to skip stuff that doesn’t interest you. I have skipped over a lot of the “supplement” chapters as it isn’t my thing.

But there are still lot of gems in this book no matter what your stage in life.

The kindle version has lots of internal links within the book.

Definitely worth your money, time and effort.

Tim Ferriss Podcast

I have been listening to Tim Ferriss’s podcasts for a few years now.

Its brilliant!

As well as having many famous people on, he also has people who aren’t so famous but are amazing nonetheless.

Many episodes go over 2 hours.

Highly recommend – great for car travel.

There is occasional swearing, not much, but if you don’t like that, you have been warned.

The content is well worth ignoring the occasional swear word.