How-to handbook: 10 quick Excel hacks

CA Today
By Eleanor O'Neill, CA Today

27 June 2016

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.

Google Sheets is a cloud-based component of Google Docs. Apache Calc is part of an open-source office software suite. Zoho Sheets is also use in the cloud and is primarily app based.


What are your go to Excel shortcuts? Tell us in the comments below.

Topics

  • Technology
  • CA life

Previous Page