The Excel Addict - Help with Excel 2013, 2010, 2007, 2003
April 28, 2016
 

Greetings from The Excel Addict
Hi fellow Excel Addict,

Another spring snowstorm! Wait! Where did it go?

2016-04-27 weather Conception Bay South, Newfoundland

In today's 'Excel in Minutes' tutorial, I will show you a simple trick to 'Quickly Find All Values Not Rounded To Nearest Cent' or any other decimal place for that matter.

If you missed my 'Excel in Seconds' newsletter on Tuesday, I showed you how
'Moving the Active Cell Around in a Selected Range' is easy when you know the right keyboard shortcuts You can read all about that here.

Wishing you another great day of Excelling,
Francis Hayes (The Excel Addict)
Email:  fhayes[AT]TheExcelAddict.com


Francis Hayes (TheExcelAddict.com)





If you missed my last newsletter, you can click here to view it online.
 

 
TheExcelAddict.com Quote of the Day

"If your success is not on your own terms,
if it looks good to the world but does not feel good in your heart,
it is not success at all."

-- Anna Quindlen --

If you have a favourite quote, send it to me and I may post it in my newsletter.


Today's Microsoft Excel Tip

Quickly Find All Values Not Rounded To Nearest Cent

I published this tip a couple of years ago but I think it deserves a repeat...for those who are new to my newsletter and those who may have missed or forgotten the tip from before.

It is a trick I discovered a few years ago when I worked in the Finance department of a large insurance company. This trick saved me so much time and frustration after many years of using a tedious and time-wasting manual solution.

In the finance department, I worked with a lot with journal entries. Information from various sources would feed into these journal entries. One of the issues that occurred frequently was when a journal entry appeared to be balanced but upon closer inspection, it was populated by one or more values that weren't rounded to the nearest cent (i.e. 2 decimal places).

Out Of Balance Error On Journal Entry in Microsoft Excel 2007 2010 2013 2016 365
My first response was usually go back to the provider of the data and ask them to use the ROUND function in their formulas to prevent this from happening.

However, often this data is copied or imported from some other source (e.g. CSV file, vendor website, screen copy, etc...) into a worksheet (i.e. the journal entry) where the numbers have been formatted to display two decimal places.

After being faced with this problem multiple times over a short period on several large sets of data, it prompted me to finally look for a better solution than simply changing the number format to General and then looking for the values showing more than two decimal places.

The solution I came up with was so simple. I can't believe it took me so long to figure it out. What I did was search for values in the worksheet that have a digit in the third decimal place.

One of the wildcards that can be used in the Find and Replace dialog is the question mark (?). The ? is used as a placeholder for any single unknown character in your search string. With this, I searched (CTRL+F) my worksheet for .???

Find Values With Three Or More Decimal Places in Microsoft Excel 2007 2010 2013 2016 365
This (.???) tells Excel to find cells that have a value that contains 'a decimal place followed by three characters/digits'. Since the values rounded to two decimal places don't have a third digit after the decimal, they are ignored. Only the values with three or more decimal places after the period will be found. And it worked perfectly!

To see for yourself how this works, please open this practice file and follow the steps below.

Note that this is practice file has just a very small sample of data but this trick is most useful in worksheets with a lot of data. And this tip is not just for journal entries or just two decimal places. This trick can be used on just about any worksheet you have that may contain rounding errors.

DOWNLOAD PRACTICE FILE HERE

1) Select the range of cells that you want to search;

2) Press CTRL+F to open the Find dialog;

3) Type .??? in the 'Find what' field and click the Find All button. You will see a list of ALL the matching cells at the bottom of the dialog with the first item highlighted. If you can't see the full list, point to the bottom right corner of the dialog and drag to resize it;

Find All Values With Three Or More Decimal Places in Microsoft Excel 2007 2010 2013 2016 365
4) With the dialog still open, press CTRL+A to select all of the items in the list. You will notice that all of those cells are now selected on the worksheet as well;

Find All And Select In Worksheet in Microsoft Excel 2007 2010 2013 2016 365
5) At this point, you can click the Close button to close the dialog;

6) While all of the 'found' cells (i.e. those with three or more places after the decimal) are still selected on the sheet, I recommend adding a background/fill color to make them easier to identify for working with later.

Highlight All Found Cells in Microsoft Excel 2007 2010 2013 2016 365
Now you can edit those cells as needed.

Can you see opportunities for using this trick in any of your own worksheets?

Be sure to download my practice file to understand exactly how this works for finding rounding errors for any number of decimal places.

DOWNLOAD PRACTICE FILE HERE

Hospitals Are #NotATarget


Thanks for supporting this newsletter and website

Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 10 years as someone who provides exceptional value to my readers. So I'm not willing to risk that. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are EVER not completely satisfied with anything I recommend, please let me know and you will get your money...GUARANTEED. You can't lose.
"Spreadsheets Tips From An Excel Addict" is a weekly publication of TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved.

8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630