There are bad characters, musical spreadsheets, and more, in this month’s Excel news.
- AutoFit Specific Cells
- Quick Grand Totals
- Bad Characters
- Number Keypads
- Spreadbeats
- Monthly Photo
New on Contextures
Pivot Table Tips: Make your Excel pivot tables look better with these 3 quick layout tips. One tip – AutoFit Cells – is described below
Overlap Error: How to find and fix those annoying Excel pivot table overlap errors.
AutoFit Cells
I increased the font size on a worksheet recently, and that messed things up, as you can see in the pivot table shown below. In cells where numbers didn’t fit in the larger font size, Excel showed number signs instead.
To quickly fix a few key columns, here’s what I did:
- First, I selected the Grand Total cells, C9:E9 (totals usually have the biggest numbers)
- Next, I went to the Excel Ribbon’s Home tab, and clicked Format > AutoFit Column Width, to fix the selected cells
- After that, I selected the grand total cells in another pivot table on that sheet
- Then pressed the F4 key, to quickly repeat the AutoFit command
To show the steps, I made a short video, and you can see that video on my Excel Worksheet Tips page.
Quick Grand Totals
Use this trick to get quick totals for rows, columns and the grand total. Excel automatically adds the formula in each cell!
- First, select the range of cells that you want to total, as well as the cells where you want the totals — cells B2:E6 in the example below
- Next, press the AutoSum keyboard shortcut: Alt + =
You can see more ways to sum in Excel, on my Contextures site.
Bad Characters
Do you know which character is “bad”, and is NOT allowed in an Excel sheet tab name?
- ; semi-colon
- + plus sign
- ? question mark
- # number sign
I asked that question on my YouTube channel, and only 52% of the people picked the right answer. You can see the correct answer, further down in this email, below the monthly photo.
Fun Fact: You can use an apostrophe in a sheet name, but not at the beginning or end of the name.
Number Keypads
Do you use your keyboard’s number pad, while working in Excel? Or do you use the numbers across the top of the keyboard?
I loved this article about the design decisions in touch-tone phones, that carried over to our cell phone keypads. The number layout is different from our computer’s number keypad, which carried over from old adding machines and calculators.
And here’s a key takeaway, if you’re designing an Excel workbook, or anything else: “What people say they prefer is not necessarily what works best.”
Spreadbeats
This YouTube video shows Spreadbeats, an amazing music video, created entirely in Excel, by Spotify’s media company. That sets the bar pretty high for the rest of us who work in Excel!
And this video, on the Spotify Ads YouTube channel, gives more details about Spreadbeats, but doesn’t include a sample file for us to download!
You’ve Got This!
It’s been a long time since I worked on a jigsaw puzzle, so I started this one with high hopes. Despite the 500-piece puzzle’s positive message, “You’ve Got This!”, I only completed the edges, and the words in the centre. It was fun, but a real strain on my eyes. So here’s my motivational message for you — “That’s good enough!”
Quiz – Correct Answer
In the Bad Characters quiz, if you picked the question mark, congratulations — you got the right answer! In Excel sheet names, the following 7 characters are not allowed:
: / ? * [ ]
Get Monthly Excel Tips!
Don’t miss my monthly Excel newsletter! You’ll get quick tips, article links, and a bit of fun. Add your email, and click Subscribe.
Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!
Stay in Touch!
That’s it for this month, and you’ll get my next monthly newsletter on Tuesday, August 13th!
If you have any comments or questions, please let me know.
– – Debra
P.S. Visit my Contextures site for more Excel tips, tutorials and videos.
Original source: https://contexturesblog.com/archives/2024/07/09/debras-excel-news-july-2024/?utm_source=rss&utm_medium=rss&utm_campaign=debras-excel-news-july-2024