Do you have the pivot table built/summarized in the format you're looking for? e.g. State in column A, this year's sales in column B and this year's sales in column C.

My only real tip to provide would be to avoid using the GETPIVOTDATA function, it is a mess from my experience, especially if you ever need to refresh the pivot table. If you're creating a fomula in column D, don't just hit "=" then click on a cell in the pivot table. Instead, type in the formula manually... "=B3/C3-1".

you can turn getpivotdata off (would recommend you do). it's an option in the top left somewhere on the pivot table ribbon.

I do have the table oriented the way I want it to.

Ideally it would be best to get the percent difference in the pivot table itself, so that it can pivot and such with it.

However, that doesn't seem to be possible. I guess best thing to do is find the format of the pivot table I like and then make some extra formulas outside of it to show what I want. Maybe even copy the pivot table data into a normal table first once I like what I see.

Thoughts, suggestions?

Thanks for replies, too.

Why is this not working with calculating fields?

I just tried it and it worked, if I understood you correctly.

In the source data each row has the number of units sold in a given month in a given state. So one column says month, one state, one is quarter, one is year, etc.

Created a pivot table from the data, comparing one year with the other. When I originally made a calculated item with the formula for percent difference (=(present year-past year)/past year), it did return the percent difference, but it also created some formatting errors (like doubling the month columns in the pivot table) that couldn't be fixed manually due to the fact that the table is a pivot table. The new pivot table was not fit for presentation.

However, I have been messing around some more with the calculated item solution. While some orientations of the table produce errant table segments, if you orient the table in other ways it looks just fine. I think I may play around with groupings, too.

Thanks for all the help.

Is it possible to make a cell 2 different colors?

my boss wants me to get a better tracking system for my quotes as I am a sales manager for a company that sells equipment in the wastewater industry.

right now I am using excel but my CRM is basically just a list with some key data and a small space for some notes...

is there any better way to expand this list to be more usable and track projects better or am I better off signing up for online CRM program that has more features and is probably more the type of stuff that my boss wants to see?

any suggestions

AFAIK no.

I am not sure what you are asking here.
But I do think it's possible to expand the thing with macros for example.
Pivot table makes the table easier to analyze.

The question is a bit broad or maybe I misunderstand.

Just wanted to say I enjoy your thread. As a master degree student, for a long time I didnt really find a lot I liked to do, or that even made much sense for that matter. It wasnt until I studied logistics for a year that I realized how awesome of a program Excel is in all its simplicity. Since then I´ve picked up advanced courses and intend to be an Excel guru.

With 61 pages im afraid to ask a question though, as its probably answered already.

a) Only 10 pages
b) I genuinely believe that every Excel problem is a bit different in its own sense. Kinda like a pokerhand. So ask away.
c) This is not my thread. So zomg might feel differently.

I have an Excel sheet that contains both a pie chart and line chart. Both of these charts need to dynamically update based on a user setting. The difficulty here is that the user setting can actually change how many rows of data there are for the chart (either 1 or 4). I used the following page to figure out how to dynamically update the pie chart - http://chandoo.org/wp/2009/10/15/dyn...t-data-series/. However, updating the line chart is more difficult. In this case, updating the setting can actually change how many series of data there are (in this case, each row is a series as each data point represents a month). As I know this a bit confusing, I will give an example below.

User Setting 1 - Pie Chart
A 50

User Setting 2 - Pie Chart
A 50
B 30
C 10
D 10

User Setting 1 - Line Chart

A 100.5 75 45

User Setting 2 - Line Chart
A 34 56 45
B 34 231 345
C 32 23 123
D 123 34 123

What I cannot figure out is how to dynamically include only the first row in the line chart when user setting 2 is selected. If this is still unclear, please let me know and I will try and explain it differently.

