Mac and Pivot Tables

[kojak]

I just a new job in an office that uses only Mac's. I have been using Excel
for PC's for years and can make pivot tables fairly easily.

However, Excel 2004 for Mac does not seem to want to play nicely. When I
create the PT, only the Page items have a drop down arrow for selection.
Rows and Columns do not. I can click and hide data in the rows and columns,
but that is pretty tedious, as I have to select each column and hide.

I also notice I can not refresh. When I add new data, I have to rebuild a
new pivot table, so the new data will be included. Not a pleasant
experience as I also have to select each column if I wish it to total or
not.

I am cerain I am doing something wrong. I can not imagine the Mac version
is so far behind the PC version.

Someone please give me the guidance to cure this as I am slowly goin' nuts!

Thanks

 

[JE McGimpsey]

I just a new job in an office that uses only Mac's. I have been using Excel
for PC's for years and can make pivot tables fairly easily.

However, Excel 2004 for Mac does not seem to want to play nicely. When I
create the PT, only the Page items have a drop down arrow for selection.
Rows and Columns do not. I can click and hide data in the rows and columns,
but that is pretty tedious, as I have to select each column and hide.

I also notice I can not refresh. When I add new data, I have to rebuild a
new pivot table, so the new data will be included. Not a pleasant
experience as I also have to select each column if I wish it to total or
not.

I am cerain I am doing something wrong. I can not imagine the Mac version
is so far behind the PC version.

Someone please give me the guidance to cure this as I am slowly goin' nuts!

I can't tell exactly what's going on from your description. At the very
least, you should be able to refresh - what happens when you click on
the refresh button in the Pivot Table toolbar?


I typically use dynamic ranges references in my PTs - it ensures that
the ranges are correct whenever the refresh happens. See

http://cpearson.com/excel/named.htm#Dynamic

 

[CyberTaz]

Hi Kojak -

Mr. McGimpsey is being quite tactful & cautious in his reply. The fact is
that *all* Mac Office apps differ in numerous ways from their PC
counterparts - both have their strengths & weaknesses when compared to the
other. This is one feature where the PC version has a more robust
implementation than the Mac version.

I believe PTs in the PC version employ ActiveX Controls not available on the
Mac. Therefore the feature for checking/unchecking certain columns & rows by
way of a dropdown list is missing from the Mac version, even in '04. Hiding
the columns is a bit of a hassle, but doesn't have to be done one at a time.
Once you select the first column to be hidden, hold the Cmd (⌘) key while
you click at the top of the others (pointer appears as a heavy black
down-arrow when positioned correctly) , then hide them all at once.

Refreshing should not, however, be any different in MacXL than on the PC. If
you are referring to adding data at the bottom of your list, even the PC
version does not automatically include the newly appended rows unless you
are using dynamic ranges as JE suggests. Changes to values in the original
list or records inserted within the range should be included when you
refresh.

I'm not really sure what you mean by

as I also have to select each column if I wish it to total or not

but will give it a shot if you can clarify the problem.

HTH |:>)

 

[kojak]

If

you are referring to adding data at the bottom of your list, even the PC
version does not automatically include the newly appended rows unless you
are using dynamic ranges as JE suggests.

As I wrote to McGimpsey, Excel 2003 for PC has a "List" that pretty much
removes the ned for dynamic ranges.

But thanks for the tip on CMD key and multiple column selection.

K

 

[kojak]

Mr McGimpsey, I appreciate your reply.

The problem is, when I refresh, nothing happens. My pivot tables are drawn
from those rows and columns that existed when I the first created the PT.
When I refresh, the new data is not included, as those rows are outside the
PT frame. I can ask the PT to be created from $A$!:$M$65000, but that will
disallow my being able to group data. It will refresh, but, as I said, not
group..
In PC Excel 2003, there is something called "List" and it allows me to
structure the PT with available datasets and then will expand as new rows
are added. It will also allow me to Group the data.
There is something in Mac Excel X that has something called List Manager,
but it does not behave the same way as Excel PC "List" does. Or, if it
does, I have been unable to crack the code to make it work.
I remember when I was using Excel 2000, I had a similar problem that I am
facing with the Mac today. Back then I inserted a named range, I guess you
called it a dynamic range, and added this formula:
=Data!$A$1:INDEX(Data!$F:$F,MATCH(9.99999999999999E+307,Data!$A:$A))
My Data was in a sheet called Data. It was located in columns A:F. As I
recall that worked for me. I will have to plug it in the Mac to see if it
will work there. If it does, that will be fine, except I am going to feel I
am backtracking with Excel. i.e. I am going from Excel's 2003 for PC to
Exel's 2000 for PC to make stuff work on the Mac.

And no, I am not enamored with the Mac for office suites. I am certain you
can tell that through my message.

But I appreciate your time and effort to respond, and allowing me to vent.

k