Overlaying images to compare them

You can make one image partially transparent if you want to compare two images in either Word or Excel.

For example, imagine you want to compare this image:

clip_image001

with this one:

clip_image002

First change the colour of the second image so it’s more obvious which is which. Select the image and then go to the Picture Tools ribbon and select Color and choose a colour from the Recolor options.

clip_image003

Here I chose a green colour

clip_image004

Then make the white areas transparent. Select the image again and on the Picture Tools ribbon select Color > Set Transparent Color.

clip_image005

The cursor will change to a pen symbol. Click on a white area of the image to make that area transparent.

If you are doing this in Excel you can now drag one image over the other to compare them. clip_image006

If you are doing this in Word you need to change the wrap text settings before you can overlay the two images. Select the second (green) image and on the Picture Tools ribbon select Wrap Text > In Front of Text. You can then drag it into position over the first image.

image

Done!

Highlight duplicates within rows in Excel

Sometimes you want to check if the same entry appears more than once in the same row. For example in this dataset you may want to know that Jan appears twice in the first row.

image

You can use conditional formatting to spot these duplicates like this.

  1. First select the cells in the first row that you want to look for duplicates in.
  2. Then use Conditional Formatting > New Rule > Use a formula to determine which cells to format.
  3. In the formula section put =COUNTIF($A1:$F1,A1)>1 . In my data the row starts and ends at A1 and F1. Change these values within the formula to match your data.
  4. Set the format you want using the format button and select OK.

image

Now the duplicate Jan will be highlighted in the first row like this

image

Now you just need to copy this formatting to each row of your data. One way to do this is to use the Format Painter.

  1. Select the first row of data again and then click on Home > Format Painter.
  2. The mouse cursor will change to a paintbrush symbol.
  3. Now drag the mouse to select all the rest of the rows of data at once and then release the mouse button.

image

Done!

Automatically create backup copies of excel files

You can set Excel 2010 to create a backup copy of the workbook every time you save. This is stored in the same folder and named Backup of (your filename).xlk. Like Word’s backup copies, it is a copy of the version you last saved.

You set this through the Save As dialogue.

Go to File>Save As
Choose General Options from the Tools menu at the bottom right of the Save As window.

Excel save general options

Then tick Always create backup

Excel always create backups

This setting will then apply to this particular workbook. You will have a current version and one previous version saved in the same folder.

If you want it to be the default for all new spreadsheets, you need to apply this setting to the file called book.xltx which is located in your XLSTART folder. That folder is usually located here C:\Users\username\AppData\Roaming\Microsoft\Excel\XLSTART