Microsoft Excel Tricks Tips

5 More Really Useful Excel Tricks & Tips

Excel Tricks Tips and More Goodies

This article contains 5 extra Excel Tricks Tips and more sage advice on using Microsoft Excel.

5 – Driving your Computer – Shortcuts (Excel Tricks Tips)

During our training courses it is very apparent that Delegates all have their preferred methods of driving their computer and I find it interesting just how committed Delegates are to their particular method. I am very impressed with those who have taken to learning keyboard shortcuts and use them without even giving them a second thought. However, I thought I would just review your options and you can decide for yourself how you might not only improve your speed but also your accuracy whilst remaining comfortable with your method mix.

The ribbon/ menu

Pros
Useful for a structured approach to driving your application.
Can be customised In Office 2010 and above.

Cons
Requires a few clicks.
Some choices are not where you might expect to find them. (Try finding Save Chart Template in Excel 2013. It’s not there! Use a right click.)

Tip
Look for Dialog Box launchers to quickly get to the “Fully Featured” dialog boxes.

Really Useful Excel Tricks The Ribbon Menu

The Right Click – The Context Menu
Pros
A great frustration reliever. Ensure that the point of the mouse is precisely over the object on which you wish to perform an operation. The context menus are getting better at each version.
They are often accompanied by a Mini toolbar.
Particularly useful in Charts where there so many discrete objects each with many properties.
Cons
Doesn’t always contain everything you might expect.
Tip
Take time to look at the choices available.

Keyboard Shortcuts
Pros
Used correctly, they are both predictable and quick.
Reduce repetitive strain injury issues.
Cons
Confusing when the wrong keys are pressed and an unexpected shortcut is invoked.

Really Useful Excel Tricks Tips Right Click The Context Menu

Tip
Those that work in all applications are well worth learning.
If there is any doubtthat your last action was as intended then review the list of “Undo-able Actions” from the drop arrow on the right hand side of the Quick Access toolbar’s Undo button.

Ctrl + Z is a shortcut for Undo (But you knew that!).

Warnings
The undo buffer in Excel is a common buffer for all workbooks that you are working on whereas in Word each document has its own undo buffer.
Since Office 2010 the Undo buffers are completely deleted after certain operations. For example delete a sheet and that’s all your undos deleted!

Quick Access Toolbar (QAT)

Really Useful Excel Tricks Tips Quick Access Toolbar

Pros
It is always available.
It can contain options which are not listed in the Ribbon.

Cons
With many items on the QAT it rather defeats the point.

Tip
With Office 2010 onwards you can export / import your QAT.
Right click on your favourite ribbon items to “Add to Quick Access Toolbar”.
To avoid interference with the Title Bar and file name choose “Show Below the Ribbon”.
Take the effort to customise it and use separators to create your own groups.

Warnings
Buttons on the QAT used to run a macro will have the full pathname of the file containing the macro embedded into the button definition. If the file containing the macro is renamed or moved then the button will need to be recreated otherwise it will attempt to run the macro in the original file. However, works well with your Personal.xlsb Workbook.

The Alt Key
The Alt key to left of the space bar will provide a method of driving the menus or ribbons in all Windows compliant programs. In older style (Pre Ribbon) menus the user can continue to work the menu by typing the underlined letter in the menu choice that they wish to make.

In this ‘Menu’ example:

The Alt key was pressed and then the letter F (For file.)
At this point the user could type:
S for Save
I for Import
E for Export
R for Remove..
P for Print
C for Close..

Really Useful Excel Tricks Tips The ALT key

In this ‘Ribbon’ example:
The Alt key was pressed and then the letter F (For file.)
At this point the user could type:
S for Save
A for Save As
O for Open
I for Info
etc. as indicated by the tags.

Really Useful Excel Tricks Tips The Ribbon Example

Pros
If your keyboard skills are accurate you can be prompted through these keypresses to the point where you will start to remember them.

Cons
This will require more key presses than any specific shortcut. For Example in MS Word / Excel to format in bold:
Keyboard shortcut: Ctrl + B
Ribbon shortcut: Alt,H, 1

Conclusion – Useful Shortcut keys

There are over 160 shortcut keys available in Excel ranging from function keys to combinations of keys involving Shift + Key, Ctrl + Key, Alt + Key, Shift + Ctrl + Key.

Here are some of my favourites:

Really Useful Excel Tricks Tips Shortcut Keys

7 – Working Backwards – Goal Seeker (Excel Tricks Tips)

So you have €15,000 which you wish to use to reward your staff for their hard work. You want to be absolutely fair. It is to be a percentage of their salary. You try 5% and that’s too much:

Really Useful Excel Tricks Tips Goal Seeker Manual Attempt

Let Excel work it out for you with a precision and speed that you will never achieve.

Goal Seeker
Goal seeker will try many values in one cell to attempt to obtain the result in a dependant cell that matches your requirement.


Data Tab > Data Tools Group > What-If Analysis > Goal Seek…

Note: The target “To value” cannot be a cell reference.

Really Useful Excel Tricks Tips Goal Seeker Automatic

Really Useful Excel Tricks Tips Goal Seek Status

Closer Inspection of the result shows:
4.54047309610774%

That’s the maximum precision that Excel works to (15 digits).

