Excel Tips & Tricks
Posted on October 12, 2014 by Christabelle HarrisMicrosoft Excel is a powerful program, and you can make it even more powerful by learning the Excel tips and tricks, that help make you an Excel guru.
Master the clipboard pane
The clipboard can be very useful when using Excel, as you often need to cut or copy and paste information from one part of a sheet to another, or between different sheets and workbooks. The chances are that you’ll need to repeatedly copy and paste different items at different times, depending on the complexity of the task in hand.
You can make this easier by opening the clipboard pane as it enables you to handle multiple clipboard items at the same time. On the Home tab of the ribbon, click the arrow button to the bottom right of the Clipboard group to toggle it open or closed.
Open ribbon dialogs
You’ll find similar buttons in the bottom right of numerous different ribbon groups on various different tabs. Clicking one will open a related dialog that includes each of the tools in that group, plus a few more for good measure.
If you’ve used Office in a version prior to 2007, you may recognise many of these dialogs as they’re identical to ones that used to be launched from Excel menus in Office 2003 and before. Where a ribbon group doesn’t have a dialog button, it has no more hidden tools.
Update calculations manually
By default, Excel performs calculations in real time, updating each value as you input numbers or as the data changes. This is a handy feature because you can be sure that all your values are up to date.
However, as spreadsheets become more complicated and include thousands of values or more, automatic calculation can cause a significant lag after each keystroke.
You can disable this feature by going to the Formulas tab of the ribbon and choosing ‘Calculation Options | Manual’ from the Calculation group. Now you need to press [F9] to update calculations after inputting any new data.
Lock copy formatting
If you need to apply the same formatting to a number of different cells, you can do so quickly by locking the Copy Formatting tool.
Apply the formatting options that you want to the first cell – for instance, bold red text with a yellow background. Select this cell and double-click the ‘Format Painter’ button in the Clipboard group of the Home tab.
This is now locked on. Any cell that you click into will take on the formatting of the source cell. Click each cell you want to format in this way and then double-click the ‘Format Painter’ button to turn the lock off .
Customise the Quick Access Toolbar
The Quick Access Toolbar appears in the top left-hand corner of the Excel window in Excel 2007 and 2010. By default, it contains buttons to save your work and undo or redo/repeat the last action. You can customise what appears here, and it’s a very useful place to dock your most commonly used tools.
Click the ‘Office’ button followed by ‘Excel Options’ and ‘Customize’ in Excel 2007. In Excel 2010, choose ‘File | Options | Quick Access Toolbar’. Select a tool that you want to add in the left-hand column and click ‘Add’. Change the available commands using the dropdown list labelled ‘Choose commands from’. Click ‘OK’ to save your changes.
Move the Quick Access Toolbar
When you’re customising the Quick Access Toolbar, you can opt to have it displayed at the bottom of the ribbon instead of at the top of the window. This means you won’t have to move the mouse pointer as far to access each tool in the Quick Access Toolbar.
Tick the box marked ‘Show Quick Access Toolbar below the Ribbon’ in the ‘Customise Quick Access Toolbar’ dialog. You can opt to apply your customisations to the current file or to all Excel files.
Increase compatibility
There are plenty of people still using Office 2003 and earlier. If they haven’t kept up to date with updates and import filters, there’s every chance that they won’t be able to open ‘.XLSX’ files without going to some trouble.
Remove this potential hassle by setting your default save format as Excel 97-2003. Click the ‘Office’ button and choose ‘Excel Options | Save’. Under ‘Save Workbooks’, choose ‘Excel 97-2003 Workbook’ and click ‘OK’. In Excel 2010, simply choose ‘File | Options | Save | Excel 97-2003 Workbook’.
Copy as a picture
If you need to copy a chart or a set of cells to a new location outside Excel, consider doing so as a picture. You won’t be able to further process the data, but all your formatting options will be preserved.
Select the chart or cell range and then click the downward arrow below ‘Paste’. Choose ‘As Picture | Copy As Picture’. You can now paste the image file wherever you like.
Name a range
Quickly create a named range of cells that you can return to later by selecting the cells you want to name and then clicking in the cell name bar directly to the left of the formula bar.
Enter the name you want to give to this range of cells and press [Enter]. You can now quickly return to this cell range by clicking the arrow to the right of the cell name bar and choosing the range that you previously saved. You can store several cell ranges.
Change the editing order
When you’re entering data in a set of cells and you press [Enter], by default you move down to the next cell in the column. If you’re entering data across rows, you can change this behaviour.
In Excel 2007, click the ‘Office’ button, followed by ‘Excel Options’, or in Excel 2010, choose ‘File | Options’. Now select ‘Advanced’ and tick the box marked ‘After pressing enter move selection’. Change the direction in the dropdown list.
Compress pictures
If you have pictures or other graphical elements embedded in your Excel spreadsheet, they can greatly increase the document size. If you need to email the spreadsheet or post it online, you can make the size much smaller simply by compressing all the pictures in the sheet.
Select one of the pictures and you should see the Format tab under ‘Picture Tools’. In the ‘Adjust’ group, choose ‘Compress Pictures’. Click ‘Options’ and ensure that the options to delete cropped parts of the picture and to apply basic compression when saving are both enabled. Below these, select the level of compression you want to use and click ‘OK’ twice.
Prevent error checking
Formula error checking is very useful – for example, when it spots an inconsistent formula in a particular region. However, there are times when you might need to leave out a few cells in a particular region and Excel will flag the error.
You can get rid of this annoyance by turning off the error check. Go to ‘Excel Options | Formulas’ and clear any of the error-checking rules that you need to turn off .
Create custom autofills
Autofill helps you complete columns or rows of sequential data. Enter the first two values in the initial cells to establish a pattern, then select the cells and drag the fill handle to extend to the rest of the cells you want to fill with that pattern.
You can add your own custom lists by choosing ‘File | Options’, or press the ‘Office’ button and select ‘Excel Options | Popular | Edit Custom Lists’. Select ‘New List’ and click in the ‘List Entries’ column. Type each entry in the list, followed by [Enter]. Click ‘Add’ to finish.
Create keyboard shortcuts
Press the [Alt] key and you’ll be able to see all of the keyboard shortcuts that are available for each visible tab on the ribbon. Press the key that’s related to that tab to go to it, and to see a map of the keyboard shortcuts for that tab.
This is very handy as you can use it to remind you of the available keyboard shortcuts whenever you want to use them, saving you time and eff ort and maximising your efficiency as you work.
http://www.techradar.com/au/news/software/applications/15-handy-excel-tips-tricks-and-techniques-907027