How-to handbook: 10 quick Excel hacks
Spreadsheets are an essential tool in any CA's arsenal. Microsoft Excel is undoubtedly the forerunner in its field with a huge global user base.
We have previously gone over some of the most common and useful Excel functions for getting you through the workday.
What follows is a rundown of some simple tricks and shortcuts to help you become even more of an Excel pro.
1. Move between workbooks quickly
When you have multiple Excel files open, avoid accidentally working on the wrong file by using Ctrl + Tab to shift between different workbooks freely.
2. Create new menu shortcuts
Generally there are three shortcuts in the top menu navigation, which are 'Save', 'Undo' and 'Redo'. However, if you want to set up a few more, all it takes is a few clicks.
Go to File > Options > Quick Access Toolbar and simply select the functions you would like to add. Options include 'Cut', 'Copy', 'Paste' and 'Quick Print'.
3. Move and copy columns
To move one column of data in a spreadsheet, select it by clicking its letter designation and move the pointer to the border. The arrow will turn into a cross icon, allowing you to drag and move the column freely.
To copy the data of an entire column to another, hold down the Ctrl button while you drag.
4. Find a unique value
The Advanced Filter can be applied when you need to pick out a unique value from data in a column.
Select the column in question and go to Data > Advanced. In the pop-up window, click 'Copy to another location' and then specify the new target. Tick 'Unique values only' and click 'OK'.
5. Move data from a row to a column
To transpose data for a better display without having to retype it, use the 'Transpose' function in 'Paste'.
Select and 'Copy' the area you want, select where to move it to and then go to Home > Paste> Transpose.
6. Change the case of text
There is nothing worse than accidentally typing with caps lock on. Apply a few simple functions to text cells to avoid starting over.
'UPPER' will capitalise all characters; 'LOWER' can change text to all lower case; 'PROPER' will only capitalise the first character of a word.
7. Input values starting with zero
Excel will automatically delete the zero from the start of values. This problem can be easily solved by adding a single quote mark before the number.
8. Use Quick Analysis to create graphs quickly
When you select a group of cells, the 'Quick Analysis' icon will appear in the bottom right hand corner. This tool pulls together your data and translates it into simple tables or graphs.
Click the icon and use the tabs to select the visual you want.
9. Automatic forecasting
A new feature in Excel 2016, users can select columns on a table and have the spreadsheet predict how the results will play out in the future using historical data.
Highlight your columns then go to Data > Forecast Sheet and set your parameters.
10. Don't use Excel?
If you don't like or use Excel or find it hard to get to grips with, there are alternatives available.
What are your go to Excel shortcuts? Tell us in the comments below.