Advance Excel

Getting started with Excel 2013

• Identifying the different Excel 2013 programs Identifying new features of Excel 2013
• Customizing the Excel 2013 program window
• Creating workbooks
• Modifying workbooks
• Modifying worksheets
• o Inserting rows, columns, and cells
• Merging and unmerging cells
• Key points

   

Working with data and Excel tables

• Entering and revising data
• Managing data by using Flash Fill
• Moving data within a workbook
• Finding and replacing data
• Correcting and expanding upon worksheet data
• De?ning Excel tables
• Key points

Performing calculations on data

• Naming groups of data
• Creating formulas to calculate values
• Summarizing data that meets speci?c conditions
• Finding and correcting errors in calculations
• Key points

Changing workbook appearance

• Formatting cells
• De?ning styles
• Applying workbook themes and Excel table styles
• Making numbers easier to read
• Changing the appearance of data based on its value
• Adding images to worksheets
• Key points

Focusing on specific data by using filters

• Limiting data that appears on your screen
• Filtering Excel table data by using slicers
• Manipulating worksheet data
• De?ning valid sets of values for ranges of cells
• Key points

Reordering and summarizing data

• Sorting worksheet data
• Sorting data by using custom lists
• Organizing data into levels
• Looking up information in a worksheet
• Key points

Analyzing data and alternative data set

• Examining data by using the Quick Analysis Lens
• De?ning an alternative data set
• De?ning multiple alternative data sets
• Analyzing data by using data tables
• Varying your data to get a speci?c result by using Goal Seek
• Key points

Creating charts and graphics

• Creating charts
• Customizing the appearance of charts
• Finding trends in your data
• Creating dual-axis charts
• Summarizing your data by using sparklines
• Creating diagrams by using SmartArt
• Creating shapes and mathematical equations
• Key points

Using PivotTables and Pivot Charts

• Analyzing data dynamically by using PivotTables
• Filtering, showing, and hiding PivotTable data
• Editing PivotTables
• Formatting PivotTables
• Creating PivotTables from external data
• Creating dynamic charts by using Pivot Charts
• Key points

Printing worksheets and charts

• Adding headers and footers to printed pages
• Preparing worksheets for printing
• Printing parts of worksheets
• Printing charts
• Key points

Collaborating with colleagues

• Sharing workbooks
• Saving workbooks for electronic distribution
• Managing comments
• Tracking and managing colleagues’ changes
• Protecting workbooks and worksheets
• Authenticating workbooks
• Saving workbooks as web content
• Importing and exporting XML data
• Working with SkyDrive and Excel Web App
• Key points

Excel B.I. With Power Pivot Power View

Microsoft Business Intelligence Vision

• Business intelligence in three ways
• Tabular business intelligence semantic model
• Excel 2013 professional power tools
• Module review
• Module objective
• Refreshing data
• Calculations
• DAX Functions
• Module review
• Module objective

Working with other Office programs

• Linking to Office documents from workbooks
• Embedding workbooks into other Office documents
• Creating hyperlinks
• Pasting charts into other Office documents
• Key points

Working With Macros

Getting Started

• Introducing Visual Basic for Application
• Displaying the Developer Tab in the Ribbon
• Recording a Macro
• Saving a Macro-Enable Workbook
• Running a Macro
• Editing a macro in the Visual Basic Editor
• Understanding the Development Environment
• Using Visual Basic Help
• Closing the Visual Basic Editor
• Understanding Macro Security

Excel 2013 Power Pivot Reporting

• Best things that PowerPivot brings to excel
• What will PowerPivot do for the analyst?
• New 2013 PowerPivot features
• Importing data
• Data models
• Relationships
• Simple pivot table reports
• Calculated columns and calculated ?elds

Working with Procedures and Functions

• Understanding Modules
• Creating a Standard Module
• Understanding Procedures
• Creating a Sub Procedure
• Calling Procedure
• Using the Immediate Window to Call Procedures
• Creating a Functions Procedure
• Naming Procedures
• Working with the code Editor
• Module review

Power View Reporting

• Microsoft power view user interface
• Tables
• Tiles
• Charts
• Multiples
• Saving and sharing
• Demonstration