Replies

Thanks Hans,

It never ceases to amaze me some of the solutions you and a few others on this site come up with in response to peoples requests.
Managers never think about the limitations of software when they ask for things, it’s just “Give me this”.
I think most people who ask for help here are like me and have no one in the workplace to ask help, so finding a site like this makes life so much easier.
The people who help with solutions on this site are so knowledgable and creative in way they go about solving problems and don’t get half the thanks they deserve.
I have done searches on so many other problems and found the solutions I need in previous posts, and not wanting to push an old post to the top have not added my thanks. This site is an excellent resource for people around the world, and I have recommended it to many others, always stressing that they search first for their problem.

capri

I agree with Tony, you might want to resave your file and check what format it is in.

I’ve had similar problems when a spreadsheet is in web page format. There is one dataset I need to download from the net and even though I save is as Excel and it has the .xls extension the file is in web page format. I need to re-save as excel format. If you do pivot tables on a spreadsheet in web page format, it creates all kinds of sub folders in your directory and it is very difficult to go back in several days and alter the pivot tables. The entire thing is very slow.

I found Pivot Table Data Crunching by Bill Jelen and Michael Alexander to be very useful.
It really helped me improve my pivot tables by showing a lot of features that I was unaware of.

Yesterday I found a link to a book called “Excel Best Practices for Business” by Loren Abdulezer. I usually like to have a quick flip through a book in the store before buying. Look up an area I’ve had a recent problem with and see how helpful the information is. If the book explains things in a manner that I can understand then I am more inclined to buy it. I will be looking for the above book, next time I am in a book store to see how useful it may be.

Reading the table of contents, to see what the book covers, then relating it to things you want to learn is also helpful. Not much point getting a book that mainly covers topics you are already proficient in.

just found out why it wouldn’t work

for whatever reason excel was putting quotes around the formula. Once I removed these it worked.

Thanks for your help.

Sorry Hans, I made a typo

My actual conditional formatting is cell value is equat to =”MAX($C$3:$E$3)” where the values are 88, 104, 11
I chose as formatting Red Bold text.
Nothing happens, not one of the cells changes colours.

Can’t understand why it won’t work.

It’s mainly Excel, although the odd time Access (file size over 700 MB). I use Excel for about 90% of my work. The problem doesn’t occur in other programs, but I only do minor things in those other programs, not the number crunching with large files like in Excel. I’m sure the problem is related to the volume of data that I am using eg data set with 40,000 rows and 80 columns, or 13,000 rows and 110 columns.

(Edited by HansV to make URL clickable – see Help 19)

Yes it is possible.

Try this site : http://peltiertech.com/Excel/Charts/format.html#tornado%5B/url%5D

scroll down to where it says clustered stacked column shart and click on the heading for instructions.
It has 4 links, one of which has a sample.

I’ve found it very helpful.

Microsoft also has an article titled : How to create charts with multiple groups of stacked bars

capri

Have you tried blocking the pivot table, then copying and pasting? I do this quite frequently and this method allows me to change one of the fields in the copied version without affecting the original. Sometimes for small pivot tables I copy and paste on the same sheet, other times if the pivot table is larger, I paste onto a new sheet.
eg. for a small one, I want to see costs for all accounts, new accounts and old accounts. New and old should add up to all. I put the three pivots on the same page and can print a copy of the results. If I change branches on all 3, I can still get the information I need without worrying that changes to one Pivot table will affect the others.

Thanks for sharing that information Jan, I’ve already tried it and saved quite a bit on file size.

I have one question about how it works. I have several worksheets that are all the same except they are for different divisions. The division all roll up to a company wide worksheet. I used your copy method successfully to replace where I had added up the divisions. In my spreadsheets there are many calculations that involve other pieces of data on the worksheet. Your method worked fine on the rollup worksheet, but when I tried to copy it to one of the division worksheets, it returned the results from the rollup worksheet rather than the division. An example is ^=IF(ISERROR(+N25/N26),””,N25/N26) I use the same formula on each division page as well as the rollup page and copy it across for each month of each year.

I would presume I would have to create a new name for each division to get the correct results, or is there a way to get the copy to recognize eahc worksheet?

Thanks

Problem solved.

I was continuing working with the file and when I tried to save it, a window appeared that said some feature were incompatible with Webpage format and I should save as Excel.

I don’t know how it got into that format as I have no idea what that format is. I wonder if it is related to the download of data from the web? The file has an .xls extension.

I still can’t access or change the earlier pivot tables in the file, but any of the pivot tables I created today are fine.
I will just have to ensure that none of my files get into that format again.

Thanks Hans,

I filtered the blanks out of the pivot table and it still won’t group.
I filtered out the blanks from the data source, then pasted the data to a new worksheet redid the pivot table and the grouping worked.

It’s frustrating to work with data and not be able to do what is normally a simple step. I woud have thought that like in other parts of a pivot table it would have grouped the blanks together.

Thanks for explaining why it wouldn’t work.

Thanks

However I tried your formula and every cell even those containing the words return blanks.
I don’t want case sensitive as the comments could be any combination of upper and lower case.

Thanks Hans,
Your solution will work fine. It never would have occurred to me to approach it that way. That’s one of the great things about this forum, for people like me who are the only numbers person in their area, having access to other people who can suggest different ways to look at a problem, or offer potential solutions is a great help.

No I haven’t, but will try next time it happens. Thanks.

I just tried to do a big copy like the ones that take forever, but for some reason everything copied quickly this time.

I do this sort of thing quite frequently. In my case I just alter the data in the given column, but you could insert a column and get similar results.
I custom filter on the column (in my case division) for Begins with ABC. I then change the top entry to ABC then a space. Next, I click on the little square on the lower right corner. This copies ABC down the entire list. I then change the filter to begins with XYZ and repeat the process.
When i do my pivot tables I can then select that field (division) and will have separate rows for ABC and XYZ.