Category: Excel
-
How to count cells which contain text in Excel
If you are trying to calculate number of cells/observations in a particular column in your dataset which contains a particular text, then this guide will surely help you. So the below snapshot gives a problem set that we are trying to solve. Column B is the target column and we need to find the count…
-
Text Functions In MS Excel
Excel Functions for formatting : TRIM – Removes spaces from text UPPER – Converts Text to Uppercase LOWER – Converts Text to Lowercase PROPER – Capitalizes the first letter in each word of a text Excel Functions for Text Extracting : LEFT – Returns the leftmost characters from a text value RIGHT – Returns the…
-
How to Repeat each Cell of a Dataset Multiple times in Excel -Formula
Recently while working on a dataset I had to repeat a particular cell for a specific no of times. For example – suppose I have a name ‘Mary’ and I need to repeat Mary for 8 times. So we know it is pretty easy to drag the small ‘+’ at the end right corner of…
-
Spearman Rank Correlation Vs Pearson Correlation (with MS Excel tips)
Correlation denotes the degree of association between two variables.If changes in one variable corresponds to changes in the values of the other variable as well, it means that the two variables are correlated.So, when one variable increases if the other one also increases then these two variables are positively correlated.On the other hand if increase…
-
Regression in Excel:How to interpret the output table
In my previous post I described how to run a regression in Excel and generate the output.Here I will focus more into what to look for in the output table and how to derive insights from that. Here is a sample of output table of linear regression generated in MS excel. Now let’s discuss what…
-
How to use count functions – MS excel
As I have listed down all the useful excel functions relevant for data analysis in my previous post, here I will elaborate on the ‘Count’ functions with examples. Count – This function simply gives us the count of cells containing numeric cells.Interestingly, if we have empty cell or have any text in the cells this…
-
How to run a Regression in Excel
MS Excel allows us to run linear regressions pretty easily.Here are the steps to add the ‘Data Analysis’ toolpack for the version of excel you are using. First go to ‘Files’ and then select ‘Options’.This will open a window similar to the below screenshot. Select ‘Add-ins’ (highlighted in yellow). Select the Excel add-in as highlighted…
-
Basic Excel functions analysts must know – Part I
Excel is still a very powerful tool in the era of advanced analytical software. Here is a list of some very easy and useful excel functions which come very handy to analyze/summarize any large data-set. Count Functions: Count : Returns number of cells that contain numbers CountA : returns number of non-empty cells CountBlank :…