Excel: Beginner to Advanced Functions Training from ATI

Data cleaning: Custom list

Custom Lists allows you drag vertically or horizontally a specific list, to populate the cells along the way. In this way, a user is not required to type and enter the details in every cell. For example the list of Months and Weekdays are already listed by Excel thereby allowing a user to simply drag the list till required. Any other list can be created and fed into the system.

Data cleaning: Number formatting

This video explains about different types of formats that can be applied to data that is number depended.For example - Dates, Time, currencies, etc

Data cleaning: Conditional formatting

Excel allows you to highlight certain values/ data on your sheet that meet certain criteria that you set. Conditional formatting which allows you create rules to set your criteria is used to do so.

Please download attached material

Data cleaning: Paste special

- Paste special is used when you want to take contents of a cell that is depended on a formula and paste it elsewhere without copying the formula along with it.

- Similarly, when you want to copy a cell and paste only the comments elsewhere.

- Also, to copy a number/value and paste it with the other number while multiplying all of them.

Several other similar customized type of pasting can be done under paste special.

Please download the attached material.

Data cleaning: Go to Special

If we have to find a text/number on an excel workbook , we can easily do a CTRL + F and do a simple find. but have you thought of the limitations of this? Here are two major limitations listed for a simple Find:

It can find and select one cell only at a time

It can search for only value typed in a cell

So, if you want to go beyond a simple find , maybe to search a cell based on its properties (Functions, Validations, Errors, etc) you need to go beyond Ctrl + F

Thus, CTRL + G

Data preparation: Shortcuts - tricks of the trade

This video explains about the pre defined keyboard shortcuts that appear on the screen automatically when you click on the Alt key once for a sec.

These keyboard shortcuts are to make you follow the keyboard shortcuts even without memorizing them.

Data preparation: Sort and Filter

The Sort function in Excel allows you to sort your data in alphabetical or numerical (ascending/ descending) order. You can also sort your data based on your own criteria using a custom sort.

The Filter function in Excel allows you to filter out data and view it from different perspectives.

Thus the sort and filter function allows you to prepare your data and in a manner best suited to you and best suited to analyse it.

Please download the attached material

Data preparation: Data validation

When working with small amount of data , a wrong entry can be easily corrected. But when working with huge datasets, a wrong entry can have dire results and add to that the effort required to find the wrong entry! Using data validation, one can ensure that only a desired type/ format of data is entered, thereby ensuring uniformity of data

Learn how to validate data at the time of entering it itself so that only the relevant data can be entered.

Data preparation: Subtotal

Use the subtotal function to generate reports based on various segments (as specified by the user).

This video explains how to add one line below each segment in your data with the total values.

Please download the attached material

Data preparation: Consolidate

As you will see in then video, combine the values of multiple tables on to one table.

Please download the attached material.

Writing smart Functions in Excel: Tricks to Explore Functions

This video is to explain the symbols important to work with functions, and help you self suffice yourself to get any new function by yourself.

This shall be treated as the foundation of all the functions, based on which every function in excel works, and will help us understand any new functions by ourselves -easily.

Writing smart Functions in Excel: Arguments of a Functions

This video is a continuation of the last video and explains some trial usage of functions. some sample functions will also be displayed in this video.

00:01 how to identify optional Arguments and usage.

01:38 Absolute/ Fixed cell references (using $ Symbols)

03:20 to make dynamic(smarter) functions

Writing smart Functions in Excel: Difference B/W Summary and Template

This Video explains how do we create a summary of the data and then how do we convert it into a self learning template.

a self learning template is a kind of report which is generated one and automatically adopts itself on any changes made on the data

00:35 Creating Summary

01:40 Formula of summary to template conversion

Please download the Data for working with this video. you can continue with the same data for the next videos, untill we complete counting.

Writing smart Functions in Excel: Count Functions

We start touching upon functions from this video. carrying an idea of learning various aspects of a function and be able to crack functions concept as a whole.

we are hereby starting with some counting functions used in summarizing data sets.

00:20 Logic comes from the historical ways of doing things (strange but true)

02:40 Thinking about the name of the functions

03:15 learning to Explore functions

04:25 CountA function ( difference between CountA and Count Function)

05:55 Popularly made mistakes while selecting a range in Excel

07:14 Correcting  the mistake by selecting a bigger range

Please download the attached material

Writing smart Functions in Excel: Countif Function

This video explains the practical usage of the functions in a correct manner and filling arguments step by step.

02:10 COUNTIF Function to conditionally calculate the summary of the data based on values in the columns.

04:00 how to name ranges and whats the usage of the same in functions

04:50 naming multiple columns at a time using their headings as names

06:45 using COUNTIFS function to work with multiple conditions at a time.

08:25 Creating charts to present the template

Writing smart Functions in Excel: Text Functions

This video talks about some random data cleaning text functions. you don't need any specific data sets to be downloaded, just open up a blank sheet and start working on it with the video.

The presentation theory Material is under revamp and shall update it asap.

Segmenting Data in Excel: IF Functions

Before we can think of a summary, we need to divide the data into meaningful segments by using some logical functions.

basically to create categorical variables in the data.

Please download the data to start with the video and replicate as when i explain.

Segmenting Data in Excel: Nested IF, And and Or Functions

This video is a continuation of the logical functions itself where the advanced logical function is used.

Nested Ifs

Please download the attached material

Why create reports the tedious way: Presentation on Pivots

Why create reports the tedious way: Introduction to Pivots

See how a Pivot table makes our life simpler by generating awesome reports in no time.

Download the data and lets start.

Please download the attached material

Why create reports the tedious way: Generating Reports

This video is a continuation of the previous video and talks further about generating reports. some more problems have been picked up in the video on report generation.

Please download the attached material

Why create reports the tedious way: Slicing the Data

In this video we specifically going to focus on data segmentation and slicing of the reports based on different criteria's.

Please download the attached material

Why create reports the tedious way: Pivots on Dates

Lecture video explains the use of Group option in Pivot tables to make the report generation based on the time factor user friendly.

Please download the attached material

A complete package to know about What and Why of Analysis.

Learn Analytics in a simple way ; please visit

https://www.udemy.com/analyticstraining/?sl=E0IbeVlQQRMALBoeCRAcKBM%3D

Showcasing your data with Excel: Presentation on Dashboards

Showcasing your data with Excel: Ways to present your data

Starting from this lecture , we talk about presenting the information that we have.

the current video talks about some trial and error approaches that we follow.

Please download the attached material

Showcasing your data with Excel: Creating Template

A self learning template is a kind of information that keeps getting updated by itself and also accommodates the output based on changes in the requirement.

Please download the attached material

Showcasing your data with Excel:Making templates Smarter

Using index and Match functions , you will notice how the output becomes smarter and smarter.

the standard step to making anything smarter is to:

identify constants and replacing them by variables

Showcasing your data with Excel: Presentation on Dashboards: Converting Templates to Dash board

In this video we are discussing how to create controllable charts.

Showcasing your data with Excel: Using controls

This video cements the concepts we have learnt and based on it creates a dynamic and well managed information dashboard which can be controlled from a single point.

Case Study: Revenue Report

This case study gives you an overall experience of data handling starting from Data cleaning to Data presentation, and the questions have been sequenced accordingly.

A smart guide for smart people ; learn how to avoid repetitive task and improve your work productivity.

https://www.udemy.com/automation/