Removing duplicate rows

Excel provides a very simple tool to remove duplicate rows from a large set of data. Here how it goes:

STEP 1: Select data in Excel sheet and Select 'Remove Duplicates' option from Data Tools section in Data Tab.



STEP 2: Duplicate rows will be removed and following will be the results.

Consolidating data from two tables

Excel provides a very simple feature using which we can merge/consolidate data given in two tables which can be logically merged. A typical example of this could be a scenario in which a user gets two data files in TXT format and wants to merge the data in a file. In the last post we saw that we can import data from a TXT file into Excel. If we import two such files in excel we can merge very easily.

STEP 1: Create two tables manually or by importing data from TXT files as shown in the below figure.

STEP 2: In the Data tab click on Consolidate option in Data Tools section.


STEP 3: Select the data sections and Navigate through the pop-up windows. A consolidated data view of selected sections will be shown as shown below.

Importing data from a Text file

Many a times we get data in the form of a simple .TXT file as shown in the below figure.



The data from a text file which is in proper format can be imported in Excel file very easily.

STEP 1: Save the Text file on your local machine.

STEP 2: Navigate to Data tab in the ribbon and click on 'From Text' option in 'Get External data' section. Provide the path of the file.

STEP 3: Navigate through the pop-up window and select right delimiters. Data will be shown in Excel as below.

Converting delimited values to columns and rows

Some times we want to convert delimited values to columns or rows. Particularly when the number of items in the list is huge it becomes a tedious job if done manually. Here is how Excel can help in this.

Sample:

STEP 1: Type a sample delimited string in Excel such as: Paris,London,Toronto

STEP 2: With the cell highlighted navigate to Data tab and in Data Tool section in Ribbon click on 'Text to Columns'. A pop-up window will be shown with some information of the string. Navigate through using Next button and make sure that right Delimiter is selected while doing this. After FINISH values will be shown in separate columns.








STEP 3: To convert the column values to row values, copy the values and while pasting select transpose option in 'Paste Options'. Value will be converted to Rows as shown below.

Useful Excel formulae for general purpose

Today I spent some time trying various Excel formulae and have I consolidated the list that I consider useful for general purpose. I hope this can be useful to others as well.

AND - Logical AND Operator
AVERAGE - Averages the number in a range
AVERAGEIF - Average based on some condition
AVERAGEIFS - Average based on multiple conditions
CONCATENATE - Joins strings
COUNTA - Count number of cells that are not empty
COUNTBLANK - Count blank cells
COUNT - Counts the number of cells that contain numbers
COUNTIF - Count based on a certain condition
COUNTIFS - Count based on multiple conditions
DATE - Returns date
DELTA - To check if the two values are equal
EXACT - To check if two strings are equal
FALSE - Returns logical value 'False'
HYPERLINK - Creates a link
IF - Checks a condition
IFERROR - Returns a particular values if there is some error
INT - Rounds a number to the nearest integer
ISBLANK - Checks if the reference is blank
LARGE - Returns the K-th largest value
MAX - Returns the largest number from a Range
MIN - Returns the minimum value from a range
MONTH - Returns the month
N - Converts a non-number value to number
NOT - Logical NOT
NOW - Returns the current date and time
OR - Logical OR
PERCENTILE - Returns K-th percentile
PERMUT - Returns the number of permutation
POWER - Returns the value of a number raised to a power
PRODUCT - Multiplies numbers
QUOTIENT - Returns the integer portion of a division
RAND - Returns a random number
RANK - Returns rank of a number in a range
REPLACE - Replaces a part of string
ROUND - Rounds a number to a given number of digits
ROW - Returns the row number of a reference
ROWS - Returns the number of rows of an array reference
SIGN - Returns the sign of a number
SMALL - Returns k-th small value of a range
SQRT - Returns the square root
SUM - Adds numbers
SUM - Conditional sum
SUMIFS - Sum based on multiple conditions
TEXT - Converts a value to text
TIME - Returns time for the given values
TODAY - Returns current date
TRIM - Removes spaces from start and back of a string
TRUE - Returns logical true
TRUNC - Removes decimal points from a number
TYPE - Returns the type of a value
UPPER - Converts a string to upper case
VALUE - Converts a text string that represents a number to number
VLOOKUP - Looks for a value in the left most column and then returns
corresponding value from other column
WEEKDAY - Returns weekday

Creating drop down list using Master data in another sheet

In the previous post we saw how to create a drop down list manually which is rather static in nature and you need to work with Data Validation tool every time you want to make changes in the list. To avoid this we can create a drop down list from Master Data provided in another sheet of the file and changes to this drop down will be as simple as adding one more value in the master list.

Sample Problem: I want to assign some cities to my sales staff and want to chose city from a drop down list. As I will be making frequent additions to my drop down list I want it to be simple to update.

