Images loading below. While you wait, can you answer this Excel related tip? Guess the answer then click SEE ANSWER to see how it can be done.

1. How do you copy from Excel (see 1 below) and paste into Word (2) WITHOUT seeing the filter button, gridlines, comment notes and more?

Guess and then view answer to see how it can be done!

2. How do you take all the formatting from chart (see 1 below) and PASTE it onto another chart (2)? Guess and then see how it can be done with 'See Answer'



If you know the answer but want the input from Excel

When you are working with complex calculations you sometimes know what you want the answer to be, but you can’t calculate backwards to determine the answer. So for example, if you know what the take home pay needs to be, but are not sure what the pre tax income needs to be to achieve it, you can get Excel to Goalseek it. This way you build/ use a calculator that logically goes from income to take home pay, but you get Excel to go backwards.

YouTube Know the answer but want the input

Know the cash payment but not the pre tax amount

Below the Tax Tables in South Africa. We want to know what the blue input cell (A31) needs to be so that the Take Home Pay (answer cell J31) is R750 000. The red box is the calculations required to determine this.

If you know the answer but want the input from Excel

We can manually type in some numbers into cell A31 and see how close we come (so trial and error) or get Excel to do it for you.

So click on:

  • Data Ribbon, then
  • What If Analysis, then
  • Goalseek (as shown below)
If you know the answer but want the input from Excel

Now follow the dialogue box. Tell it

  • which cell you are working towards (the take home pay),
  • what value do you want it to become (R750 000 in this case), and
  • which cell is Excel allowed to change to achieve this number.
If you know the answer but want the input from Excel

Excel will then run through all the possibilities until it either finds an answer, or tells you that what you want can’t be achieved. As shown below, you need to start on R1 133 262.54 to end up with R750 000 assuming just tax deductions.

If you know the answer but want the input from Excel

This works for any calculation. So if you have a complex spreadsheet that takes an input and turns it into an output, Excel can work backwards so that it will tell you what the input needs to be in order to achieve a desired output.

You can learn more about Goalseek in the What If Analysis Course.

Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course  or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.

Slow Spell Check, GoalSeek and Find/ Replace in Excel

Goalseek precision

Simple way to create scenarios in excel (when you have already built the spreadsheet)

Impact of Interest increases on loans (calculator)