Ten hot tips for Excel users

We’ve singled out 10 tips which you may not usually come across in your day-to-day use of Excel. You’ll find they make life much easier!

1. Printing titles / labels on every page
If you want to make the labels at the left or top of a spreadsheet print out on every page,

  • Click on file menu and select page setup
  • Now select the sheet folder from the dialogue box
  • To repeat column labels on every page, click rows to repeat at top, the enter the rows that contain the column labels
  • To repeat row labels on every page, click columns to repeat at left, and then enter the columns that contain the row label

    2. Quick copying

  • To copy the formula from a cell above, press Control + ‘
  • To copy the value from the cell above, press Control + “

    3. Excel 97 3D game
    This is a hidden treat in Microsoft excel 97, but it doesn’t work on any earlier or later versions.

  • On a new worksheet, press F5
  • Type X97:L97 and press enter
  • Press the Tab key once
  • Hold Ctrl-Shift
  • Click on the chart wizard button on the toolbar
  • And here you are!
  • Press escape to return to reality!

    Use your mouse to fly around. The right button propels you forward, the left is reverse. During the game, you will come across several monuments with the names of the software developers on. If you’re lucky, you might also stumble on some eggs. Apparently, there is a special reward if you collect them all.

    4. Add texture to charts
    Sick of boring looking charts – Liven them up by adding textures

  • Right click on a chart, and select format plot area from the options.
  • Click on fill effects (under colour pallete)
  • Select the texture folder in the dialogue box, you can chose your own, or use one of those provided.
    That’s it, your chart should now look snazzy.

    5. Back where you came from
    When you scroll or jump to a different region of your worksheet, you can return to the active cell by pressing Ctrl + backspace.

    6. Inserting the current date and time

  • To insert the current date as text, press Ctrl + ;
  • To insert the current time, press C + :

    7. Only display the area you are working on
    You can display only the area you are currently working on by;

  • Firstly, select the area you want to use by clicking and dragging your mouse over it
  • From the toolbar, select the zoom drop down menu (the box which usually displays the value ‘100%’ ), and click on the selection option
  • This will now zoom the page to suit the size of your selected cells
    To undo this, simply repeat the steps but choose your desired zoom from the drop-down menu.

    8. Using custom Excel headers and footers
    To define your own headers and footers for excel pages,

  • Open the File menu and click Page Setup
  • Now select the Header/Footer folder form the dialogue box and customise your
    header and footer

    9. Password protecting a workbook
    To password protect a workbook:

  • Open the File menu and select Save As
  • Click the Options key which will allow you to enter a password in the respective field (be warned, this is case sensitive), you can choose a password to modify the document, and a password to open it
  • Remember to click OK when you’ve chosen a password
  • Now save the file

    10. Adding hyperlinks to cells
    Excel allows you to add hyperlinks to cells, but it’s not as easy as Word or PowerPoint. You need to make a hyperlink formula:

  • Click the paste function (shown as a cursive fx, to the right of the sum-of button) on the toolbar to bring up the dialog box
  • Under the function category, select Lookup and Reference, and under function name select hyperlink
  • A hyperlink box will open, enter the required URL and the name that you want this link to be displayed as
  • Click on OK, the selected name will appear underlined, this is your hyperlink.

Related reading