Updating data in excel automatically

Rated 3.82/5 based on 909 customer reviews

Michael Alexander, Microsoft Certified Application Developer (MCAD) and Microsoft MVP, is author of several books on advanced business analysis with Excel and Access.

9Defining a Pivot Table .................................................................9Why You Should Use a Pivot Table .............................................10Advantages of Using a Pivot Table .........................................11When to Use a Pivot Table ......................................................12Anatomy of a Pivot Table .........................................................12Filters Area ........................................................................14Pivot Tables Behind the Scenes ..........................................14Pivot Table Backward Compatibility .................................................15A Word About Compatibility .................................................16Next Steps............................................................................172 Creating a Basic Pivot Table ........................................19Preparing Data for Pivot Table Reporting ..............................................19Creating a Report Filter.......................................................31Understanding the Recommended Pivot Table Feature...........................31Using Slicers ......................................................................................33Dealing with an Expanded Data Source Range Due to the Addition of Rows or Columns ..............39Sharing the Pivot Cache .........................................40Saving Time with New Pivot Table Tools ............................................41Relocating a Pivot Table .................................................43Next Steps.......................................................................433 Customizing a Pivot Table ................................................45Making Common Cosmetic Changes .................................................46Tracking Relative Importance with the Index Option ...................................74Next Steps...............................................................754 Grouping, Sorting, and Filtering Pivot Data ..........................77Automatically Grouping Dates ...................................................77Using a Custom List for Sorting ..................................................93Filtering a Pivot Table: An Overview ...................................................95Using Filters for Row and Column Fields .........................................96Driving Multiple Pivot Tables from One Set of Slicers .......................................110Next Steps......................................................................................1125 Performing Calculations in Pivot Tables ...........................113Introducing Calculated Fields and Calculated Items ............................................113Method 3: Insert a Calculated Field Directly into a Pivot Table ............................116Creating a Calculated Field ...................................116Creating a Calculated Item ...........................................124Understanding the Rules and Shortcomings of Pivot Table Calculations ..................127Documenting Formulas ........................................................133Next Steps............................................................................1346 Using Pivot Charts and Other Visualizations .........................135What Is a Pivot Chart... ..........................................................135Creating a Pivot Chart ..........................................................136Preprogrammed Scenarios for Condition Levels ........................................151Creating Custom Conditional Formatting Rules .............................................152Next Steps...................................................................................................1567 Analyzing Disparate Data Sources with Pivot Tables ................................157Using the Internal Data Model ..................................................158Limitations of the Internal Data Model ...............................................................167Building a Pivot Table Using External Data Sources .............................................168Building a Pivot Table with SQL Server Data ......................................................171Leveraging Power Query to Extract and Transform Data .............................................174Power Query Connection Types ......................................................188Next Steps..........................................................................................1928 Sharing Pivot Tables with Others ........................................193Designing a Workbook as an Interactive Web Page .......................................................193Sharing a Link to a Web Workbook ................................................................196Sharing with Power BI ...............................................................................196Sharing Your Dashboard ..............................................................202Next Steps........................................................................2029 Working with and Analyzing OLAP Data ..........................203Introduction to OLAP .............................................................................203Connecting to an OLAP Cube .....................................................204Understanding the Structure of an OLAP Cube ...................................................207Understanding the Limitations of OLAP Pivot Tables .........................................208Creating an Offline Cube ...................................................209Breaking Out of the Pivot Table Mold with Cube Functions ................................211Performing What-If Analysis with OLAP Data .....................................220Next Steps...............................................................................22210 Mashing Up Data with Power Pivot ...................................223Understanding the Benefits and Drawbacks of Power Pivot and the Data Model ............223Building a Pivot Table ....................................................................237Understanding Differences Between Power Pivot and Regular Pivot Tables ............................238Using DAX Calculations .............................................................239Using Time Intelligence .............................................................242Next Steps.....................................................................................24311 Dashboarding with Power View and 3D Map ..........................245Preparing Data for Power View ....................................................245Creating a Power View Dashboard .............................................................247Using Tile Boxes to Filter a Chart or a Group of Charts .....................................255Replicating Charts Using Multiples .................................................256Showing Data on a Map......................................................257Using Images ..........................................................................258Changing a Calculation ..........................................................................259Animating a Scatter Chart over Time .............................................259Some Closing Tips on Power View ......................................................261Analyzing Geographic Data with 3D Map ................................................261Creating a Video from 3D Map...................................................271Next Steps.........................................................................27412 Enhancing Pivot Table Reports with Macros ................275Why Use Macros with Pivot Table Reports ................................275Recording a Macro ..................................................................276Creating a User Interface with Form Controls .................................278Altering a Recorded Macro to Add Functionality.................................280Inserting a Scrollbar Form Control ............................................281Next Steps......................................................28813 Using VBA to Create Pivot Tables.................................289Enabling VBA in Your Copy of Excel .....................................289Using a File Format That Enables Macros ..................................290Visual Basic Editor .................................................................291Visual Basic Tools .........................................................291The Macro Recorder ..................................................................292Understanding Object-Oriented Code ................................................292Learning Tricks of the Trade ........................................................293Using With and End With to Shorten Code ................................................295Understanding Versions .....................................................................295Building a Pivot Table in Excel VBA ......................................296Putting It All Together ......................................................349Next Steps....................................................................35114 Advanced Pivot Table Tips and Techniques .....................353Tip 1: Force Pivot Tables to Refresh Automatically........................................353Tip 2: Refresh All Pivot Tables in a Workbook at the Same Time ...................................354Tip 3: Sort Data Items in a Unique Order, Not Ascending or Descending ....................355Tip 4: Turn Pivot Tables into Hard Data .............................................355Tip 5: Fill the Empty Cells Left by Row Fields .........................................356Option 2: Use Excel’s Go To Special Functionality ..............................357Tip 6: Add a Rank Number Field to a Pivot Table ...........................359Tip 7: Reduce the Size of Pivot Table Reports .........................360Delete the Source Data Worksheet ......................................360Tip 8: Create an Automatically Expanding Data Range .................................361Tip 9: Compare Tables Using a Pivot Table ..........................361Tip 10: Auto Filter a Pivot Table .............................................363Tip 11: Force Two Number Formats in a Pivot Table ..................................364Tip 12: Create a Frequency Distribution with a Pivot Table ..................................366Tip 13: Use a Pivot Table to Explode a Data Set to Different Tabs ....................

Supported platforms are: Excel® 2016 PIVOT TABLE DATA CRUNCHING CRUNCH DATA FROM ANY SOURCE, QUICKLY AND EASILY, WITH EXCEL 2016 PIVOT TABLES!

Use Excel 2016 pivot tables and pivot charts to produce powerful, dynamic reports in minutes instead of hours…

The updates will be delivered to you via a free Web Edition of this book, which can be accessed with any Internet connection from your account on

I have a Share Point that has list and I need to be able to update this list with new values from an Excel sheet (using VBA).

Leave a Reply