Chart with wide range of data
I had a line chart displaying wide range of data
Tried to plot DeptA data in 2nd axis but feel that it is
not easy to understand the chart cus there is a large gap
between DeptA and DeptC data.
Appreciate if anyone could advise or suggest better way
of plotting this chart.
Conventional wisdom suggests using a log scale, but you're still have a
wide span across all the data. Also, most people who read figures about
training expenses for different departments are not well vers...Conversion of Cross-Tab Formatted data to qualify for Pivot Table
It's amazing how one can take pivot table data -- double-click
in the data section and it "converts" the PT format to a data-table format
(fit for a database environment, say access)...
Well any way, I receive files that are in a sort of a crosstab format,
which I'd love to be able to get to respond to (the creation) of a Pivot
Is there a way to make this "conversion"?
Thanks in advance for any direction offered..
Do you mean something like this:
from John Walkenbach's site.
Jim May wrote:
> It...Conditional Format on "Active Cell"
I have a spreadsheet where the rows in Column A are descriptions and Columns
B thru H are Mon thru Fri.
As you arrow down entering dollar amounts by day for each description I
would like the Description is say A44 to bold or change color when the cell
in say H44 is active.
Is this possible?
I'd suggest you take a look at Chip Pearson's RowLiner add-in:
But if you just want to change the color of the cell in column A:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim rArea As Range
...Possible to Enforce Users to place dates in a column using dd/mmm/yy format
Hello - is it possible to use validation to ensure that users (a) use
the date format in a given column, and do not stray from the dd/mm/yy
Is formating the column with a custom format my only option? Can I
also lock the format for a particular column in some way?
Thanks in advance for any suggestions.
Date formatting is not for data entry. Dates are numbers. Formatting is used
to express the numbers for visualization, not data entry. Formatting does
not make people enter Excel date numbers which range from 1 to 2,958,465
When those numbers are formatted for display purposes as ...Label columns in column charts
Now, it's Easter time, but I still need to work to complete my
thesis...well, let's go on...:-)
I've got a column chart in which the height of each column represents the
sum of unique values, so that if the column's height is 4, it means it
represents the occurence of 4 single events (1+1+1+1)
Now, referring this example I wish to label each column placing the name of
the single events, so, if the column's height is 4 the labels should be
from 0 to 1 -> e1
from 1 to 2 -> e2
from 2 to 3 -> e3
from 3 to 4 -> e4
(note that I have the labels in my active sheet)...Ungrouping a Bar Chart
I have a bar chart that I put into Powerpoint 2007. I have learned to ungroup
by copying and pasting special as a emf.
The problem I have is that it ungroups the labels and lines but the bars
still stay grouped together. Does anyone know how to get the bars to ungroup
from each other.
In 2003 this was very easy and useful to do, but in 2007 that functionality
Also is there a way to change individual bars into some with coloring like
slash marks or other types - not change the color gradient. This was also
available in 2003
Finally someone else is complaining a...Fonts in Formatting Toolbar/Palette
How do I change Calibri as a default selection for (Theme Headings) and
Cambria for (Theme Body) for fonts of my choice?
...Pivot Table Question #2
I use Excel 2000
I have a worksheet that has duplicate (or more) entries in some columns.
When I generate the pivot table these items that are duplicated only display
once on the table and there is a blank line/s under them.
GREY 555 AAA
BLUE 666 BBB
I would like that if there is a duplicataion of an entry that that
duplication also shows on the pivot table. In the case of the example above
that the work GREY & BLUE shows up in every line of the pivot table.
How do I format the pivot table not to hide the duplicates.
Th...Change field format based on condition
On my report I am displaying field with control source:
=IIf([Status]="Closed",[Closed Date],[Last Update]) however I would like to
Closed in Short Date format and if condition is false [Last Update] should
be displayed in Short Time format.
How do I set this control source?
Try it like this:
=IIf([Status]="Closed",Format([Closed Date],"Short Date"),Format([Last
Steve Schapel, Microsoft Access MVP
> On my report I am displaying field with control source:
> =IIf([Status]="Clos...Unrecognizable format message
Version: Older version
Operating System: Mac OS X 10.6 (Snow Leopard)
A coworker and I have been successful exchanging .xls documents. I then took 4 of the Excel documents and merged them into one document making 4 sheet tabs so that all the info could be in one document. <br><br>She now can't open this document and gets a message that says it is an "unrecognizable format". <br><br>I do not know what program she uses to work with Excel but I could ask. Also, is it a size thing...the new document is 111 KB. <br><br>It is a Microso...How To Refresh Chart Data Without Calculating The Worksheet
I am trying to refresh a chart in an Excel 2000 worksheet without
recalculating the whole worksheet. Tried using the following syntax to no
avail (this is not a pivot chart):
Please try one of these:
Dim Cht As Chart
Set Cht = ActiveChart
"Carl Bowman" wrote:
> ...Short date format
When I enter a birthdate that is prior to 1925, the display is 20.. instead
of 19.. I would like to make the default year 1901. For instance, if I
enter 2/2/02, the display should be 2/2/1902 insteand of 2/2/2002.
Thanks for any help you may render.
Try enterin the year as 4 digits. The problem you are having is how a DLL
sets the dates for entries of 2 digits. Changing it, changes it for
everything on the computer. Right now the date of century change is 2029.
That can be altered in Control Panel >>> Regional Settings >>> Regional >>>
-- ...adding data to chart
I have Excel 2000 and am have a line chart with quarterly data from 2002 to
now. When adding the 1Q07 data to my chart, the computer won't accept the
new cells and erases all the data for that particular series. I've tried
starting over with the same results. Any suggestions to get around this?
Please say more. Your original words are subject to various interpretations.
There are 21 quarters from 2001 to Q1 2007. A line chart has no difficulty
displaying much more than 21 x-categories in a single series. Also, a line
chart has no difficulty displaying much more than 21 d...conditional formatting #67
I have a range of cells containing days of the week as text:
MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY
The text of each day name is 'centered across cells' with the cell to its
right. I would like both cells conditionally formatted in yellow pattern. I
can format one with 'Cell Value Is | equal to | =TEXT(TODAY(),"dddd")', but
I would like the adjacent cell formatted the same way.
I tried '=A1=TEXT(TODAY(),"dddd")' for B1, but got ugly results if I tested
by changing the system date, i.e. only part of the 2-cell range was
...Formating Post Import
Hope everyone had a pleasant holiday weekend.
Would someone please explain to me how to delete all instances of a quotation
mark across all fields in a table?
Message posted via AccessMonster.com
Depending on which version of Access you are using, take a look at the
Microsoft Office/Access MVP
"NeonSky via AccessMonster.com" <u23580@uwe> wrote in message
> Good Morning,
> Hope everyone had a pleas...Summarize project time from different sheets in one without losing references when sorting the cells.
hi I have a a lot of sheets in one excel-file, about 25 upwards , and
in these I fill in the time for each project each person is involved
in. What I want is a sheet with a summary for all the projects. Every
project has a six digit code.
Can I create a formula that searches through the entire project for
example project 440011 and return the value to the sheet where I
summarize all the time?
The problem is that I want the projects to be sorted by the six digit
code but when I sort them in each persons sheet the references in the
summarized sheet will be wrong.
Does anyone understand what ...Cell Number Format
Is there a way to format a cell's number from 1.2 to 1,200,000? I
know how to format millions to ones but how do I convert from ones to
millions? (I don't want to have to multiply in another cell).
I could be wrong but, suspect there is no way to achieve what you want using
You could use the sheet change event to multiply numbers entered by one
million and format the result as you wish using code below. Just right-click
on the tab of the appropriate worksheet, click view code and paste. Note.
this will multiply value in column C only. You will need to modify ...formatting color based on source column
Hi and thanks to anyone who reads this.
I have a worksheet which contains two columns of dates.
In a second worksheet i have a column which adds the two dates together
as TEXT and ommits dates which are blank which works perfectly,
I would like to know how i could format each of the 2 dates in the 1
cell to have different font colors?
Here is my existing cell formula:
I have a feeling its not ...Question on pivot tables
Let's say a pivot table has two fields - "country" and "fruit". There are
seven products and seven countries, and I want to flip through all 49
However, if one product isn't present in one country (for example, let's say
"apple" does not exist in "england"), then rather than returning the error,
the vba code forces "apple" over the top of another fruit that does exist.
So the code firstly selects "england", then (if I were doing it manually) I
would drop down the fruit box and see that "...Functions greyed out when using Pivot tables
I have an end user who is using pivot tables, and they were working
fine up until the end of Jan. Whe she added her data for Feb and went
to sort using the privot table order function they were all greyed
She was originally using Excel 2k. Think that something had just
become corrupt we upgraded her to Office 2K3. This did not solve the
issue. I have insured the Office has been activated and that the
sheet is not protected. We can send the sheet to another user and it
functions correctly, as should.
Anyone see this before or have any ideas?
...Excel 2000 Pivot table using offline OLAP CUB as the data source
I need to use Pivot table using offline OLAP CUB as the data source to
analys a large amount of data. I would like to know:
What is the maximum size for a CUB file in Excel 2000?
What is the maximum size of data that Excel 2000 pivot table support?
Thank you very much.
Not sure what the maximum size is for an external data source, or even if
it's restricted. Maximum size for a pivot based on a worksheet is 65,536
records (maxm. worksheet length). I regularly use pivots reading from Access
databases of 2-300,000 records without a problem.
"Samson Tang&quo...Symbols not displayed properly in Excel chart
Hello. I am trying to create a chart of percentages with the data table
represented with the chart in Excel 2003. My problem is that I have symbols
in my original data table (e.g., <1%) that are not displayed properly when I
chart the data with the data table displayed as part of the chart (the cells
with symbols show up as zeros). I would appreciate any suggestions as to how
to get these symbols to show up properly in a chart's data table.
...Adding in another conditional format on the same cell
I have a cell containing "conditional format" when 2 weeks before the due
date ...the cell colour changes to "orange"....it turns red when it reaches
the "due date" .....
Now.......i need to add in another condition ....when i add in another
column e.g. "B" and the font colour will change to "yellow" ....i think its
something to do with....if equal to....then it would change to this
colour.... Can someone teach me the formula pls? urgent....thanks everyone....
What exactly are you trying to add as another condit...Microsoft Excel Pivot Tables
I keep getting the message "A pivot table report cannot overwrite another piot table report" when I refresh the data in my pivot table................I do not know what is the cause of this ???
I guess you have 2 pivot tables on the sheet. Pivot tables normally
expand in size when data is added. If the pivot table expanded it would
overwrite the other table. You will need to move a table to allow extra
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum...POS Receipt format change #2
I would like to have the "Sales Receipt" read "Sales Invoice" or "Invoice" on
our transaction receipts. Also on the same receipt I would like to have
"transaction #" replaced with "Invoice #". Can anyone help me with this? I
have been able to edit the "Sales Receipt" portion in an xml editor but have
not been able to change the "Transaction #" as there must be some other
coding that needs to change.
Thank you in advance.
I have already put the solution on this newsgroup kindly search the receipt