|Excel is a grid. Selection in Excel is very different from Word or PowerPoint.
Excel offers many powerful and interesting ways of selecting things.
You will be surprised to know the capabilities …
Read on to find out more.
One cell is always selected – the active cell
Even when a range is selected, one cell is still visibly different – that is called the Active Cell. What is so special about it? You can type in the Active Cell without disturbing the selection.
This behavior differentiates Excel from all other document centric products.
When you type something in the active cell and then press Enter, the next cell becomes the active cell. This allows you to continue data entry in the selected region. This works across non-contiguous selection as well.
Edit active cell and copy to selection with CTRL ENTER
Typing in the selected cells one-by-one is of limited benefit. But what about typing something in the active cell and then pasting it across the selection? That is very useful in some circumstances…
Select a range. Type something in the active cell and press CTRL ENTER. Now the contents of the active cell will be copied across the selection.
We will cover the practical usage in a separate article. Here are two practical usage scenarios – to create sample, random data and to fill gaps in tabular data.
Select the block. This works in 2007 onwards. Click anywhere inside a block of data and press CTRL A. The block will be selected. This works even while you are entering a formula. Remember to use it.
If you have a table, CTRL A works in three levels. First CTRL A selects the data portion. Second time you press CTRL A, the entire table – including the header is selected. Third time you use CTRL A, the entire sheet is selected.
Selecting only the used portion of the sheet
Press CTRL Home to go to the FIRST CELL – which is always A1 and then press SHIFT CTRL END to select all the way up to the LAST CELL.
Selecting a pivot table
CTRL A works sometimes. But it depends upon the report layout (compact, tabular or outline). A better method is to use Analysis – Select – Entire Pivot Table option.
GoTo – CTRL G – F5
This is a simple method to go to any place within a workbook quickly. Type the reference and press Enter. Range names and table names are also shown in the list for quick navigation. The reference can be a range or ranges as well – separated by comma.
For defined range and table names, direct selection is possible from the dropdown next to the formula bar.
Special cases of selecting things
Selection is so important and potentially cumbersome in Excel that there is a separate collection of special tools – available under GoTo – Special.
If you see these options for the first time, you will get confused because it looks like an assortment of apparently unrelated items. There is, of course, one thing in common – it is about selecting cells having special attributes.
We will cover these options in a separate article. But remember that you can understand each option by attaching a prefix “Select cells containing…” to each item.
For example, in the case above, you are asking Excel to select all cells which contain constants (values – not formulas) which are of numeric type.
Now think about it… why would you want to do that? Because you want to create a reusable template from an existing spreadsheet!
Selection is not an end in itself – you will do something after selection. Therefore, all these special options are a part of some larger activity.
Selecting all pictures
When you copy paste things from browsers, many unwanted items get pasted.
(Ideally you should use Power Query to get data from browser pages. )
Many icons or pictures get pasted and removing them one by one is difficult. Often each of the pictures is a hyperlink making the process even more tedious. But don’t worry. There is a way. GoTo – Special – Objects will select all pictures in one stroke!
Similar thing can be achieved by selecting one picture manually and pressing CTRL A.
Selecting elements in charts
This is a bit tricky. There are so many different areas in Chart that it is difficult to choose the exact one you want. Typically we right click on the element which needs to be tweaked. But there is better non confusing way.
Go to the FORMAT tab in Chart ribbon and look at the leftmost group of buttons. The dropdown here lists every chart element. Choose the one you want and then click on the button below “Format <whatever is selected>”.
All items are listed here. Therefore, selecting exactly what you want is precise and painless.
Since 2010 onwards, double clicking any item in chart automatically opens its customization dialog.
Slow double click
Clicking on a plotted series or data label selects the entire series of items. If you want to select an individual item, you must use SLOW DOUBLE CLICK – click , wait for few seconds and then click on the desired item!
Remember that SHIFT is the selection key. While the SHIFT key is pressed, any cursor movement becomes a selection.
Often, we want to select from the current cell to the last cell in the row or column, in either direction. This is like asking Excel to move the cursor from current position to the last filled row in right side.
How do we say that to Excel? It is a two step process… to communicate the first part (blue) press the END key. Look at the status bar – now Excel is in “End Mode”
Pressing the END key does not do anything active. It just puts Excel in End mode… what does that mean? Excel is now asking you – “I know you want to move the selection to the end – but in which direction?”
You reply by pressing one of the arrow keys – Up, Down, Right or Left – depending upon the direction you want. Now the cursor will move to the appropriate cell.
If SHIFT key was pressed while all this is happening, the range will be selected. It is difficult to describe ( or show on video). Just try this out.
Remember, END key is not pressed along with the arrow keys. Press End key and release it. Now press the arrow key. If you are selecting, Press the SHIFT key first and keep it pressed while you are pressing and releasing the End and arrow key.
Pivot Table selection
Pivot table has some useful selection modes – which we often miss. Look at the mouse icon while hovering mouse cursor over various parts of Pivot table. Whenever the cursor shape changes, click and see what happens.
The horizontal arrow selected all the data for the entire year in ONE click.
Regular cursor will select the current cell like this…
But moving the cursor slightly to the left changed the cursor shape – and its behavior!
It selected all March months and the data associated with it.
Clicking on a subtotal while the cursor is a horizontal arrow selects all subtotals.
Select chart before annotation
Often we add some annotation or arrows or textboxes on top of a chart to highlight some interesting feature or abnormality. One irritating thing in such cases is that if you move the chart, the annotation does not move with it.
There is a simple solution to it. Click on the chart first. Then add the annotation. Selecting the chart informs Excel that the annotation is being added in the context of the chart. Simple extra steps – irritation is gone!
Selecting in Excel Online
All the above methods will not work in online (browser) based version of Excel or iPad version. Each platform has its strengths and limitations. In case of iPad or tablets, mouse may not be there. Therefore, you have to work with a more imprecise finger as the pointer.
To make this type of work easier, Excel for iPad has been redesigned with touch friendly menus.
Even on Windows tablets where there is a touch mode – which is active by default. Try it out and see how convenient it is.
Selection in Power View
Power View is designed to be an interactive analytical dashboard. Therefore, selection of an element does not affect only that element. It filters every other item on the dashboard as well. Therefore, in Power View selection is same as filtering!
In the next article we will see selection methods available in PowerPoint.