Excel Deep Dive: Formulas, Functions and more!

Increase your productivity with advance excel tools

Microsoft Excel contains almost 500 built-in worksheet functions. Strip away the ones nobody uses. Discount the ones that have a specialized use-case and you’re left with a small set of functions that all Excel users should know, regardless of their job function, industry, or business sector.

Microsoft Excel is not only capable of doing basic data computations, but you can also perform data analysis using it. It is widely used for many purposes including the likes of financial modeling and business planning. It can become a good stepping stone for people who are new to the world of data analysis.

Microsoft Excel has a variety of tools like sorting and filtering to manage tons of data, but if you need to analyze all that data and do it quickly, there's no better feature than a PivotTable. You can quickly create a compact summary report (based on huge data) without needing to write complex formulas or rely on lengthy techniques.

The PivotTable feature is perhaps Excel's best analytical tool and in addition to its speed, you get amazing flexibility and dynamism that let you quickly change the data interrelationships you're viewing. Most PivotTable users discover that the feature is relatively easy to learn, but not so easy if you are simply seeing the instructions on the printed page; this is a visually-oriented feature based on displaying fields in different locations. You'll be amazed to see how in very little time, you can create a complete summary report with tons of data and you won't even need to write complex formulas and rely on obscure techniques.

Session 1 - Excel – 10 Must-Know Functions to Boost Your Productivity

Objectives

To become familiar with some of the most useful and important of Excel's 500+ worksheet functions in order that, no matter what your job role, you will be more productive in your use of Excel and able to achieve more in less time

Agenda (Functions covered)

• IF: Automate data entry…if this condition is true, enter this value into a cell else enter that value into a cell
• COUNTIF: Count how many times a particular value (date, number, text) appears within a range (e.g. how many times was Customer X a late payer?)
• SUMIF: Add up a set of numbers that match a criteria (e.g. what was the total number of hours worked on Project 123)
• IFERROR: Want to get rid of those ugly #DIV/0 and #N/A errors? IFERROR is your friend

Session 2 - Advanced Microsoft Excel Pivot Tables: Data Analytics Made Simple

Objectives

• Pre-requisites for source data - preparing data so that it can be analyzed by PivotTables
• Creating a PivotTable with a minimum number of steps, including the new Recommended PivotTables option now available in Excel 2013
• Manipulating the appearance of a PivotTable via dragging and command techniques

Agenda

• How to compare two or more fields in a variety of layout styles
• How to sort and filter results
• How to perform ad-hoc grouping of information