Getting the most from Excel is a vocation – there are books bigger than concrete blocks on the subject.
If
you're dedicated enough to read one or two, the passage to an Excel
black belt is assured – as is the loss of weeks or maybe months of your
life.
Instead, try our Excel tips – they should ensure you're getting more from Excel in next to no time.
1. 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.
2. 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.
3. 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.
4. 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 .
5. 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.
6. 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.
7. 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'.
8. 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.
9. 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.
10. 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.
11. 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.
12. 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 .
13. 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.
14. Autocorrect hacks
You
can save time typing by entering shortcuts for commonly used terms in
Autocorrect. In 'Excel Options', choose 'Proofing | Autocorrect
Options'. On the Autocorrect tab, enter the shortcut you want to type,
followed by the phrase you want to automatically replace it with.
Make
sure your shortcut isn't a number or letter combination that you're
likely to use in your spreadsheets as it will be replaced automatically
each time you type it. To apply your shortcut, click 'Add', followed by
'OK'.
15. 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.
