Goal Seek

The Goal Seek feature can save you a lot of trial and error when you want a calculated cell to equal a specific value.

The keyboard shortcut to open the Goal Seek dialog is Alt T G.

Related Posts

Shortcut to lock or unlock a cell

The keyboard shortcut to unlock or lock a cell or a range (its a toggle, so it switches between the two) is Alt H O L pressed in sequence, not held down.

Related Posts

 

Windows Clipboard

Did you know you can accumulate copied entries in the Windows clipboard?

The default setting is to only have the last thing you copied, but a setting change can give you access to multiple items to paste.

You can also pin items to the clipboard to keep them there.

To change the setting.

Click the Windows button and choose the Settings icon

The select System.

Then select Clipboard (bottom left) and change the Clipboard History to On – done.

 

If you copy multiple items you can press the Windows key and V to access what’s on the clipboard.

Then you can click the top right elipse icon to Pin the item to the clipboard.

Related Posts

Restart a Pivot Table in one go

If you want to clear all the fields from a Pivot Table you can use the clear all option.

On the PivotTable Analyze or Analyze tab click the Clear drop down and select Clear All.

 

This removes all the fields and allows you to start again from scratch.

Some actions clear the clipboard

Some actions in Excel will clear the clipboard after you have copied or cut.

Two common ones are

  • inserting a new sheet
  • clearing a filter – using Clear in the Data menu (keyboard shortcut Alt a c )

So if you are going to do either of these, do them before copying or cutting.

Related Posts

Fastest way to copy an image, graphic or chart

To copy an image, graphic or chart simply have the object selected and press Ctrl + D. You can press multiple time to paste multiple times.

If you line the first one up then the others will also line up as you duplicate them.

Related Posts

 

Make a Dent

Do you use Indenting in reports? Example below.

If you do, you make like this keyboard shortcut.

To increase Indenting press Alt H 6 pressed in sequence, not held down.

To decrease Indenting press Alt H 5 (is that an Outdent?)

 

Related Posts

 

Open the Filter Drop Down

If you have filters turned on and you are in the heading row of the table you can press Alt + down arrow to open the filter drop down.

You can then use the arrow keys to move up and down.

Related Posts

 

Bold Your Headings

Apparently this is not widely known, but you should always bold the headings in your tables.

Then when you use Format as Table (Ctrl + t) on the Home ribbon tab the header row will be correctly identified.

This also applies to the Ctrl + Shift + L shortcut to insert the filter drop downs.

It also applies to the ranges used for charts.

In general ALWAYS BOLD your headings – it is something Excel looks for.

Ctrl + b is the bold shortcut.

Related Posts

Delete a cell comment

From a question in a recent webinar I found a keyboard shortcut to delete a cell comment.

Alt r d pressed in sequence, not held down.

Printing Tip

If your sheet has a lot of colour and you want to print it on a black laser printer, one of the Page Setup options can improve the print.

On the Sheet tab of the Page Setup dialog there is a Black And White option – see image below.

This removes all the colour and prints in black only,

You can access the Page Setup dialog from the Page Layout tab. Click the small arrow on the bottom right of the Page Setup section.

 

Data entry and calculating

In some large models Excel may calculate for a few seconds after you make an entry.

In most cases you don’t have to wait for Excel to finish calculating before you make your next entry.

Type your entries as fast as you like. Excel will finish calculating once you are done.

Show all comments

Cell comments are useful for instructions and documentation.

If you want to make all the comments on a sheet visible, use Alt v c pressed in sequence, not held down.

Once visible this shortcut also hides all the comments in one go.

This is an old Excel 2003 shortcut that still works.

Name Box and Macros

I have found out yet another feature of the Name Box.

The Name Box is on the left of the Formula Bar and above the column letters – see image below.

During a recent macro webinar I tried to create a new range name called Test using the Name Box.

But I also has already created a macro called Test as part of the training.

As soon as I pressed Enter after typing Test into the Name Box to create the Test range name, I was magically transported to the VBA window to the Test macro – Wow!

This means you can’t create a range name in the Name Box that is the same as a macro name.

You have to use the Define name icon on the Formulas tab to do that.