Search
  • Chris Rowley

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



Expert Excel Hints & Tips


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.



Microsoft Excel Specialist Hints & Tips

You can set up the parameters for these by going into Home -> Conditional Formatting -> Manage rules, which will give you the below menu:



Insightful Dynamics Excel Hints & Tips

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



Excel Hints & Tips


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



Hints & Tips - Insightful Dynamics - Excel Specialists

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:



Microsoft Excel Hints & Tips

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)



Expert Excel Tips

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


Expert Excel Hints and Tips

If you'd like to receive a notification when the next part of this series is posted shortly then you can subscribe to the blog.

If you found this article useful then you can subscribe for further content, you can also visit Insightful Dynamics at www.insightfuldynamics.co.uk or contact us at contact@insightfuldynamics.co.uk.

0800 193 4700

  • LinkedIn
  • Facebook
  • Twitter

©2020 by Insightful Dynamics.

T&C's & Privacy Policy