In Excel 2011 for mac, a PivotTable is a special kind of table that summarizes data from a table, data range, or database external to the workbook. If you’re PivotTable aficionado, you will be in seventh heaven with the new PivotTable capabilities in Office 2011 for Mac. Here’s how to make a PivotTable:
- (Optional) Select a cell in your data range or table.
- Choose Data→PivotTable. Alternatively, on the Ribbon’s Tables tab, go to the Tools group and click Summarize with PivotTable.
- Choose the data to analyze:Make choices from the following options:
- Location: If you performed Step 1, your table or range is already filled in for you. If you didn’t start with a table or range, you can select a data range or table using the mouse.
- Use an External Data Source:Displays the Mac OS X ODBC dialog.
- Choose where to put the PivotTable:
- New Worksheet: If selected, adds a new sheet to the workbook and places your PivotTable in Cell A1 of the new worksheet.
- Existing Worksheet:Choose a cell on your worksheet. The cell will be the upper-leftmost corner of your PivotTable. Make sure there’s enough room so your PivotTable doesn’t overlap existing cell ranges.
- Click OK.
- Drag field names from the Field Name section at the top to the panes below.
- Selecting and deselecting the field names includes or excludes the columns from the pivot table.
- Clicking the pop-up buttons within the pivot table displays Filter dialogs appropriate for the data type in your pivot table.
- You can filter the Field Name list by typing field names in the search box in the Pivot Table Builder dialog.
- Drag fields from one pane to another to generate new pivot table variations.
Welcome to Excel 2016 for Mac: Pivot Tables in Depth. In this course, I'll show you how to use Pivot Tables to gain valuable insights from your organization's data. I'll begin by showing you how to create a Pivot Table from data already in your Excel workbooks. How to unpivot a table in Mac Excel 2011: Press: Command + Alt + P to bring up the Pivot Table wizard; Choose the 3rd option 'Consolidate Multiple Ranges' Choose 'I will define my page fields' Select the whole table and add it to the ranges; Click finish (to create the pivot table in. I'm studying a course, and it shows how to create a pivot chart in excel 2010, but the insert tab does not exist on excel 2011, and where there is a pivot table creation button there is not option for a pivot chart.
You can change the column names, calculations, and number formats provided by the PivotTable Builder. There’s a little information button at the right end of each field name in the panels at the bottom of the PivotTable Builder. Click the information button to display the PivotTable Field dialog. The properties displayed are for the field name of the button you clicked:
It won’t work on a Mac or Excel online. Your first step is to create a new table that has a sorted, unique list of the industries found in either report. You might use an Advanced Filter for this, but an easy way is to copy the Sector column from both data sets to a new table and then use Data, Remove Duplicates to make sure each industry.
- Field Name (Optional): Type a new field name.
- Summarize By: Choose which type of calculation to use.
- Show Data As: Select how you want to show the data from the pop-up menu. You can choose from Normal, Difference From, % Of, % Difference From, Running Total In, % of Row, % of Column, % of Total, or Index.
- Base Field and Base Item: If you choose Difference Fromin the Show Data As pop-up menu, choose which fields you’re comparing.
- Delete: Removes this field from the PivotTable report.
- Number: Displays the Number tab of the Format Cells dialog so you can choose a number format or make a custom number format.
When you select a cell in a PivotTable, look at the Ribbon to find the PivotTable tab, which you click to display all sorts of PivotTable tools. The PivotTable tab is for experts. PivotTable Ribbon offers additional formatting options and still more controls for your PivotTable, but it goes beyond the scope of this book. If you find PivotTables to be useful, then by all means explore the PivotTable Ribbon.
- Excel Power Pivot Tutorial
- Excel Power Pivot Useful Resources
- Selected Reading
A PivotChart based on Data Model and created from the Power Pivot window is a Power PivotChart. Though it has some features similar to Excel PivotChart, there are other features that make it more powerful.
In this chapter, you will learn about Power PivotCharts. Henceforth we refer to them as PivotCharts, for simplicity.
Creating a PivotChart
Suppose you want to create a PivotChart based on the following Data Model.
- Click the Home tab on the Ribbon in Power Pivot window.
- Click PivotTable.
- Select PivotChart from the dropdown list.
The Create PivotChart dialog box appears. Select New Worksheet and click OK.
![How To Do A Pivot Chart In Excel For Mac How To Do A Pivot Chart In Excel For Mac](/uploads/1/1/8/5/118542457/650004560.jpg)
An empty PivotChart is created on a new worksheet in the Excel window.
As you can observe, all the tables in the data model are displayed in the PivotChart Fields list.
- Click on the Salesperson table in the PivotChart Fields list.
- Drag the fields − Salesperson and Region to AXIS area.
Two field buttons for the two selected fields appear on the PivotChart. These are the Axis field buttons. The use of field buttons is to filter data that is displayed on the PivotChart.
Drag TotalSalesAmount from each of the four tables– East_Sales, North_Sales, South_Sales and West_Sales to ∑ VALUES area.
The following appear on the worksheet −
- In the PivotChart, column chart is displayed by default.
- In the LEGEND area, ∑ VALUES are added.
- The Values appear in the Legend in the PivotChart, with title Values.
- The Value Field Buttons appear on the PivotChart. You can remove the legend and the value field buttons for a tidier look of the PivotChart.
- Click on the button at the top right corner of the PivotChart. The Chart Elements dropdown list appears.
Uncheck the box Legend in the Chart Elements list. The Legend is removed from the PivotChart.
- Right click on the value field buttons.
- Select Hide Value Field Buttons on Chart from the dropdown list.
The value field buttons on the chart are removed.
Note − The display of field buttons and/or legend depends on the context of the PivotChart. You need to decide what is required to be displayed.
PivotChart Fields List
As in the case of Power PivotTable, Power PivotChart Fields list also contains two tabs – ACTIVE and ALL. Under the ALL tab, all the data tables in the Power Pivot Data Model are displayed. Under the ACTIVE tab, the tables from which the fields are added to PivotChart are displayed.
Likewise, the areas are as in the case of Excel PivotChart. There four areas are −
- AXIS (Categories)
- LEGEND (Series)
- ∑ VALUES
- FILTERS
As you have seen in the previous section, Legend is populated with ∑ Values. Further, field buttons are added to the PivotChart for the ease of filtering the data that is being displayed.
Filters in PivotChart
You can use the Axis field buttons on the chart to filter the data being displayed. Click on the arrow on the Axis field button – Region.
The dropdown list that appears looks as follows −
You can select the values that you want to display. Alternatively, you can place the field in FILTERS area for filtering the values.
Drag the field Region to FILTERS area. The Report Filter button - Region appears on the PivotChart.
Click on the arrow on the Report Filter button − Region. The dropdown list that appears looks as follows −
You can select the values that you want to display.
Excel Pivot Table Training Pdf
Slicers in PivotChart
Using Slicers is another option to filter data in the Power PivotChart.
- Click the ANALYZE tab under PIVOTCHART tools on the Ribbon.
- Click Insert Slicer in the Filter group. The Insert Slicer dialog box appears.
![For For](/uploads/1/1/8/5/118542457/251664989.jpg)
All the tables and the corresponding fields appear in the Insert Slicer dialog box.
Click the field Region in Salesperson table in the Insert Slicer dialog box.
Slicer for the field Region appears on the worksheet.
As you can observe, the Region field still exists as an Axis field. You can select the values that you want to display by clicking on the Slicer buttons.
Remember that you are able to do all these in a few minutes and also dynamically because of the Power Pivot Data Model and defined relationships.
PivotChart Tools
In Power PivotChart, the PIVOTCHART TOOLS has three tabs on the Ribbon as against two tabs in Excel PivotChart −
- ANALYZE
- DESIGN
- FORMAT
The third tab − FORMAT is the additional tab in Power PivotChart.
Microsoft Pivot Table 2016
Click the FORMAT tab on the Ribbon.
The options on the Ribbon under FORMAT tab are all for adding splendor to your PivotChart. You can use these options judiciously, without getting over bored.