Excel 2007 FAQ (Draft)

This is my first stab at a FAQ for Excel 2007. It’s based on Beta 2 Technical Refresh, so there is a chance that some of the problems noted will be fixed in the final version. Please feel free to add your own Q & A’s. Or, just the Q if you don’t have an A.

[Note: Updated Nov-6-2006 with some corrections.]

User Interface

Q: How do I open a file?

A: That round logo in the upper left corner is not just for cosmetics. It’s called the Office Menu button, and it’s used for a variety of purposes, including file operations. Click it.

Q. How do I get to the Excel Options dialog box?

A. Click the round Office Menu button, then click Excel Options.

Q. I clicked the round Office Menu button, but I don’t see Excel Options.

A. Look at the very bottom. It’s a button, not a menu item. And make sure you don’t click Exit Excel by mistake.

Q: how do I hide/show the Ribbon?

A: Use Ctrl-F1 to toggle the display of the ribbon.

Q: Where did the xxxxxx command go?

A: There’s a pretty good chance that it’s on the ribbon somewhere. But there’s also a chance that it’s not on the ribbon. In the latter case, you can add the command to your QAT. Also, try using the old Excel 2003 hot keys (for example, Alt+T, I to display the Add-Ins dialog box).

Q. What’s a QAT?

A. QAT is Quick Access Toolbar. This is the only user interface element that can be customized by the end user.

Q. Where are my old custom toolbars?

A. Click the Add-Ins tab and you’ll see them.

Q. I can’t make my old custom toolbars float.

A. No, you can’t.

Q. How do I “tear off” the Fill Color icon so I can float it?

A. You can’t.

Q. How do I get Help? The ‘ask a question’ box is gone.

A. Press F1, or click the little question mark icon in the title bar.

Q: How can I hide the status bar in Excel 2007?

A: You must use VBA to hide the status bar: Application.DisplayStatusBar = False

General

Q: I opened a workbook and my worksheets have only 65,536 rows.

A: Save it in an Excel 2007 format, close it, then re-open it.

Q: Where is the list with open workbooks?

A: Use View / Window / Switch Windows. Better yet, add this command it to your QAT. Right click on Switch Windows to add it so it is always one click to access it.

Q: Ctrl+A doesn’t select all of the cells in my worksheet.

That’s probably because the cell pointer is inside of a table. Press Ctrl+A three times to select all worksheet cells.

Q. The Custom Views command is grayed out.

A. That’s probably because your workbook contains a table. Convert the table to a range, and then you can use Custom Views.

Q: What happened to the ability to create a pivot table using the Multiple Consolidation Ranges option?

A: That option still exists, but you need to add the ‘PivotTable and PivotChart Wizard’ command to the Quick Access toolbar (Found in ‘Commands not in the Ribbon’), and use that command to start a new pivot table.

Q: I can’t find the command to apply names to cell references in a formula. In Excel 2003, the command was Insert / Name / Apply.

A: The Define Name control in the Formulas / Defined Names groups is a drop-down. Click the down-arrow, and you’ll see the Apply Names command.

Q: Why doesn’t the F4 function key repeat all of my operations? 

A: I don’t know. The very useful F4 is much less useful in Excel 2007.

Q. What happened to the ability to “speak” the cell contents?

A. To use those commands, you must customize your QAT. They are listed under ‘Commands Not in the Ribbon’.

Q: Where is the Mail Recipient (body) option in Excel 2007?

A: You must customize your QAT. They are listed under ‘Commands Not in the Ribbon’

Formatting and Printing

Q: How do I get my old workbook to use the new fonts?

A: Press Ctrl+N to create a blank workbook. Activate your old workbook and choose the Home tab. Click the very bottom of the vertical scrollbar in Styles gallery, and choose Merge Styles. In the Merge Styles dialog box double-click the new workbook you created with Ctrl+N. But this only works with cells that have not been formatted. For example, bold cells retain their old font.

Q. How do I get a print preview?

A. Try using the Page Layout view (icon on the right side of the status bar). Or, add the Print Preview button to your QAT.

Q: When I switch to a new document template, my worksheet no longer fits on a single page.

A: That’s probably because the new theme uses different fonts. After applying the theme, use the Page Layout / Themes / Fonts control to select your original fonts to use with the new theme. Or, modify the font size for the Normal style. If page fitting is critical, you should choose the theme before you do much work on the document.

Q: How do I get rid of the annoying dotted-line page break display in Normal view mode?

A: Open the Excel Options dialog box, click the Advanced tab, scroll down and look for the ‘Display options for this worksheet’ section, and remove the checkmark from ‘Show Page Breaks’.

Q: Can I add that ‘Show Page Breaks’ option to my QAT?

A: No. For some reason, this very useful command isn’t available as a QAT icon.

Q: I changed the text in a cell to use Angle Clockwise orientation (in the Home / Alignment group). I can’t find a way to get the orientation back to normal. There’s no Horizontal Alignment option.

A: To change the cell back to normal, click the option that corresponds to the current orientation (that option is highlighted). Or, choose the Format Cell Alignment option and make the change in the Format Cells dialog box.

Q. I’m trying to apply a table style to a table, but it has no effect.

That’s probably because the table cells were formatted manually. Remove the old cell background colors, and applying a style should work.

Q: I thought Office 2007 was supposed to support PDF output. I can’t find the command.

