Marking cells Good-Bad-Neutral

Sometimes when you are reviewing a file against a printed report, you may need to identify when cell values are correct, wrong or close.

Consider using the built-in Styles Good/Bad/Neutral on the Home ribbon tab – see below.

These can be quicker than using the usual fill colour icon.

Remember they may not be useful for colour blind readers if you are sharing with other people.

Don’t forget once applied if you want to apply the same format to another cell press the F4 function key.

Once formatted you can sort or filter by colour – see right click options below.

Adding Values to Values

Let’s say you have an input cell that someone enters multiple values into eg

=10+5+34

You may have many such cells.

Now let’s say you want to add 20 to all these cells but keep the original values that have been entered in those cells.

Paste Special to the rescue.

  1. Enter 20 in a blank cell and then copy the cell
  2. Select the cells you want to amend – you can hold the Ctrl key down to select  multiple cells with the mouse
  3. Open the Paste Special dialog
  4. Click the options Values and Add – as per image below – then click OK – done!

The resulting formula will be something like

=(10+5+34)+20

 

Find and Replace Tips, Tricks and Traps

The keyboard short for Find is Ctrl + f.

For Find & Replace it is Ctrl + h.

Tips

Always, I mean ALWAYS, select the range you are working with before you run Find and Replace.

If you have a single cell selected it will affect the whole sheet – maybe not what you want.

Leave the Dialog Open

In the old days we used to close the Find dialog. Now you can leave it open if you need to change things in multiple files or sheets. You can navigate around with the dialog left open.

When using Find if you want to select all the cells it has found, click in the bottom section of the Find dialog where the cells are listed and press Ctrl + a this will select all the cells at once.

Number Formats

Be aware that sometimes numbers won’t be found due to formatting. eg if you search for 1000 but you have used the comma format eg 1,000 then the number might not be found.

You might need to do two Finds, with and without commas.

Formulas vs Values

Click on the Options button to see these options.

The default Look in: setting for Find (unfortunately) is Formulas. See image below.

This means if a formula returns what you are looking for it won’t be found. You need to switch this Look in: setting to Values.

Data Validation Search – Free Add-in

Jon Acampora
2017-04-27

A common Excel request is to be able to type characters and see the in-cell data validation drop down list reduce, based on what you have typed.

This free add-in from Jon Acampora (Excel MVP) does just that. He has recently added a few new features.

If you have long drop down lists this add-in is a great addition to Excel.

This link has a video of how it works and the new features like Auto Open when a data validation list cell is selected.

The Happiest Refugee

Highly recommend.

This book has it all, humour, drama, intrigue, family secrets and running jokes throughout.

Plus its all true. Anh is a nice guy and a great comedian and he writes well. I didn’t realise he had done so some many things, and done them successfully. It shows the impact of a new country on refugees. A great read!

I heard it might be made into a movie – look forward to it.

The R Language

David Iseminger - Microsoft
2017-03-29

If you are into statistics then you probably already know about the R language – but if you don’t it may be worth looking at.

It is open source code that is built to handle statistics and big data. It has some limitations when used with Power BI but it can be used on its own.

This article looks at using visuals created with R in Power BI.

DAX Studio

DAX is the function and query language of PowerPivot and PowerBI. It has been around for a while, but it didn’t really have many editing tools until DAX Studio came around.

DAX Studio has been created by the SQLBI.com guys – the legends in PowerBI.

I saw a demo today at the Perth Modern Excel User Group at Microsoft Perth – its great.

Whilst it is a powerful editor, it is not just a code editor.

There are lots of options to allow you to optimise your DAX code. Lots of output options as well.

You can test and see how long it takes to run your DAX commands.

If you regularly use DAX in Power BI or PowerPivot then DAX Studio is a must have.