Tips and Tricks

Tips and Tricks in using Excel

Here are some tips and tricks that may make your life easier. If you have any questions, or maybe you have some tricks you would like to share with the world, please feel free to get in touch with us.

As a rule we believe that most problems can be solved WITHOUT making use of macros. So look here first before you are tempted to go the macro and VBA route.

Calculate the Compound Annual Growth Rate (CAGR) using Excel

Use the RATE function to calculate the CAGR

Show the underlying formula in all cells

A shortcut which allows you to show either the results in all cells or the underlying formula in the cells.

Filling in the blank cells quickly and easily

There are occasions when you would like to fill in all blank cells with a formula. This is a quick and easy way to achieve this. Use is made of the GoTo Special command so it may be worthwhile learning about it.

 

Cleaning up data using the Data Autofilter

Excel is often used as a means of cleaning up data and produces reports. Often the most time consuming aspect is the data cleanup. Using the Data AutoFilter it is possible to achieve more in less time.

Changing Multiple Worksheets at the same time

In Excel it is possible to change a number of worksheets at the same time. Extremely useful but don’t forget to switch it off once you are done!

Automatic sorting of a list

Rank a list of numbers and then automatically (without macros) have the numbers sort themselves into your preferred order (and pull other information with it if necessary). Can be used to generate sport league tables e.g. English Premiership log or the World Cup Soccer logs.

You need to know the workings of the VLOOKUP and RANK functions.