Advanced Excel Cheat Sheet – Series 2

So one fine day you are handed an Excel spreadsheet by your Manager and the very look of it scares you because the data is recorded in an un-orderly format. The moment the term ‘ORDER’ comes to your mind, you shouldn’t think of any other tool than sorting!

 SORTING

Data sorting is an integral part of any analysis that one wants to make. Sorting helps arrange data in an orderly fashion that helps you to visualize data quickly and understand it better in order to make decisions effectively.

Excel gives you the advantage to sort by cell value, cell color, font color and cell icons.

A simple sort allows you to order the rows based on one key column. However there may be cases when you would like to sort your data based on multiple columns and Excel again comes to your rescue by providing you with the option of ‘Custom Sort’.

Using Custom Sort you can add levels in the order that you want your data to be sorted. The ‘Options’ box provides you to create your own customized list on which basis you want to sort.

An important feature in Excel that comes handy when you are dealing with sorting is GROUPING.

 GROUPING

 An excellent feature of Excel is Grouping that gives you the control over how information you want to share is displayed. To create summaries on sorted data Grouping through SUBTOTAL command plays an important role.

It is a pre requisite to sort the data prior to grouping.

 

Post sorting go to Data -> Outline -> Subtotal to get a view like above.

This method allows you to create outlines up to eight levels, one for each group. Each inner level gives a detailed summary of the preceding outer level.

Level 1 contains the total sales for all detailed rows.

Level 2 contains total sales region wise.

Level 3 contains total sales in a detailed manner.

You can expand or collapse outline using the ‘+’ or ‘‘ signs.

Seems you just learnt the Excel sort functionality!

That looks pretty now.

So go ahead and impress your manager with your dapper looking worksheet!

Learn Advance Excel from Experts! Call us at 9748441111 to register for free demo!

Leave a Reply

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