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.)
Close All Icon
If you have a lot of files open and you want to close them all and still leave Excel open you can add this icon to the Quick Access toolbar.
See this post if you are not sure about using the Quick Access Toolbar.
One of the secrets to staying young is to always do things you don’t know how to do, to keep learning.
Do you use the “Filter by Selected Cell’s Value” option? If you do then you will be pleased to know there is a Quick Access Toolbar icon that applies it in one click.
This Excel keyboard shortcut is powerful, but it has a few quirks you need to be aware of. Its quick to use because the A and Ctrl keys are so close together.
I had a question during one of my Date and Times Webinars in February. It was about months and fortnights. I couldn’t answer it during the webinar, but I did follow up with an email with the solution. The answer follows.
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.
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.
How to get CREATIVE! by John Cleese
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.