Microsoft Excel Tips and Tricks - Computergaga

Home / Archives for Excel Tips

Entering Fractions in Excel

Entering fractions in Excel can be confusing. When a fraction is entered, Excel converts the value to a date format e.g. 1/2 is stored as 01-Feb.

This is frustrating and without storing the value correctly as a fraction, any formulas dependent upon this value will not work correctly.

To enter a fraction in Excel, type a 0 and a space followed by the fraction. Excel then stores the number as a fraction and reduces it to the smallest possible denominator. So if you type 0 2/16, Excel stores it as 1/8.

Now with a fraction stored correctly the cell can be referred to within formulas without error. Further formatting can also be applied to the cell through the Format Cells dialogue box.

Formatting fractions in Excel
[Read more…] about Entering Fractions in Excel

6 Secret Excel Buttons that Many People Do Not Know

In this blog post, we explore the secret Excel buttons that are hidden in plain sight (yes on the Home tab), yet many people do not know them.

There are 6 buttons I would like to mention. They have been ordered both by how useful they are, and almost my view on how aware people are of them.

This list does not even include Conditional Formatting, Clear Cells or the Copy as Picture tool which could all have easily made the cut.

Watch the Video – 6 Secret Excel Buttons

[Read more…] about 6 Secret Excel Buttons that Many People Do Not Know

10 Excel Paste Special Tricks to Make you a Pro

To be an Excel pro you must master these Paste Special tricks.

Copy and paste is one of the most common tasks a computer user performs daily. Paste Special in Excel allows you to specify which elements of the copied item get pasted. It also allows you to perform operations on values and other cool stuff.

This blog post explores 10 Excel Paste Special tricks that an Excel pro needs to know.

[Read more…] about 10 Excel Paste Special Tricks to Make you a Pro

Highlight the Cells that Contain a Specific Word (and it Matches Case)

In this blog post, we look at how to highlight the cells that contain a specific word. We will also ensure that the word in the cell matches the case of the word being looked for.

For this example, we have a list of the Ballon d’or winners of all time. Column B contains 3 letters in uppercase (after the name) which identify the country that the player represented at the time of winning the award.

In cell E1 I have entered the 3 digits for a country. I would like to automatically change the colour of all the cells that contain the country written in E1.

Highlight cells that contain a specific word

There is a good chance that the 3 digits identifying a country could also occur in a players name. For example, the letters for France – FRA do occur in the name Franz Beckenbauer.

To prevent this happening, we will match the case of the word we are searching for, as it is always written in upper case.

[Read more…] about Highlight the Cells that Contain a Specific Word (and it Matches Case)