Category: Formulas

Reverse FirstName with LastName & Vise Versa

Formulas, Excel Tips

Hi there,

 

In this post, we will discuss about how to change firstname with lastname and lastname with firstname. This is very easy to do. Here we are not even using LEFT or RIGHT functions to do so.

Here we go:

In our latest post, Extract Only Names from Outlook email, used same technique to extract Names. Similarly we will use same technique to extract first name and last name to the different columns and concatenate firstname first and lastname last to replace. Might be bit confusing but very simple and easy concept. Look at the images below to illustrate the example. 

 

Extract Last Name by removing FirstName

Full story »

Count Colored Cells using Simple Function (No VBA)!

Formulas, Excel Tips

Hi there,

 

Today's tip will be a second version of what we have seen in the previous post "Count Colored Cells (Simple VBA)". This tip will definitely make you enjoy this weekend with a great drink in the evening for sure.! :)


Are you eager to know what it is.


Today, I had a requirement to count the number of cells highlighted with some colors. You all know, if we apply conditional formatting for certain requirement it will show up cells with some colors. Same way, I want to count the number of colored cells for a very big data.


Go here to find some Conditional Formatting Tips


Do you really think this will be an easy task? Definitely not, right?


It is actually not a big task because my tip here will help you to count the colored rows before the time taken to apply that formula. Hey! this is true. That's why I said you can enjoy this weekend with a great drink with your friends or family.

Full story »

Compare Two Lists in Excel (3 Easy and Simple ways)

Formulas, CF, Excel Tips

Hi there,

  Comparision between the two lists using 3 Excel tricks


Have you ever worked on comparing two different data sets in Excel? I know, this is a funny question for the analysts because they do this atleast once in a day. However, just to know how many of you will respond to this question. Any how, today's post is to tell you or explain you the number of ways we can compare the two data's to fix the missings or adjust the numbers or to cross check as part of quality check analyst or so on.

 

Once you finish this posts, I bet you will atleast say that I have learnt one new trick. Now, lets see the easy and simple 3 tricks for comparing the two data sets. 


1. CONDITIONAL FORMATTING:

 

Using conditional formatting, we can compare the data just by selecting. The process goes like this:

  i. Select the data in List A and List B (Use CTRL while selecting the second list)

 ii. Go to Conditional Formatting > Highlight Cells Rules > Duplicate Values > OK

That's it. Magic 1 Works!!!


Full story »

Collect Time in Hourly Buckets

Formulas, How-to's, Pivot Tutorial

Hi There,

 

Have you ever come across how to group the time in terms of hourly basis? You understood?, Say, if you are working for an application support and few of the issues are escalated by your clients due to employee delay. To find the number of escallations raised, in total 10, in hourly basis which falls between first 0-2 hours, 2-4 hours, 4-8 hours, 8-24 hours or >24 hours. Here is a challenge we need to crack as a Reporting Analysts.

 

World's Costliest Watch Collection in Excel website
 

To fill the number of escallations in these buckets is a very easy task once you complete this post. If you feel can achieve this, then don't wait, REGISTER and post your comment using below comment section. Registration is absolutely free for commenters. If you are not aware don't worry, just follow me till the end of the post.

Firstly, convert the time into hourly basis use this formula 

Full story »

HOW TO EMBED TABLE in VLOOKUP

Formulas, Trainings

Hi there,

 

 

I know you are well-versed with VLOOKUP. I have a question for you. Do you know how to hide the table-list which uses for vlookup? Hide, I mean not to keep it in the worksheet. We can even say delete. If you are aware of it, please use the comment section below to post your views on this before you read the complete post. If you are not aware how to do this, step in with me.

You all updated your comments, right? then proceed with me.

VLOOKUP uses the table-list to compare the values before displaying the results. So, the table-list can be kept next to the comparision list or we can even embed it to the vlookup function. To know how to embed the table-list is the reason why we are here now. So, we will proceed how to do this.

Download working file.xls now

Full story »

Working with Time on Excel

Formulas, Excel Tips

Hi there,

 

 

Folks, I am back to work. (it sounds like coming back to college after 1st semister holidays :) ). Here, I would like to say you one thing. I am not able to update the posts since these days due to more travelling with family and there is no computer setup with me. As I am using only Desktop PC till date, I was finding very difficulty to update the blog very oftenly. Now, it's the time for joy because I bought new LAPTOP (DELL).

 

So, where ever I GO! my lappy will follows now. 

 

I have many posts kept ready to publish however I just need to add some cosmetics and add clothing to it. I will do those things and publish. I am saying this because the posts might be here and there in terms of published dates because those are already prepared and kept aside.


 

TOPIC FOR TODAY:

Full story »

Contact / Help. ©2019 by Abhilash VK. Design & icons by N.Design Studio. Skin by Tender Feelings / Evofactory.