Formatting window/menu missing in Excel 2013

Posted on 2015-02-02
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

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

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).
Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.


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

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

Michael74 earned 500 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

777 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