A: You need to download a free add-in from Microsoft. Blame the Adobe attorneys. After you download and install the add-in, click the Office Menu button and then select Save As / PDF or XPS.

Charts and Graphics

Q: Double-clicking on a chart element doesn’t display the Format dialog box.

A: Yes, that handy mouse action no longer works. Right-click a chart element, and choose Format xxxxx from the shortcut menu. Or, press Ctrl+1.

Q. I find that it’s very difficult to select some of the elements on a chart by clicking. Is there any easier way to select a particular chart element?

A. Use the arrow keys to cycle among the elements on a chart. Or, use the Chart Elements drop-down control in the Chart Tools / Layout / Current Selection group. Better yet, add the Chart Elements control to your QAT so it’s always visible.

Q. I added the Chart Elements control to my QAT, but the original control no longer shows added elements such as trendlines and error bars.

A. Maybe this will be fixed in a future service pack. [Fixed in RTM]

Q: How do I prevent a chart from changing its size when I resize the underlying rows or columns?

A: Select the chart, then click the dialog box launcher in the Chart Tools / Format / Size group to display the Size And Properties dialog box. Use the controls in the Properties tab to change the move and size properties.

Q: What’s a dialog box launcher?

A: It’s the tiny icon in some of the ribbon groups. The icon is displayed on the right side of the group name.

Q: I’m working with a chart, using the modeless Format dialog box. If I click in a cell, the Format dialog box inexplicably displays the title ‘Format Shape,’ and it has the focus. So the arrow keys move within the dialog box, not the worksheet.

A: Annoying, isn’t it? When you’re finished working with the Format dialog box, press Escape to close it and return the focus to your worksheet. Maybe this will be fixed in a future service pack.

Q: In a chart, how do I control plotting empty cells and plotting hidden cells?

A: Select the chart, then choose Chart Tools / Design / Data / Select Data. In the dialog box, click the button labeled Hidden and Empty Cells. 

Q: In previous versions, I could use the Increase and Decrease Decimal buttons to change the number of decimal places displayed in a chart trendline equation. Those buttons don’t work in Excel 2007.

A: Click the trendline equation box and press Ctrl+1 to display the Format Trendline Label dialog box. Click the Number tab, select the Number category, and set the number of decimal places.

Q. I added a few shapes to a Chart sheet. Those shapes don’t get changed when I apply a new document theme.

A: Maybe this will be fixed in a future service pack. [Fixed in RTM]

Q. How do I change the shape of a cell comment? In Excel 2003 I used Change Autoshape on the Drawing toolbar.

A. Right-click your QAT and choose Customize. Choose ‘All Commands’ and then select ‘Change Shape’. Click Add to add the command to your QAT. Then you can use it to change the shape of a comment.

Q. A cell comment is a shape. Why can’t I use the ribbon commands to format it?

A. Comment formatting is done via the Format Comment dialog box. Right-click the comment’s border and choose Format Comment. Oddly, the color options available are not from the document theme.

Q: I can change the Height and Width of an object by entering values in the controls in the Format / Size group. How do I enter values for the Top and Left properties?

A: You don’t. You can, however specify the Top and Left properties by using VBA.

Q: When I right-click a Shape, I see an option to “Set as Default Shape.” This command seems to have no effect.

A: When you choose that command, Excel uses the *formatting* that you’ve applied to the shape as the default (fill, outline, effects). The command should probably read “Set as Default Shape Formatting.”

Macros

Q. How do I record a macro?

A. Click the little square icon in the bottom left of the status bar.

Q. How do I run a macro?

A. Choose Macros in the Code group of the Developer tab.

Q. I don’t have a Developer tab.

A. Display the Excel Options dialog box, click Popular, and then enable ‘Show Developer tab in the Ribbon.’

Q. I recorded a macro and saved my workbook. When I reopened it, the macros were gone!

A. By default, Excel proposes that you destroy your macros when you save the workbook. When you save the file, read the warning very carefully, and don’t accept the default “Yes” button.

Q: I recorded a macro while formatting a chart, and the macro was empty.

A: The VBA macro recorder ignores formatting applied to individual chart elements. Maybe this will be fixed in a future service pack.

Q: Using VBA to modify Shapes is very tricky, so I tried to record a macro while working with a Shape. The macro was empty.

A: Maybe this will be fixed in a future service pack.

Q: I’m trying to automate creating a simple SmartArt diagram. Recording a macro produces an empty macro.

A: Maybe this will be fixed in a future service pack.

Q: How do I use VBA to add a simple button to the ribbon?

A: You can’t. You must write XML code and insert the document into a workbook file using 3rd party tools. Or, if you’re a glutton for punishment, you can do it by unzipping the document and making the edits manually.

Q: How do I use VBA to activate a particular tab.

A: Sendkeys is your only choice. Press the Alt key to find out the keysroke(s) required. For example, to switch to the Page Layout tab, use this: Application.SendKeys “%w{F6}”

Q: I’m trying to display a topic from a *.chm help file from a Messge Box or an Input Box. Using Application.Help simply displays the main Excel help window.

A: Maybe this will be fixed in a future service pack.

Q: Can I use the VBA Application.Help method to display a particular Excel 2007 help topic?

