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.