 |
|
| TheExcelAddict.com | |
| May 28, 2020 |
|
| Hi fellow Excel Addict, | |
| When I was
freezing here in Newfoundland in back in March and
April, I was watching other parts of North America
enjoying nice warm spring weather and I was envious. But today, when I see those areas having heat waves of 35-40+°C (90-100+°F), and summer hasn't even started yet, I'm perfectly happy to be living in this windswept part of the world where the only serious weather we have to worry about is an occasional winter snow storm or the remnants of a Caribbean hurricane gone astray. Newfoundland weather may not always be perfect but when the weather clears, the scenery here is amazing.    If you're somewhere where restrictions are being relaxed, I hope you are keeping safe. If you my tip today, please share it on social media and with your colleagues and any other Excel users you know. And while you're at it, please recommend that they sign up for my newsletter. Have a great day and keep on Excelling, Francis Hayes (The Excel Addict) Email: fhayes[AT]TheExcelAddict.com |
|
|
|
|
|
|
|
| Missed my last newsletter? | |
Click Here to View it Online |
|
| |
|
Having a positive attitude can help us in difficult times. I hope today's quote will help you foster a positive attitude today. Quote of the Day "If you can't fly, then run. If you can't run, then walk. And if you can't walk, then crawl, but whatever you do, you have to keep moving forward." -- -Martin Luther King, Jr. -- |
|
|
If you have a favourite quote, send it to me
and I may post it in my newsletter.
|
|
THIS WEEK'S 'EXCEL IN MINUTES' TIP |
|
Find All Values Not Rounded To 2 Decimal Places |
|
|
As you probably know, numbers in your reports that are not rounded as they are displayed can often cause problems. Have you ever been embarrassed after you've given your boss or a client a report and they come back and say, "This doesn't add up." Sometimes when a worksheet is printed, it may appear that some of the totals are incorrect because the numbers that are printed may appear to be rounded (e.g. to 2 decimal places), however, the underlying values in the Excel worksheet may not be. In the example below, the total of the three numbers in column E is 1,036.9678. However, the numbers in column B are the same numbers as column E but formatted to display only 2 decimal places. Although, the individual values appear to be correct, the SUM of those numbers include the values that are greater than 2 decimal places and this sometimes causes a total to appear incorrect.  Also, if you copy or import data from some other source into a worksheet where numbers have been formatted to two decimal places, often things don't add up. You may immediately realize that the problem is a result of 'rounding' but finding every cell in a very large worksheet that has a rounding problem can be quite a challenge. After being faced with this challenge multiple times over a short span of time, on a fairly large set of data, I finally decided I needed to find a better solution than just changing the number format to General and manually looking one by one for values with more than two decimal places. The solution I came up with was to search for values in the worksheet that have a number in the third decimal place. One of the wildcards that can be used in searching is the ? as a placeholder for any single unknown character. So, using this knowledge, 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 values with only two decimal places don't have a third digit after the decimal, they will be ignored. Only values with three or more decimal places after the period will be found. How to find and highlight all of them If you'd like, open this practice file and follow along. 1) Press CTRL+F to open the Find dialog; 2) Type .??? in the 'Find what' field and click the Find All button. You will see a list of the matching cells at the bottom of the Find and Replace dialog with the first item highlighted; 3) 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 become selected on the worksheet;  4) At this point, just close the dialog; 5) While all of the 'found' cells (i.e. those with three or more places after the decimal) are still selected on the sheet, apply some format to them (I usually add a yellow fill color) to make them easier to identify for working with later. Now you can edit these cells as needed. |
|
|
|
|
|
|
|
 |
|
|
|
|
 |
|
| 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 16 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. | |