This step-by-step workbook will guide you through learning the Excel program. It can be used by a beginner to understand core concepts, and an advanced student to learn the in’s and out’s of Excel. The workbook includes over 80 Practice Exercises that demonstrate how to use Excel concepts, and can also be used as a reference guide to quickly look up a topic.
The primary focus of this manual is to cover PivotTables and PowerPivot capabilities. The first chapter will answer the basic question of “Why do you want to use a PivotTable?” Once you understand “why,” you will have a deeper appreciation for its capabilities. PivotTables are very similar in all versions; however, new features that have been added to Excel 2013 and 2016 will also be covered. We will then review the following:
• Filtering - This will allow you to focus on the required records and reduce the amount of data available.
• Formatting - This will review many Excel formatting features as well as some custom PivotTable commands.
• PivotCharts - This will allow you to chart information for better understanding.
• Layout Adjustments - This will allow you to arrange and adjust fields in order to focus on the most valuable information.
• Calculations - Calculations can be internal or external to the PivotTable.
The Business Intelligence tools include The PowerPivot, PowerView, PowerQuery and PowerMap. They are Add-In features in Excel 2010/2013 which means they are not installed by default, but must be added through the options menu. All these tools will provide the capabilities to analyze data and better understand the information in an Excel workbook. The following topics will be covered:
• PowerPivot - This is a technique used to create relationships between sheets similar to Access databases.
• PowerView - This is a new way to generate reports from an Excel sheet.
• PowerQuery - This is a tool to extract information from the internet and filter it prior to saving to an Excel sheet.
• PowerMap - This is a technique to build charts on a map of the world indicating population or revenue.