A: No, but you can use Application.Assistance.ShowHelp method. First, navigate the local Help system and identify the topic ID. Right-click, and select the “Copy xxxxxxxx” option (this copies the topic ID to the clipboard. Then use a VBA statement like this: Application.Assistance.ShowHelp “HP10062493″. The text in quotes is the topic ID pasted from the clipboard.

97 Comments

  1. Harald Staff says:

    Nice material John. Thanks.

  2. Great post John!
    Here are a couple of FAQ’s I get.

    Q: How do I get back to the classic PivotTable wizard?
    A: Press Alt+D+P

    Q: Why can’t I drag fields in my PivotTable any more?
    A: You can. Right click on the pivot table in question, and select PivotTable Options. Next, select the Display tab, and then place a check next to “Classic PivotTable Layout”

    Q: What’s that thing on your face?
    A: I don’t like to talk about it.

    Q: Isn’t Bob Umlas sexy?
    A: I’d tap that

  3. Jon Peltier says:

    This was the answer to two dozen questions:

    “You must customize your QAT.”

    I have resisted customizing my QAT, partly because I couldn’t believe that’s the only customizable part of the UI, and I guess I was waiting for the real UI to come out from behind the curtains. With this great new interface, several things are no longer “discoverable”, so we have to add them to the QAT. If I have to add all this stuff that I shouldn’t have to, there won’t be much room for the buttons I want to add to it, and those must number around 80, if I use my customized toolbars in 2003 as a guide.

    Here’s another FAQ:

    Q: Why is the QAT so limited and inflexible?

    “A: Maybe this will be fixed in a future service pack.”

    Oh wait, you used that two dozen times too.

  4. Oh, Jon… Just get with the program and customize your QAT. All the geeks are doing it.

    When you get to the point where you can no longer drag the Excel window by its title bar without clicking a QAT button, then you’re finished.

  5. Simon Murphy says:

    Very useful info, thanks John.

  6. Richard Schollar says:

    Despite having downloaded and installed the Beta I can’t claim to have used it (at all). From my first read of your FAQ John, I get the impresion that Excel2007 is going to be inflexible compared to Excel2003. The question arises therefore, why on earth would anyone want to upgrade? The extra rows/columns is nice, but hardly essential.

  7. Jon Peltier says:

    John -

    Being a geek was fine when you could do cool things, like customize your interface, or, I don’t know, make charts. Excel 2007 has taken all the fun out of it.

  8. MikeC says:

    Jon: re the “fun element”…

    Maybe this will be fixed in a future service pack.

  9. Steve Hansen says:

    Great list John – I’m sure lots of new XL 2007 users will find this very useful.

  10. Jon Peltier says:

    Mike: “Maybe this (‘fun element’) will be fixed in a future service pack.”

    … Or perhaps I can add it to the QAT.

    OT: In one of my old jobs, I’m sure QAT would stand for “Quality Assessment Technology”. We were so six sigma, it felt like seven sigma.

  11. ross says:

    that QAT

    Rod and Back spring to mind!!! god bless MS!

  12. Ken Puls says:

    >Q: I’m trying to display a topic from a *.chm help file from a Messge Box or an Input Box. Using Application.Help simply displays the main Excel help window.
    >A: Maybe this will be fixed in a future service pack.

    Isn’t that nice!

    In the mean time, you could use the HTMLHelp API. John kindly let me inlclude a custom messagebox (based on his code) for an article on my site: http://www.excelguru.ca/node/73

    Near the end of the article is a link to download a custom messagbox which support all the standard Excel arguments except showing modally. (Up to 2003, anyway.) It does work with Excel 2007 as well.

  13. Hey John, really nice list. :-)

    I think this is not right, however:

    > “A: You must use VBA to hide the status bar:
    Application.DisplayStatusBar = False”

    I’m pretty sure this command has been disabled. (It was for the Beta 2, but I never downloaded the Technical Refresh, but I assume that it’s the same.) In the Beta 2, this command is quietly ignored without raising an error.

    However, you can hide it with:

    Application.CommandBars(“Status Bar”).Visible = False

    This is fairly increadible since the entire ‘CommandBars’ section of the object model has essentially been disabled — except for this!

    I think this is an “intentional obfuscation”. I think the idea is to disable this command for the “casual” VBA programmer, but enable it for the “really good” VBA programmer. This may seem absurd, but there is more going on in the StatusBar for 2007 and so there should be more of a bias to keep it open. (I used to always keep it closed, but for Excel 2007, I now prefer it open.)

    Strange stuff though…

  14. Jon Peltier says:

    Mike -

    You’re right about the status bar. I ran into this problem, and was completely astounded when the solution was to use CommandBars to access the status bar. I thought it was rather an insult after what has happened to our trusty command bars in 2007.

  15. Application.DisplayStatusBar = False
    Works in Beta 2 TR

    No need to go to CommandBars object.

  16. Jon Peltier says:

    Mike -

    Thanks for checking. It must have been in Beta2 where I encountered the problem.

  17. sam says:

    Mike…
    “This is fairly increadible since the entire ‘CommandBars’ section of the object model has essentially been disabled — except for this!”

    The “Cell” commadbar has also not been disabled… you can customise your right click,,,,
    At least the the Beta1

    Sam

  18. Most of your existing CommandBars code will still work in Excel 2007 (it hasn’t been disabled). It’s just that the result isn’t what you originally had in mind. For example, instead of getting a floating toolbar, you get a toolbar in the Add-Ins / Custom Toolbars group.

  19. Kimberly says:

    Does Excel 2007 have the ability to customize it’s look? For instance, like changing back to the old 2003 layout?

  20. Doug Glancy says:

    Kimberly, I think you need to use the “Uninstall” feature for that ;).

  21. Mike says:

    I have purchased loaded (and then unloaded Excel 2007). Working with a lot of data, I use filters often to split data into different worksheets. Excel 2007 ignores filters when copying dta from one worksheet to another. Any advise or am I being stupid? M

  22. Guy says:

    Does anyone know how to disable(With VBA code) a menu command in excel 2007?
    Ex:”Save as” or “macro menu”

    The code in 2003 was
    Application.CommandBars(“Visual Basic”).Enabled = False
    Application.CommandBars(“Tools”).Controls(“macro”).Enabled = False
    Application.CommandBars(“File”).Controls(“Save As…”).Enabled = False

    As you mentioned the custom toolbar is in the add-ins Tab and works just fine.

    Any help would be appreciated!
    Thanks!

  23. Jordan Samuels says:

    You absolutely saved my life and sanity with your tip about removing dashed page break lines from Normal view. Thanks!

    – Jordan Samuels

  24. Acex says:

    I have just one question: how to switch Excel 2007 back to usual 2003 interface???
    I didn’t find any option or button to do that.

  25. Juan Pablo Gonzalez says:

    Acex, you’re looking for the “Uninstall Microsoft Office 2007? button and then you’ll need the “Install Microsoft Office 2003?.

    There’s no way to switch it back. (Although, I’ve seen one Addin that puts the Menus and Standard/Formatting toolbar in the ribbon)

  26. Juan Pablo Gonzalez says:

    And this is what I was talking about:

    http://www.addintools.com/english/menuoffice/

  27. Acex says:

    Thanks for pointing two ways, Juan!
    I guess I will try second way before uninstalling Excel 2007 :-)

  28. emurhfkq says:

    people are stranger

  29. Ashutosh says:

    i have a boubt
    i have some values in each cell like
    0
    0
    36
    36
    0
    0
    12
    12
    0
    0
    but if want to exclude the count of zeros before first non zero +ve number (i.e. 1st 2 zeros)
    then again i want to exclude last two zeros which are after +ve whole number. but the zeros in between start of 36 till end of 12 are to be included
    how do i do it.

    Thanking you in anticipation
    and hoping for favourable reply.

  30. Andrew says:

    Is there a hot key for toggling between workbooks similar to “Ctrl Page Up/Down”. Any light shed will undoubtedly make my life a lot easier. Thank You.

  31. Andrew: Control+Tab

  32. Susan says:

    When I open Excel 2007, half the time I get a plain, blank blue screen, with only the zoom, page layout icons along the top right corner (these are usually on the bottom right). And a “ready” message on the to left corner. Nothing else. The only way I can close this screen is to right click on the Excel icon on the task bar along the bottom. I’ve spent 8 hours so far on the phone with Microsoft and they are baffled. Has anyone else experienced this, and if so, how can it be fixed.

  33. lily says:

    Hi – I can’t figure out where else on this blog to leave a question about excel. I apologize if this is not the correct avenue.

    I can’t figure out how to edit a label box in a chart in the new Excel 2007. With previous versions, one could drag the width of the box, so that one could choose how the title or a chart label appears. Now it automatically decides on the dimensions of your title box and does not let you change it — unless I just can’t figure it out.

    Please help!

    thanks, Lily

  34. Jon Peltier says:

    Lily -

    In previous versions of Excel, you cannot drag to change the size of data labels or chart or axis titles. You can drag to resize a textbox, which is a different object entirely.

  35. Tim says:

    Concerning Susan’s issue above

    “When I open Excel 2007, half the time I get a plain, blank blue screen, with only the zoom, page layout icons along the top right corner (these are usually on the bottom right). And a “ready” message on the to left corner. Nothing else. The only way I can close this screen is to right click on the Excel icon on the task bar along the bottom. I’ve spent 8 hours so far on the phone with Microsoft and they are baffled. Has anyone else experienced this, and if so, how can it be fixed.”

    I get the same exact problem. I have spent hours on this and sometimes excel opens correctly and sometimes it gives me the same screen she has. I have researched on the mircosoft user forums and found MANY users have the same issue. I am running on a NEW Dell with vista home premium. Does anyone know how to fix this?

  36. I have an Excel 2007 issue you might be able to resolve. I’m doing a lot of copy/pasting from other workbooks and when I paste them in it gives me a circular reference error. This is easily solved by just pasting and selecting ‘value only’ from the drop down on the side. BUT! Excel reads the circular reference error as an opportunity to annoy the crap out of me by forcing the Excel Help window open, disrupting my groove and not letting me do anything until I close it. I have a lot of these circular reference errors to deal with, is there a way to shut off the Excel Help ‘pop up’?

  37. Mohammed Anas Aadil says:

    Hi,

    Will anyone please tell how to take the reference of a cell in Macros ie A1, B1, … and how to increment A1 to B1. Another one is how can I seprate a cell contain number and character example A1=123abc; A2=5678ghi ; A3=98ghi12.

    Advance thanks for the above.

  38. same as tim and susan above, blue screen with page and zoom tools when attempting to open an excel 2007 file. It is one month after Susan’s plea for solution. I can’t find any other forums with a solution. Any suggestions on this forum?
    thanks

  39. J-Walk says:

    Tim, Susan, and John: I have no idea what causes that problem. I assume you’ve tried reinstalling.

    What kind of system are you running it on? Other apps running? Does it always work correctly after a re-boot? There are lots of variables involved. When things get that screwy for me, I figure it’s time to reinstall Windows, or (more likely) buy a new computer.

  40. frank says:

    Ashutosh,

    Let’s define your data column as D, then

    =MAX(IF(D=0,””,ROW(D)))-MIN(IF(D=0,””,ROW(D)))+1

    (introduced into any free cell as an array-formula)

    tells you the distance in cell positions between the first and the last non-zero positive number; you add 1 to obtain the required count.

  41. Aaron says:

    My name is Aaron and I am a Literacy Coach in Reno, Nevada. I use the Excel application for a number of things including keeping track of school wide data. I have a question that I have really been struggling with and I am hoping someone can answer. How do I copy and paste data into a new workbook without losing the formatting? Maybe, this isn’t even possible. If you can help me, I would really appreciate it.

    Aaron

  42. Jason says:

    Susan: I experienced the same problem as you (strange blank screen on startup) just now. Restarting Excel did not help. Starting it by clicking on a document in explorer didn’t make any difference. I had to reboot Vista to get Excel to start properly. .. Jason

  43. Thanks for posting that link Jason. Someone uninstalled the Business Contact Manager from Outlook and that fixed it. Another person uninstalled Quickbooks 2007 and that fixed it. I don’t think there’s any relationship between BCM and QB (in fact I’ve heard they don’t work well together). Let us know if you find anything else on this and I’ll do the same.

  44. Brian says:

    I have the same problem with Excel as Susan / Jason / Tim / John. Running on Vista Ultimate. I don’t have Business Contact Manager or Quickbooks installed.

  45. Doug Jenkins says:

    I have a question about copying charts in XL 2007.

    I wanted to make 7 copies of a chart, then adjust the data ranges with some VBA. I started by making the 7 copies (using ctrl-c and ctrl-v), but found that they all had the name “Chart 1?, and I could only activate the original in my VBA code.

    Using the same process in XL 2000, with the same file, I get 7 different chart names.

    Starting from scratch with a simple chart in a new file in XL 2007 I also get new chart names with the copy-paste process.

    But with the existing file with the data I really want to plot I can’t find any way to copy and paste a chart so it has a new name (other than using XL 2000).

    Does anyone have an idea what is happening here?

  46. Doug Jenkins says:

    Of course I could use the ChartObjects index number, rather than the name (and that works), but it would still be good to know what is happening here.

  47. Jon Peltier says:

    Was this an Excel 2000 workbook that you were editing in 2007?

    I’ve noticed problems in versions of Excel prior to 2007 where pasted shapes on a worksheet (and in PowerPoint, on a slide) with the same name as the copied shape. I haven’t used 2007 enough to notice it there. I’m not sure if it happens on old, beat-up files (i.e., corrupted) or if it’s more generic. The fact that you don’t see it in a new workbook leads me to think it might be the file, and not just Excel 2007.

  48. Doug Jenkins says:

    Jon – Thanks for your comments. The great great … grand-daddy of the present file would have been created in Excel 2000, and it is still saved as a 97-2003 workbook. Saving as an xlsm didn’t make any difference though, except now when saving back in the old format I get a warning about significant loss of functionality, and the charts alarmingly dissapear while the file is saving!

    Further investigation has revealed:
    You can re-name a shape by typing its name in the name box and hitting enter, but this doesn’t work for charts.
    The chart name property is read only for embedded charts, which would explain why you can’t change it.

    I have now changed my macro to use the chart index number, and create a table of index numbers and chart titles, so I can check that I have applied the right ranges to the right charts.

    It seems that the chart copies having the same name as the original in this one file is “just one of those things”, but it doesn’t seem to have any adverse consequences so far.

  49. Jon Peltier says:

    Doug -

    You can rename a chart in pre-2007 versions by shift-clicking on it and changing the name in the Name box. I don’t have 2007 on this computer, but I think I remember a command buried somewhere in the triptych of ribbon tabs that represent charting tools, which allows you to change the chart’s name, or what would have been the chartobject’s name, before the chartobject was in some ways dropped from Excel. But it’s not read only. You should be able to select the chart, then in the VB Editor’s Immediate Window, type a command like

    ActiveChart.Parent.Name =”New Chart Name”

    And believe me, you’ll find an adverse consequence for any strange behavior sooner rather than later.

  50. Doug Jenkins says:

    Jon – Thanks again. You are right, I needed to select with a shift-click in 2000. In 2007 I couldn’t find the ribbon command to do it, but the VBA code you gave does re-name the chart. I didn’t have .Parent in my attempt, and got an out of memory error. I’m not exactly clear why the parent has to be there. Excel help (after initially telling me that it couldn’t find the keyword) says:

    “Returns the parent object.

    expression.Parent
    expression Required. An expression that returns one of the objects in the Applies To list.
    Example
    This example returns the parent object of the application.

    Sub UseParent()

    Application.Parent

    End Sub
    Was this information helpful?”

    They didn’t have a button for “spectacularly unhelpful” so I didn’t bother responding to the question.

    I was also misled by the help on the name property:

    “Returns or sets the name of the object. Read-only String.”

    I’m not sure how it can be a read only string if it can be used to set the name of an object, but that’s what led me to the incorrect conclusion in my earlier post.

    “And believe me, you’ll find an adverse consequence for any strange behavior sooner rather than later.”

    Advice noted, I’ll keep a close eye on this file :)

    Now I know how to do it, I think I will re-name the charts in my code, and also set the chart title in the code, so I can make sure I’m assigning the right ranges to the right chart.

  51. Doug Jenkins says:

    By the way, I’ve just found an excellent tutorial on naming charts by a certain Jon Peltier :)

    http://peltiertech.com/Excel/ChartsHowTo/NameAChart.html

  52. Jon Peltier says:

    The parent of the Chart is the ChartObject, which is the thing you can name. So ActiveChart.Parent is the chart object that contains the active chart.

  53. Jon Peltier says:

    Doug -

    I knew there was a way to do it, and I just found it:

    In Excel 2007, go to the Chart Tools > Layout tab, click on the Properties button (far right of the tab), then edit the name in the Chart Name box.

  54. Doug Jenkins says:

    D’oh! Confession, not having spent much time looking at charts in 2007, I’d seen the “chart tools” tab when you select a chart, noticed that all the other ribbon tabs to the left (Home, Insert etc) were unchanged, and just assumed that the “design” ribbon under chart tools was the only one available, totally failing to notice the “layout” and “format” tabs to the right. It should make adjusting my charts a bit easier now I’ve found them :)

    I’m not totally convinced that these ribbon things actually make finding commands any easier.

    As for using VBA to work with charts, I’ve got a better handle on that now, having read your tutorial.

  55. Michele says:

    I have a question pertaining to headers in Excel 2007.
    Is there a way to intergrate the Built in headers in Word 2007 with Excel 2007?
    I have been playing with this for two days now and I have had no luck.
    Although I love the way Office 2007 works, I am becoming totally frustrated with trying to insert my custom header into a spreadsheet. Yes, I have tried saving the header as a picture and inserting picture but then the picture has to be formatted, etc,
    Please tell me there is an easier way.
    Thanks so much,
    Michele

  56. Mangesh says:

    How do I change Line style of data series of charts in excel 2007 through VBA ? I would like to make width of line 1 pt insted of default. Is it possible through coding ?

  57. Art says:

    Most of my graphs have to be black and white and are best produced with simple left or right diagonal line fills, dots, or the like.

    In drawing bar charts in previous versions of Excel, one could always click (focus?) on a a bar or series of bars and then click on
    format data series->fill effects-> pattern and easily obtain such effects.

    Does anywhere here know whether the same is possible in Excel 2007 and, if so, how? I’ve search the menus and can’t find that feature.

    Thanks in advance,
    Art

  58. J-Walk says:

    Art, the chart pattern fills were removed from the UI in Excel 2007. However, you can still apply them via a macro. You might want to record some macros while you apply a pattern in Excel 2003, and then use them in Excel 2007.

    In fact, this is an excellent idea for an add-in. I’m trying to take a break from Excel, but maybe someone else will take this idea a run with it.

  59. Dave says:

    Regarding: Susan:
    When I open Excel 2007, half the time I get a plain, blank blue screen, with only the zoom, page layout icons along the top right corner (these are usually on the bottom right). And a “ready” message on the to left corner. Nothing else. The only way I can close this screen is to right click on the Excel icon on the task bar along the bottom. I’ve spent 8 hours so far on the phone with Microsoft and they are baffled. Has anyone else experienced this, and if so, how can it be fixed.

    2 July 2007, 5:44 pm

    Same problem here. System is a Toshiba Tecra M5 running Windows Vista Business.

    No BCM or QB installed. And rebooting doesn’t solve the problem either.

    Any other suggestions? (other than the moronic “buy a new computer” suggestion above.)

    Thanks,

    Dave

  60. Emma says:

    Regarding: Susan, Dave, Tom. Having the same issue – has anyone found a fix for this yet. I saw somewhere that it might be linked to Logitech webcam software – do you guys have that installed. I am on a Dell Inspiron.

  61. jkpieterse says:

    About the Excel 2007 Blank blue screens:

    What happens if you start Excel in safe mode (hold control key whilst starting Excel, click Yes)?

  62. Dee says:

    Like Emma and others, I am still getting the EXCEL blue screen. Yes, I have Logitech Webcam software installed on my HP. I will check with Logitech. In the meantime, has anyone found any solutions to get rid of the blue screen?

  63. jimbo says:

    Concerning Susan’s issue above

    “When I open Excel 2007, half the time I get a plain, blank blue screen, with only the zoom, page layout icons along the top right corner (these are usually on the bottom right). And a “ready” message on the to left corner. Nothing else. The only way I can close this screen is to right click on the Excel icon on the task bar along the bottom. I’ve spent 8 hours so far on the phone with Microsoft and they are baffled. Has anyone else experienced this, and if so, how can it be fixed.”

    I found someone else’s comments on another website and that person only had two pieces of software installed Office and a logitec device. I too am having this problem and have a Logitec QuickCam. The other website suggested contacting Logitec.

  64. Amanda says:

    RE: BLUE SCREEN ON OPEN – no way are Logitech/QB/Business Contact Manager the culprits. I have NONE of these things and I get the blue screen each and every time I open a previously saved document. This is annoying me.

  65. Mark says:

    re: Blue Screen. I have the same problem with Excel on a brand new Dell Vista Home Premium setup. I found that if I use the task manager (Ctrl-Alt-Del) to close the faulty Excel window then close the task manager and open Excel again it re-started correctly. This may save you having to reboot your machine. I’ve only done this a couple of times so not sure yet if it is a reliable work around. I sure would like to know how to fix the real problem.

  66. Emma says:

    RE: Excel blue screen issue.
    Thanks for the hint Mark. I have been finding an email attachment, previewing it in Outlook and then opening it. It has been the only way I can guarantee the program opens correctly. I will try the task manager fix. I see Office 2007 SP1 has been sent to the Elite testers – we can only hope they release it soon and it fixes the problem. I am also on a Dell with Vista Business. Ihave Logitech, Acrobat and Quickbooks – they are all suspected of causing this but I cannot live without those programs so have to put up with Excel for now. I love Vista but Office was definitely not ready for release.

    Word Issue – has anyone found a fix for the Word not installed for this current user that occasionally pops up when I try to open a email attachment?

  67. Luke says:

    I have the same issue with the blank blue screen. I run XP SP2 and I had Office2007 Enterprise and Office2003 installed concurrently. I figured, uninstall 2003 and it would fix it. Unfortunately I was wrong. I uninstalled both versions and did a clean install of 2007 and I have the same issue. I found though that if I wait a while(sometimes up to a half hour) it finally displays the spreadsheet. ???

  68. Mario says:

    RE: Excel blue screen issue

    I had the same problem and I found the solution on the WEB.

    The best solution for this bug is the following : Startup Excel 2007 by typing “Excel.exe” from the Start | Searchbox

    The alternative solution is to hold down the control key while opening up Excel or opening up an Excel document.

    I have tried both ways and it works.

    I hope it will work for you too.

    Regards

  69. Brian says:

    Mario, the CTRL option seems to work. Thx. This blue screen thing with Excel is too annoying. Why hasn’t M$ fixed it yet?

  70. Keith Malpass says:

    I have the blue screen problem running XP and Office 2007. I am not sure my problem is the same but when Excel opens with the blue screen going to full screen view shows the data. As soon as I come out of full screen it goes back to the blue screen. If this helps in the investigation great, if not please ignore.
    Keith

  71. Andrea says:

    Has anyone actually managed to change the shape of a comment? I’ve tried adding both the change shape and edit shape options to the QAT but neither becomes active when I select a comment.

  72. Onder says:

    >>Blue Screen Problem : Startup Excel 2007 by typing “Excel.exe” from the Start | Searchbox

    This worked for me. It is still annoying but good to one at least one way around it.

    Thanks for the info.

    Onder

  73. Mike says:

    We have a similar issue here to the blue screen, some users try to open excel and the program launches but theres no workbook, by Clicking minimise and excel minimises, immediately restores and shows/loads the sheet. Also clicking on restore works.

    all very odd, tho i think i may have found the culprit.

    Aware97.xla is a file in some but not all of the users profiles, c:documents and settings\%username%application datamicrosoftexcelxlstart

    You can tell if you have this by having the addin tab the funny thing is the plugin is written in italian, i have hacked about at it and managed to see the source and its from omni page pro few tweaks to the code and i have it in english now so i can see what the buttons and error messages say when you click on them but the strangest thing is we dont use omni page pro and theres about 3000 copies of the file on our network :(

    I have found a script that will delete it and resolve the issue of opening excel with no workbook to use, but the safe mode is a great work around until i can get the script out to every machine cheers!

  74. Jason McCarter says:

    I used Mario’s CTRL option to work around this issue, and I noticed that the result was opening the workbook in “compatability mode”. That seemed odd to me, so when I looked closer I saw that the workbook had been saved as an .xls instead of .xlsx. I simply reopened the workbook and saved it as an .xlsx and now the CTRL is not needed to avoid the blue screen issue. Thanks Mario!

  75. Sue says:

    Hi John

    I wrote code in excel 2003 that print preview a sheet. When I opened the program in excel 2007 and run my code, it seems to go into some kind of weird print preview but you can’t see the sheet, it’s way too small sitting like a white dot in the big grey landscape of excel 2007. I can’t seem to click on anything on the screen and the only button that works is the close button on the top right hand side. When I close it I’m back to my original workbook. Any help?

  76. Elango M says:

    Their are no advanced questions in Excel. All are small childrens questions. Please try to improve by uploading advanced questions.

  77. Don says:

    To resolve the issue where Excel 2007 opens to a blank blue background without the data shown, open the Excel options using the big round button and select Advanced from the right pane. Then scroll down towards the bottom of the screen and click to clear the “Ignore other applications” check box, then click OK. After you do this, you should be able ot open workbooks by double clicking on them in explorer. NOTE: taken verbatim from the Microsoft Knowledge base article 211494. Hope this helps others – it resolved my problem with the blank backgrounds…

  78. JAne Strohm says:

    Hi there!
    I had the same issue with the blue locked screen when opening Excel. I noticed, though, that with mine the column width of the entire sheet had defaulted to 0. I set the width to 10 and low and behold all my worksheet came back. I think that setting the column width to anything will bring your worksheet back. You may have to re format but at least you have ur data back. Maybe you could look there before trying anything more complicated and time consuming – JAne :-)

  79. Mick Quigley says:

    In excel office 2007 how do I write an if/then formula that allows charting the line without showing 0’s if cel info has not been entered yet?

  80. Bob Neuendorf says:

    How do you use VBA in Excel 2007 to change the font size for the labels on the vertical axes? I can’t find the answer anywhere?

  81. Amu says:

    when i save file in 97-2003 mode and open the file again it lose all the formats i have applied on cells

  82. Jon Peltier says:

    Amu -

    97-2003 mode does not accommodate as many formats as 2007 mode. Naturally, a lot of formatting applied in 2007 is removed when saving in the earlier mode. The save to the earlier version applies the default color palette from Excel 97-2003, further reducing formatting compatibility.

  83. Jon Peltier says:

    Bob -

    In general, code that worked in 2003 will still work in 2007. You won’t have access to the new formatting features, but that’s mostly a good thing.

    I recorded this in 2003:

    Sub Macro1()

    ‘ Macro1 Macro
    ‘ Macro recorded 5/26/2009 by Jon Peltier



       Selection.TickLabels.AutoScaleFont = True
        With Selection.TickLabels.Font
            .Name = “Arial”
            .Size = 8
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ColorIndex = xlAutomatic
            .Background = xlAutomatic
        End With
    End Sub

    This condensed version works fine in 2003 and 2007:

    Sub AxisFontSize()

    ‘ Macro edited 5/26/2009 by Jon Peltier

       ActiveChart.Axes(xlValue).TickLabels.Font.Size = 8
    End Sub
  84. Ray says:

    I have a problem that is similar to:

    “Sue says:
    September 03, 2008 at 1:45 am.

    I wrote code in excel 2003 that print preview a sheet. When I opened the program in excel 2007 and run my code, it seems to go into some kind of weird print preview but you can’t see the sheet, …”

    I do not have Excel 2007 and I have not found an answer that addresses the above problem directly.
    My code runs from a toolbar I created in XL03 and ends up in an AddIn for XL07.
    The print preview allows the user to adjust the final hardcopy to suit the default printer, without using the space-wasting “fit to sheets” option.
    But the preview screen is never displayed.
    I’m getting this second-hand, but the user has to:
    “close a window with F4 to continue” << uses a mouse click, I am told

    I’ve seen suggestions to use SendKeys to pick another button before displaying print preview, setting ScreenUpdating=True, but I’m not certain these will work in my case.
    (Like if I use SendKeys, which button can I safely pick?)

    I hope I have included enough detail. I’ve been combing Excel sites for a week for answers, but I’ve found nothing this specific issue.
    I rarely find a problem that someone else hasn’t already experienced.

    Thanks in advance for any help or redirection to a solution!

  85. Ashish says:

    hi,

    Please help Me?

    IF Cell A1=B1 than A1 “D” other wise “P” or Cell A1 not equal to B1 than A1 “NO” or B1 = Blank than A1 = “WIP”

    i try this on also.

    =+IF(OR($B3=A1$A$4:$A$50023),”D”,”P”) but its not working as per my requierment.

  86. Brett Hamilton says:

    @Ashish

    Not sure exactly what you are trying to do, but I think you want nested if’s. =IF(A1=B1,”D”,IF(ISBLANK(B1),”WIP”,”P”)). This is a stab in the dark, because as you have it, the result for a non-blank, unequal value is both “P” and “NO.” You can’t have a cell be two values at once.

    Brett

  87. Thanks says:

    Thanks! Those annoying dotted lines after the page break previews were driving me nuts! Great post and great Excel blog.

  88. Penu says:

    Regarding the Excel 2007 blue screen try this:

    Log on as an administrator
    Rename the user profile that is having problems to .OLD
    Log on again to create a new profile
    Check Excel — working now?
    Copy over profile data such as desktop, my doc’s, and favorites

    Working now??

  89. Brian K.S. says:

    Wow you saved my life with the stupid dashed lines in normal view… what a stupid feature, you would think when you switched back to normal view it was because you were done using the page break!!!!!

  90. Jane Eckerle says:

    Hi,
    Can you tell me how to globally change the default font and font size for new comments?
    Thank you
    Jane
    **email was wrong in first request**

  91. Jason says:

    I have office 2003, in excel I have a document that has taken 3 weeks to create. Something happened and now I have a blank grey screen, until I do a print preview. It is ALL THERE in print preview but not in normal or page break views. How can I get my document back to normal?

  92. Ctibor Skoda says:

    I automate production of a large number of charts. In this proces I need to record macros editing chart formatting. I can do it only in Excel 2003. I cannot find this mechanism in Excel 2007 or 2010. Could you help ?

  93. Kenneth says:

    Thank you, this is very useful Excel 2007 informattion!

  94. Sharon says:

    We have shapes added to a spreadsheet that are assigned to macros. If you print the spreadsheet or go into print preview, the shapes start flashing when you return to home. The original spreadsheet was created in version 2003 and we are now on 2010. Any suggestions?

  95. Julie says:

    Six years later and it’s still full of useful information! (Like how to remove the dashed print break lines in Normal view…) Thanks!!

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: