Some cool Excel tips

One of the chapters that we give in the SharePoint courses is the Excel Services chapter. Before we publish an Excel report to SharePoint, I show some cool things in Excel and from time to time there is a student who knows some tips as well, like today.

Apparently these tips where available in the older versions of MS Excel but I didn't know them.

First one is selecting a table


How did I do it:

Use my mouse :-), because the shortcuts that I knew weren't very handy (with page downs and the end button ...). From time to time I end up on row twenty thousand ...

In the new version of Excel, when you select a table, Excel is going to wait for a small second before continue selecting. Normally that should be enough for you, off course this is depending on the distance between brains and your mouse-finger.

How am I going it to do:

She used a shortcut. She selected the first cell of the table and use the 'CTRL + *' key combination. Simple but very handy.

Dragging the selection to the last row

If you use a function and you want to copy it down, or in this demo you want to drag numbers down to the last row you can use a very helpful technique as well.

How did I do it:

Use my mouse (again), select the little square and drag it to the last cell


How am I going to do it:

Instead of dragging it, I'm going to double-click on that little square:


To end this article, something that I really like. If you have a lot of rows in a table and you scroll down, you loose the headers of the table:


You can fix this with having a fixed row, but I don't like that. In excel 2007 there is again great functionality. Actually it is like something very small but it is sooo very nice:


By selecting a column in the table and scrolling down, the names of the headers will be placed on top.

So, if you have more of these things, please let them know. It will help a lot of people.