Free Microsoft Excel 2013 Quick Reference

Excel 2007 - Partial Printing of a print area

I have recently found that my excel 2007 is printing only portions of my spreadsheets. I can set the print area, see the entire print area in the print preview, but only get portions to print out. this is occurring on old files that use to print properly. It is not printer related (tried multiple printers). I have sent the file to others and they can print the files with no problems. Any suggestions.

Post your answer or comment

comments powered by Disqus
Browse Excel 2007 Help Table of Contents Accessibility Activating Excel Add-ins Automation and programmability Charts Collaboration Customizing Excel and the Web Excel Demos Exchanging data with other programs File conversion and compatibility Filtering, sorting, and conditionally formatting data Forms Formula and name basics Function reference Getting help Glossary Importing data Installing Macros PivotTable reports and PivotChart reports Publishing to SharePoint Server Excel Services Quizzes Saving and printing Security and privacy Summarizing, consolidating, and outlining data Training Validating data What-if analysis What's new Workbook management Working in a different language Working with graphics Working with other programs Working with XML Worksheet and Excel table basics

Excel 2007 hanging while doing a copy and paste.

I'm getting officeboathung in excel while doing a copy and paste in a large spreadsheet.   While Customer is doing copy and paste excel is using 50% of processor.   Thanks.   Work email

Does anyone know how to insert a calculated field (eg: percentage variance for 2 columns) in a Pivot Table in MS Excel 2007?

I've got a series of revenue types running down the left hand side and monthly results for columns all coming from an OLAP data cube and I'm not able "insert a calculated field" like the old version.


I've searched the archives but can find nothing on this topic.

Prior to excel 2007, I could select a data series (xy scatter chart)
and press the right arrow key to cycle through the data points, and a
data point highlighter would move from point to point so I would know
which point was currently selected. Through many versions of excel I
have found this feature very useful.

Excel 2007 seems to be missing the little data point highlighter. I
can still format individual data points, but I don't know beforehand
which point is selected.

Is this a know feature of excel 2007? Is it known a bug?


Brian Murphy

The Situation:
We graph data from data files in a Visual Basic 6 app by creating an Excel application object and having it generate the 2-D line chart. This application was developed about 6 years ago and the engineer who originally wrote it is long gone, so I have inherited it.

It has worked fine until we tried to use it on Excel 2007. We go with a lot of the default chart settings. When it was written, Excel 97 and Excel 2000 were current and it still works fine with Excel 2003. But now with Excel 2007, some of the default settings seem to have changed. For example, the chart title's default color has changed from black to white. I've solved that problem by explicitly setting the color to black, thus overriding the new default.

It's the other change that I can't get a handle on. The plot line itself used to be thin and black, but now it's thicker and blue. Blue isn't so much a problem, but we want to make it thin again so as to not hide the details.

The Visual Studio 6 help files provide no information on working with Excel objects. I have been using the Excel 2003 help files to see what objects are available, but when I try most of what it provides VB6 chokes on it. Here's the latest that I've been able to find and try:

    With wkbExcel.ActiveChart
      .ChartType = xlXYScatterLinesNoMarkers
      .SetSourceData Source:=wkbExcel.Worksheets(1).Range(strSourceData), _
      GoSub AdjustProgress
      .Location Where:=xlLocationAsNewSheet
      .HasTitle = True
      sr = Selection.ShapeRange
      sr.Line.ForeColor.RGB = RGB(255, 0, 0)
sr is Dim'd as ShapeRange, as per the help file. It chokes on the Selection.ShapeRange, which was also written precisely as per the help file.

BTW, it's just for test purposes that I'm trying to set the line to red, so that I can see whether the change I'm trying to make does actually appear. Of course I'll change it back to black once I'm there.

So I need to extract partial contents of a cell.. right now in the cell it looks like

address: 9999

so I want my code to see the cell... dropp the "address: " and only retrieve 9999.

I know it's on google somewhere but I can't seem to describe it properly for the right results to come up. Help is very much appreciated. Thanks.

I am trying to simplify the values of two cells. What I want to do is return a new value based on only a partial value of a cell. Probably easier to show you.

In the example, I'd like to associate just the first part of the text string in the cell to return a specific value. I'm assuming I would need to setup a separate table of the text strings and what they associate to. But after that I get stumped.

thanks for any help!


Hi there, I am sure this would have been asked b4 but I haven't found a thread.
What I want to know is if it is possible to sort by the partial contents of a cell, in particular in this case by the first 2 numbers in a string that starts with letters(Numbers to sort by coloured red in the table below).
The layout of the cell is always the same i.e. 2 letters/2numbers/1 letter/2 numbers.
The result I am looking for in the case below is in the attached file.
Any help (including letting me know if it is not possible) will be appreciated.

code prod loc qty
52282 sugar AA10A02 9
82094 salt DC09B03 1
60013 rice AD12A09 5
38374 pepper FA19D04 8
34365 tomato JA25K01 8
78150 pea LB36M01 10
40550 bean LK67L01 8
83045 potato MP65G01 5

am trying to write a function to find the partial derivative of a function.
The function can then be called from a worksheet cell almost exactly as one
would write them normally.

For example,
we have this equation
f(Z1, Z2, Z3) = Z1^2+Z2*Z3^3-Z3^0.5

and Z1 = 2, Z2 = 4, Z3 = 6

if we want to find the partial derivative with respect to Z1 then:

= DerivativeX("Z1^2+Z2*Z3^3-Z3^0.5", 2,"Z1")
= 4

with respect to Z2
= DerivativeX("Z1^2+Z2*Z3^3-Z3^0.5", 2,"Z2")
= 216

and so on .............

So all I am doing is to substitute the values of Z2 and Z3 in the function
and then do a normal differentiation with respect to Z1. Similarity, I
substitute Z1 and Z3 to get the derivative with respect to Z2, .....

I don't know what is wrong with the code. So can anybody help me to correct
it and get it working.

Thanks for any help


Option Explicit
Dim Z1 As Double
Dim Z2 As Double
Dim Z3 As Double

Function DerivativeX(func As String, a As Double, V As String) As Double

Const h = 0.0001
Dim n1 As Double, n2 As Double

Z1 = Range("C13")
Z2 = Range("C14")
Z3 = Range("C15")

Select Case UCase(Left(V, 1))
Case Is = "Z1"
func = Replace(func, "Z2", Z2)
func = Replace(func, "Z3", Z3)
n1 = (eval(func, a + (h / 2), Z1) - eval(func, a - (h / 2), Z1)) / h
n2 = (eval(func, a + h, Z1) - eval(func, a - h, Z1)) / (2 * h)

Case Is = "Z2"
func = Replace(func, "Z1", Z1)
func = Replace(func, "Z3", Z3)
n1 = (eval(func, a + (h / 2), Z2) - eval(func, a - (h / 2), Z2)) / h
n2 = (eval(func, a + h, Z2) - eval(func, a - h, Z2)) / (2 * h)

Case Is = "Z3"
func = Replace(func, "Z1", Z1)
func = Replace(func, "Z2", Z2)
n1 = (eval(func, a + (h / 2), Z3) - eval(func, a - (h / 2), Z3)) / h
n2 = (eval(func, a + h, Z3) - eval(func, a - h, Z3)) / (2 * h)

End Select

DerivativeX = (4 * n1 - n2) / 3

End Function

Function eval(funct As String, Z As Double, V As String) As Double
eval = Evaluate(funct)

Select Case UCase(Left(V, 1))
Case Is = "Z1"
Z1 = Z
Case Is = "Z2"
Z2 = Z
Case Is = "Z3"
Z3 = Z
End Select

End Function

Hi all,

I need a feature which will delete an excel file on occurence of a particular date.

For example: If i would like to delete an excel file after 27th of july 2010, the moment i open excel file after 27th of july 2010 the file should get deleted.

I am using Excel 2007. When I try to Print Preview or Print a group of charts,
the first one displays, but the remaining charts don't show up, just blank

Anyone have heard of this? Very frustrating.

Thanks in advance!!


Message posted via

I have a golf schedule created in Excel 2003 with borders. After saving that
document as an Excel 2007 document most of the borders will not print to my
HP LaserJet 5L printer. (Print Preview looks fine) Actually all of my older
HP's will not print the borders. If I print to a Brother or Ricoh or newer
HP printer everything prints fine. If I save the Excel 2007 document as an
Excel 97-2003 document, open it in Excel 2007, it prints fine. If I redo the
borders in Excel 2007 (which I don't really want to do as I have many
documents) then they will print out fine. I'm pretty certain I have the
latest HP drivers. Any ideas on what may be happening?

