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]
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.
I had a question on another post on how to convert Nov 21, 2014 into a date Excel recognises. The solution involves six functions working together.
If you need to convert a number into a text number within a formula there are a couple of ways to achieve this, but one way is a lot easier.
Let’s say you have a table of codes and every month there are a few you want to check out. You could use a VLOOKUP to extract all the details for each code, but let’s say you want to view the codes in the table.
Excel 2013 added the Data Model to Excel.
The Relationships option (Data ribbon) is part of that model. It allows you to create relationships between tables so that you can use a PivotTable to report on multiple tables.
See my December 2016 INTHEBLACK article for an example.
The keyboard shortcut to create or edit, a Relationship is easy to remember – it is
Alt a a
Pressed in sequence, not held down.
Pasting Charts in Word and PowerPoint
When you paste Excel charts into Word or PowerPoint you may also be pasting all the underlying data that created the chart.
To get around that problem, you can use the Copy as Picture option.
This option is on a drop down on the Copy button on the Home ribbon – see image below.
You have a few options to choose from on what and how to copy.
This treats the chart as a graphic, which breaks any links to the underlying data. It also makes it much easier to re-size the chart when you paste it in the destination document.
It is not dynamic at all – it is a point in time capture.
There are times in Excel when you want to see as much of the Excel grid as possible. You may be reviewing or presenting a dashboard or looking at a large PivotTable. Excel has a Full Screen mode that shows just the grid and the sheet tabs.
Format part of a text string
Many people don’t know that you can format part of a text string in a cell.
This can be useful if you want to highlight, or emphasis, a particular word or phrase in a text string.
You can double click a word in the Formula Bar or while editing in a cell and a small menu pops up – see image below.
The options are reasonably limited, but you can use Bold or Italic, change the font; the font size and font colour – see examples below.
Show or Hide Cell Comments
Cell comments (they are like a sticky notes for cells) are great for instructions and documentation.
To show all the comments press, in sequence, not held down
Alt r a
This is a toggle, so you can use the same sequence to hide all the comments.
Shortcut to Re-Apply Sort
To re-apply the current sort – select a cell inside the table and press in sequence
Alt a s s Enter
Could be called the donkey shortcut.
Reapply a Filter
Let’s say you have applied a filter to a list.
The list is dynamic and the values have been updated by a refresh – the filter is out of date.
You don’t have to go into the filter drop down to re-apply the filter.
You can use a keyboard shortcut whilst in any cell in the table.
Ctrl + Alt + L
In Excel you can us Save As to save a file as a pdf, but it isn’t quite as effective in Excel as it is for MS Word. Often you only want to save a single sheet or a few sheets to pdf. Try this.
Another nice email
Always great to start the day with an email like this.
This one followed a free webinar I ran in October 2016.
You can right click a sheet tab and select Hide, but it is just as easy to Unhide the sheet. What if you want to make it harder to unhide the sheet?