- Manual Pivot Table On Mac Excel 2008 Data Analysis
- Manual Pivot Table On Mac Excel 2008 Shortcut Keys
If you select a row or column label in the pivot table, then click the Sort button on the ribbon, you’ll see that sort options are set to Manual. To return a pivot table to it’s original sort order at any time, just sort the field alphabetically again. Pivot Charts Are Here! That's right Mac users, you have something to celebrate today! ? Pivot Charts have finally made their way into the Mac version of Excel. This means you can now create interactive dashboards with pivot tables, charts, and slicers on the Mac. Creating Pivot Table. The first step to make a pivot table is to select the Data ribbon and click on “Pivot Table”. On the drop-down menu, select “Create Manual PivotTable”. Then the “Create PivotTable” window should pop up and you could select data to make the pivot table. I was eventually asked to create a pivot table and within the hour my fears were gone. With Microsoft Excel for Mac, creating a pivot table is easily achieved in just a few steps using the toolbar. No formulas or shortcuts necessary! Step 1: The original data. For this example our data for the pivot table will be in Sheet 1. How to calculate median in an Excel pivot table? For example, you have created a pivot table to statistics sale amount of drinks as below screenshot shown. Now you want to add the median of each drink into the pivot table, any idea? This article will introduce a solution for yours. Calculate median in an Excel pivot table.
Course Info
- Duration:1h 6m 58s
- Skill Level:Intermediate
- Released:September 22, 2009
- Viewers:641
Learn the most in-demand business, tech and creative skills from industry experts.
Start my free monthCourse details
In Excel 2008 for Mac: Pivot Tables for Data Analysis, Microsoft Most Valuable Professional Curt Frye helps dispel the common fear of the Pivot Table feature, demonstrating how to use this powerful tool to discover valuable business intelligence. Curt shows how to create Pivot Table reports from internal Excel data and outside data sources, use filters to focus on the most important data in the sheet, and prepare a Pivot Table report by applying formats and rules. Exercise files accompany this course.Skills covered in this course
Welcome
(cheerful music) - Hi, I'm Curt Frye. Welcome to Excel 2008 for Mac Pivot Tables for Data Analysis. In this course, I'll show you how to use Pivot Tables to make sense of your organization's data. I'll begin by showing you how to create a Pivot Table from data already in your Excel workbooks. Then, using that knowledge as a base, I'll demonstrate how to create Pivot Tables using data from an external source. We'll enhance your ability to analyze your data by showing you how to reorganize, or pivot, a Pivot Table. After you organize your Pivot Table's data, you can sort the data to show relative importance and relevance, or create filters that enable you to focus on the data you need to develop useful insights into your business. In short, I'll show you how to summarize and analyze data using one of Excel's most powerful and overlooked features. Let's get started with Excel 2008 for Mac Pivot Tables For Data Analysis.Practice while you learn with exercise files
Download the files the instructor uses to teach the course. Follow along and learn by watching, listening and practicing.Download the exercise files for this course. Get started with a free trial today.Download courses and learn on the go
Watch courses on your mobile device without an internet connection. Download courses using your iOS or Android LinkedIn Learning app.Download on the App StoreGet it on Google PlayWatch this course anytime, anywhere. Get started with a free trial today.
Course Contents
A PivotTable is a powerful tool to calculate, summarize, and analyze data that lets you see comparisons, patterns, and trends in your data.
WindowsmacOSWeb
Create a PivotTable
- Select the cells you want to create a PivotTable from.Note: Your data shouldn't have any empty rows or columns. It must have only a single-row heading.
- Select Insert > PivotTable.
- Under Choose the data that you want to analyze, select Select a table or range.
- In Table/Range, verify the cell range.
- Under Choose where you want the PivotTable report to be placed, select New worksheet to place the PivotTable in a new worksheet or Existing worksheet and then select the location you want the PivotTable to appear.
- Select OK.
Building out your PivotTable
- To add a field to your PivotTable, select the field name checkbox in the PivotTables Fields pane.Note: Selected fields are added to their default areas: non-numeric fields are added to Rows, date and time hierarchies are added to Columns, and numeric fields are added to Values.
- To move a field from one area to another, drag the field to the target area.
Before you get started:
- Your data should be organized in a tabular format, and not have any blank rows or columns. Ideally, you can use an Excel table like in our example above.
- Tables are a great PivotTable data source, because rows added to a table are automatically included in the PivotTable when you refresh the data, and any new columns will be included in the PivotTable Fields List. Otherwise, you need to either Change the source data for a PivotTable, or use a dynamic named range formula.
- Data types in columns should be the same. For example, you shouldn't mix dates and text in the same column.
- PivotTables work on a snapshot of your data, called the cache, so your actual data doesn't get altered in any way.
Create a PivotTable
If you have limited experience with PivotTables, or are not sure how to get started, a Recommended PivotTable is a good choice. When you use this feature, Excel determines a meaningful layout by matching the data with the most suitable areas in the PivotTable. This helps give you a starting point for additional experimentation. After a recommended PivotTable is created, you can explore different orientations and rearrange fields to achieve your specific results.
You can also download our interactive Make your first PivotTable tutorial.
Recommended PivotTable | Manually create a PivotTable |
|
|
Working with the PivotTable Fields list
In the Field Name area at the top, select the check box for any field you want to add to your PivotTable. By default, non-numeric fields are added to the Row area, date and time fields are added to the Column area, and numeric fields are added to the Values area. You can also manually drag-and-drop any available item into any of the PivotTable fields, or if you no longer want an item in your PivotTable, simply drag it out of the Fields list or uncheck it. Being able to rearrange Field items is one of the PivotTable features that makes it so easy to quickly change its appearance.
PivotTable Fields list | Corresponding fields in a PivotTable |
PivotTable Values
![Manual Manual](https://www.techonthenet.com/excel/pivottbls/images/create2011_002.gif)
- Summarize byBy default, PivotTable fields that are placed in the Values area will be displayed as a SUM. If Excel interprets your data as text, it will be displayed as a COUNT. This is why it's so important to make sure you don't mix data types for value fields. You can change the default calculation by first clicking on the arrow to the right of the field name, then select the Field Settings option.Next, change the calculation in the Summarize by section. Note that when you change the calculation method, Excel will automatically append it in the Custom Name section, like 'Sum of FieldName', but you can change it. If you click the Number.. button, you can change the number format for the entire field.Tip: Since the changing the calculation in the Summarize by section will change the PivotTable field name, it's best not to rename your PivotTable fields until you're done setting up your PivotTable. One trick is to click Replace (on the Edit menu) >Find what > 'Sum of', then Replace with > leave blank to replace everything at once instead of manually retyping.
- Show data asInstead of using a calculation to summarize the data, you can also display it as a percentage of a field. In the following example, we changed our household expense amounts to display as a % of Grand Total instead of the sum of the values.Once you've opened the Field Settings dialog, you can make your selections from the Show data as tab.
- Display a value as both a calculation and percentage.Simply drag the item into the Values section twice, right-click the value and select Field Settings, then set the Summarize by and Show data as options for each one.
Refreshing PivotTables
If you add new data to your PivotTable data source, any PivotTables that were built on that data source need to be refreshed. To refresh just one PivotTable you can right-click anywhere in the PivotTable range, then select Refresh. If you have multiple PivotTables, first select any cell in any PivotTable, then on the Ribbon go to PivotTable Analyze > click the arrow under the Refresh button and select Refresh All.
Deleting a PivotTable
If you created a PivotTable and decide you no longer want it, you can simply select the entire PivotTable range, then press Delete. It won't have any affect on other data or PivotTables or charts around it. If your PivotTable is on a separate sheet that has no other data you want to keep, deleting that sheet is a fast way to remove the PivotTable.
You can now insert a PivotTable in your spreadsheet in Excel for the web.
Important: Creating or working on PivotTables is not recommended in a spreadsheet when other users are working in it at the same time.
- Select the table or range in your spreadsheet.
- Go to Insert > PivotTable.
- Excel will display the Create PivotTable dialog with your range or table name selected.
- In the Choose where you want the PivotTable report to be placed section, select New Worksheet, or Existing Worksheet. For Existing Worksheet, select the cell where you want the PivotTable placed.Note: The cell you refer to should be outside the table or the range.
- Click OK, and Excel will create a blank PivotTable, and display the PivotTable Fields list.
Working with the PivotTable Fields list
In the PivotTable Fields area at the top, select the check box for any field you want to add to your PivotTable. By default, non-numeric fields are added to the Rows area, date and time fields are added to the Columns area, and numeric fields are added to the Values area. You can also manually drag-and-drop any available item into any of the PivotTable fields, or if you no longer want an item in your PivotTable, simply drag it out of the Fields list or uncheck it. Being able to rearrange Field items is one of the PivotTable features that makes it so easy to quickly change its appearance.
PivotTable Fields list | Corresponding fields in a PivotTable |
Working with PivotTable Values
- Summarize Values ByBy default, PivotTable fields that are placed in the Values area will be displayed as a SUM. If Excel interprets your data as text, it will be displayed as a COUNT. This is why it's so important to make sure you don't mix data types for value fields. You can change the default calculation by first clicking on the arrow to the right of the field name, then select the Value Field Settings option.Next, change the calculation in the Summarize Values By section. Note that when you change the calculation method, Excel will automatically append it in the Custom Name section, like 'Sum of FieldName', but you can change it. If you click the Number Format button, you can change the number format for the entire field.Tip: Since the changing the calculation in the Summarize Values By section will change the PivotTable field name, it's best not to rename your PivotTable fields until you're done setting up your PivotTable. One trick is to use Find & Replace (Ctrl+H) >Find what > 'Sum of', then Replace with > leave blank to replace everything at once instead of manually retyping.
- Show Values AsInstead of using a calculation to summarize the data, you can also display it as a percentage of a field. In the following example, we changed our household expense amounts to display as a % of Grand Total instead of the sum of the values.Once you've opened the Value Field Setting dialog, you can make your selections from the Show Values As tab.
- Display a value as both a calculation and percentage.Simply drag the item into the Values section twice, then set the Summarize Values By and Show Values As options for each one.
Refreshing PivotTables
If you add new data to your PivotTable data source, any PivotTables that were built on that data source need to be refreshed. To refresh the PivotTable, you can right-click anywhere in the PivotTable range, then select Refresh.
Deleting a PivotTable
If you created a PivotTable and decide you no longer want it, you can simply select the entire PivotTable range, then press Delete. It won't have any affect on other data or PivotTables or charts around it. If your PivotTable is on a separate sheet that has no other data you want to keep, deleting that sheet is a fast way to remove the PivotTable.
Manual Pivot Table On Mac Excel 2008 Data Analysis
Need more help?
You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.