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]
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.
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.
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
There are two reasons your Excel file won’t calculate.
The first, and easiest to fix, is that Calculation is turned off – see a
previous tip on that issue.
The other reason is that there is a Circular Reference in the file. Circular References can stop calculation.
You can confirm if there is a Circular Reference in a file by looking at the bottom left-hand corner of the screen – see image below.
A Circular Reference means that a cell is referring to itself in a calculation. A simple example is shown in the image above, where cell A1 has a formula =A1.
Excel cannot resolve this calculation, so zero is displayed.
In the Formulas tab, on the right-hand side, the Error Checking icon drop down will help you identify the Circular References in the file.
Turn on Calculation
The shortcut to turn on automatic calculation – pressed in sequence – is
Alt m x a
To turn it off
Alt m x m
Learning Excel’s Function Arguments
When you start to use a function it can take some time to learn the arguments required and understand what Excel expects for each argument. Eg should it be a cell or a range or either?
When you have the start of a function in the formula bar, you can either press Ctrl + a or click the fx symbol on the left of the formula bar – see image below.
In the image above, the argument in square brackets [range_lookup] is optional. Square brackets around an argument mean it is optional.
This will display a dialog with a listing of the arguments required by a function. The bold names are required, the non-bold names are optional.
This listing provides a lot more detail on what Excel is expecting for each function argument. This helps you learn more about how to create and use the function.
Insert a New Sheet
To insert a new blank sheet press Shift + Alt + F1.
Open Paste Special Dialog
To open the Paste Special Dialog after copying or cutting use
Ctrl + Alt + v
WARNING: This conflicts with a default Evernote clipping shortcut. You can change it in Evernote use Tools > Options > Shortcut keys.
Refresh a PivotTable
The keyboard shortcut to refresh a PivotTable is Alt + F5.
PivotTables don’t automatically update if the data changes.
You Can Undo After You Save
I amazed how many people are unaware of a change that happened way back in Excel 2007.
In Office 2007 and later versions you can use Undo after you have saved the file.
In the old days (Excel 2003 and earlier) saving used to clear the undo list – it no longer does.
So undo to your heart’s content.
Remember there is a keyboard shortcut to undo.
Ctrl + z
Summing a range with Errors
If you have a column of values with errors, but you want to see what the values add up to, use the AGGREGATE function (added in Excel 2010).
If column A has the values and errors use
The 9 means SUM. The 6 means ignore errors.
Working with Quarters in Excel
You might not know that Excel knows about quarters. That’s 3 months, not 25 cents.
If you type Q1 in a call and drag the cell with the Fill Handle (bottom right corner of cell) you will see that Excel repeats the sequence Q1, Q2, Q3 and Q4. It doesn’t do Q5 or above.
This works for Qtr1 as well.
Entering Today’s Date in a Range
If you need to enter today’s date in a range try this. Select the range and then press
Ctrl + ;
With the Ctrl held down, press Enter.
This will also work on a range in a filtered list.
When selecting a range with the mouse or keyboard, keep an eye on the Name Box – top left corner of the screen, just above the grid.
It tells you how many rows (R) and columns (C) you have already selected – see image below.
As soon as you release the mouse, or stop using the keyboard, the values will disappear. You have to be quick to see it.