Fix pivot table errors, show images in cells, and more, in this month’s Excel news.
Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.
Thank you for reading the news, and you’ll get next month’s news on Tuesday, December 10th.
- New on Contextures
- Quick Tip: IMAGE Function
- Sheets – Very Hidden
- Excel Quiz
- Excel Stories
- Monthly Photo – Image Enthusiast
New on Contextures
If you use pivot tables, you probably run into problems occasionally, and might see an error message, like this one: “The PivotTable field name is not valid…”
To help you troubleshoot and fix those pivot problems, I’ve added 3 new pages on my Contextures site:
- Error: Field Name Not Valid
- Error: Reference Isn’t Valid
- Problem: Pivot Table Duplicate Items
Quick Tip: IMAGE Function
Last month, I downloaded a CSV file from the public library, with all the books I’d borrowed online. Each row had a link for the book cover, so I used Excel’s new IMAGE function (Excel 365) to show the covers. That makes it easier to remember what each book was about!
- =IF([@cover]=””,””,IMAGE([@cover]))
Note: In older versions of Excel, you can use a Note (old-style Comment) to attach a picture to a cell.
Sheets – Very Hidden
In a large Excel file, you can hide some sheets, to reduce the clutter.
- Right-click a sheet tab, and click Hide, to temporarily hide a sheet
Later, to show the sheet again:
- Right-click any sheet tab, click Unhide, select the sheet name, and click OK
Very Hidden
For an extra “cloak of invisibility”, you can make a sheet “very hidden”.
- Press Alt + F11, to open the Visual Basic Editor (VBE)
- In the Project Explorer, find your workbook, and open the Objects folder
- Click on the sheet, to select it
- In the Properties window, for Visible, choose 2-xlSheetVeryHidden from the drop-down list
When you close the VBE, and go back to Excel, the sheet tab is hidden. And if you right-click and click Unhide, the Very Hidden sheet isn’t listed there!
- Tip: There’s a short video on my Contextures blog, that shows the steps.
Quick Excel Quiz
Do you know the answers to these two quick Excel quizzes?
- Who is credited for inventing pivot tables?
- a) Bill Gates (Microsoft), b) Katherine Glassey (Brio), c) Pito Salas (Lotus), d) Steve Wozniak (Apple)
- What was Excel’s code name during its development?
- a) Calypso, b) Odyssey, c) Medusa, d) Cyclops
The answers are below the monthly photo, and you can find more Excel quizzes and polls on my YouTube channel.
Excel Stories
There were a couple of interesting Excel articles in The Guardian recently.
- First, in this article, Excel fans talk about the wide variety of things they do with Excel. Do you do almost everything in Excel, or work-related tasks only?
- Next, things can go horribly wrong with spreadsheets, and this article has a “blooper reel” of spreadsheet errors. Nobody mentioned problems caused by “very hidden” sheets, but be careful if you hide things in your Excel files!
Photo: Image Enthusiast
Google must have seen me working with the IMAGE function, because it gave me an Image Enthusiast badge last week!
While searching for the title of a Monet painting, I clicked on an image in the search results. To my surprise, a message announced, “Congratulations, you’re an Image Enthusiast!”
Anyway, Google Arts and Culture has lots of interesting things to explore, if you ever need a break from Excel. For example, I enjoyed this virtual hike up Mount Fuji. Did you know that there’s a post office at the top?
Quiz Answers
- c) Pito Salas is called the “father of pivot tables”, because of his work on Lotus Improv
- b) Odyssey was the initial code name for Excel, and that project started in 1983
That’s it for this month! If you have any comments or questions, please let me know, in the comments below!
P.S. Visit my Contextures site for more Excel tips, tutorials and videos.
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!
_____________
Original source: https://contexturesblog.com/archives/2024/11/12/debras-excel-news-nov-2024/?utm_source=rss&utm_medium=rss&utm_campaign=debras-excel-news-nov-2024