Excel Magic Shrinking Font
July 17, 2018
NOTE: You'll get the next newsletter on July 31st -- 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!
In the screen shot below, all the cells are the same font and font size - Calibri 11.
Do you know what that setting is?
All the number cells are formatted with the"Shrink to Fit" setting. If you make columns narrower, the font shrinks, so all the text fits in the column width. With this setting, you don't have to spend time fussing with font sizes or column widths.
To use the Shrink to Fit setting:
Now, the font will get smaller, if the column is too narrow to show all the text. However, the font will NOT get bigger than the set font size, if you make the columns wider.
If people select letters A, B, C or D to answer survey questions, how can you calculate a total score, all in one formula?
I've uploaded a new sample workbook that shows one way to do that, and you can go here to get it FN0057 -Calculate Survey Scores from Letters.
The formula that I used is below the screen shot. Don't scroll down if you're still working on your own formula!
In my sample file, I made a table (tblScores), with the letters in one column, and each letter's score in the next column. (A-10, B-20, C-30, D-40).
To calculate the total for the survey, I used this formula:
=SUMPRODUCT(SUMIF(tblScores[Letter], $D$4:$D$11, tblScores[Score]))
See more Sum Function examples on my website.
If things are a bit slow at work, July and August would be a good time to learn something new in Excel. Jon Acampora is running free webinars on macros & VBA next week. Choose any date from Monday, July 23rd to Friday, July 27th. Learn how to automate some of your Excel tasks, and save time when things get busy again!
The free webinar is titled, "The 7 Steps to Getting Started with Macros & VBA". Jon will explain why you'd want to learn VBA and show how to get started writing your own macros.
Then if you want to dig deeper into Excel macros, Jon is running a new session of his popular VBA Pro Course. Sign up soon - registration is open for a short time only -- from July 23rd to August 2nd.
Here are a few Excel articles that you might find useful or interesting.
New Look - If you're using a subscription version of Excel (Office 365), you might see a change to its appearance soon. The Ribbon is simplified, there are new colours and icons, and a Search box will be featured. Do you like the new look? (Level - All)
Prediction - Dave Smith, from Excel wtih ML (Machine Learning), shows how you can build a recommender system in Excel, similar to the Netflix system. It's always fascinating to see what cool things Excel can do! To get the workbook, click the link near the top of the article, which will open Google Drive. Right-click the Netflix file, and click Download. (Level - Advanced)
It was too hot to weed the garden during the recent heatwave, and the weeds went wild! Our granddaughter came to visit last weekend, and she "volunteered" to help me clean up the garden. I'm sure all the weeds grew back, as soon as we finished, but it was nice to get outside for a while, without melting!
That's our hydrangea, at the top right, and a lovage plant at the top left. It smells like celery, and we use the leaves as seasoning. Apparently lovage roots and seeds are edible too, so we'll find ways to use those.
That's it for this week! If you have any comments or questions, send me an email.
NOTE: For the online version, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2018/20180717ctx.html
I'll also post any article updates or corrections there.
dsdalg @ gmail.com
Last updated: July 18, 2018 9:13 AM