=If("User Setting"=1,#N/A,30)

If you give a cell the value "#N/A" (no quotes) then it won't display in a graph.

Let me see if I understand what you're saying. I have four series (under Select Data Source, they are listed under Legend Entries (Series).). If all of cells in a particular series say N/A, the corresponding line in the chart will not display? What about the actual legend? Won't it leave a blank square that is the colour of the data points that would appear if there was data?

I'm trying to wrap my head around a pie chart that has only one value.

It's a special case that just needs to display properly in case it is selected. The pie chart with four values is typically used but there has to be something shown when the single value option is selected.

No, not "N/A" it needs to be "#N/A" (again, no quotes).

Basically, Excel treats #N/A as a number that doesn't exist.
I can only suggest that you try it.

Let's see if you wizards can help me with this

I have a template in excel that I use as a basis for data entry for a side business I've been running.

What I've been doing is this:

Right Click on Sheet 1 (named, but whatever)
Click "Move or Copy"
To Book: New Book
Check "Create Copy"
Press OK

Then I type the data into the copied file etc.

Is there a faster way of doing this? A macro perhaps (whatever that means) to avoid this 5 step process and 'streamline' it? I'm always looking for ways to improve efficiency.

Are you saving your template as an actual template, ie .xlt? Then every time you open it you just save as a new xlsx with whatever changes you want and it won't save over your original xlt.

Have you recorded a macro of this already?

Anyways, this should do what you want:

You know what dudes - I didn't realize how easy it is to record a macro, I recorded myself doing the steps I mentioned just now and made it "cntrl N" to activate and that took care of it.

sorry for asking such a dumb question and wasting your time

This is probably an exceedingly easy thing to do, but I'm a total Excel newb.

I have a targeted list of names, addresses, phone numbers, all in varying cities. I would like to sort this data in the most efficient way possible to go door to door selling a product.

How can I sort this data first by city, then by street name? City and address are already pulled apart into separate columns. Part of the problem I envision is that the address starts with a number, so I'd somehow have to get the filter to ignore that number and alphabetize just based on the words.

Bonus points if anyone has a tip on how to further sort the street names to a most efficient walking route. I realize Excel can't do this, but there must be some sort of mapping program that for instance a mailman would use to go to a list of houses in the most efficient manner possible.

Thank you so much!!!

If your address is in A1 and there's a space between the numbers and the street name, use this to get just the street name:

=RIGHT(A1,LEN(A1)-FIND(" ",A1))

There's a space between the quotes.

Then sort the whole table by the city column and then this new column.

I have a similar question to the template. It involves a cheque requisition form for accounts payable. basically i have a template set up, which you fill out and then make a payment to the account payable you owe. My boss wants to to save each form in a folder, (kind of like hand histories from HEM) by month, or quarter, and by company.

My problem is that everytime i fill out a new form, and go to save it in its specific folder. it says, "this file already exists". is there anyway that i can set up the excel document to automatically save into its specific folder by name and date?

For example: we have monthly phone bill payments to "Telephone company"

I open the Accounts payable excel template and fill out what i need in order to make the bill payment. to keep as an electronic record on the computer, i would save it under folder AP>Telephone Company>July and it stores it there. Is there anyway to automate the saving? as in creting the folder for itself each month for each vendor? the reason i ask this is that we have a lot of vendors and doing it manually will be a huge pain and hastle. Not to mention, if we get the same bill payment in the same month (ie. Courier fee) i would save template in AP>Courier company>July but a file previously exists in there already with the same name. Actually, I guess i can name the files by their date? like DD/MM/YYYY...does anyone have any suggestions?

Thanks

What is it about update KB2597166 that makes excel sorting such a pain in the ass? Any ideas?


I've had to remove it from several PC's at work here.

Yeh it's definitely do-able something like, adding a save button with the following code (you will need to modify it but should put you on the right path

you would also need to disable the usual method of saving, somthing like

There's probably a way to override the beforesave event and force the save to that location but im not 100% sure of the top of my head