Hi fellow Excel Addict,
Another spring snowstorm! Wait! Where did it go?
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)
If you missed my last newsletter, you can click here to view it online.
If you have a favourite quote, send it to me and I may post it in my newsletter.
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).
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 .???
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.
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;
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;
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.
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.
|"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