Tonar Systems Advanced MS Excel Course 2013 course covers all the Excel functions to perform financial, mathematical or statistical calculations such as, DSUM, DCOUNT, Pivot Table, Pivot Chart, Formulas, Functions, and Macros. You will also get to implement an Excel project towards the end of the course.
Prerequisites This Advanced MS Excel course is designed to anyone who wants to become a pro in Excel 2013. Also this course enables you to turn data into information with powerful tools to analyze, communicate, and share results. It can help you work better in teams, help to protect and control access to your work. • Skills: Excel Basics. • Software: Windows 7 / 8 – 32 / 64-Bit, Microsoft Office 2013 • Hardware: 500 MHz processor, 256 MB Ram, 3 GB HDD. • Internet: 1.0 Mbps Broa
Why learn Advance Excel Course 2013? Excel is one of the most used software applications of all time. This course is designed for the users those who want to perform accurate and rapid calculations with results ranging from simple calculation to complex financial analyses. Advanced MS Excel Course helps you to structure and present your data impressively.
Course Objectives After the completion of Advanced MS Excel 2013 course at Tonar Systems, you will be able to: 1. Understand Excel Abilities and its Functions. 2. Explain Data Security, Formatting and Analyzing concepts. 3. Understand the specialized functions like DSUM, DMAX etc. 4. Explain the advanced options of Pivot Table. 5. Summarize data by using Sparklines. 6. Record and play a macro. 7. Edit a macro using VBA Editor. 8. Explain how to manage scenarios. 9. Describe the restric
1. Excel Abilities and its Functions
Learning Objectives - In this module, you will learn what are the new features added in Excel 2013. How to customize quick access toolbar and ribbon. Differences between relative and mixed cell references, to perform complex calculations.
Topics - Overview of Microsoft Excel 2013, Improved ribbon, Microsoft Office Backstage view, Excel Mobile 2010 for Windows 7 Phone, Sparklines – An Introduction, Slicers – An Introduction, Customize the Quick Access Toolbar, Customizing common options in Excel Setting, Absolute, Relative and Mixed Cell References, Work with Comments, Using Hyperlinks, Introduction to Excel Functions, Adding a Function to a Formula, Writing conditional expressions (using IF), Building an AutoSum Formula, Adding a Range Name to a Formula.
2. Data Security, Formatting and Analyzing
Learning Objectives - In this module, you will learn to protect and unprotected workbook, Inserting the existing file and new file as an object, share a workbook with others and filtering a table.
Topics - Protecting and un-protecting worksheets and cells, Hiding Formulas, Track Workbook Changes, Share a workbook with other users, Inserting, Formatting and Deleting Object, Changing the font and font size, Applying effects to fonts, Changing font color, Working with text within a cell, Wrapping text within a cell, Applying auto format to a range, Applying a number format, Applying a style to a range, Sorting a Table, Filtering a Table, Filtering a Range, Filtering data for selected view (AutoFilter), Using advanced filter options, Sort by color Sorting in a Pivot Table, Filtering in a Pivot Table, Filtering Using the Report Filter Area.
3. Data Reports
Learning Objectives - In this module, you will learn aggregate functions, reference functions and specialized functions. This module also deals with Pivot Tables and Pivot Charts.
Topics - Using Aggregate Functions like Dsum, Dcount, Dmax, Sumif, Countif, Averageif, Sumifs, Countifs, Averageifs, ISERROR, ISBLANK, using lookup and reference functions (VLOOKUP, HLOOKUP, MATCH, INDEX), Summarizing Data with subtotals, Pivot Tables, Using advanced options of Pivot tables, Types of Pivot Report, Consolidating data from multiple sheets and files using Pivot tables, Import and export data in Excel.
4. Graphical Representations of Data
Learning Objectives - In this module, you will learn Data Visualizing and Automating Excel.
Topics - Examining Chart Elements, Understanding Chart Types, Create a Chart, Format Charts, Create a Chart Template – Combination chart, Summarizing Data by Using Sparkline’s, Creating Dynamic Charts by Using Pivot Charts, Changing the Chart Layout and Style, Introduction to Macros, Recording and Playing a Macro, Editing Using VBA, Mapping a Simple Document.
5. Specialized Functions
Learning Objectives - In this module, you will learn scenario based analysis of data with ‘what if’ analysis & specialized functions.
Topics - Date and time functions, Time Function, Text functions, Proper Function, Concatenate Financial Functions Pmt, Fv, Named Ranges, creating a Data Table, Introduction to Scenarios, Managing Scenarios, Using Goal Seek, Solver, Saving Solver Models, Configuring Solver.
6. Working with Other Microsoft Office Programs
Learning Objectives - In this module, you will learn restrictions in Worksheet, manipulating External Data, and working with other Microsoft Office Programs.
Topics - Specifying a valid range of values for a cell, Input Message, Custom Error Alert, Specifying custom validations based on formula, Getting External Data in Excel, Refreshing data, Add own error messages, Object Linking and Embedding, Insert Drawing Object, Data Forms, Drop Down List, Including Office Documents in Workbooks, Pasting Charts into Other Documents, Preparing Worksheets for Printing, New Methods For Headers And Footers In 2010, Insert Specific Elements In A Header Or Footer, Add A Predefined Header Or Footer, Custom Header Or Footer For A Chart.
Duration: 16 Hrs