Excel For Mac Getpivotdata

Posted on  by

  • Outline

    • 1: Customise Your PivotTable
    • 2: Summarize Values By
    • 3: Show Values As
    • 4: Grouping
    • 5: Sort
    • 6: Filter
    • 7: Slicers
    • 8: Calculated Fields and Items
    • 9: Pivot Charts
    • 10: Conditionally Formatting PivotTables
    • 11: GETPIVOTDATA
    • 12: Macros
    • 13: Data Management
    • 14: Conclusion and Bonus Videos

  • Author

    John Michaloudis

  • Subject

    Xtreme PivotTables

  • Benefits

    Begin Now: begin training immediately
    Moderated: ask questions in the moderated forums
    Certificate: download and print your certificate instantly upon completion
    Pause and Resume: pause and resume training as needed
    Exam: unlimited exam attempts

  • Software

    Featuring Microsoft Excel for Windows Versions

  • Format

    On-Demand

  • Access Begins

    Immediately

  • Duration

    Online enrollment provides access for one year.

  • Certificate of Completion

    Download instantly once a passing score on the final exam is achieved. You may re-attempt the final exam as many times as necessary.

  • Enrollment Period

    Your enrollment is valid for one year.

Return blank cell instead of zero using GETPIVOTDATA (Excel for Mac 2011) If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. The advantage of using the GetPivotData function is that it uses criteria to ensure that the correct data is returned, even if the pivot table layout is changed. GETPIVOTDATA function returns visible data from a pivotTable. Syntax = GETPIVOTDATA(datafield, pivottable, field1, item1. HI, I am having difficulties with Excel 2004 for MAC with the Getpivotdata function. The syntax is different between EXCEL 2003 for Windows and MAC 2004. I have constructed a very complex budget of operations dealing with dynamic tables and for some reason whenever i opened the excel file in. Purpose of Excel GETPIVOTDATA Function. The advantage of using the GetPivotData function is that it uses criteria to ensure that the correct data is returned, even if the pivot table layout is changed. GETPIVOTDATA function returns visible data from a pivotTable. Advanced Excel functions; Excel Shortcuts for PC and Mac Excel Shortcuts PC Mac Excel Shortcuts - List of the most important & common MS Excel shortcuts for PC & Mac users, finance, accounting professions. Keyboard shortcuts speed up your modeling skills and save time.

  • Module 1: CUSTOMISE YOUR PIVOT TABLE
  • 1.1 ARRANGING YOUR DATA SET
  • 1.11 Tabular Format (2:17)
  • 1.12 No Gaps (2:14)
  • 1.13 Formatting (1:17)
  • 1.14 Tables (2:36)
  • 1.15 Clean Your Data Set (2:08)

  • 1.2 CREATING A PIVOT TABLE
  • 1.21 Inserting a Pivot Table (3:12)
  • 1.22 Field List - Activate, move, resize & layout (1:30)
  • 1.23 Field List & Areas (5:15)
  • 1.24 Drill down to audit (1:33)
  • 1.25 Sort Field List from A to Z (0:35)
  • 1.26 Double click on any labels to show more Fields (0:43)
  • 1.27 Defer Layout Update (0:45)

  • 1.3 OPTIONS > DATA
  • 1.31 Pivot Cache explained (0:54)
  • 1.32 Refresh (1:11)
  • 1.33 Refresh All (2:11)
  • 1.34 Refresh External Data (4:49)
  • 1.35 Import from Access database (2:18)
  • 1.36 Change Data Source (3:00)

  • 1.4 OPTIONS > ACTIONS
  • 1.41 Clear Filters & Clear Pivot (1:08)
  • 1.42 Select & format (3:26)
  • 1.43 Move a Pivot Table (0:50)

  • 1.5 DESIGN > STYLES
  • 1.51 Pivot Table Styles (3:44)
  • 1.52 Customising a Pivot Table Style (4:03)
  • 1.53 Use a customised style in another workbook (1:17)

  • 1.6 DESIGN > LAYOUT
  • 1.61 Subtotals (1:00)
  • 1.62 Grand Totals (0:53)
  • 1.63 Report Layouts (2:43)
  • 1.64 Blank Rows (0:33)
  • 1.65 Show the Classic Pivot Table Layout (0:58)

  • • 1.7 OPTIONS > SHOW
  • 1.71 Expand & Collapse buttons (4:26)
  • 1.72 Move & Remove Fields and Items (2:29)
  • 1.73 Show/Hide Field List (0:17)
  • 1.74 Show/Hide Field Headers (0:17)

  • 1.8 CUSTOMISATION
  • 1.81 Change Count of to Sum of (1:00)
  • 1.82 Number formatting (1:32)
  • 1.83 Field name formatting (2:06)
  • 1.84 Predetermined number formatting (1:18)
  • 1.85 Change Sum views in Label areas (0:42)

  • 1.9 LAYOUT & FORMAT
  • 1.91 Indent rows in compact layout (1:00)
  • 1.92 Change the layout of a report filter (1:15)
  • 1.93 Format error values (0:39)
  • 1.94 Format empty cells (1:17)
  • 1.95 Keep column widths upon refresh (0:43)
  • 1.96 Automatic Refresh a Pivot Table (0:46)
  • 1.97 Printing a pivot table on two pages (1:11)
  • 1.98 Show report filter on multiple pages (2:13)

  • Module 2: SUMMARIZE VALUES BY
  • 2.1 Create multiple subtotals (1:34)
  • 2.2 Count (2:17)
  • 2.3 Average (2:33)
  • 2.4 Maximum (1:53)
  • 2.5 Minimum (1:16)
  • 2.6 Product (3:28)
  • 2.7 Count Numbers (2:16)
  • 2.8 Std Dev (6:57)
  • 2.9 Varp (3:26)
  • 2.1 Show various Grand Totals (2:26)
  • 2.11 Shortcuts to Field & Value Field Settings (2:09)
  • 2.12 See all pivot Items (5:50)
  • 2.13 Show a unique count (2:26)

  • Module 3: SHOW VALUES AS
  • 3.1 % of Grand Total (2:00)
  • 3.2 % of Column Total (2:05)
  • 3.3 % of Row Total (1:45)
  • 3.4 % Of (3:53)
  • 3.5 % of Parent Row Total (NEW IN EXCEL 2010) (3:09)
  • 3.6 % of Parent Column Total (NEW IN EXCEL 2010) (2:13)
  • 3.7 % of Parent Total (NEW IN EXCEL 2010) (2:42)
  • 3.8 Difference From (4:44)
  • 3.9 % Difference From (4:08)
  • 3.1 Running Total in (2:10)
  • 3.11 % Running Total in (NEW IN EXCEL 2010) (2:48)
  • 3.12 Rank Smallest to Largest (NEW IN EXCEL 2010) (2:04)
  • 3.13 Rank Largest to Smallest (NEW IN EXCEL 2010) (2:11)
  • 3.14 Index (3:35)
  • 3.15 Shortcuts to Show Values As (1:07)
  • 3.16 ACCOUNTING: % of Revenue Margins (2:28)
  • 3.17 FINANCIAL: Actual v Plan Variance Report (4:33)

  • Module 4: GROUPING
  • 4.1 Group by Date (2:35)
  • 4.2 Group by Months (1:41)
  • 4.3 Group by Quarters & Years (1:38)
  • 4.4 Group by Sales ranges (3:09)
  • 4.5 Group by Text fields (2:06)
  • 4.6 Group by Time (1:35)
  • 4.7 Shortcuts to Grouping (1:29)
  • 4.8 Grouping by Half Years (1:45)
  • 4.9 Group by a Date that starts on a Monday (1:50)
  • 4.1 Grouping by a custom date (1:39)
  • 4.11 Group by fiscal years & quarters (6:26)
  • 4.12 Errors when grouping by dates (2:44)
  • 4.13 Group two pivot tables independently (3:42)
  • 4.14 Fixing the problem of counting grouped sales (0:35)
  • 4.15 Display dates that have no data (0:49)
  • 4.16 ACCOUNTING: Quarterly Comparative Report (6:35)
  • 4.17 FINANCIAL: Min & Max Bank Balance (4:32)

  • Module 5: SORT
  • 5.1 Sorting by Largest or Smallest (1:49)
  • 5.2 Sort an Item Row (Left to Right) (1:21)
  • 5.3 Sort manually (drag, write, right click) (1:51)
  • 5.4 Sort using a Custom List (3:10)
  • 5.5 Override a Custom List sort (1:17)
  • 5.6 Sort row from A-Z and sales from Z-A (1:13)
  • 5.7 Sort new items added to your data source (1:08)
  • 5.8 Clear a sort (0:18)
  • 5.9 Sort Largest to Smallest Grand Totals (0:16)
  • Module 6: FILTER
  • 6.1 Filter by Dates (6:38)
  • 6.2 Filter by Labels - Text (2:33)
  • 6.3 Filter by Labels - Numerical Text (2:41)
  • 6.4 Filter by Values (3:48)
  • 6.5 Filter by Values - Top or Bottom 10 Items (2:03)
  • 6.6 Filter by Values - Top or Bottom % (1:36)
  • 6.7 Filter by Values - Top or Bottom Sum (1:39)
  • 6.8 Filter by Report Filter (3:28)
  • 6.9 Shortcuts to filters (1:33)
  • 6.1 Keep or hide selected items (0:47)
  • 6.11 Filter by Text wildcards * and ? (3:17)
  • 6.12 Filter by multiple fields (0:57)
  • 6.13 Apply multiple filters (1:42)
  • 6.14 Filter by multiple values (1:03)
  • 6.15 Include new items in manual filter (1:48)
  • 6.16 Clear filters with one click (1:27)
  • 6.17 Add a filter for the column items (0:30)
  • 6.18 ACCOUNTING: Top 5 Expenses report (2:20)
  • 6.19 FINANCIAL: Top 25% of Channel Partners (2:08)

  • Module 7: SLICERS
  • 7.1 Insert a Slicer (3:51)
  • 7.2 Slicer Styles (2:16)
  • 7.3 Creating a custom style (5:28)
  • 7.4 Copy a custom style into a new workbook (1:30)
  • 7.5 Slicer Settings (4:40)
  • 7.6 Slicer Size & Properties (2:53)
  • 7.7 Slicer Connections for multiple pivot tables (3:14)
  • 7.8 Different ways to filter a Slicer (1:12)
  • 7.9 Use one slicer for two pivot tables (1:16)
  • 7.1 Lock the workbook but not the slicer (1:23)
  • 7.11 Interactive employee photos with Slicers! FUN! (9:18)
  • 7.12 ACCOUNTING: Select a Monthly P&L report with a Slicer (4:10)
  • 7.13 FINANCIAL: Base, Best & Worst case Forecast (5:00)

  • Module 8: CALCULATED FIELDS & ITEMS
  • 8.1 Creating a Calculated Field (3:56)
  • 8.2 Use an existing Calculated Field in a new calculation (2:13)
  • 8.3 Editing a Calculated Field (1:53)
  • 8.4 Excel formulas & Calculated Fields (2:57)
  • 8.5 Creating a Calculated Item (4:02)
  • 8.6 Use an existing Calculated Item in a new calculation (1:33)
  • 8.7 Editing a Calculated Item (2:15)
  • 8.8 Excel formulas & Calculated Items (1:48)
  • 8.9 Calculated Item on Column Labels (2:04)
  • 8.1 Shortcomings of Calculated Items (1:53)
  • 8.11 Solve Order for Calculated Items (4:01)
  • 8.12 List Calculated Field & Item formulas (1:02)
  • 8.13 Remove a Calculated Field temporarily (0:50)
  • 8.14 Order of operations (1:46)
  • 8.15 ACCOUNTING: Creating a P&L Pivot Table Report (8:15)
  • 8.16 FINANCIAL: Actuals v Plan with Calculated Fields (6:26)

  • Module 9: PIVOT CHARTS
  • 9.1 Insert a Pivot Chart (3:14)
  • 9.2 Insert a Slicer with a Pivot Chart (1:50)
  • 9.3 Pivot Chart Designs (3:45)
  • 9.4 Pivot Chart Layouts (4:54)
  • 9.5 Pivot Chart Formats (5:47)
  • 9.6 Limitations of Pivot Charts & workarounds (1:51)
  • 9.7 Saving a Pivot Chart template (1:26)
  • 9.8 Shortcuts to formatting a Pivot Chart (1:13)
  • 9.9 Link chart title to a pivot cell (0:55)
  • 9.1 Copying a second chart (0:41)
  • 9.11 Put a chart on a separate page with F11 (0:37)
  • 9.12 Insert Pivot Chart straight from the data source (0:49)
  • 9.13 Paste Pivot Chart to your email as a picture (1:02)
  • 9.14 Paste Pivot Chart to PowerPoint & make live updates (1:24)
  • 9.15 Printing a Pivot Chart (1:17)
  • 9.16 Include a Sparkline with your pivot table (1:14)
  • 9.17 Charts Do´s & Don'ts (2:35)
  • 9.18 Change Chart Type with Slicers! FUN!!! (7:58)
  • 9.19 Workaround to creating an interactive Scatter graph (2:17)
  • 9.2 ACCOUNTING: P&L Pivot Table report with Graphs (8:11)
  • 9.21 FINANCIAL: Pivot Table Slicer & Chart Dashboard (15:45)

  • Module 10: CONDITIONALLY FORMATTING PIVOT TABLES
  • 10.1 Intro to Conditional Formatting (2:33)
  • 10.2 Highlight Cell Rules based on values (1:17)
  • 10.3 Highlight Cell Rules based on text labels (0:44)
  • 10.4 Highlight Cell Rules based on date labels (1:12)
  • 10.5 Top & Bottom Rules (3:21)
  • 10.6 Data Bars, Color Scales & Icon Sets (NEW IN EXCEL 2010) (5:39)
  • 10.7 Format only cells that contain - For Bonuses (1:35)
  • 10.8 Format only Top or Bottom ranked values - Top 3 sales per year (1:29)
  • 10.9 Format values that are above or below the average - For Promotions (1:34)
  • 10.1 Use a formula to determine which cells to format (2:29)
  • 10.11 Use selected cells to format multiple fields (1:28)
  • 10.12 All cells showing values to format multiple fields (1:28)
  • 10.13 Control Conditional Formatting with Slicers (2:44)
  • 10.14 Show text in the Pivot Table Values area (4:41)
  • 10.15 Cond Format blank cells or cells (1:04)
  • 10.16 ACCOUNTING: Accounts Receivable Ageing Report Matrix (6:07)
  • 10.17 FINANCIAL: Conditionally Format your sales results (3:22)

  • Module 11: GETPIVOTDATA
  • 11.1 Intro to GETPIVOTDATA (4:49)
  • 11.2 Create a custom report with GETPIVOTDATA (5:14)
  • 11.3 Reference Dates with GETPIVOTDATA (3:07)
  • 11.4 Data validation with GETPIVOTDATA (2:42)
  • 11.5 Shortfalls of GETPIVOTDATA (1:38)
  • 11.6 Grand Totals to the left of the pivot table (3:09)
  • 11.7 ACCOUNTING: Live forecasting with GETPIVOTDATA (7:14)
  • 11.8 FINANCIAL: Channel Analysis with GETPIVOTDATA (5:20)

  • Module 12: MACROS
  • 12.1 Adding the Developer tab & disabling macros (2:19)
  • 12.2 Record a simple macro to Refresh a pivot table (4:16)
  • 12.3 Date filter macro (4:17)
  • 12.4 Different pivot table views macro (4:33)
  • 12.5 Top 10 macro (3:20)
  • 12.6 Add macro to quick access toolbar (1:10)

  • Module 13: DATA MANAGEMENT
  • 13.1 Reducing file memory by copying existing pivot table (1:19)
  • 13.2 Reducing file memory by deleting the data source (1:36)
  • 13.3 Reducing file memory by saving file as Excel Binary Workbook (0:47)
  • 13.4 Reducing file memory by keeping data source in MS Access (2:02)
  • 13.5 Compatibility Issues with Excel 2007 and Excel 2010 (1:09)
  • 13.6 Sharing a Pivot Table via OneDrive (2:39)

  • Module 14: CONCLUSION & BONUS VIDEOS
  • 14.1 Sales Forecasting with Calculated Fields (4:08)
  • 14.2 Consolidate with a Pivot Table (4:36)
  • 14.3 Frequency distribution with a Pivot Table (2:06)
  • 14.4 Break Even Model (2:54)
  • 14.5 Several slicer custom styles for you to use (1:53)
  • 14.6 Interactive Balance Sheet Pivot Table (12:11)
  • 14.7 Monthly Sales Manager Performance Report (4:25)
  • 14.8 Reconciling customer payments (1:32)
  • 14.9 Pivot Power Add-In to make you more efficient (6:53)

Materials

The course includes the following course materials:

  • Quick Reference Guide
  • Sample Excel files
  • Download the lecture videos (mp4)
  • Glossary

Comments from participants

  • “I have learned more about Pivot Tables within a week of taking this course than I have over the last couple of years.”

  • “Really amazing stuff!”

  • “The videos are short and precise, easy to understand and gives you the ability to refer back to them at any time.”

  • “This course is tremendously comprehensive. The best part is that the course is served in small bite-sized pieces that are easily digested”

  • “Definitely worth your time and money”

  • “This is the best Excel course that I have taken! It not only talks about Pivot Tables in great detail, it also touches on charts, conditional formatting and macros.”

  • “Excellent course for any Excel level”

Objectives

  • State how to group Pivot Table reports
  • List ways to manually sort a Pivot Table
  • Recognize how to show the data behind a Pivot Table value

Program Level

Basic

Prerequisites

None

Advance Preparation Needed

Ensure you have access to Excel for Windows, preferably versions 2010 or later, so that you can work through the required homework exercises. Excel for Mac is not officially supported.

Delivery Method

QAS Self-study

Recommended CPE Credits

16

Field of Study

Computer Software & Applications

Expiration

Program enrollment is valid for one year and participant must complete the final exam within this time period.

Registration

To register, add this course to your cart by clicking the Buy Now button and then complete the checkout process.

Excel Getpivotdata Sum

Refund Policy

Full 30-day refund policy. Full refund for any course cancellations or registration into upcoming session if preferred.

Complaint Resolution

For information regarding adminstrative policies such as complaint resolution, please contact Jeff Lenning CPA CITP at 949-200-7688 or via email info@excel-university.com

Official Registry Statement

Excel University, Inc. is registered with the National Association of State Boards of Accountancy (NASBA) as a sponsor of continuing professional education on the National Registry of CPE Sponsors. State boards of accountancy have final authority on the acceptance of individual courses for CPE credit. Complaints regarding registered sponsors may be submitted to the National Registry of CPE Sponsors through its website: www.nasbaregistry.org.
Price: $347.00
Buy 2 or more for $277.60 each
Buy 20 or more for $173.50 each

Excel Pivot Tables for Accountants - Virtual Only

Description

Excel Getpivotdata Cell Reference

After much thought, MSCPA has decided to take all our CPE Classes till the end of December 2020 to virtual, this includes all conference. The risk of COVID-19 is just too high and we would not want any of you to get sick at one of our classes. All materials will be electronic.

PivotTables are widely considered the most powerful feature in Excel, yet most accounting and financial professionals do not use them in their day-to-day activities. If you are ready to learn how to take advantage of PivotTables or how to extend your existing knowledge of PivotTables, then you should make time to participate in this course. In this program, you will learn how to build PivotTables, how to group data inside PivotTables – including grouping options for fiscal periods – and how to create user-defined calculations in your PivotTables. You will also learn advanced PivotTable techniques, including how to build PivotTables that consolidate data from multiple data ranges and PivotTables dynamically connected to external databases and financial accounting systems. Additionally, you will learn how to take advantage of other Excel features associated with PivotTables, including Slicers, Power Query and Power Pivot. At the end of the day, you will be a master of Excel PivotTables! If you have been leery of working with PivotTables or have struggled to realize their many benefits, this course is for you. With a little information, guidance, and coaching from your K2 instructor, you will be ready to use PivotTables to analyze and report on very large data sets in a fraction of the time you are spending presently to complete such tasks.

Instructional delivery method: Group-live demonstration and discussion – laptop friendly, with access to demonstration files. Program focuses on Excel for Windows, but many of the features can be found in Excel for Mac.

Objectives

Upon completing this course, you should be able to:

Excel Help Getpivotdata

For

Excel Getpivotdata Get Grand Total

  • Identify the six main elements of a PivotTable report and their use when creating PivotTables
  • Create PivotTables and manipulate data in PivotTables using grouping techniques, modified Field
  • Settings, user-defined calculations, and sorting and filtering options
  • Import and link data from external data sources into PivotTables; additionally, use Tables and Data Models as data sources for PivotTables
  • Create Consolidation PivotTables to consolidate data from multiple data sources in Excel
  • Construct PivotTables in enterprise-class organizations using Power Pivot and Slicers
  • Analyze data in PivotTables using tools and techniques, such as sorting, filtering, Excel’s GETPIVOTDATA function, and PivotCharts
  • Apply specific PivotTable techniques to solve problems in areas of PivotTable formatting, creating multiple PivotTables from a single data source, grouping PivotTable data into fiscal reporting periods and keeping PivotTable row order intact

Excel Getpivotdata Filter Items

Designed For

Excel 2010 Getpivotdata

Accounting and financial professionals who want to use the power of Excel PivotTables to analyze and summarize data.