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.
Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.