Excel Number Format Trick
July 16, 2019
You'll get my next newsletter in 2 weeks -- we're on the summer schedule now.
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
If you have a column of numbers in Excel, and want to line them up at their decimal points, you can use the Number format, with a set number of decimal places showing.
However, that format adds zeros, so you can't easily see how many decimal places the original numbers have. For a cleaner view of the numbers, create a custom number format, with question marks after the decimal point.
For example, custom format -- #,##0.??? -- will show up to 3 numbers after the decimal point, with no zeros added. This example is in this week's sample file (below)
P. S. I've also had problems lining up currency symbols, in Accounting Format. See the solution to that problem on my Contextures blog.
We usually put one value in each cell on a worksheet, but my website shows you how to choose multiple items from a data validation drop down.
But after you create multi-value cells, how can you work with them? For example, a hotel rents rooms, with a different rate for each weekday. If a guest stays Sunday (40) and Monday (50) , their total cost would be 90.
What formula would you put in column D, to calculate the cost in each row, based on the days selected in column C? You can download the sample file (below) with the challenge, and my solution is below the screen shot.
If you'd like a hint, there's a similar example in the Count Numbers in Numbers section here.
=SUMPRODUCT(--ISNUMBER( SEARCH($F$2:$F$8,C2)), $G$2:$G$8)
Click here to get the sample Excel file for this week's newsletter. It has the number formatting sample, the formula challenge, and the formula solution. The zipped file is in xlsx format, with no macros.
Here are a couple of Excel articles that you might find useful or interesting.
Formula - Can you guess how Kevin Lehrbass sums the overlap of two ranges, before you read his article? (Level - All)
Excel Tips: You probably know most of the 22 Excel Tips in PC Mag's article, but maybe you've forgotten to use some of them recently, like making chart templates. (Level - All)
I've had this mouse for a couple of years, and use it every day. It works well, and I love using the scroll wheel to move around in Excel. But last week, I accidentally clicked on the little white logo, and finally discovered another trick -- that logo opens the Windows Start menu!
Does your mouse (or another gadget) have hidden features that it took you a while to discover? Do you ever read the little booklet that comes with a new gadget? Obviously, I don't!
NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2019/20190716ctx.html
I'll also post any article updates or corrections there.
That's it for this week! If you have any comments or questions, send me an email.
Last updated: July 13, 2019 3:34 PM