Export a sheet as a PDF

It takes a few clicks but it is possible

I have previously posted about using CutePDF to create pdfs from Excel sheets. There is another way, but it takes a few clicks and it only works in Excel 2010 and later versions. (It may work in Excel 2007 but I have taken that version off my PC so I can’t test it.)

One of the secrets to staying young is to always do things you don’t know how to do, to keep learning.

Ruth Reichl

Power Query M Language Editor

Power Query (Get & Transform) uses the M language to allow you to customise and build in flexibility to your queries.

Currently writing the M language is tricky to write. It is case sensitive and there is no built-in help to assist you using the language.

Apparently Microsoft is working on creating an M editor, but there is no time line on when it will be ready.

This article takes you through some steps to create your own editor using Notepad ++.

If you use Power Query a lot then this may be worthwhile.

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.