Solution:
STEP 1: Create a sheet named Cities and provide some city names as shown in the below figure. On the Sales Staff sheet select the Cell for which you want to create a drop down list of the Cities. In the Data tab in the Ribbon click on 'Data Validation' button. In the Pop-up window select 'List' option as shown in the below figure.





STEP 2: Click on the Source text box and then navigate back to the Cities sheet. Select the entire column containing city names by clicking on the column header. Click OK on the Pop-up.



STEP 3: Check the selected cell in STEP 1 has a drop down for the city names. Drag this and apply the drop down to other cells in the column.



STEP 4: Go to Cities sheet again and add one more city: Singapore

STEP 5: Check the drop down now in Sales Staff sheet. It shows Singapore in the drop down!

Manually creating a drop down list

In the previous post we saw how we can get a drop down list to chose our data from. Though this serves the basic purpose, it does not provide any kind of validation and the user can enter whatever she likes. The solution to this problem is creating drop down list which forces the user to select a value from the available options. In this post I am describing how to manually create a Static drop down list.

Sample Problem: user wants to assign cities to the sales people and wants to make sure that the city is selected from a given list only.

Solution:
STEP 1: Select the cell for which you want to create the drop down list and then move to Data tab in the Ribbon. And then click on Data Validation in Data Tool section. A window as shown in the below figure will come up. Select 'List' option in the drop down.



STEP 2: Provide a comma separated list of the cities which you want to use in your data sheet and click on OK.
STEP 3: Check that the selected cell in STEP 1 has an option to select the city name from a drop down list as shown in the below figure.



STEP 4: You can apply this drop down to all other cell in the column by dragging.
STEP 5: Try typing a city name which is not present in the drop down list and then hit ENTER. An error message as shown in the below figure will be shown.

Selecting Cell Data from a drop down list without creating one

While working with large data sheet sometimes we need to update some column providing status of completion or some other value which has to be chosen from a limited set of values. Though Excel provides the auto fill functionality as we type in the cell however sometimes when we have values with slight differences and starting with many same initial characters it becomes annoying to types the whole text. Also sometimes we make typing errors as well in this process in terms of spellings or case of the letters.

I just discovered that Excel provides you drop down functionality to update the cell content with even creating a drop down list. This is how it works:

Sample Problem: I want to assign a city to every member in my sales staff. As the list go long I don't want to type same city names again and again, instead I would like to have a drop down from which I can pick a city name.


Solution:
STEP 1: Create a table with some sales people names in one column and start providing city names in next column.
STEP 2: After providing some names, now you want a drop down option to chose from. In the next cell where you need to provide a city name, just right click on the cell. Select the option 'Pick from Drop Down List'



STEP 3: A drop down list containing already populated city names will be shown. Select the desired option from the list.

Naming Cells and Cell Blocks

Ever Wondered B23 in a complex Excel file belongs to what or what does A11+C2 mean? I found this very confusing at times and then had to do some reverse engineering of the formulae in the sheet to understand the real meaning. Recently I came across a very simple feature in Excel by which you can name any cell or a block of cells and then use these user friendly names in you equations.



Sample Problem: For given Sales and Expenses I want to calculate profit and want to look my Excel formula more readable.

Solution:
STEP 1: In Excel sheet fill data for Sales and Expenses as shown in the figure and create a third row for calculating profit.
STEP 2: Highlight the box containing value of Sales(B1) and then type 'Sales' in the box name section as highlighted in red color. This is it! Now instead of B1 we can use 'Sales' in our calculation. Do the same for Expenses in B2 and for Profit in B3.
STEP 3: Write the following formula in 'Profit' cell as shown in the below figure: =Sales-Expenses



You should be able to see the correct value of Profit in the respective cell and the formula looks much better than =B1-B2

Similarly we can name a block of cells and then use in our calculations.

Sample Problem: I want to calculate the total of my marks in all the subject and the formula should look readable.
Solution:
STEP 1: Fill Some relevant data in Excel as shown in below figure and then select the cells for which you want to get the total.
STEP 2: With the above mentioned cells selected type a relevant name in the Cell Name box. e.g. 'Marks'



STEP 3: In the cell for Total type the following formula as shown in the figure: =SUM(Marks)

The Beginning!

Long back when I was in college I used to wonder whenever some friend of mine used to magic using Excel formulas and other features. Similar was the case for all other office software like Word, PowerPoint and other. I for some reason found all this very geeky and uninteresting. However as time passed and I moved into corporate life I realized the real importance of knowing these things and started improving my skills. Thank God.. I can now claim of having decent skills on these software.

For past sometime I have zeroed-in on Microsoft Excel and realized this is the tool that managers use more often than any other tool. Especially my current project and Project Manager taught me that controlling data and ability to do magic with it in Excel is of utmost importance. Also, considering that I will start my MBA in some months and my inclination towards finance, I decided to go for a deep-dive into advanced Microsoft Excel features and reporting.

I thought it will be a nice idea to record my learning in the form of a blog, though I know that there are thousands already. So here is the beginning...