Grouping icons allow you to hide and unhide rows and columns really quickly. Sometimes you might want to hide the grouping icons to maximise the Excel grid. The keyboard shortcut to hide the grouping icons is Ctrl + 8 The same shortcut makes them visible again.
You may have thought the Pivot Table Wizard had been removed from Excel, but it hasn't. You can use the following keyboard shortcut to activate it. Alt d p Pressed in sequence, not held down. The Pivot Table Wizard doesn't recognise formatted tables, and you should only use it if you need to define Multiple Consolidation Ranges.
In a sorted list you can have Excel automatically insert subtotals based on the sorted column. With a cell selected in the table, the keyboard shortcut for the Subtotals Dialog is Alt a b Pressed in sequence, not held down.
In Excel you can click, then double click a ribbon tab name, to hide or unhide the whole ribbon.
To open the Data Validation dialog use the following keyboard shortcut. Alt a v v Pressed in sequence, not held down. Data Validation enables you to limit what a user can enter in a cell.
The keyboard shortcut to move between open workbooks is Ctrl + Tab Sometimes this can be quicker than using the mouse. Ctrl + Shift + Tab will go through the files in the reverse direction.
When doing validations in Excel you often need to check values against a tolerance figure, usually 1 cent or 1 dollar.
In Excel, to set or remove Freeze Panes use Alt w f f Keys pressed in sequence, not held down. Freeze Panes keeps certain rows and columns on the screen as you scroll down and across.
Online webinars have lots of advantages. They can be a great way to learn new topics. They can also be based solely on PowerPoint slides. It all depends on the topic and the presentation. (more…)
In Excel, if you want to view the next sheet to the right use Ctrl + Page Down Next sheet to the left use Ctrl + Page Up
If you want to see all the cell comments in an Excel sheet, in one step use Alt r a Keys are pressed in sequence, not help down. Pressing again, will hide all the cell comments. (Cell comments are like sticky notes for cells) Note in the most recent versions of Excel Comments have been renamed to Notes. The terms Comments is now used for threaded (linked) Comments.
A shortcut to hide (or unhide) the Excel ribbon is Ctrl + F1 Works for the ribbons in the other Office apps as well.
A keyboard shortcut to enter today's date in a cell. Ctrl + ; Hold the Ctrl key down and press the semi-colon. This is a data entry and it won't change.
In Excel to apply row or column grouping, select the range and press Shift + Alt + right arrow To remove grouping use Shift + Alt + left arrow After you use it you can use function key F4 to repeat it.
To insert or edit a cell comment in Excel use Shift + F2 Hold Shift key down and press the F2 function key. A cell comment is like a sticky note for a cell. It can contain extra information on the cell to help you or the user.
To apply the dollar format $9,999,99 in Excel use Ctrl + Shift + $ Hold Ctrl and Shift down and then press 4 on the keyboard (not the numeric keypad).
The shortcut key in Excel for Save As is function key F12. This works in all MS Office Applications. I use this all the time.
The keyboard shortcut combination to add or remove the Wrap Text format, where you can have multiple lines in a cell is Alt h w Press keys in sequence, no need to hold them down.
When you are using sheet passwords in Excel it can be handy to have a macro to unprotect all the sheets in one step. This can make maintenance easier.
(more…)
The more you use macros the more important they become and the more you want to make sure the VBA* code doesn’t get changed by someone who shouldn’t change it. You may also want to stop people viewing your code. (more…)