Advanced Excel Course

What is ANALYTICS?
The purpose of analytics is to take existing data collected from either a single source or multiple sources and use it to arrive at the optimal decision. Essentially, analytics can be best defined as a science of analysis.
Commonly, one of the most frequent uses of analytics is in the field of business. Managers or researchers for an analytics company can use various data mining techniques combined with historical patterns to make predictions about the performance of a particular market or business.

10 most popular analytic tools in business

• Business analytics is a fast growing field and there are many tools available in the market to serve the needs of organizations. The range of analytical software goes from relatively simple statistical tools in spreadsheets (ex-MS Excel) to statistical software packages (ex-SAS, SPSS).

• To sophisticated Business Intelligence suites (ex-SAS, Oracle, SAP, IBM among the big players).

Getting Started With Excel.

• A overview of the screen, navigation and basic spreadsheet concepts

• Understanding workbooks, worksheets, rows, columns, cells

• Understanding workbooks, worksheets, rows, columns, cells

• Explore the User Interface and the Ribbon

• Navigate and Select in Excel

• Other Help

Modifying a worksheet.

• Move and Copy Data Between Cells

• Fill Cells with Series of Data

• Edit Cell Data

• Insert and Delete Cells, Columns, and Rows

• Go To Cell Data

• Inserting, Deleting, Moving and Copying Sheets

• Spell Check a Worksheet

Transposing data.

• Importing text and delimiting by space, comma or tab

• What does naming a cell range mean?

• Rules for naming cells and ranges

• Naming cell range(s) in a worksheet

• Named ranges within formulas

• Navigating through workbooks using named ranges/li>

• Creating named ranges automatically based on cell values

• Deleting named cells/ranges

• Creating subtotals

• Removing subtotals

Creating and Editing Formula.

• Concept of Formula

• Creating Formula, Editing Formula

• Bodmas : Mathematical Order

• AutoSum

• Copying Formula

Creating Charts.

• Chart basics

• Modifying charts

• Change the Chart Type

Data Management.

• Organizing Data

• Sorting and Filtering

• Data subtotaling.

Functions.

Advanced Functions: Logical function

Statistical / Mathematical Functions

SUM, SUMIF, SUMIFS, COUNT, COUNTA, COUNTIF, AVERAGE, AVERAGEIF, MIN, MAX.

Date/Time Functions .

TODAY , NOW ,DATE, WORKDAYS, WEEKNUMBER, EDAY, DATEDIF/ NETWORKDAYS/ EOMONTH Function.

Text Functions.

MID/ SEARCH/ LEFT/ RIGHT/TRIM/ CLEAN/ UPPER/WER/PROPER.

Data Management.

• Pivot tables and Report

• Goal seek

• Solver function.

• Scenario

• Data Analysis

• Formatting and conditional Formatting.

Data Analysis.

• Descriptive Statistics

• TTEST

• ZTEST.

• Regression

• Correlation

• ANOVA.

Hyperlinks.

• Inserting a Hyperlink/li>

• Editing a Hyperlink

• Removing a Hyperlink.

Macros.

• Creating Macros.

• Editing Macros.