Microsoft Excel is an essential spreadsheet tool utilised by millions of users around the world to store, organise and analyse data. When using efficiently, Excel is extremely powerful and can be used for a variety of functions.
For many users, trying to manually analyse and organise spreadsheets in Microsoft Excel can be time consuming and overwhelming. There are, however, several tips and shortcuts that can be used to maximise your proficiency with Excel and improve efficiency.
To ensure you can use Excel more effectively, we have put together a list of essential keyboard shortcuts, tricks and functions which will boost efficiency and accuracy.
Why Learn Excel?
Excel is standard software within many businesses and so having the ability to use the program aptly can benefit both employees and employers alike.
As a business, having individuals with strong Excel knowledge can help you learn more from your data, create more accurate forecasts, quickly analyse data and make informed decisions.
As an individual, strong excel knowledge makes you a valuable asset to any business, improving your chances of securing a role within an organisation or even opening up opportunities for advancement.
Other benefits of understanding Excel include:
- Increasing efficiency – When used adeptly, Excel can speed up the time it takes to organise data. This is particularly useful for large amounts of information or calculations. Using Excel at an advanced level can also allow your business to better streamline calculations, minimising the time you spend on repetitive tasks and boosting overall productivity.
- Improving accuracy – Not only can Excel help to organise data quickly, but when utilising Excel’s commands, shortcuts or functions, the calculations are likely to be more accurate as opposed to those completed manually.
- Achieving better insights from data – Excel allows your business to interpret data at an advanced level and to pick out patterns and hidden information which you may have otherwise overlooked.
Top Tips for better Excel Use
Use Keyboard Shortcuts
A sure way to improve efficiency when using Excel is to utilise keyboard shortcuts.
Using shortcuts within Excel can help increase efficiency, improve accuracy and may allow for better concentration.
Shortcuts can also be particularly helpful for users with vision or mobility difficulties who cannot use a traditional mouse.
Here is a list of some useful keyboard shortcuts for Windows.
| Function | Shortcut |
| Go to the home tab | Alt + H |
| Remove cell contents | Delete |
| Go to the view tab | Alt + W |
| Centre align cell contents | Alt + H, A, C |
| Save a workbook | Ctrl + S |
| Create a new workbook | Ctrl + N |
| Insert a hyperlink in a cell | Ctrl + K |
| Undo your last action | Ctrl + Z |
| Insert columns or rows | Ctrl + Shift + = |
| Select an entire column | Ctrl + Spacebar |
Utilise the VLOOKUP Function
VLOOKUP stands for “Vertical Lookup” and is an Excel function which is useful for when you need to look up data in a table organised vertically. As one of the most popular functions in Excel, VLOOKUP supports both approximate and exact matching.
For example, if you have a list of products and current prices and wish to find out the price of a specific product, you could use the VLOOKUP function to do so.
- Start by selecting a cell in which you want to publish the price you are looking for.
- In the selected cell, type VLOOKUP(
- Select the value you wish to find out (in this case the product you wish to find the price of).
- Enter “;” and select the range in which you want to find the lookup value (this would likely be the range of the entire table of products and prices).
- Enter “;” and the number of the column within the defined range in which you want to look for the return value (In this case it will be the number of the column which lists the prices).
- Enter “;” and “0” or “FALSE” for an exact match for your lookup value.
Use the Fill Down Command to Autofill Spreadsheets
Excel contains a useful command called “fill down” which allows the contents of a cell to be populated into many cells without having to copy and paste or fill out each value manually.
The true potential of the fill down function can be seen when used in conjunction with formulas. When using the fill down function on a cell containing a formula, the formula itself is copied rather than the result of the formula.
To use this function:
- Select the cell that contains the value or formula you wish to duplicate.
- Highlight the range to which you want to copy it.
- Use the keyboard shortcut Ctrl + D to successfully autofill the information down in a column.
- You can also use Ctrl + R to fill the formula to the right in a row.
Relative and Absolute Cell References
There are two key types of cell references: relative and absolute. These references behave differently when copied and filled to other cells. A relative reference will change when copied to a different cell. In comparison, an absolute reference will remain constant regardless of where it is copied.
By default, all cell references within a spreadsheet are relative references. When these are copied across multiple cells, they will change based upon the relative position of rows and columns.
For example, if a formula contains a reference to a cell that is two spaces to the left, when copying that formula, the reference will always be two spaces to the left.
Unlike a relative reference, an absolute reference will not change when copied and filled but will keep a row and/or column constant.
To define an absolute reference, a dollar symbol ($) should be placed before the letter and number of the cell references. A reference to the cell B1 for example, would be made into an absolute reference using $B$1.
Use the IF Function to Tidy Up Spreadsheets
Using functions and equations in Excel is a great way of automating and speeding up data entry. However, you might find that your spreadsheet starts to show zeros or errors where data has not yet been published. One way to solve this is to use the “IF Function” which performs a basic test to determine which data to display on the spreadsheet.
The IF Function has three parts to it: =IF (the test, do this if true, do this if false)
- The first is called a logical test and determines whether something is true or false.
- The second is a statement of what to do if the test is true.
- The final section is a statement of what to do if the test is false.
For example, you may be interested in whether column A contains data or not. If column A is empty, you would like column C to also be empty. If, however, column A contains a value, you want column C to perform the calculation and add up the values of the two previous cells.
You would begin the function as follows:
=IF(A2””,
The second part of the function is what you wish to happen should the test prove true. In this example, if column A contains no data, column C should also be empty. This is shown by the use of double quote marks as follows:
“”
The final part of the function is what should happen if the test is false. On this occasion, if column A contains some data, column C should add up the previous two cells, hence the following formula:
A2+B2
The entire IF function would look like this:
=IF(A2=””,””,A2+B2)
As a result, the zeros or errors in the spreadsheet will show as blank cells until such point when data is entered into column A.
Ensure your Business Runs Efficiently
We hope you find out tips and tricks for mastering Microsoft Excel useful and can put them into practice with your own data.
If you are looking to make use of Microsoft Excel, we can support your business with installation and ongoing support for Office 365 which gives you access to Microsoft Office functionality including Excel.
We can also help to maximise efficiency in your business by expertly selecting the most appropriate IT hardware configuration for your business requirements.
To find out more about how to maximise efficiency, or to learn more about our other services, fill out a contact form to discuss your requirements or alternatively, call a member of our team on 0117 975 9523.