In Excel:Mac 2011, we made a big investment in improving matters for our power users, which includes some great improvements to pivot tables. If you’re an Excel guru, check it out and tell us what you think.
Editor's note: This is a review of the final, shipping version of Microsoft Office 2011 for Mac, provided to Macworld by Microsoft in advance of the suite's general release. represents the largest step forward for serious Mac spreadsheet jockeys in many years, more so than either ( ) or ( ).
There are literally hundreds of improvements, some very noticeable, others not quite as much so. The big news for power users is the return of macro support (more on that later), but there’s good stuff to be found for Excel users of all levels. Some uneven performance issues and a lack of Mac-standard features, however, prevent this version from Excel from attaining perfection. The interface Excel 2011 looks much different than its 2004 and 2008 predecessors. Gone are the numerous floating toolbars and the floating formatting palette. Excel 2011 replaces all of those floating bits with two toolbars (standard and formatting) and the Ribbon, a collection of small tabs that provide easy access to often-used commands.
You can customize the Ribbon, or even disable it if you wish. It's context sensitive, so it changes to match the task at hand. For example, if you double-click an image, the Ribbon will open to a greatly improved set of image-editing commands.
The Ribbon and toolbars are now integrated in each Excel window, so there’s nothing floating around outside your workspace. A couple of optional floating windows remain, but they’re not required in most typical spreadsheet work. The Ribbon’s tabs are compact, and the Ribbon itself can collapse to a single row of tabs when not in use. As a result, the new Excel’s work area doesn’t feel smaller than that of the older versions. With the interface now contained in a single window, working with multiple workbooks at once is simpler.
For example, you previously couldn’t compare formulas between workbooks, because the formula bar existed only once for every open workbook. Now each workbook has its own formula bar. Other minor touches abound. There’s a full 32-bit color palette instead of 40 colors.
Drag-resizing a window now updates it in real time, instead of merely dragging an outline. SmartArt has over 150 pre-made templates (up from 80-ish), all of which you can customize. A media browser provides fast access to photos, audio, movies, clip art, symbols, and shapes. For the most part these new features work quite well. There is some lag when you live-resize windows, even on current hardware, but the delay is bearable. UI overhaul: Excel’s new all-in-one-window interface with the Chart ribbon open.
Working with spreadsheets Beyond the visual overhaul, Excel 2011 provides many new or improved ways of working with spreadsheets and the data they hold. Right up front, you’ll notice a new Workbook Gallery (similar to the Template Choose feature in ) that appears when you launch the application. Although it doesn't contain a ton of stock templates, you can browse a huge online template collection directly from the gallery window. Tiny, yet helpful: The new sparklines provide useful insight in a single cell. One of Excel 2011’s innovative new features is sparklines, which are simply graphs of data values that appear within a single cell. Instead of building a full chart to look for a trend in your data, you can often use a sparkline to show what you need to see in a simpler and quicker manner. Creating sparklines is as easy as selecting some data, choosing a menu item, and clicking a destination cell.
Conditional formatting, long one of my favorite ways of spotting key values in a large data stream, is greatly improved in Excel 2011. No longer are you restricted to three conditional formatting rules per cell; the limit now depends only on your available RAM. The archaic editor in prior versions of Excel has been replaced with a larger and more intuitive interface. You can now pick from four predefined conditional formats (including data bars, color scales, and special icons), or set the format based on a formula (as before). You can also easily rearrange existing rules—a most welcome improvement. You can even include formulas based on data on other worksheets. To help you format your data for presentation, Excel 2011 offers both themes (which apply rules to your entire spreadsheet) and cell styles (which apply styles to cell ranges).
You can customize these as you wish, and save customized versions for easy reuse. Themes and cell styles are available from the Ribbon via resizable drop-down windows. Unfortunately, Excel doesn’t remember custom size/shape settings for any Ribbon-based windows, so you’ll have to resize them each time they’re activated. Improved filters: Excel 2011's filters are more in line with their Windows counterparts. People who rely on pivot tables will appreciate Excel 2011's automatic pivot table creation, a new PivotTable Builder to ease building and modification of pivot tables, and pivot table report designs, layouts, and styles.I found both building and manipulating pivot tables to be much simpler in Excel 2011 than in the 2008 version.
Tables (previously known as lists) have similarly gotten a full makeover in Excel 2011. Creating tables is as simple as selecting your data and choosing a layout from the Ribbon (or from the menus). Once you've done that, it’s easy to filter and sort your data. Sorting and filtering in general is better in Excel 2011 than in Excel 2007 and 2010 for Windows.
You can create multi-select filter conditions without using custom filters; sort or filter on a font color or cell color; see matches immediately as you create your filter; and utilize built-in filters (such as Above Average or Below Average) to speed your work. Macros are back Excel 2011 addresses about its predecessor: no support for macros. Macros (via Microsoft’s Visual Basic for Applications) are back in Excel 2011, which means I can finally retire my copy of Office 2004, the last version to support macros. Excel 2011 supports some new macro features, such as the ability to set watch points, and it handled all of my existing macro spreadsheets (including a complex model containing custom menus and input forms) just fine. In addition, Microsoft says that cross-platform macro compatibility with the Windows version of Excel has been improved, although I was unable to test this.
While many Mac users may never use macros, their return is good news for power users and those who work in cross-platform environments. Sharing and protecting spreadsheets If you share your Excel projects with others, Excel 2011 has more to offer than previous versions.
In the past, you could protect a worksheet’s cells, contents, and scenarios, and lock or unlock a given cell for editing. Better protection: Excel 2011 offers greater flexibility in sheet protection.In Excel 2011, however, you can protect a cell’s contents while allowing changes to formatting. You can also allow or prevent insertion and deletion of rows and columns, use of filters and sorting, and more. These features match the protection options available in Windows versions of Excel, making for better cross-platform compatibility.
Beyond enhanced worksheet protection, Excel 2011 offers full information rights management, which allows users in a corporate environment to specify users and groups of users with rights to a workbook, including restricting who can read, print, forward, edit, or copy its contents. If your sharing needs are simpler, you can save directly to (free; 25GB of storage) from within Excel 2011. Once you've saved your documents to SkyDrive, you can access and edit them online, from any browser, using the. With the Web application, you can edit your worksheet, and open the modified copy in Excel on your Mac.
Multiple people can even edit the spreadsheet at the same time, similar to the way you can collaboratively edit a Google Docs spreadsheet. In my test of this feature, it worked well enough. The Web version's features are nowhere near as extensive as the desktop version's, but most of the basics are there.
In addition, the sparklines in my test file updated when I changed their base values in the Web application, though you can’t actually create sparklines in the Web application. Some room for improvement While this release makes great progress with the user interface and feature set, there are still things that don’t work quite as you’d expect. Excel uses its own dictionary and thesaurus instead of the OS X-provided tools; Command-A won't select all the text in the formula bar; and, very annoyingly, OS X's Services are still not available. The fact that these features don't work in Excel will be disappointing for those hoping for a fully 'Mac like' application.
In addition to the lack of Mac-expected interface features, Excel 2011’s performance is a bit uneven. When you're scrolling large spreadsheets (either by clicking and holding on a thumb scroller or by drag-scrolling), the sheet feels like it's moving quite slowly, even on current hardware.
![Pivot Table In Excel For Mac 2011 Pivot Table In Excel For Mac 2011](/uploads/1/2/5/5/125535799/529131396.jpg)
In back-to-back comparisons between Excel 2004, 2008, and 2011, the 2011 release was easily the slowest of the three—it took over six times as long to scroll through my test document as did Excel 2004. (Microsoft has told us they slowed the scrolling down due to user complaints about it being too fast. While it may have been too fast in Excel 2004, it's currently twice as slow as Excel 2008, which seems like an excessive slowdown to me.) When you add in the lag-on-window-resize, the Excel 2011 interface can feel slow at times. When put to a number-crunching test, however, Excel 2011 showed great results. Using a 15,000-row by 22-column worksheet containing a mix of slow-to-calculate formulas, Office 2004 and 2008 each took more than five seconds to recalculate.
Excel 2011, on the other hand, didn’t even blink, recalculating the same test worksheet in well under a second. So while Excel 2011’s on-the-surface performance suffers compared to its predecessors, it’s clearly working much more efficiently under the hood. To me, this is the stronger measure of performance, as it's the one that will most affect the ability to get things done with Excel—not waiting on calculations in large worksheets will be a huge timesaver. I saw similar results with all the workbooks I tested; they scrolled better in prior versions of Excel, but Excel 2011 easily trounced its predecessors in speed of calculation. In addition to my tests, Macworld Lab ran a series of benchmark tests. Overall, Excel 2011 performed faster than Excel 2008 in the Lab tests. We'll have more benchmarks result in an upcoming lab report.
Excel 2011 benchmarks Open Open with File Recalc Scroll Save File Excel 2011 2 7 0 95 3 Excel 2008 3 12 3 64 4 Results are in seconds. Lower results are better and are in bold. How we tested. We opened a spreadsheet with 225,000 cells of data. We recalculated the file, scrolled through the spreadsheet, and performed a Save As.—Macworld Lab testing performed by James Galbraith Macworld’s buying advice Should you upgrade to Excel 2011 if you’re using an older version? I definitely think so. The new interface and improved functionality make short work of even large projects, while the protection and sharing features make it easier to work with others—both on the Mac and on that “other” platform.
There are only a few negatives with Excel 2011. I feel scrolling has been overly slowed down, the live window resizing is jumpy, and the lack of support for Services is troubling, as that's an OS X feature that I rely on daily.
However, these things are not big enough issues to really affect Excel's performance and features—the program just works, and has many new features and improvements that spreadsheet users have been asking for. Excel 2011 is a solid program for anyone whose work involves a heavy dose of spreadsheet duty.
![Mac Mac](/uploads/1/2/5/5/125535799/252899709.gif)
Macworld senior contributor Rob Griffiths is Master of Ceremonies at.
Excel 2011:: How To Filter Pivot Table With Multiple Values On Mac Apr 17, 2014 I'm on Mac using Excel 2011. This means I don't have 'slicers', which is all my Googling kept turning up.
I have a pivot table with 4 different value columns, and I want to be able to filter it the same way you would a normal table - i.e. Remove everything below a specific number in one column, and filter for only specific strings in another column, etc.
Similar Messages:. ADVERTISEMENT Jan 17, 2014 I have a pivot table which has a report filter. The report filter can have anywhere from 20 to 350 values.
The user will be able to select multiple values from the listing to produce the table as needed. I would like to be able to display what values were selected in the report filter so that the user can see this information once the pivot table is rendered.
Is there any way to display this information either above below or along side of the table itself? I am using Excel 2011 for Mac, but I also have access to Excel 2011 for Windows. Either way will work. I have been able to get quite a bit of what works on Windows to work with the Mac. Mar 15, 2012 I have looked and experimented on how to include multiple ranges on a pivot table in excel 2011, i have found how to to it with the wizard but i can not find the wizard.
How to separate the ranges in the selection box? They are going to be going across sheet is that makes any difference. Jul 19, 2013 Is there is some way to filter based on the value in a specific subcolumn. Using the example of a list of salespeople and their transactions over the year, who sell multiple products, the PT is Sales Person name for the Row Labels, and Type of Product for the columns. The resulting PT has 3 columns, for each of the products - e.g. Table, chair couch, and the Values are the total number of that item sold. Is there any way I can filter, so that I will see only those sales people who have sold 3 tables or more lets say.
(Thus enabling me to quickly see what other products those sales people have sold.) (The actual situation is a lot more data heavy than that - it is actually a list of donations for a non-profit, coming in from hundreds of people, across a dozen different categories; I am trying to analyse the extent to which people who gave for a particular category (Direct Mail solicitation) also gave across other categories. So far I've manipulated things by inserting a '% of row total' value into the PT and then using countif/sumif functions outside of the PT to figure out how many people donated solely to this category (= 100% of row total), and how many donated to other categories too (= more than 0% of row total, less than 100%), which gives me a decent summary of sole donations to this cause vs other categories too, but doesn't visualise what the actual other categories donated to were. I've also investigated making a PT of the existing PT, but I'm not sure if that's even possible.) (I'm using Excel 2011 for Mac, but if there's some other version that would make this possible, I may be able to use another computer.) May 31, 2013 In Excel 2007 is it possible to filter the 'Values' in a Pivot Table? I am tracking the Gross weight of shipping containers.
My table sums the weight of all items in a container by container number. So my rows are 9 digit container numbers and my data values is a Sum of part weights. I want to be able to filter out containers above a certain weight. For example: Row Labels Sum of Gross Wt. Lbs 8932 56549761 I know I can simply copy and paste into a new tab and sort it there, but I'd like to be able to do it internal to the pivot table if that is possible. Oct 7, 2011 Named my data range using this formula: =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1)) I was hoping that my pivot table would refresh with the new data that I added on sheet 1 but it is not working and yes I have refreshed my pivot table. The new data is not capturing.
Is this the correct formula for Mac Excel 2011? Jan 10, 2014 I have a perfectly working pivot table and I would like to make some graphs based on the report filter. My report filter has 4 categories, with each more than 10 sublevels. When I make one pivot graph/chart, this goes fine, the data is ok, and I am happy. But one I make a second, and thus adjust the report filter, the first graphs changes according to the filter. I dont want that to happen Ultimately I would like a powerpoint presentation with multiple charts, based on one table, with different report filter filters.
Updated ONLY on the values, not the filter. Aug 2, 2012 I have a huge excel file. This file contains Projects, Project Manager, departments and sales for different quarters. Each department has sales and the quarter the sale was done. Now I want to create a pivot table where I can see the sales for each quarter for each project or each project manager. Attached file may elaborate the problem.
ShaA1.xlsx Oct 9, 2008 I have a pivot table in Office 2007. I want to filter the last column such that the values in the data area are greater than a certain number. But all those filter options are grayed out. The only option available (and working) is to select the top n entries. So clearly a reference into the data field to filter a column works, but why not by value? Feb 21, 2012 I have created a pivot table in Excel 2010 that lists amounts used of particular items each month. Inside the pivot table value field settings i have changed the show values as field so that it displays the difference in usage from this month to last.
However if i now select a top 10 filter it only filters by the total number used as opposed to the difference in usage from one month to the next (which is the values displayed). Is there a way that i can filter by top 10 by the actual values displayed in the pivot chart and not just the underlying data that creates it? Jun 5, 2014 I have a pivot table like the one below.
What I would like to do is filter the drill down keeping the total of the products (in bold) and showing just one of the name (just ENTA for Example). Basically I would like to add a filter that Hide some of the data keeping the row total. I'm Using Excel 2013. Products Sell out 4 weeks Stock Units Avg 4 weeks Wks of stock 3160-24PC-AP12 1 code. Jun 19, 2013 I'm trying to use a List Box to select multiple items on my Pivot Table but I can't seem to get it to work.
Its the Report Filter part of the pivot table. When the list Box is set to fmMultiSelectSingle it work and selects the indivdual items, but I need it set as fmMultiSelectMulti for if i'm also needed to select multiple items. (which doesn't seem to work!) Apr 1, 2014 Using Excel 2013, I clicked on a field in my RowLabels I then clicked on the Filter Arrow for the RowField The SelectedField prompted with the correct field of 4 possible fields However the item I am looking for is not in the list but I can plainly see it on the screen. Aug 12, 2010 In Excel 2010; the pivot Tables drop down filter is limited to 10,000 records.
If more than 10,000 are available then a message saying 'Not all items showing' is displayed at the bottom of the list. Clickin on the message would display a window saying: 'This field has more than 10,000 items under one or more parent items. Only the first 10,000 items are displayed under each parent item.' Dec 22, 2011 In the coming months the company i work for will be transferring from Excel 2003 to Excel 2010 (i know, a little late.) and now i am testing some things at home. We deliver lots of Excel reports to our clients where we are using Pivot tables. In excel 2003 we where able to hide items from the dropdowns using properties - hide items but when i now open an excel 2003 file in excel 2010 and want to filter the pivot table to (let's say) another month i see all my hidden items.
Is there an option in excel 2010 to hide items like i could do in 2003? (either regular option or VBA) May 29, 2014 Any way to create a calculated field in an Excel 2010 pivot table that will find all the Transaction Types (Report Filter) with 'transportation' in them and make the field Quantity 0 and leave all other quantities the same? I do not want the quantity of transportation added in twice and may not have the flexibility of adding a column to the raw data. I used the formula below in a calculated field and it does not match the values using the added column to the data file. =IF(ISERROR(SEARCH('.transportation.'
,'Transaction Type')),Quantity, 0) I am trying to get the sum of Quantity field to equal the AdjQuantity field using a calculated Pivot field and not add a new column to the data. Jul 21, 2011 I have a set of four pivot tables on a sheet that I need to programmatically change a Report Filter (Page Field) so I can create sets of reports in an automated fashion. This will be the first step in that process. The change will involve choosing 1 Role each time the code loops through based on Named Ranges I've defined that are associated with that Role. My code thus far: Code: Sub TestCode Dim pt As PivotTable Dim pf As PivotField Dim pi As PivotItem Code. Emmdcgsr is one of many Named Ranges that will contain a variable number of elements.
Just using the one right now to see if I can get the code to work, I'll eventually make another Named Range/Array of all them so I can loop through each Report ('ReportPick'). I want the Report Filter to consult that Named Range for its values and apply those values to PivotField 'Role' that is used as a Report Filter. When running this code above, I get a 'Role' Field that says 'All' but no values (the table is completely blank), with no evidence as to why it'd be blank (all filters in every Report, Column and Row are working normally and are filled in). When I choose a value manually after the code is run, the pivot table values populate.
Do I need to somehow index the Named Range in that loop? I'm just confused about this step right here: For Each pi In pf.PivotItems If pi.Value = RolePick Then pi.Visible = True Else: pi.Value = False When I've run other versions of the code, I've gotten an array version of it to 'work' using LBound and UBound, but it never chooses the right two values even though those are verified as stored in the array via a pass-through. It chooses the first few values in the Report Filter.
Here's the corresponding code for that: For i = LBound(myArray) To UBound(myArray) pf.PivotItems(i).Name = myArray(i, 1).Value pf.PivotItems(i).Visible = True Next I do not care if I use an array or a Named Range. I just want something that is simple and works. Passing the values directly from the named range seems easiest to my brain, but I'm open to anything and I'm clearly missing something (probably silly). I also have no idea why '.AutoSort xlManual,.SourceName,.EnableMultiplePageItems' is necessary though every piece of sample code I've seen seems to have some variation of it.
(Using Excel 2010, Windows 7.) Jan 3, 2014 I was wondering if it were at all possible to add multiple columns to the values portion of a pivot table at once, instead of individually dragging. When you click on them, it auto-sends them to Row Labels, which I don't want. I have a bunch of colums I want to add to Values and don't want to waste a bunch of time dragging them individually. Aug 1, 2008 Is there a way to drill down the data of 100 values in a pivot table into one worksheet without resorting to drilling down each value, having 100 worksheets for each value, to paste into one worksheet? I got my company to give me an experimental computer to test for this in 2007, though if there is a way to do this in 2003, May 13, 2003 I have a Pivot Table with pivot fields and data. I would like, through VBA, to get the list of values that can be chosen from a given pivot field.
For example, a list would be Product1 / Product2 / Product3. I would like to read that list and put it into a drop down list in a form. GOAL: I have several Pivot tables on the same sheet with similar fields and I want through macro to allow the user to update them all with one click. May 28, 2014 I have some nominal data that I'd like to get into a pivot table (Excel 2013).