Increase decimals
To increase the number of decimal places press in sequence Alt h 0 (Alt h zero) To decrease the number of decimal places press in sequence Alt h 9
Excel Hiding and Unhiding Error Fix
Sometimes when you hide or unhide, rows or columns, you can get an error message saying that Excel can’t move objects off the sheet. The solution is in the macro below.
(more…)Activating a sheet in VBA
Here’s the problem. You have a number of sheets that are named after Department codes. Those sheets contain the details for each department. You have reports throughout the model that refer to these department codes. You want to be able to select a cell that contains a department code and click a button that will take you to that department’s sheet.
(more…)Excel Options Dialog
To open the Excel Options dialog you can use, in sequence Alt t o Don't hold down the keys, press one after the other. To open the Advanced tab Alt t o a
Close Existing File
To close the existing file you can use Ctrl + w If you want to save and close you can use on after the other. Ctrl + s Ctrl + w The s and w keys are close to each other, so it can be a quick way to save and close a file with one hand.
Useful Formatted Table shortcuts
These apply to a formatted table (Format As Table icon used from Home ribbon - see below) Ctrl + a Selects the table data - no headings Ctrl + + inserts a new blank row in the table Ctrl + - (minus) deletes a row from the table Ctrl + Space selects all the data in a column - no heading Shift + Space selects all the data in a row The shortcut below works in any table formatted or…
Running a Macro From the Quick Access Toolbar
The Quick Access Toolbar (QAT) can really speed up your work in Excel. You can even attach macros to the QAT. (more…)
Copy As Picture
When copying between Excel and Word or PowerPoint, in some cases you may want to copy the range as a picture. This has the advantage of being easier to re-size and manipulate in Word or PowerPoint. Select the range first and then use the drop down on the Copy icon on the Home ribbon - see below and select Copy as Picture A dialog will display allowing you to specify the type of picture to create. Click OK to accept…
Adding a Percentage to a Subtotalled List
Excel’s formulas are powerful. As an example we can create one formula that can be copied down to add a percentage calculation to a subtotalled list. This formula demonstrates a couple of useful techniques. (more…)