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.
