Microsoft Office 2013 Essentials (Speedy Study Guides). Speedy Publishing

      

      MICROSOFT

      OFFICE 2013

      ESSENTIALS

      The most powerful spreadsheet app. Automated suggestions for creating charts, totals, and pivot tables make pivot tables easier beginners. New FlashFill feature saves manual labor by creating columns of data on the basis of examples you type in.

      In Excel 2010, you installed the Power Pivot for Excel 2010 add-in so that you could import and relate large amounts of data from multiple sources.

      In Excel 2013, much of that functionality, including the infrastructure that supports it, is built directly into the Data Model in Excel. Without installing a separate add-in, you can now:

      • Import millions of rows from multiple data sources.

      • Create relationships between data from different sources, and between multiple tables in a PivotTable.

      • Create implicit calculated fields (previously called ‘measures’) – calculations created automatically when you add a numeric field to the Values drop zone of the Field List.

      • Manage data connections.

      Thus all Excel users can now build data models, which they can use as the basis for PivotTables, PivotCharts, and Power View reports. Excel automatically loads data into the in-memory analytics engine, which used to be available only with the Power Pivot add-in. The data model in Excel has other advantages:

      • Processing data is fast.

      • Data is highly compressed, creating a file of manageable size.

      • Data is saved inside the Excel workbook, so it’s portable.

      The Power Pivot add-in is available, too, and provides more advanced modeling capabilities for more experienced data modelers.

      The Power Pivot in Microsoft Excel 2013 add-in, which connects to the same data model, provides a richer modeling environment that allows more experienced users to enhance their models.

      With the Power Pivot in Microsoft Excel 2013 add-in you can:

      • Filter data when importing. You can import data in both Excel and Power Pivot, but when importing data in Power Pivot, you can filter out unnecessary data to import just a subset.

      • Rename tables and columns as you import data in Power Pivot.

      • Manage the model and create relationships using drag and drop in the Diagram View.

      • Apply formatting (to be used in Power View and PivotTable reports).

      • Define your own calculated fields to use throughout a workbook.

      • Define key performance indicators (KPIs) to use in PivotTables.

      • Create user-defined hierarchies to use throughout a workbook.

      • Define perspectives.

      • Author your own calculations by writing advanced formulas that use the Data Analysis Expressions (DAX) expression language.