Free Microsoft Excel 2013 Quick Reference

Colour Scales in excel 2007


i would like to learn how to apply colour scales in excel 2007 to my best advantage.

I have a pivot table with the rows as "Expenditure type" the columns are "Month"

I have the data showing one total for expenditure type for every month of the year. this works very well, but i would like to highlight using colours varioances over the months.

For example, januarys figure would be yellow as the benchmark for the year as it is the first figure. i would like feburary to go red if above jan, or green if the same or a lower figure. march would do the same, but checking against the feburarys figure. I feel that this should be possible, but i have only just got office 2007 and havent been able to fathom it out as yet!!

many thanks

Dear Community,

We are half way to mid week! (glass half full)!

Is it possible to create and store your own colour theme in excel 2007?

Have a good day
Darren Chapman

I want to make highlight colour darker when selecting cells in Excel 2007. I
have Vista Business / Office 2007 SBE... please help.

After just finishing typing in 12 pricelists (W x H) of 15 x 11 items I thought of using colour scales to verify the input, sure enough most of my mistypes where highlighted!

It wont catch all errors but quickly points out 52.10 insted of 152.10 and 123.43 instead of 213.43 etc! Which in this case would have been most of the errors!

You only have to highlight the list data and select conditional formatting/colorscales the auto preview will show you without making it permanent!

Is there a way to Show the Gridlines in Excel 2007, after an area has been selected, and highlighted with a colour?
As near as I can see, once you put the colour in, then the cells are still there, but the Gridlines just go, or is this incorrect ?


