Lesson 27: CTRL, ALT, Function Keys and the Mouse in Excel
On your keyboard you see some function keys at the top (F1, F2...). Some of these keys perform interesting functionalities in Excel. Other interesting functionalities in Excel are activated when you hold the CTRL or the ALT key while pressing on some letters. The right click of the mouse also gives access to nice functionalities.
Here are these shortcuts by order of importance. I use the ones at the top of the list every day.
CTRL/x cuts (and deletes) the value or formula in a cell or set of cells to be pasted somewhere else.
CTRL/c picks up the value or formula of a cell or a set of cells to be pasted somewhere else.
CTRL/v pastes (I call it CTRL/Velcro)
CTRL/f to find a value in a row a column (that you have selected) or a sheet. To find a value in the whole workbook select all sheets (see how below).
CTRL/h to find and replace.
ALT/Enter to force a line break within a cell.
The F4 key allows you to add dollar signs to addresses AUTOMATICALLY. See chapter 11 on absolute and relative references.
CTRL/a selects all the cells of a sheet. You can also use your mouse and click on the small gray square between the " 1" and the " A" at the top left corner of the sheet.
CTRL/Arrow Down will send you to the last cell in a column
Shift/CTRL/Arrow Down will select all the cells from where you are in the column to the last non-empty cell of the column
Apply the same logic to Up arrow, the Right arrow and the Left arrow
CTRL/Enter: Select many cells and then enter a value in the formula bar (just above the sheet on your screen). Hold the CTRL key and click Enter. The same value is entered in all the cells.
To replace a word just click on it and start writing its replacement. No need to delete it.
Many different contextual menus are available when you use the right button of the mouse. Right click on a chart, a cell, the columns and rows headings, a text box....right click on everything and you will discover wonders.
When you select a cell, many cells, a row or a column you can right click on the mouse and discover the VERY important " Clear Contents" . This functionality doesn't make the selection disappear like the " Delete" functionality it just clears the contents of the selected item. When you start working with Excel databases and automated reports using SUMPRODUCT it is vital that you use " Clear Contents" instead of " Delete" .
To select 2 or more cells that are not contiguous click on the first one hold the CTRL key down and select the others.
To select many contiguous cells click on the first one hold the SHIFT key and click on the last one.
To select 2 or more sheets click on the tab of the first one hold the CTRL key down and select the others. The word [Group] appears beside the workbook name at the top of your screen. To deactivate the grouping click on any single tab. When a group of sheets is selected anything you enter in cell A1 of the active sheet (for example) is also entered on the other sheets.
To select all the sheets click on the tab of the first one hold the SHIFT key and click on the tab of the last one.
If you have deactivated the calculation "Tools/Options/Calculation/Manual" and you want a sheet to be recalculated use the F9 key.
There are five sections in this website
Section 1: About Databases: 4 Lessons
Database 101 Excel and External Data Analyzing Data with Excel Reporting with Excel Other Functionalities