Lets excel in Excel - Funda # 3

Last updated: 14 January 2017

Save Share

Hey Friends!

Here is another tip related to financial analysis, which should be helpful to you guys.

Sorting from left to right

We all must have used sorting in lot of our exercises. We generally sort the data either alphabetically (A-Z or Z-A) or on the basis of values (increasing or decreasing) from “top to bottom”, but excel offers one more functionality which most of us are unaware of us, which is sorting data “left to right”. I will explain the trick here with its practical application in financial analysis.

How to do it?

As name suggests, here we will be sorting data from “left to right” in place of “top to bottom”. Let us try to appreciate its hands-on usage in our work through the exercise below.

Consider the following set of data:

Now if we observe the data, we will notice the following 2 things:

Data is available in reverse order (i.e. “December to September” in place of “September to December”)
% are given in the data which is not relevant for us

This is the kind of output that we want:

One way of arranging/ cleaning the data for analysis, is to follow the following steps:

Delete the unnecessary columns from data
Then, manually re-placing the data in required order (using cut and paste function)

However, there is a systematic way of approaching towards the output shared above. Let’s discuss that:

Step 1: We have to number the columns as shown in the image below. The numbering shall be in order that we want our output to be. Please note the following in the below image:

Months- September to December have been assigned numbers 1 to 4 (on the basis of required output)
All the irrelevant columns have been assigned last number which is 5 in our case so that after sorting, all these columns are shifted in the end

Step 2: Once the columns are numbered, follow the following steps

Select the cells containing numbers
Then, press “Ctrl + Enter” for selecting entire columns

By doing this, the entire columns shall be selected (Refer image below):

Step 3: In “Home” ribbon, there is an option for “Sort and filter”. Click there and then select “Custom sort”. A sorting window shall pop up with various options.

From there select “Options”. Another window shall open with 2 options viz “left to right” and “top to bottom”. By default it is top to bottom, but we have to select “left to right”. Refer image below: