MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Show or Hide All Shapes

June 26, 2018 - by Bill Jelen

Show or Hide All Shapes

Today's question is from me. I have a workbook that I use frequently. It has a mix of illustrations and macro buttons. I've been using the same workbook for years.

Lots of images of various types
Lots of images of various types

Suddenly, all of the images were gone. Those images live on Sheet1 and Sheet2 has no images. I was working on Sheet2, doing stuff. When I went back to Sheet 1, everything was gone.

The data remains, but everything else is gone
The data remains, but everything else is gone

My first stop was Home, Find & Select, Selection Pane, but it was greyed out.

Maybe the shapes were made invisible in the Selection pane? Argh - the Selection Pane is greyed out.
Maybe the shapes were made invisible in the Selection pane? Argh - the Selection Pane is greyed out.

Worse, everything in the Illustrations group on the Insert tab is greyed out. What is going on? How did I damage this workbook to remove the entire objects path in the XML file and make Excel think that images are not available.

Excel won't accept new images
Excel won't accept new images

I love to learn something new in Excel and I quickly learned that Ctrl + 6 is a keyboard shortcut to Hide or Show All Shapes. When you press Ctrl + 6, all the shapes are hidden and you are not allowed to add more shapes.

But the solution is simple: Press Ctrl + 6 again and everything comes back.

I frequently use Ctrl + 5 to apply Strikethrough. I must have accidentally press Ctrl + 6 instead.

When would Ctrl + 6 ever be useful? Once, I had a friend who frequently copied worksheets. They had managed to replicate the four shapes on the worksheet hundreds of times and the worksheet slowed to a crawl. Ctrl + 6 would have allowed the worksheet to return to normal speed.

Watch Video

Video Transcript

Learn Excel from MrExcel, Episode 2219: Show or Hide All Shapes.

Hey, welcome back to the MrExcel netcast, I'm Bill Jelen. Today's question sent in by me. I had a workbook, I had a whole bunch of shapes in it, and all of a sudden, BAM! all the shapes were gone. My macro buttons were gone, everything was gone. I'm like what happened? How do they--how did they-- delete all those shapes like did one whole tree of the XML get just cut off?

You know, I use a trick here all the time. I do strikethrough with Ctrl+5, right? So Ctrl+5 will toggle strikethrough on and off, on and off. Apparently, I missed-- I pressed Ctrl+6, never used Ctrl+6, Ctrl+5 is strikethrough, Ctrl+6 is hide all the shapes. Now, if I go to Find and Select, I see these are grayed out, right? Really, really annoying. And I think even the tools to Insert New Shapes are all grayed out. Like, all of a sudden, Excel pretends like it has no idea what the heck a shape is. So all kinds of bizarre behavior here, just because I press Ctrl+6 inadvertently when I meant to press Ctrl+5.

And it happens to all the sheets. Like, here's my great title card-- all gone. Ctrl+6 to get everything back. I didn't even know that was a keyboard shortcut-- seems really dangerous to have that right next to Ctrl+5. But, there you are, Ctrl+6 to hide all shapes, BAM! everything's gone; press it again, everything comes back. If you're watching this video, you probably searched for "what happened to all my shapes". That's the story.

While you're at it, while you're here, let me try and sell you this book: "MrExcel LIVe, The 54 Greatest Excel Tips of All Time." Check out that "I" in the top right hand corner.

Episode wrap-up: Suddenly all buttons, shapes, SmartArt, word art, is missing from the workbook. Where'd it go? What happened? Let me guess, you were just trying to cross something off with Ctrl+5 and there's a good chance you accidentally pressed Ctrl+6. Just press it again, everything will come back.

To download the workbook from today's video, visit the URL in the YouTube description. See you next time for another netcast from MrExcel.

Hey, here's a bonus outtake for you. Why is Ctrl+5 strikethrough? Think about back in the old days when you used to take inventory with a mechanical pencil making remarks-- that's one, that's two, that's three, that's four-- it's the fifth one, Ctrl+5, that strikes out the other ones.

Download Excel File

To download the excel file: show-or-hide-all-shapes.xlsx

Excel Thought Of the Day

I've asked my Excel Master friends for their advice about Excel. Today's thought to ponder:

"Don't merge cells"

Title Photo: Cris DiNoto on Unsplash

Bill Jelen is the author / co-author of
Microsoft Excel 2019 Pivot Table Data Crunching

Use Excel 2019 pivot tables and pivot charts to produce powerful, dynamic reports in minutes instead of hours, to take control of your data and your business. Even if you’ve never created a pivot table before, this book will help you leverage all their remarkable flexibility and analytical power–including valuable improvements in Excel 2019 and Excel in Office 365.