ADVANCED EXCEL

ADVANCED EXCEL

1
Getting started with Excel 2013 Customizing the Excel 2013 program window
 
 
 
 
Creating workbooks
Modifying workbooks
Modifying worksheets
Merging and unmerging cells
2
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
Defining Excel tables
3
Performing calculations on data Naming groups of data
Creating formula to calculate values
Summarizing data that meets specific conditions
Working with iterative calculation options and automatic
Using array formulas
Finding and correcting errors in calculations
4
Changing workbook appearance Formatting cells
Defining 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
5
Focusing on specific data by using filters Limiting data that appears on your screen
Filtering Excel table data by using slicers
Manipulating worksheet data
 
 
Defining valid sets of values for ranges of cells
6
Reordering and summarizing data Sorting worksheet data
Sorting data by using custom lists
Organizing data into levels
Looking up information in a worksheet
7
Combining data from multiple sources Using workbooks as templates for other workbooks
Linking to data in other worksheets and workbooks
Consolidating multiple sets of data into a single workbooks
8
Analyzing data and alternative data sets Examining data by using the Quick Analysis Lens
Defining an alternative data set
Defining multiple alternative data sets
Analyzing data by using data tables
Varying your data to get a specific result by using Goal
Finding optimal solutions by using Solver
Analyzing data by using descriptive statistics
9
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
10
Using PivotTables and PivotCharts 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 PivotCharts
11
Printing worksheets and charts Adding headers and footers to printed pages
Preparing worksheets for printing
 
 
Printing worksheets
Printing parts of worksheets
Printing charts
12
Working with other Office programs Linking to Office documents from workbooks
Embedding workbooks into other Office documents
Modifying an Inserted Excel Worksheet
Creating hyperlinks
Pasting charts into other Office documents
13
Working with macros and forms Enabling and examining macros
 
Creating and modifying macros
Running macros when a button is clicked
Running macros when a workbook is opened
Inserting form controls and setting form properties
 
 
 
 
 
14
VBA Concepts Using the Visual Basic Editor
Editing recorded macros
Finding and replacing code in the macro
Changing settings for VBA properties
Getting macro input by adding a dialog box
Writing new macros in the Visual Basic Editor
Saving Macros in Global template
15
Assignments Creating workbook and perform specified operations
Creating and populate Tables
Creating Charts
Creating a Pivot and summarize data
VBA - input / msgbox sample
VBA - Decision structures
VBA - Loops