Microsoft Excel is a spreadsheet application consisting of a collection of cells arranged into rows and columns to organize and manipulate data.
Excel features the ability to perform calculations, format & store data, use graphs & histograms, create pivot tables, and create macros. Excel is typically used to do data processing & analysis.
Excel is used across all business functions and at organizations of all sizes. The main uses of Excel include: Data entry, Data management, Accounting, Financial analysis, Charting and graphing, Programming, Time management, Task management, Financial modeling, Customer relationship management (CRM), and so on.
Besides the core features of Excel such as creating formulas, filters, sorting, data formatting, data validation, charts & graphs, etc. this MS Excel training will help you learn the advanced elements such as create macros, collaborate with others, audit and analyze worksheet data, incorporate multiple data sources, and import & export data.
Being proficient in Excel can open doors to many career opportunities such as: Administrative Assistant, Data Analyst, Accountants, Financial Analyst, Retail Store Manager, Project Manager, Business Analyst, Consulting, and the like.
Uplatz offers this comprehensive MS Excel training with focus on practical and efficient usage of Excel spreadsheet to perform data analysis as well as to manage the data records. You will also learn magical keyboard shortcuts to help you work on Excel in the best possible manner.--------------------------------------------------------------------
Section 1: Foundation
Unit 1.1 – Excel Introduction
A description of the purpose and application of Excel – what it can do, why it’s useful.
Unit 1.2 – The Excel Interface
Understanding and working with the Excel interface – getting to know your way around the application, how to access functionality and how to customize it. How to create, save and open Workbooks. Under Save As, a brief explanation of backwards compatibility and other formats (e.g. PDF) save options. Skydrive, alternative online storage, and ‘local’ saving. Basic printing.
Unit 1.3 – Basic Navigation & Editing
Selection and navigation. Moving around a spreadsheet efficiently. Entering information into cells, types of data (text, numbers, dates). Basic formatting. Working with multiple Worksheets.
Unit 1.4 – Getting going
Takes a quick look at a first ‘real’ spreadsheet, in order to introduce the concept of functions and formulas, and give a feel for how Excel can be useful.
Section 2: Orientation and efficiency
Unit 2.1 – Editing
Copying, pasting, inserting and deleting cells, ranges of cells, rows, columns and Worksheets. ‘Special’ pasting – for example to copy just formulas, or just values. Common problems with pasting. The differences between copying and ‘cutting’. Excel aids to copying – Fill Handle and Flash Fill.
Unit 2.2 – Viewing
The various ‘Views’ or modes of spreadsheet display available in Excel, and how to choose the right View for the task in hand. Techniques for making working with a spreadsheet easier – Freezing Panes, splitting windows, and using multiple windows.
Unit 2.3 – Spreadsheet structure
Changing the structure of your spreadsheet to make it clearer. Grouping rows and columns; changing cells size and shape.
Unit 2.4 – Cell references
How (and why) to connect (or link) cells and ranges, and how connected cells behave. Fixed or absolute references (dollar signs) for cells; partially fixed references. The "F4" shortcut.
Unit 2.5 – Named Ranges
Making formulas clearer and more easily understood by replacing cell references with meaningful names.
Unit 2.6 – Basic Macros
Using Macros to automate repetitive tasks in Excel. (This Unit covers recorded Macros – the later unit 5.8 covers writing and editing Macros using VBA in detail.)
Unit 2.7 – Design
Why spreadsheet design important in increasing efficiency, reducing errors and aiding transparency. Principles of good spreadsheet design.
Section 3: Administration
Unit 3.1 – Customising Excel
Changing Excel settings to suit the way you use Excel. Includes: font face, number of worksheets, frequency of autorecovery, default view, Autocorrect and Ribbon alterations. Excel Add-Ins – Solver, Analysis TookPak. PowerPivot and Inquire.
Unit 3.2 – Housekeeping
Naming and storing Excel files. Version control. Making backups.
Unit 3.3 – Connecting Workbooks
· Setting up links between Workbooks. The dangers and pitfalls of doing so.
· Managing links between Workbooks and updating external references.
· Troubleshooting – finding ‘missing links’ and using auditing tools.
Unit 3.4 – Documentation
What to document, and how, in order to produce clear and robust spreadsheets.
Unit 3.5 – Protecting and Sharing
Protecting Workbooks and when it’s useful or necessary locking cells, Worksheet protection, Workbook protections, allowing users to edit ranges.
Permissions and granting access to folders & files, how to work with others on the same files and different files (both in process terms and using Excel functionality; when not to use it, avoiding conflicts). Sharing via the Cloud.
Unit 3.6 – Googledocs
What is Googledocs, and why use Google Spreadsheets for spreadsheeting?
Unit 3.7 – Excel troubleshooting
Common problems and how to avoid or overcome them. For example building in checks into spreadsheets, techniques for finding errors. Advanced techniques for diagnosing more complex problems.
Section 4: Data handling
Unit 4.1 – Sorting & Filtering
Techniques for sorting and filtering data, including controlling the order of precedence in a sort, advanced filters, and an introduction to PivotTables. Using sorting and filtering to check and ‘clean’ data.
Unit 4.2 – Controlling user input
Controlling the way users can enter data into a spreadsheet to reduce risk of error and increase efficiency. Covers Data Validation and using IS- functions to trap errors.
Unit 4.3 – Working with Dates and Times
Date and time formatting, performing basic maths with dates and times, and date and time functions.
Unit 4.4 – Working with Text
Entering and formatting text, extracting or combining parts of text. ‘Cleaning’ data for typos and bugs.
Unit 4.5 – Lookup & Reference
Looking up information in a basic table, and more flexibly. VLOOKUP() and the more flexible INDEX()/MATCH() combination. Advanced lookups.
Unit 4.6 – Logical Functions
Automatic decision making and error handling. Advanced logical tests. Matrix user of IF as an array function.
Section 5: Data analysis
Unit 5.1 – Working with Numbers
Maths: basic addition, subtraction, multiplication and division. How to use brackets to get the correct result. Powers. Rounding.
Unit 5.2 – Summarising data
Understanding a data set by summarizing it. Totals and counts – SUM(), COUNT() and COUNTA(). Other statistics. Conditional totals and counts. Advanced conditional sums, SUMPRODUCT and array formulas.
Unit 5.3 – PivotTables 1 – Simple Summaries
Uses of PivotTables. Summarising data (presenting database in terms of different subcategories). Understanding data (understanding categories, spotting typos, understanding range of values). Interpreting data (analysing to work out trends, areas of growth / profit / loss etc). Filtering. Sorting and grouping
Unit 5.4 – PivotTables 2 – Manipulating Data
Changing the layout and format of data in a PivotTable. Changing Value Field Settings to show counted rather than ‘summed’ data. Setting up references to a PivotTable.
Unit 5.5 – PivotTables 3 – Interpreting Data
Using PivotTables to understand and interpret data. Includes Slicers and Timelines, PivotCharts and Conditional Formatting of PivotTables.
Unit 5.6 – PowerPivot – Handling Big Data
Why PowerPivot is different to a normal PivotTable: size, data model (inclusion in 2013 standard Excel), DAX, KPIs. PowerPivot data tools. Connecting to data, managing multiple tables and connections, including data from within the Excel spreadsheet
Unit 5.7 – Formula Auditing
Understanding and debugging spreadsheets. Tracing calculations and monitoring the effect of changes. Formula evaluator, Watch window and the INQUIRE Add-In.
Unit 5.8 – Advanced Macros and VBA
Troubleshooting macros. Writing code directly. User defined functions. Program structures. Using Macros to interact with users.
Unit 5.9 – Modelling
Principles. What modelling is; modelling broken down into distinct stages; important principles - simplification, built-in checks; including a presentation-ready sheet
Techniques. Calculating growth with data points over several years; extrapolating that into the future; scenario modelling and optimisation; prioritisation (RANK); build process.
Section 6: Presentation
Unit 6.1 – Cell Formatting
Basic font formatting, alignment – Including horizontal and vertical alignment, wrapping, merging, orientation, Alt-Return; consistent, sensible row heights & column
Styles – to make spreadsheets more and consistent. Formatting tools
Unit 6.2 – Number Formatting
Types of number (E.g., currency, %, decimal, negative numbers) – and Excel formats best employed. Custom number formats and how to maximise impact and clarity.
Unit 6.3 – Conditional Formatting
Changing the format of cells depending on their value. Graphical conditional formats. Writing conditional format formulas.
Unit 6.4 – Graphs and Charts
Creating simple charts and editing them to control and improve formatting. Choosing the right chart Principles and guidelines for communicating well with charts. Sparklines and Maps (when and how to use).
Unit 6.5 – Page & Print Setup
Displaying spreadsheets as pages. Page layout view, page breaks, print area,. Printing from multiple Worksheets (and that default will be to print only from active Worksheet). Headers and footers. Adjusting page setup. Printing very large sheets of data
The Microsoft Excel training offers you an opportunity of working with spreadsheet application, MS Excel. The learners can master the basic and every single feature of MS Excel to apply them in day to day tasks.
The Microsoft Excel course is designed for clearing the Microsoft Excel Expert exam conducted by Microsoft.
The Microsoft Excel course tutorial focus is to build a strong understanding in the basic concepts of MS Excel. Microsoft Excel course content explains the usage of Excel such as accounting, financial analysis, chart and graph presentations, data formatting, validation and much more thus making the participant an Excel expert.
The Microsoft Excel certification exam demonstrates whether the participants can prove their competency in creating, managing, and updating professional spreadsheets for varied purpose and situations.
Uplatz online training guarantees the participants to successfully go through the Microsoft Excel certification provided by Microsoft. Course Completion Certificate will be awarded by Uplatz upon the completion of the Microsoft Excel course training.
Below given are the certification Details of Microsoft Excel Expert:
· Certification Level: Expert
· Exam Name: Microsoft Office Specialist - Microsoft Excel Expert
· Exam Code: MO_201
· Exam Mode: Online
· Time Duration: 120 minutes
· Total number of Questions: 45-55
· Exam Price: $100
The Excel Expert can draw a salary of $87,626 per year depending on the knowledge and hands-on experience. The Excel Expert job roles are in high demand and make a promising career.
The Excel Experts are known for the efficiency in calculations, graphs and tables. The demand of the MS Excel in companies help the participants to find a job opportunity easily. The leading companies hire Excel Expert considering the skill of using MS Excel for development. The Microsoft Excel certification adds value and benefit in professional growth.
The Microsoft Excel certification is ideal for participants who are from analysis background and want to become as an Excel Expert.
The following are the job titles:
· MS Excel Expert
· Office Specialist
· Excel Specialist
· Excel Analyst
· Data Analyst/Consultant
· Business Analyst/Consultant
· Finance and Marketing Analyst
· Data Entry & Management Specialist
This MS Excel course program intention is to help you understand the business challenges and model the business scenarios as well as to perform deep analysis using MS Excel.