Nine uncommon Excel Tips

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.

hide-unhide-rows-or-columns-in-excel

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.