Go Premium for a chance to win a PS4. Enter to Win


Formatting window/menu missing in Excel 2013

Posted on 2015-02-02
Medium Priority
Last Modified: 2016-02-10
The window / menu / toolbar (not sure exactly what to call it) that pops up on the right of screen in Excel 2013 when you want to change settings for a chart is missing. Currently I can't change the axis min / max values, change data series chart types or anything at all that requires that side menu/window/toolbar.

Likely related to this, I had a whole lot of strange things going on recently. For example right click menu disappeared altogether. I managed to get this back by reenabling via VBA. I'm wondering if some addin I've used has crashed leaving several features off/disabled. However, I couldn't find any examples of how to force enable the formatting window / menu / toolbar but that is possibly simply as I don't know what to properly call it!

My hunch is that the right vba code will allow me to reanable the formatting window.

Any ideas?
Question by:JDido
  • 4
  • 4
  • 2
LVL 23

Expert Comment

by:Michael Fowler
ID: 40585358
Try this to restore all toolbars

   Sub ShowAllToolbars()
       'loop variable
       Dim i As Integer

       ' Loop through the total number of toolbars.
       For i = 1 To Application.Toolbars.Count

           ' Show each toolbar.
           Application.Toolbars(i).Visible = True

       ' End of loop.
       Next i

   End Sub

Open in new window


Author Comment

ID: 40585500
No, unfortunately that didn't do it.

The right click menu came back after I messed with application.commandbars.
Is there a commandbars that refers to the formatting menu/window/toolbar?
LVL 23

Expert Comment

by:Michael Fowler
ID: 40585506
OK ensure that the menu is selected. Go to Tools > Customize, then click the Toolbar tab. Make sure the option for 'Chart' is selected.

Don't forget that the 'Chart' menu item only appears in chart mode (by starting a chart or activating any chart command).
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Author Comment

ID: 40585698
This is Excel 2013 so I don't think there is a Tools menu.

I took a screenshot of what I'm talking about on another PC.

The menu/toolbar is this one that normally appears on the right of screen whenever you right click and go format... for example FORMAT PICTURE... or FORMAT AXIS... or any number of other options. For some reason this will not appear on this particular PC.

Different PC, same workbook = no problem.
I have repaired and restarted several times to no avail.


Expert Comment

by:Katie Pierce
ID: 40586688
Under File> Options>Customize the Ribbon, if you select the drop down for Customize the Ribbon on the right, are all the check boxes checked below it? Specifically, you should see Design and Format checked under the subheading Chart Tools.

Author Comment

ID: 40587569
Hi Katie,

Yes they are all checked.
I tried turning them off and closing Excel down then starting again and reenabling them: This didn't solve the problem either.

Is there a way this could have been disabled by VBA? (And can be reenabled?)


Expert Comment

by:Katie Pierce
ID: 40587585
Unfortunately I have limited knowledge of VBA, so I'm not much help there.

Last ditch effort, though: When the menu is active on the right, you can hover over the edge and get a sizing arrow that will allow you to make the formatting menu narrower and narrower.  Is there any chance that when you try to click format that it does come up, but is very narrow?  Maybe if you hover the cursor there you can see the arrow and expand the menu?
LVL 23

Expert Comment

by:Michael Fowler
ID: 40587593
Sorry about that.

Have you tried opening the task pane using some of the other methods


CTRL + 1
On a chart, select an element. On the Ribbon, select the Chart Tools Format tab, then click Format Selection.
On a chart, select an element. Right-click, then select Format <element> where <element> is the axis, series, legend, title, or area that was selected.
LVL 23

Accepted Solution

Michael Fowler earned 2000 total points
ID: 40587626
The following script will display all of the task panes which appear to the right

Sub test()
    Dim i As Long
    Dim cmd As CommandBar
    i = 2
    Range("A1").Value = "Name"
    Range("B1").Value = "Index"
    Range("C1").Value = "Enabled"
    For Each cmd In Application.CommandBars
        If cmd.Position = msoBarRight Then
            Range("A" & i).Value = cmd.Name
            Range("B" & i).Value = cmd.Index
            Range("C" & i).Value = cmd.Enabled
            i = i + 1
        End If
End Sub

Open in new window

On my my machine the following listing appears

Name	Index	Enabled
PivotTable Field List	10	FALSE
Task Pane	127	FALSE
XML Source	131	FALSE
Research	132	FALSE
XML Document	133	FALSE
Signatures	134	FALSE
Document Actions	135	FALSE
Clip Art	136	FALSE
Selection	137	FALSE
Format Object	138	TRUE
Document Management	139	FALSE
Document Updates	140	FALSE
Mail Merge Panes	141	FALSE
Fax Service	142	FALSE
Meeting Workspace	143	FALSE
Attachment Options	144	FALSE
Accessibility Checker	145	FALSE
Proofing	146	FALSE
Dictionaries	147	TRUE
Thesaurus	148	TRUE

Open in new window

If any of these are incorrectly disabled then use

Sub EnableCmdBar(idx As Long)
   Application.CommandBars(idx).Enabled = True
End Sub

Open in new window

where idx is the index of the offending commandbar

Author Closing Comment

ID: 40587656

This fixed it!

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

877 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question