Can someone explain to me how to use the scale-to-fit printing option in Excel 2007, or is there a bug in the program? I have a spreadsheet that is a little wider than landscape width at 100% that I want to print on a single page width (it's several pages in length). If I use the "width" adjustment to "1 page", print preview shows that I have 450 pages! If I leave the "width" and "height" adjustments at automatic and use the "scale" option, it still doesn't work properly. Thanks.

When I started using Excel 2007 I immediately noticed that the colour of
selected cells appeared "washed out", and as a college instructor was
concerned that projecting Excel 2007 through an overhead projector will not
show very well on the projection screen. Students will difficulty following
what is being shown.
I was right.
Is there any way that the intensity of the colours can be changed?
I realize that there are three colour options to choose from through the
Excel Options dialog box (blue, silver, black) but in all three the colour is
still washed out.
My students will thank you.

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?

When I hover over a line on a scatter chart in Excel 2007, I see incorrect inormation about the nearest data point.

When I hover over a line on a scatter chart in Excel 2007, I see incorrect inormation about the nearest data point.

I believe that this only occurs when I set the Min and Max scales for the chart.

If I save the workbook to 2003 format and open in Excel 2003, the data point information is correct.

Wondering can anyone help me. I am doing graphs in Excel 2007 where they used 2003 before. I need to keep the colour scheme for the colum graphs that they had before.It will take me a long time to go in each time and change the colours. How can I set up a template so every time I use the graph the set colours I want come up. Please help as trying to do reports and have only started the job here. THanks a million

Have just got Excel 2007. In my pivot tables I hav always used the autoformat Report 6 (xlReport6). In Excel 2007 I don't find the autoformat function for pivot tables at all.

There is a Pivot Table format under Design, but that is only the colouring (and I don't find anything that resembles the styles of the old autoformats), not the other formatting. Since I always use the same Autoformat I do not even know what different format commands this comprise.

Are the Autoformats completely gone? What can I do to keep applying the formatting of the old Report 6 Autoformat?

When I create a diagram in Excel 2007 the autofill colors are not the same as
the color palette I have made. The program takes a similar color but makes it
a bit dirty. Does anyone know how to make Excel pick the original colours (my
theme colors) by default?


Hi everyone,

I would be grateful if someone could help me with a problem related to format painting conditional formatting to other cells in Excel 2007.

The problem comes from continually having to use Excel 2007 at home to work on spreadsheets for work which are 2003 versions. The compatibility conflicts invariably come from overlapping conditional formatting, and part of the problem is having only just discovered what seems to be (maybe its something I don't understand) a bug in 2007?

In Excel 2003, if you painted a format from one cell to another, it completely replaced whatever format was there, including the conditional formatting.

In 2007, if you paint a format onto a cell, it doesn't replace the conditional formatting, but merely adds to the conditional formatting which was already there .... even if it's the exact same conditional formatting! As an example, if I paint a format from A1 to B1 that says if the cell says "Yes" colour it red, and I paste the format into that cell 8 times, in the 'Manage Conditional Formatting' there will be eight copies of the exact same conditional formatting saying if the cell says "Yes" colour it red.

To use the paint metaphor, when I paint a door, I like to take the all the paint off back to the bare wood, not continually paint on top of the old paint

Just wondered if there was any option to change the way format painter works in 2007 so it works the same as it does in 2003?

Thank you everyone/anyone for any help you can offer.

I have a piece of code that arranges a chart to fit the data it should show. The code used to work perfectly in the old Excel version, but after an "upgrade" to Excel 2007 I get "Automation Error" from the line where the scale type is set.

    ActiveChart.Axes(xlCategory, xlSecondary).Select
    With ActiveChart.Axes(xlCategory, xlSecondary)
        .MinimumScale = Range("S5")
        .MaximumScale = Range("S5") + 14 * Range("S2")
        .MinorUnitIsAuto = True
        .MajorUnitIsAuto = Range("S2")
        .Crosses = xlAutomatic
        .ReversePlotOrder = False
        .ScaleType = xlLinear
        .DisplayUnit = xlNone
    End With
If I click "Help" on the error message, I am told to insert a "Microsoft Outlook 2002" disk, but I don't have that and I fail to se the relevance.

How do I set the scale type in Excel 2007?

I know I should not 'select', but I have copied the method directly from the recorder. If someone can show me how to write the instructions without selecting, please do so.


Is there a way of changing the shade colour of selected cells at the application level to provide a higher visual contrast between selected and unselected cells in Excel 2007?


I have a spreadsheet which I am trying to print on 2 pages in Excel 2007 - should be pretty simple right!! If I set the Page Setup options to fix the report to be 2 pages wide and 1 page tall it automatically sets the print to use about a quarter of each page. If I try to manually set the page scaling (it sets it to 14% if I fix it to 2 pages) to any higher number if goes above 2 pages. I cannot understand why Excel is only using around a quarter of the A4page to print each page on. Is it possible to ensure that a print fits 2 pages but also to get it to use as much of the page as possible? Having 2 tables shrunk into a quarter of the page seems ridiculous but I can't work out how to get it to use more of the available page space....


How to create a User Form in Excel 2007 with several objects such as a Blank Text-Box, OPtion Button, Check Boxesand Command Buttons?

Dear Forum,

I am trying my hands at creating an USER form for the first time for making a Banking Application which takes all different types of Receipt and Withdrawal Types of Entries.

I have several types of entries for Outgoings (Debits) and Incomings (Credits) in account and want to use a Single form to records these transaction.

So, how do I start to make a User Form which can categorise these entries as well as offer options while entering..

I have about 25 Debits and around 13 Credits identified as per regular usage uptill now and that might increase however I will be happy to confine it at the specified limits.

I was doing this earlier using Dropdowns but realised that a User Form would be an appropriate choice..

However, I need some helping hand as I ahve never done it before and this is going to be a slightly bigger form to manage as there are some transactions which can be done in 2 types like I can pay my Electricity charges with a Cheque or directly through Internet..

Now, if this can be handled at the form level nothing like it..

Since I am not familiar with creating forms i will just use Colours to explain what I require?

Wam Regards

After upgrading from Excel 2002 to 2007, the charts that I had made in 2002 no longer print full page as they did with 2002. I do not want to resize all my charts in the worksheets, I just want them to scale to full page when printing in Excel 2007 as they did in Excel 2002. I tried different printers, and different options in the print dialog to no avail. Any help would be appreciated.

Dear Friends,

When you run the code below in Excel 2007 why does is the text written vertically although I have specified "msoTextOrientationHorizontal" and AutoSize = True. This code runs fine in Excel 2003. Does anybody have a solution to this? Please help.

Sub PlotTBox()

Dim dRectX As Double, dRectY As Double, dRectW As Double, dRectH As Double
Dim dStartPos As Double
Dim shTextBox As Shape
Dim strNameShort As String
dRectX = 50
dRectY = 50
dRectH = 14
dRectW = 14

strNameShort = "Horizontal Please"
            Set shTextBox = Sheet1.Shapes.AddTextbox(msoTextOrientationHorizontal, dRectX, dRectY, dRectW, dRectH)
            With shTextBox
                .TextFrame.AutoSize = True
                .TextFrame.Characters.Font.Size = 8
                .TextFrame.Characters.Font.Bold = True
                .TextFrame.Characters.Text = strNameShort
                .Line.Visible = msoFalse
                .Fill.Visible = msoFalse
            End With

End Sub

A similar problem was discussed in this thread. Since the text in the textbox changes I cannot give an exact width for the text box.

Thanks in advance.


Please use CODE tags rather than colour formatting the code.

Hi all,

Apparently one 'save as picture' option should be available in shortcut menus when I rightclick any image or picture in excel 2007, but I just couldn't find it.

I was trying to save a smartart as jpeg and I came across this article I tried following it, but no luck so far, that option is just not there.

Any idea on why my excel 2007 is missing that option?



I really need some help to start up a small business in excel 2007. I am trying to set up a Cash Book designed to handle all the bookkeeping needs for small business in Excel 2007.The Cash Book to include if possible the items below:

Income and expense itemsSales and purchase ledgersUnlimited accounts added or inserted with a click of the buttonBank reconciliationsBalance sheetProfit & Loss and Cash FlowAged Customer and Supplier Summary Reports if posibleTrial Balance with Monthly Movement and YTD BalanceAccrual prepaymentsVAT Return summary
Can anyone please give me some advise?
Thank you very much


I want to use VBA to insert an Organisation Chart in Excel 2007 (ie if I did this manually it would be Insert, SmartArt, Hierarchy), but I cannot find anything on line that shows how to do this in Excel 2007. I know the code for Excel 2003, but that does not work in 2007.

Many thanks in advance for any help anyone can provide.



I was running a user defined function in Excel 2007, is working fine but when i apply in excel 2000 it was failed and with the result #values!

Would it be the coding below cannot run at excel 2000? Any setting to run the UDF in excel 2000?
Thanks and appreciate your help.

Below is the function created:
Option Explicit
Function LookupAll(vVal, rTable As Range, ColumnI As Long, ColumnII As Long, ColumnIII As Long) As Variant
Dim rFound As Range, lLoop As Long
Dim strResults As String

With rTable.Columns(1)
Set rFound = .Cells(1, 1)
For lLoop = 1 To WorksheetFunction.CountIf(.Cells, vVal)
Set rFound = .Find(What:=vVal, After:=rFound, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)

strResults = strResults & "; " & rFound(1, ColumnI) & "," & rFound(1, ColumnII) & "," & rFound(1, ColumnIII)
Next lLoop

End With
LookupAll = Trim(Right(strResults, Len(strResults) - 1))
End Function

Dear forum

I am writing this code in excel 2007 VBA and have got stuck and therefore hoping for your help

this below code generates a menu bar under "Add-In" and launches a userform upon action

    Set mymenubar = CommandBars.ActiveMenuBar 
    Set newMenu = mymenubar.Controls.Add(Type:=msoControlPopup, Temporary:=True) 
    newMenu.Caption = "PLAY" 
    newMenu.OnAction = "Uform" 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
now I want this menu to be deleted whenever I switch into another excel this menu should be available only from that particular file..I checked up this post
and have written this code under

End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
but whenever the window gets deactivated I am getting a runtime error...I am not able to understand..

ps the error comes even when I use Tag:="newmenu"

can someone pl tell me where I am going wrong and how to deactivate the menu when the file is not in use

thanx a million in advance

Programming with the C API in Excel 2007

It enabled users to create custom commands and functions on special Macro sheets that look like ordinary worksheets. XLM macro sheets are still supported in Excel 2007.