Really Useful Excel Tricks Goal Seeker Result

Another Scenario – Compound Interest:
Your savings are receiving compound interest of 10% per annum. (Not currently likely but a nice round number). They are invested for 5 years.
You could calculate interest for each year or, as shown in row 33 & 34, for the entire period.
Use Goal Seeker to work backwards from actual Interest received to deduce just what % interest rate you actually received.

Really Useful Excel Tricks Goal Seeker Compound Interest

8 – Random Function Rewards (Excel Tricks Tips)

Continuing the theme of true fairness in the workplace, imagine that you have been asked to place staff in random groups.
The following illustration shows a couple of functions and how to force recalculation of “Volatile Excel Functions”.

Column B uses the Excel Rand() Function which generates a random number between 0 and 1:
Cell B2=RAND()

Really Useful Excel Tricks Tips Random Function

Column C Ranks the random number in relation to all the other random numbers in the range of interest. This will give a whole number in the range 1 to however many numbers are in the list:

Cell C2 =RANK.EQ(B2,$B$2:$B$21)

Column D is just a method of then grouping the results into the appropriate groups using the INT() function. We are looking for 4 groups of 5 staff members each. The INT() function rounds a number down to the nearest integer.

Cell D2 =INT((C2-1)/5)+1

If you don’t like the results use F9 to force Excel to recalculate all volatile functions like:
=Rand(), =Today(), =Now(), =Offset(), =Indirect(), =Info() & =Cell()

9 – Excel’s Automatic Recalculation v Manual Recalculation (Excel Tricks Tips)

This is more of a warning than a tip.

Prior to the days of multi-threading multi-processor computers many of those of an Accounting persuasion noted that their large workbooks took so long to calculate that their keyboards either failed to respond or buffered their keystrokes whilst Excel was busy recalculating their workbook. The practice was to turn off Automatic Calculation:

Formulas Tab > Calculation Group > Calculation Options > Manual

You have to remember to manually force a recalculation.

Typically you would use F9.

Really Useful Excel Tricks Calculate F9

I have seen many examples where users have failed to note that their Workbook is in need of a recalculation. The clue is in the status bar and the word:

Calculate!

The absence of the prompt is “Evidence” that the sheet is not in need of recalculation. (It has no “Dirty” cells).

Infectious Manual Calculation Settings

As soon as you go down this route you will discover that what you thought was a Workbook setting is “Infectious”!
A look at the Excel Options suggests that it is indeed a Workbook setting:

Really Useful Excel Tricks Manuak Calculation Settings


However, the Setting Migrates From File To File

The first file that you open in an instance of Excel will “Set” the rule for calculations for the Excel Instance. So if a “Manual Calculation Workbook” is open in Excel when you then open an “Automatically calculated workbook” the setting will “migrate” to the second file. If you then save that file, next time you open it you will discover that it has now become a “Manual Calculation Workbook”. The reverse is also true.

Tip
Avoid the problem. Don’t use Manual Calculation. Review you workbook, if you were to turn off Manual Calculation does it still make your Workbook unresponsive during recalculation?

If you must use a Manual Calculation Workbook investigate some simple macros to remind you to recalculate before Printing, before saving, on an open etc.

10 – The Power of Conditional Formatting (Excel Tricks Tips)

If you are looking to provide visual indicators in your lists, Tables and PivotTables take a detailed look into all the options available in Conditional Formatting:

Home Tab > Styles Group > Conditional Formatting >…

Really Useful Excel Tricks Tips Conditional Formatting

Really Useful Excel Tricks Tips Conditional Formatting

Really Useful Excel Tricks Tips Conditional Formatting

Really Useful Excel Tricks Tips Conditional Formatting

Really Useful Excel Tricks Tips Conditional Formatting

Use the Conditional Formatting Rules Manager to edit these defaults to suit your needs.

Highlight the entire row of interest

A frequently requested requirement is to flag up the complete row of data subject to some logic in a specific cell on that row. On the face of it one imagines that it is just a question of creating a Conditional formatting rule using “Use a formula to determine which cells to format”. However, when it doesn’t work it is difficult to troubleshoot.

Really Useful Excel Tricks Tips Conditional Formatting Trouble Shooting

So here is my step by step guide to solving this problem.

Objective: Identify Customers whose Date of Last Contact > 28 days ago using Conditional formatting.

Really Useful Excel Tricks Tips Conditional Formatting Example

Step1: Develop a formula and test it copies correctly
Note the use of a Mixed Address ($C11) which is referring to the Date of Last Contact

Really Useful Excel Tricks Tips Conditional Formatting Example

Step 2: Copy the formula into the New Formatting Rule for just one cell. Format as required.

Really Useful Excel Tricks Tips Conditional Formatting Example

Step 3: Use the Format painter to copy that formatting and apply it to all cells in your list.

Really Useful Excel Tricks Tips Conditional Format Painter

Step 4: Check the Conditional Formatting Rules Manager for any accidents.

Really Useful Excel Tricks Tips Conditional Formatting Example

Step5: Re-apply any numeric formatting as necessary.

Click here for more details on Microsoft Excel training courses.

Really Useful Excel Tricks Tips Links

Microsoft Excel Conditional Formatting Example

Wikipedia on Microsoft Excel

Stuart Tayler
10 June 2016