028 3824 5819 traincert@tdt-tanduc.com
Tiếng ViệtEnglish

Excel 2013 Expert Skills

This course will teach you how to use Excel’s advanced features to streamline your work. This course will allow the participants to learn some powerful new features of Excel 2013 and train the participants how to convert raw data into Excel Table that will enable effective data filtering and sorting, format data in Excel table or PivotTable to highlight important figures using different colors based on pre-defined rules, extract useful information from the database using VLookup & Lookup functions, and advanced filter, group data into category to calculate subtotals and grand totals automatically, outline complex worksheet data to show and hide details, validate data entry to accept only valid data, protect confidential contents from unauthorized access using passwords, linking worksheet formulas and consolidate data from multiple worksheets and workbooks. Participants will also learn how to use PivotTables to analyze large database more efficiently, use Pivot Charts to summarize important figures, drop-down list, filter and database functions to create interactive and impressive reports for data analysis. By the end of the course, you’ll be able to create sophisticated business tools.

Session 1: Tables and Ranges

  • Apply a simple filter to a range
  • Apply a top 10 and custom filter to a range
  • Apply an advanced filter with multiple OR criteria
  • Apply an advanced filter with complex criteria
  • Apply an advanced filter with function-driven criteria
  • Extract unique records using an advanced filter
  • Add totals using Quick Analysis
  • Add percentage and running totals Quick Analysis
  • Convert to range into a table and add a total row
  • Format a table using table styles and convert a table into a range
  • Create a custom table style
  • Sort a range or table by rows
  • Sort a range by columns
  • Sort a range or table by custom list
  • Name a table and create an automatic structured table reference

Session 2: Data Integrity, Subtotals and Validations

  • Split fixed width data using Text to Columns
  • Split delimited data using Text to Columns
  • Automatically subtotal a range
  • Create nested subtotals
  • Consolidate data from multiple data ranges
  • Use data consolidation to generate quick subtotals from tables
  • Validate numeric data
  • Create user-friendly messages for validation errors
  • Create data entry input messages
  • Add a formula-driven date validation and a text length validation
  • Add a table-based dynamic list validation
  • Use a function-driven custom validation to enforce complex business rules
  • Remove duplicate values from a range or table
  • Use a custom validation to add a unique constraint to a column

Session 3: Advanced Functions

  • Use the IF logic function
  • Use the SUMIF & COUNTIF functions to create conditional totals
  • Use the AND and OR functions to construct complex Boolean criteria
  • Understand calculation options
  • Concatenate string using the concatenation operator (&)
  • Use the TEXT function to format numerical values as strings
  • Extract text from fixed width strings using the LEFT, RIGHT and MID functions
  • Extract text from delimited strings using the FIND and LEN functions
  • Use a VLOOKUP function for an exact lookup
  • Use an IFERROR function to suppress error messages
  • Use a VLOOKUP function for an inexact lookup

Session 4: Using Names and the Formula Auditing Tools

  • Automatically create single-cell range names
  • Manually create single cell range names and named constants
  • Use range names to make formulas more readable
  • Automatically create range names in two dimensions
  • Use intersection range names and the INDIRECT function
  • Create dynamic formula-based range names using the OFFSET function
  • Create table-based dynamic range names
  • Create two linked drop-down lists using range names
  • Audit a formula by tracing precedents
  • Audit a formula by tracing dependents
  • Use the watch window to monitor cell values
  • Use Speak Cells to eliminate data entry errors

Session 5: PivotTables

  • Create a one dimensional pivot table report from a table
  • Create a grouped pivot table report
  • Understand pivot table rows and columns
  • Use an external data source
  • Understand the PivotTable data cache
  • Apply a simple filter and sort to a pivot table
  • Use report filter fields
  • Filter a pivot table visually using slicers
  • Add a timeline to a PivotTable
  • Use slicers to create a data-driven interface
  • Use report filter fields to automatically create multiple pages
  • Format a pivot table using PivotTable styles
  • Create a custom PivotTable style
  • Understand pivot table report layouts
  • Add/remove subtotals and apply formatting to pivot table fields
  • Display multiple summations within a single pivot table
  • Add a calculated field to a pivot table
  • Add a calculated item to a pivot table
  • Group by Text & Date
  • Group by numeric value ranges
  • Show row data by percentage of total rather than value
  • Create a pivot chart from a pivot table
  • Embed multiple pivot tables onto a worksheet
  • Use slicers to filter multiple pivot tables

Session 6: What If Analysis and Security

  • Create a single-input data table
  • Create a two-input data table
  • Define Scenarios
  • Create a scenario summary report
  • Use Goal Seek and Solver
  • Hide and unhide worksheets, columns and rows
  • Create custom views
  • Prevent unauthorized users from opening or modifying workbooks
  • Control the changes users can make to workbooks
  • Restrict the cells users are allowed to change
  • Allow different levels of access to a worksheet with multiple passwords

Thời lượng: 03 ngày
Giảng viên: Microsoft Certified Trainer, MOS Master
Chứng nhận hoàn thành khóa học của Microsoft

KH 2
KH 3
KH 5