If you are a
regular user of Excel and deal with lot of text data, then mastering text
functions in Excel is crucial. Excel has in-built text functions to help
cleansing data for you. Below are some the text functions in excel helpful for quick
data cleansing
1.CLEAN: CLEAN function...
Showing posts with label excel. Show all posts
Showing posts with label excel. Show all posts
How to convert mailing list in Excel column into a string using Collections?
Let us assume you have say 20 email Ids in an Excel sheet to which a common mail communication is to be sent. One way of combining all the emails into a string is using CONCATENATE function. However it is difficult if the no. of email IDs to be combined are more. Another way to accomplish this is by using Collections in VBA. Collections are nothing but group of related objects.
First store all...
How to use curly braces in non-array formula in Excel
Curly Braces are generally used in array formulas in Excel. Array formulas are entered by pressing CONTROL+SHIFT+ENTER and Excel automatically inserts curly-braces in formula. Array formulas are useful when one has to get results from multiple set of values into a single cell or range of values.
Array Formula:
For example, Consider Range A1 through A3 has text "No", "Yes" and "None". If we have...
How to remove comments only from filtered cells in Excel
Consider a column which has number one to five. Cell with odd number has a comment "ODD" and even number as "EVEN".
If you have to delete comments only from even number then,
- Filter the even numbers in column (i.e. 2 and 4)
- Then, hit the keystroke (Alt + :) this will highlight...
The Difference between Cumulative Distribution Function (CDF) and Probability Density Function (PDF)
Cumulative Distribution Function (CDF) vs Probability Distribution Function (PDF)
The Cumulative Distribution Function (CDF) of a random variable 'X' is the probability that the variable value is less than or equal to 'X'. It is the cumulative of all possible values between two defined ranges.On the other hand, Probability Distribution Function (PDF) is the probability of random variable...
How to repeat the Header row in each page using VBA?
Consider an Excel document which has employee details with 5000 employees. The 1st row of the sheet has details, such as Name, Age, Designation, Date of Birth and Date of Joining of employees.
Now, If we want to take print of these employee list, and if we want the header row to appear in each page of the print out then, select the sheet which has the data and use the below VBA code to...
Excel Shortcuts to convert Number into different formats
Excel Shortcuts:
Keyboard shortcuts in Excel allow you to reduce the time spent doing routine task using mouse and thereby boosting productivity.
Below is a list of Excel shortcut keys to convert number into different Time and Date formats.
1) To Convert Number to Date format: Select the range of cells that contain numbers and press CTRL+SHIFT+3
2) To...
CAGR calculation in Excel
CAGR:
CAGR - Compounded Annual Growth Rate is a measure of growth over a period of time. It is usually calculated for number of years. Businesses use CAGR to measure the growth in their revenue over a period of time. In finance CAGR is used as a measure that indicates appreciation in the value of...
How to convert time format in to Minutes in Excel
How to convert time format in to Minutes in Excel:
Consider an example where Start Time, End Time and Total Time Taken for a set of activities are stored in a standard Excel Time format (i.e. hh:mm:ss). Here TOTAL TIME = END DATE - START DATE. In column F, let us calculate total time taken in Minutes.
...
How to use SUMPRODUCT to calculate mean of Discreet Distribution
Discreet Distribution:
A Discreet Distribution is a distribution constructed from a random variable produced by non-negative whole numbers. MEAN or AVERAGE is nothing but Expected Value of a Discreet Distribution.
The MEAN of any discreet distribution is calculated by taking the sum of product of...
How to get only Integer portion of a division using VBA operator
Excel has a built in function QUOTIENT() to return integer portion of a division. The similar result can be obtained using ROUND() function. However, VBA has operator to return only integer portion of a division. The symbol for getting division with integer portion as result is "\".
Below is an an...
How to make Excel UserForm Immovable using VBA
By default excel userforms are movable. This will let the user to view the Excel sheet by dragging the user form aside. In case if the the creator of the user form does not want the user to view Sheets, then UserForm position can be fixed using a VBA code as given below.
...
How to use wildcard characters in Excel formulas
Wildcard Characters:
Wildcard characters are useful when we have partial search criteria to extract or manipulate data. In Excel, we use "?" and "*" are wildcard characters. "?" represents single character, and "*" represents series of characters.
Wildcard characters are used with Excel functions,...
How to convert number to words in Excel
In this example let us learn to build excel template to convert a three digit number to words. Consider a three digit number say 256 in cell B1. We need to get "Two Hundred and Fifty Six" in cell C1.
First create two separate table as shown below in sheet 2 of the workbook. Values have to be entered...
How to create barcode in Excel
This tutorial is aimed at generating barcode equivalent of a alphanumeric number into an Excel cell. There are different types of Barcodes, such as
EAN 13,
EAN 8,
UPC A,
UPC E,
Code 128,
ITF 14 and
Code 39.
The first step is to decide on the right Barcode type to create (or print) depending...
How to use Goal Seek in Excel
Goal Seek is one of the most important tool in Excel which helps in calculating the value input when the output is known. In other words, it calculates the value of input for the desired value of output.
Consider an Example, where a product of $ 50000 is bought on installment basis. The down payment...
How to save an Excel Workbook as HTML document?!!
Saving an Excel workbook as HTML document is necessary whenever it is required to publish reports generated in Excel on Web portals.
The following are the steps to save an Excel sheet as HTML document.
1) Open the Excel workbook which you want to save as HTML.
2) Select File menu and click...
How to generate dummy data in Excel
Whenever you want to test a formula or build a data model involving complex business logic, It becomes necessary to create set of sample data by yourself to work with. RANDBETWEEN function is an useful tool to generate random numbers.
syntax:
RANDBETWEEN(lower value, upper value)
Consider a scenario...
How to use skip blanks feature in paste special option?!?
There exists a lot of confusion among excel users on usage of "skip blanks" feature in paste special. They expect the skip blanks to filter and paste the non blank cells but, skip blanks retains the original value of the destination cell if the corresponding cell in the source cell is blank.
Let...
How to import file names of a folder into an Excel spreadsheet
Following are the steps to import list of file names from a folder in to an Excel sheet.
1) Select a folder in which the files to be imported are saved. In this case, I've selected AV files folder on my desktop with 4 files in it.
2) Now, hold the Shift key on keyboard and right click on...