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.

Linking text boxes

A trick to allow copying between sheets

Linking to a text box in a sheet is straightforward, unless you want to copy that linked text box to another sheet and retain the link. Here is how you do it.

Standard Technique

To link to a text box you click the text box and then click in the Formula Bar and press = and then click the cell to link to and press Enter.

This works OK on the sheet but if you copy the text box to another sheet it links to the same cell in the other sheet. If that’s what you want, great. If it isn’t then you need to use this technique.

Text box copy technique

Click the text box click in the Formula Bar and press = then instead of clicking on the current sheet click on another sheet tab and click a cell in another sheet then return to the current sheet and then click the cell you actually want to link to and press Enter.

By doing it this way the sheet name is included in the link and that ensures the link is kept when you copy the text box to another sheet.

You could also manually type the sheet name into the Formula Bar, but using the mouse is much easier.

Fix dd.mm.yy date format

On a recent Webinar I was asked a question about an unusual date structure that was imported. The structure dd.mm.yy was not recognised by Excel as a date. Here is formula that fixes it.

Below is an example of the date issue.

The formula in cell B2 is

=SUBSTITUTE(A2,".","/")*1

As you can see the dates in column A are left aligned. That is a clue that they are not recognised as dates in Excel. Dates are right aligned.

The SUBSTITUTE function replaces the full stop between the numerals with a / and makes it look like a date.

This isn’t sufficient as the SUBSTITUTE function will return text. The *1 at the end converts the text date in to a real date that Excel recognises.

Note: Power Query can also automatically fix dates like these when it imports data.

 

 

 

Free Webinar Recording – Excel Format as Table Feature

Feedback score 95% based on 58 responses

In March 2020 I presented a session on Excel’s Format as Table Feature. I covered it’s advantages and how to use it to improve your Excel files.

The detailed pdf manual and example file can be downloaded using the button below. Content is listed below the video.

Download Materials

CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.

In this session you will learn all about Excel’s formatted tables.

Many of Excel’s features and functions work seamlessly with formatted tables. They can help you improve the structure and reliability of your spreadsheet files.

Formatted tables can allow you to create powerful reports like those in a relational databases.

Topics covered

  • advantages and limitations of formatted tables
  • keyboard shortcuts
  • using formatted tables with formulas
  • solutions to some of the limitations of formatted tables
  • using range names with formatted tables
  • using formatted tables with data validations
  • creating a running total
  • using PivotTables
  • Relationships (Data tab)

As always I will share a few other tips.