About admin

Hi, I am MS Office trainer and a bit of an excel geek. I enjoy solving problems and generally geeking around with excel!

Turn a horizontal list into a vertical one and vice versa with paste special transpose.

lets say you have a list of values in a spreadsheet that is going along a row like below:

10  20  30  40  50  60

But you want the list to be formatted like this:

10
20
30
40
50
60

Normally you would end up retyping the list of doing a lot copy and pasting.  Well you don’t have too do this!  You can use paste special to solve your problem. 

Highlight the text you want to rotate
select copy from the right click menu or from the toolbar / ribbon at the top of excel
move to where you want the new rotated list to be on your spreadsheet
select paste special from the right click menu of from the toolbar / ribbon at the top
tick the box at the bottom right of the screen that states ‘Transpose’
press ok.

This method will also change a vertical list into a horizontal list.

We are offering some FREE webinars too (see below)

Moving to the bottom of a range or list without scrolling

If you have a list of say 1000 items and you want to get to the bottom of it you would usually need to use the scroll bar or press the down cursor key and get finger ache!  Well providing you click somewhere within the list you can use ctrl and the down cursor key.  This will move you directly to the end of that particular list.  In the same way ctrl and up / left / right works.

Also if you combine this method by pressing shift at the same time it will move to the end of the list and also select the cells between where you started out from and the end.

Run a line through your text by adding a strike through quickly.

You can run a line through all the contents of a cell giving a clue that the item is complete of finished E.G.:

John
Dave
Sarah
Chuck

Normally this is a fiddly affair requiring many mouse clicks.  To do this quickly simply select the cell or cells you want to add the strike through to and press ctrl and 5.  This action toggles the strike through on and off.

Split a cells contents over multiple rows automatically

If you have a cell that contains a large sentence that is not easy to read without resizing the cell, you can use the justify function to split the large sentence into other cells without the need for copy and paste. 

Highlight the cell with the sentence you want to split (NOTE this requires blank cells to be beneath)
Then in:
   
    Excel 2007 click Home -> Editing -> Fill -> Justify
    Excel 2000 – 2003 click Edit -> Fill -> Justify.

This will result in the text from the cell you selected being split into the cells beneath.

Build a sequence of numbers, text or dates quickly

You can build a sequence of numbers, text or dates within excel very quickly.  Simply enter the sequence you want to repeat as the example below:

5
10

Now drag over the two or more cells containing the pattern of sequence you want to create and select the black fill handle at the bottom right and drag down to create a sequence as below:

5
10
15
20
25
30
And so on …

VIDEO TIP BELOW



Stop your headings from disappearing with freeze panes

When you create a list in excel that ends up being quite large scrolling up and down can result in you not being able to see your headings.  This means that the information you see might not make a lot of sense.

To correct this you can use freeze panes.  Select the cell below the section you want to freeze and:

In Excel 2007 click View -> Window -> Freeze Panes
In Excel 2000 – 2003 click Window -> Freeze Panes

This function toggles on and off so to unfreeze panes use the same menu option and instead of freeze panes the option will be called unfreeze panes.

VIDEO TIP BELOW



Shortcut for inserting the dollar symbol into your Formulas $$

Instead of physically typing the dollar symbol into your cell reference when creating a formula, simply click on the part of the formula that you need the dollar symbols to be in and press F4 on your keyboard. If you continue to press F4 you will find that Excel will cycle through the different options for the dollar sign. E.g. $A$1 -> $A1 -> A$1 and back to A1.

This tip also works with refernces to other Excel Sheets or Workbooks.

VIDEO TIP BELOW



We are offering some FREE webinars too (see below)

Hello

This is my excel blog.  Over the past few years of training I have come to realise that there is a need to provide some decent post course support for the people I work with.  This is my first attempt at bridging the gap.  I am going to post weekly excel tips written in plain English for the world to see. 

I welcome all comments as I want this to be an interactive exercise.I look forward to hearing from anyone and enjoy my Blog.

Ben Woodthorpe