Contextures

Contextures News 20180717

Excel Magic Shrinking Font

July 17, 2018

Shrinking font size, total score calculation, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.

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!

Font Size Magic

In the screen shot below, all the cells are the same font and font size - Calibri 11.

  • In cell D3, number signs are showing, because the column is too narrow to show the full number.
  • In cell D8, the same number shows correctly, because of a special cell format setting.

Do you know what that setting is?

Smaller font in some headings

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:

  • Select the cells, then press Ctrl+1, to open the Format Cells window
  • Click the Alignment tab, and add a check mark to Shrink to Fit
    • NOTE: This is not available if Wrap Text is turned on
  • Click OK to close the window

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.

See more formatting tips on my Contextures site, and you can use Conditional Formatting too (but not for settings like Shrink to Fit, that could change the cell size).

Get the Total Score

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!

total score from survey letters

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]))

  • The SUMIF function looks in the tblScores Letter column, to match the letters selected in the survey, and returns the score for each selection.
  • total score from survey letters

  • Then, the SUMPRODUCT functions returns the sum of those scores.

See more Sum Function examples on my website.

Macro Training

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.

Excel Articles

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)

Also see: My Excel Products || Excel Events || Previous Issues || Weekly Humour

The Weeds are Winning

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.

weekly photo

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.

Debra Dalgleish
dsdalg @ gmail.com

Debra Dalgleish

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.

 

 

Get weekly Excel tips from Debra

 

pivot power free

 

 

 

 

 

Last updated: July 18, 2018 9:13 AM