We have talked about Excel shortcuts here are some more tips which will help you to be more productive with Excel.

How to Hide/Unhide a particular Row or Column

Select the rows or columns to be hidden; now under the ‘Format’, you can get the option to hide or unhide the Rows or Columns.

Move around the Spreadsheet, using your Keyboard

• Ctrl-Arrow:  Move to the first/last data cell in the arrow direction
• Ctrl-Shift-Arrow:  Selects the cells between the current cell and the first/last data cell

People working with loads of spreadsheets will definitely find this useful

The SUMIF Function –

This is used for conditional additions, and to use this SUMIF(Range,”Comparison”,SumRange)

The SUMPRODUCT Function –

Useful, if you need to multiply two columns and need the sum of the multiplication.

Rounding Functions (ROUND, ROUNDUP, ROUNDDOWN) –

• ROUND(Number,Digits); Will round the number (or cell) to the specified number of digits
• ROUNDDOWN(Number,Digits) and ROUNDUP(Number,Digits) work the same way as ROUND, but the direction of rounding is specified by the function

The AUTOFILTER Command –

You want to find some specific information, e.g. all sets that meet a criterion or the top 10 items etc., from a huge database.

• Click into your table or better mark the data area and select Data: Filter: Autofilter
• Using the drop-down boxes per item allows you to display only specific filtered information
• Selecting multiple matches (up to 3 maximum with autofilter) you can narrow down your search
• Or add your own criteria for filtering by clicking on the custom criteria

GROUP/UNGROUP Parts of Spreadsheets -

If hide or unhide parts of a complex spreadsheet is something you need to do very often. Mark the row or column that you would like to “fold”, i.e. hide for the moment.

• Click on Data: Group and Outline: Group
• To “fold” click now on the “minus” sign outside of your column or row
• You may also group or ungroup hierarchically

Handful of Useful Shortcuts –

• Alt + ‘ Display the style dialog box
• Ctrl + 9 Hide rows
• Ctrl + Shift + 9 Unhide Rows
• Ctrl + 0 Hide Columns
• Ctrl + Shift + 0 Unhide Columns
• F6 Next pane
• Alt + F8 Macros Dialog Box
• Alt + F11 Visual Basic Editor
• Ctrl + ‘ ( ~ ) Toggle formula display
• Ctrl + Shift + } Selects all cells within formulas that directly or indirectly refer to the active cells

Shortcuts for special Characters –

• Alt + 0149
• £ Alt + 0163
• ¥ Alt+0165
• ^TM Alt+0153
• © Alt + 0169
• ¼ Alt + 0188
• ½ Alt + 0189
• ¾ Alt + 0190

I got these tips in email from a friend who regularly works with Excel, hope you find these useful.