Dr. Mark Gardener


Providing training for:

  • Ecology
  • Data analysis
  • Statistics
  • R The statistical programming language
  • Data management
  • Data mining

Tips and Tricks - for R and Excel

On this page you can find tips, tricks and hints for using both R and Excel. At the end of each tip there are links forwards and backwards as appropriate. There is also an index of R tips and an index of Excel tips.

For most analytical purposes the combination of Excel and R is unbeatable! Excel is great as a data management tool and for preparing data for analysis. You can also use it to get an overview of your data or to make simple (and not so simple) graphs. R is an analytical "swiss army knife" and can carry out a mind-boggling array of analytical routines as well as producing great graphics.

Tips & Tricks for R | Tips & Tricks for Excel | An Introduction to R | MonogRaphs | Writer's Bloc


Keyboard Shortcuts in Excel.

Use the Name Box to jump directly to a cell by its Column & Row reference or named range name.


Keyboard shortcuts

If you have a lot of rows and/or columns in your spreadsheet it can be a real pain navigating from one spot to another. Selecting many rows of data is also a tedious operation. The trick is to learn some of the keyboard shortcuts, key-combinations that can save you a lot of time and effort. Note that these only work for Windows versions of Excel.

Named ranges

All cells have a row and column reference, which is shown in a box (the Name Box) to the left of the formula bar. You can type a cell reference in this box to jump directly to a cell. This works fine if you know the column and row number of the cell you want – of course A1 will always take you to the top of your worksheet.

If you select one or more cells and type a name in the Name Box you assign a name to those cells (the cells do not have to be adjacent to one another). Later, you can type the name to jump to the location defined by the name. You can also use the drop-down icon and select a name. If your name "points" to several cells they are all selected.

Name Box in Excel
The Name Box allows you to navigate to a cell or named range.

You can define named ranges in other ways, and manage them using the Name Manager. See more details in the previous Tip page "Named Ranges in Excel".

The Name Box is not quite a keyboard shortcut, but it links the Named Ranges to the keyboard shortcuts you'll see next.

Arrow keys with Control allow you to jump to the end of blocks of data.

Arrow keys with Shift and Control allow you to select large blocks of data quickly.


Moving around and selecting with arrow keys

There are two important "key modifiers" that you need in conjunction with the arrow keys:

  • Ctrl
  • Shift

The control (Ctrl) key allows you to jump rapidly from one end of a block to another. The Shift key allows you to select cells.

If you hold the Ctrl key and press the arrows you'll jump in the direction of the arrow. The distance of the jump depends on the data, if there are data in the row or column you'll go to the end of the current block of data. If you press Ctrl+Arrow again you'll jump to the start of the next block, press Ctrl+Arrow again and you'll jump to the end of that block. If there are no data you'll travel to the extreme edge of the worksheet, which can be hundred or thousands of rows/columns, depending on your version of Excel.

The Shift key allows you to select cells. If you click in a cell then press Shift+Arrow you'll select cells in the direction of the arrow.

Using Ctrl+Shift+Arrow allows you to select a block of data in a row or column. Thus you can highlight many rows and/or columns of data quickly and easily.

Use Ctrl+A to select blocks of data.


Selecting "everything"

You can select everything using the space between the row and column headers, which contains a triangle. Clicking in the box selects the entire worksheet, including empty cells. This is not always what you want so you can use Ctrl+A to select all the data.

However, Ctrl+A does not work how you might expect. If you click in a block of data then press Ctrl+A the block of data will be selected, if there are other data cells they will not be selected if there are empty cells between the blocks. The selection is rectangular so if one row or column is bigger than the others, the selection area is expanded to incorporate the cells.

If you click a cell adjacent to a block of data and type Ctrl+A the block of cells next to the insertion point will become selected. You can position the insertion point so that several blocks of data become selected, essentially Excel looks at the cells surrounding the insertion point and expands the selection to include any non-empty cells. This is how you can get a chart or a Pivot Table without having to select any data. Knowing this behaviour also allows you to insert blank charts, by ensuring that the insertion point is not in, or adjacent to, any data.

Use Ctrl+Mouse to select non-adjacent cells


Selecting using the mouse

The Ctrl and Shift keys can be used with the mouse. The Ctrl key allows you to select non-adjacent cells or cells in a non-rectangular block. The Shift key "fills in" the selection between the first block you select and subsequent blocks. This behaviour extends to entire rows or columns, so you can click in the headers to select several rows or columns.

Use keyboard shortcuts in Excel menu windows to help select data ranges.


Selecting data from menus

The shortcuts can be used in conjunction with various menu windows. For example, if you are selecting data for a chart using the Select Data button. You can click the topmost cell in a column for example and extend down the entire block using Ctrl+Shift+Down Arrow. This is helpful when you have many rows of data.

Use Keyboard shortcuts in Excel menu windows
Keyboard shortcuts can be used to help select data ranges in many menu windows.

Keyboard shortcuts can save you a lot of time and effort. This topic is covered in my current writing project, Managing Data Using Excel: Arranging, Summarizing and Visualizing Scientific data. More details on my Publications and Writers' Bloc pages.

Top << Previous Tip: Named ranges  
Follow me...
Facebook Twitter Google+ Linkedin Amazon
Top Tips & Tricks Home Index of R Tips Index of Excel Tips  
More links:

An introduction to R

See my Publications about Excel, R, statistics and data analysis Courses in R, data analysis, data management and statistics Visit the R Project website

See my Publications about statistics and data analysis.

MonogRaphs: random topics in R

Writer's Bloc – my latest writing project includes R scripts

Courses in data analysis, data management and statistics.

Top Home
Data Analysis
Contact GardenersOwn Homepage