Excel Hints, Tips & Hacks - Part 2
Updated: Feb 20
The second part of our multi series of quick Excel hints and tips to help you become an Excel Expert.
Need to Quickly Visualise Numbers into Red, Amber, Green? Use Excel Icon Sets
You can find these under the Excel conditional formatting menu (Home --> Conditional Formatting) and as you can see below there are a wide range to choose from.
You can set up the parameters for these by going into Home -> Conditional Formatting -> Manage rules, which will give you the below menu:
Need to Only Display the Icon in the Above Without the Number?
You can do this in the conditional formatting settings menu by selecting the cells you want to change then click Home --> Conditional Formatting --> Manage Rules and then ticking the box circled in the screenshot below
This then results in the below, icons displayed without the underlying numbers:
Highlight All Duplicates In A Chosen Range
This one is a quick and easy way of highlighted any duplicate values within a range. Firstly, select the range you want to check for duplicates.
Next, go into the conditional formatting (Home --> Conditional Formatting) and then click on 'Highlight Cell Rules'
At the bottom of that menu there will be 'Duplicate Values...' select that option
Next, you'll see the menu below. You can choose what colours scheme you want using the dropdown to the right hand side. Click OK when done.
Any duplicates will then be highlighted in your chosen colour scheme
Highlight The Top (or Bottom!) 10 Numbers In a List
In a similar method to the above, you can also highlight the top or bottom numbers in a list. This does not need to be limited to 10, but that is the default option.
Highlight the range you want to conditionally format. Then go to Home --> Conditional Formatting --> Top/Bottom Rules
As you can see you then have lots of options. For this example, we'll choose 'Top 10 Items' but you can see alternative options on the menu above
You then have the option of changing the number you want to highlight (Top 10, Top 20 etc.) and can also change the colour scheme
Click OK once done and you will then see the below result:
You can see that the 10 highest values have been formatted using the above method.
Other Conditional Formatting Standard Menu Options
You're not limited to just duplicates and as you can see using the menu above you can also do things such as format numbers greater than, less than, equal to or between two values you provide.
You've also got options such as highlighted certain date ranges or text that begins with, ends with or that contains certain text.
There's some really useful options in there that there are definitely worth exploring if you're not familiar with them.
Rename a Sheet Quickly
As opposed to right clicking and clicking 'rename sheet' you can also double left click not the sheet name to update it
Input Todays Date in a Cell
Use Ctrl + :
Input The Current Time in a Cell
Use Shift + Ctrl + :
Input The Current Date & Time in a Cell
Use Ctrl + : then press space and then Shift + Ctrl + :
Format a Number As Thousands
This can be useful when you want to retain the true value of a number, but display it in a condensed format of thousands. You can do this in the custom number format box of Excel (right click --> format cells)
As you can see in the above example you enter a 0 for whole thousands (0.0 if you wanted to see it to one decimal place) and then put a comma after it, the comma indicates that you want to display it in thousands.
The "k" in quotes indicates to Excel that you want to display the letter K after the number, and this will not affect your ability to calculate or use the number in formulas.
This is a neat way of displaying the number in a more readable format, without having the confusion and risk of error of multiplying/dividing numbers manually when changing format.
You can also extend this by adding a currency symbol before the 0 value in the custom formatting box.
Format a Number As Millions
This works identical to the thousands scenario above, except that this time you use two commas after the 0 (or 0.0 if you wanted 1 d.p). This time we've used the "m" after the number to indicate millions