Advanced Excel Cheat Sheet – Part 1

 

MS EXCEL , the most wanted skill set in the industry irrespective of your academic background. With an easy User Interface this tool is popular across every segment of business. It allows a wide array of activities ranging from bringing data together to extracting information from it using a variety of functions at your disposal to visualising the extracted information and deriving the best possible outlook.

So is it that you know Excel well?

With this Excel Series to come we make sure that you not only gain an insight of its properties but also gain an in depth knowledge about them.

DATABASE FUNCTION

You are well aware of functions like SUMIFS, COUNTIFS, AVERAGEIFS etc that help compute when your problem involves numerous AND conditions. Have you ever thought what if we have combinations of both AND and OR conditions.

Well DATABASE FUNCTIONS come to your rescue at exactly this point. It has has an added advantage of letting you compute and get specific information when you face multiple AND and OR conditions.

TYPES

DSUM Allows you to add data based on set criteria
DAVERAGE Allows you to average data based on set criteria
DMAX Allows you to find max based on set criteria
DMIN Allows you to find min based on set criteria
DCOUNT Allows you to count numeric values based on set criteria
DCOUNTA Allows you to count non-blank values based on set criteria
DGET Used to return a single field of data from a column of a database that matches conditions you specify
DSTDEV Allows you to find std. deviation based on set criteria
DVAR Allows you to find variance based on set criteria

 

Each Database function has the same 3 arguments:

Find an example below of how to work with these functions:

 

 

 

Advanced Filtering

Well keeping in tune with conditions based setups, an important feature that Excel provides is Filtering whereby we can extract rows of information based on some criteria. The only drawback is that this allows filtering in a step wise such that at each step of filtering lesser data is available. Advanced Filtering is a step ahead by allowing its users to apply multiple filtering criteria at once.

All you need to mention are these 3 options:

 

Let’s have a look at an example:

Advanced filtering not only provides you with the option to copy paste your output to some other location and also has the option whereby you can extract Unique Records only without requiring removing duplicates separately!

Ivy Pro School is the best Data Science Training institute since 2007 with campuses in Bangalore, New Delhi, Pune and Kolkata. Ivy’s flagship program – Data Science and Machine Learning Certification has created a benchmark in the Analytics Training Industry. Call/Whatsapp us at 9748441111 for more details.

 

Leave a Reply

Your email address will not be published. Required fields are marked *