I use complex spreadsheets with macros to report company sales data. The
reports were created in Excel 2002. When I print the charts from 2002, they
print fine... text is crisp and gradients are gradients. When I print the
same charts in Excel 2007, the gradients don't print. Rather I get a series
of four distinct color bands. The lines and text are also not crisp but
rather jagged.
We use a color "powerhouse" laser printer -- Gestener aka Ricoh (too many
copies to print on an inkjet).
Has anyone had this problem? Anyone know of a fix?

I have an Excel spreadsheet program with multiple tabs. When, in Excel 2007
BTR, I try to print from some, but not all of the worksheets, the only thing
that comes out of the printer is a blank sheet. When I look at the same
worksheets in Print Preview, they look as they should.

There have been no problems printing in Excel 2003, and I've reverted to
that in the past to complete my printing.

I have one observation: the worksheets that fail seem to be the ones in
which I have inserted a button that invokes a macro. [I have had problems
with buttons for macros in another workbook;the font size changed

I have an annoying bug that I have been able to reproduce on 2
distinct XP machines, by simply installing Office 2007 as usual on
them. Both times I opted to only install Word, Excel, Outlook and
Powerpoint with all other options left at their defaults.

The problem is that:

Excel 2007 seems to refuse to print preview or convert to PDF/XPS
formats (combined plug in is installed) unless the machine has a
printer defined. The printer does not need to be functional or present
in any way - there just seems to always have at least one printer

Print previews/conversions in Word and Powerpoint do not seem affected
by the problem, it is just Excel.

I have been unable to find anything relating to this in the known
issues or after a good few web searches. Anyone have further details
on this problem?

Many Thanks.

[cross posted from]

Just started using Excel 2007 and in many documents I create I need to paste screen capture graphics into the excel file. I use the !Quick Screen Capture software.

I noticed that when I am in print preview with margins showing, if I adjust the column widths in order to adjust areas with text the graphics I have pasted change size - they get really small. I have to exit print preview, right click on the graphic, select format picture and then click the reset picture button to get the graphic back to original size. This resizing does not happen if I adjust the column widths in normal view mode.

This never happened in Excel 2003 so I'm confused on how to fix it. Is this just normal and I'll have to adjust to it or is there a setting that will prevent the graphics from autosizing if I adjust columns while in print preview mode?

Many thanks for your help.


Hi all,

I have results from about 300 print surveys that I am starting to compile in Excel, and I figured it would be wise to ask this question after I've inputted results from only 20 of them rather than after 300 and be told I did it wrong ...

My experience with Excel is very limited — I've used it twice, after others set up the documents for me. So I thank you in advance for your patience. Anyway, here's what I'd like to accomplish:

* I want to configure it so this project will calculate (and chart, if possible) the percentages for the multiple-choice answers (represented by the the range of letters a-f) to each question — i.e. I want to be able to make this statement after quickly glancing at Excel: For question 6, 23 percent of respondents answered "a", 46 percent answered "b", 3 percent answered "c", and 28 percent answered "d".

* I also would like to be able to filter the data, so I can say something like, "X percent of those who answered "a" to question 9 said (result of another question)."

I've attached the the barebones files that I've started with. When respondents failed to answer or circled multiple answers, the corresponding cell was left blank.

Again, thank you for your time & suggestions.

Hope this is the right place to post this.
I have created a simple spread sheet with 3 sets of 2 rows by 24 columns of cells with my data. There are 10 blank rows between each 2 row set.
I placed a simple (Insert chart) line graph of each of the 3 data sets on the spreadsheet.
The first 2 charts were manually sized in height to fit in 8 of the 10 rows of vertical separation between set 1 and 2 and set 2 and 3. The third chart sized 8 rows tall placed one row below set 3. I selected a print rage to include all three of the 2 row sets and the three graphs,
I am using EXCEL 2007.

Here is the question:
When I hit print preview the graphs fit and look fine but the 24 columns do not fit landscape on 8 1/2 by 11 paper, so I adjusted the "Page set up", "Page" tab, "Scaling" option to 1 page wide by 1 page tall (this gives me a 60% reduction).
The 24 column rows now fit fine in the print out, but the inserted graphs print but do not scale with the 60% page reduction. They remain 100% of their size.
They remain full size and cover up some of the rows of data in the printout.
Have any of you encountered this? Has anyone figured out how to get the graph to rescale automatically to page scaling reductions in EXCEL 2007?

I'm running Acrobat 9.1, Excel 2007 and Vista.
In Excel 2003 I made a worksheet with 50 page containing text and graphs. Printing with Excel 2003 was no problem. When opening in Excel 2007 it is impossible to print the pages. It takes so long that is looks Excel hangs, eventualy one empty page rolls out of the printer / PDF.Next problem: When printing one page including a graph to PDF in Excel 2007 the file becomes 500 kB, while the same file in 2003 only is 75 kB.
Can somebody help?

HI there

I have a workbook of some 15 worksheets - however there is just one of the worksheets I want to disable the printing of it. So that when users select print whole document - all worksheets will print except for thus one worksheet

The worksheet tab is named "15.ADMIN WORKBOOK CONTROL ONLY"

I know I need to put some vba code into the code area of that worksheet

I tried this code but it didnt work

    Cancel = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
any ideas please


Tony Mos

I am posting this message for two reasons: (1) partially to confirm the existence of an Excel 2007 VBA bug that appeared after applying Office 2007 SP1, but (2) mostly to highlight the existence of such a bug so that others are aware of it. (I'm pretty sure it's a real bug, and not just an issue with my PC.)

When there are two or more series on a scatter plot, it appears that a call to Series.XValues(n) will return "n" rather than the value of the nth point. For example, if there is a Series object named "mySrs", then Code:
 will incorrectly return "5", rather than the x-value of the fifth point in the series. This seems to occur only when there
are two or more series; if there is only one series, Series.XValues(n) returns the proper value.

Here are the steps to recreate the issue in Excel 2007 with SP1:

* Create a new spreadsheet
* Populate two columns of data for the X and Y values for the first series in the scatter plot. Make sure that the x values are not equally-spaced. (I.e., don't use "1, 2, 3, 4, 5..." for the x values, but instead use someting like "1, 2.3, 4, 4.8, 5.2...")
* Create the scatter plot
* Run a macro to determine the XValues for each point in the series (an example is provided below)

You should notice that--at this point--everything works as expected. Continuing on...

* Populate two additional columns of data for the second series in the scatter plot.
* Add the second series to the scatter plot
* Run the same macro to determine the XValues for each point in the series

At this point you should see that the XValues are simply (and incorrectly) "1, 2, 3, 4..."

Here is the sample macro code that I used to discover the bug:
Sub CheckChart()
      Dim myCht As Chart
      Dim mySrs As Series
      Dim Npts As Integer
      Set myCht = ActiveSheet.ChartObjects(1).Chart
      Set mySrs = myCht.SeriesCollection(1)
      Npts = mySrs.Points.Count
      Dim NumPts As Integer
      For NumPts = 1 To Npts
        Debug.Print "Point " & NumPts & ": XValue=" & mySrs.XValues(NumPts) & ", Value=" & mySrs.Values(NumPts)
      Debug.Print vbCrLf & "=================================" & vbCrLf
End Sub
The take-away is that if you are using VBA code that depends on the XValues of a series (such as Jon Peltier's excellent line and fill effects procedures), your code will not work properly.

It would be helpful if someone could validate this bug, butóas I indicated aboveóI'm pretty sure it's real.

I did submit feedback to Microsoft (here), but I'm not confident that it will result in a fix anytime soon.

Hope this helps you avoid hours of frustration and troubleshooting!

Hi people, i have another question for you.

Is it possible to print some of a sheet area to another sheet or a form?

I have 40 different sheet's in a single workbook. Column A to M is headlines
and row 7 to .......... is input in every sheet.

My question is; After all the input is done the result in column M appears
with text and a background color. Is it possible to print the row's that has
ex. red color and the sheet name into another sheet or a form.

I need a vb code that can find the red color itself and copy that row with
the sheet name to another location no mather if the sheet were it's found is
the active one or not.

After the user has done that he should have the opportunity to send the
result to a printer.

Is it possible?

I have an Excel 2007 spreadsheet that uses lower case o's (Oh) in some cells
that show in print preview but do appear on the printed document. The
document is printed on a HP 4600 color laser. I can print the same document
to the same HP4600 from a different PC with Excel 2003 and the o's print as
expected. I have verified that both PC's are using the same print driver and
both PC's are XP Pro machines. I've applied all updates to the Office 2007
machine with no change. Any suggestions?

Hi! all,
In Excel 2007, I'm unable to view or print a text box. I can create the
text box within the Excel sheet just fine, but unable to see it in the
Print preview and unable to print it to a printer. Any ideas?

Help the perplexed.

No luck finding an answer? You could always try Google.