Generating list of contents of any folder
Clever Excel formulas March 5th, 2008
This article shows how to make a list of the folders, or even the files inside a folder on an excel spreadsheet.This is a very useful tool as it is impossible to type in the names manually especially when there are plenty of files.
Share ThisPopularity: 51%
Performing Two-Criteria Lookup
Clever Excel formulas January 26th, 2008
At first sight, this article may look long-winded to you, but trust me, with a little concentration and patience, this article alone can help you master all the difficult excel functions like offset, indirect, index, match etc. So, if you are ready to take the plunge, here is the magic article. Read the rest of this entry »
Share ThisPopularity: 59%
Dragging a formula down
Excel shortcuts January 16th, 2008
This article shows the magic of Shift and Control keys and how we can use them with arrow keys to work faster.
Share ThisPopularity: 21%
Insert Excel Spreadsheet into Microsoft Word Document
General January 15th, 2008
This article is a basic guideline on how to insert excel spreadsheet into word document. It gives a comprehensive list of the available options and describes each one of them in graphic detail.
Share ThisPopularity: 73%
Putting Borders around bunch of non-adjacent Cells at once
General December 26th, 2007
SUM(IF()) array formula
Logical Excel formulas December 16th, 2007
This article basically compares excel’s vlookup and sumif array functions and explains which one to use, when and why?
Share ThisPopularity: 34%
See all Formulas at Once
Excel shortcuts December 16th, 2007
If you hit F2 on any cell, you can see the formula of that particular cell only.This article gives you a shortcut with which you would be able to see all the formula’s present on your spreadsheet all together.
Share ThisPopularity: 5%
Is there any de-concatenate command??
Excel Functions December 16th, 2007
This article talks in great detail about excel’s Left and Right formula’s and explains how by combining “Find” and “Len” functions with Left and right we can easily handle more comlicated situations which may arise.
Share ThisPopularity: 17%
Changing case in Excel
Excel Functions December 9th, 2007
Unlike microsoft word, Excel does not have an option wherein you could Format text to change case. But don’t worry, excel does have “change case” functions, so in case you have incorrectly typed everything in one case than another, you won’t need to rekey them.
To change case you can use upper, lower or proper formula.
Upper would change everything to upper case. Similarly lower would put them in lower case.Proper case would capitalise the first letter, keeping the rest in lower case.
Suppose you wish to change the contents of column A to lower case instead of upper, you can do that as follows:
Share ThisPopularity: 10%
Concatenate function
Excel Functions December 9th, 2007
The Excel’s Concatenate function can be used to join up contents of multiple cells into one cell.
The syntax for the CONCATENATE function is
=CONCATENATE(text1, text2)
When can “Concatenate” function come in handy??
When say first name is given in column A and last name in column B and you want to combine the two.
Concatenate function can help you do this as shown below:
But notice there is no space between the two so if you want to insert a space you can modify your concatenate function as follows:
Points to remember:
Concatenate function uses contents of other cells(like A and B here). So if the linked data is changed, the concatenated cell( Say C1) would change as well. And if you delete the related columns then you will get a reference error in the concatenated cell.To overcome this, you can either hide column A and B or simply copy the contents of column C and use paste special values command under the “Edit” tab.
An alternative to concatenate function:
The ampersand (&) operator can also be used instead of the CONCATENATE function to do the same task.
So in our example above if we enter =A1&B1 in cell C1, it would give the same value as =CONCATENATE(A1,B1) as shown below:
Popularity: 14%
Excel frequency function or simple logic??
Logical Excel formulas December 8th, 2007
Suppose you have the marks of each student and you want to generate a frequency distribution ie total number of students who obtained marks within a given interval.
You can do this using either Excel frequency function or by applying logic.
One of the con of using set functions like “Frequency” is that you have to familiarize yourself with the syntax first.
Like the syntax of the Frequency function is:
Frequency(Data_Array,Bins_Array)
So you should know what are data and bins array.
Data_array is an array of or reference to a set of values for which you want to count frequencies. Bins_array is an array of or reference to intervals into which you want to group the values in data_array.
The following example would make it clear.
Here is a list of marks and the interval for which you need a frequency.
So you want to find out how many students got marks below or equal to 20, how many got between 21 and 30 and so on.
Since it is array formula, so we first need to select the cells where we want the frequency i.e. E5:E10, then enter the data and bin array and hit CTRL+SHIFT+ENTER.
So, 2 students got marks below or equal to 20, 2 got between 21 and 30 and so on.
We can also do this using SUM(IF()) array formula.
This function uses two criteria and “* “in array formula denotes the second condition. So it says sum column B if marks are greater than 0 and less than or equal to 20. As you would notice, the answer is same using both options.
This function can also handle multiple frequencies which “Excel frequency function” can not. For eg. if 3 students got the same score 12 and 4 students got 47. Then if we want to do the above exercise using frequency function we will have to enter the same marks thrice or four times whatever be the case. Whereas if we use the above mentioned SUM(IF()) array formula we can simply enter these in column B as shown below:
Share ThisPopularity: 23%
Formatting the cell as text
Clever Excel formulas December 4th, 2007
Sometimes when you enter text in excel, it automatically converts it to some other format. This can be very annoying. Like if I want to enter age bands as 11-20,21-30 etc then excel would read 11-20 as 20-Nov.
We can get around this problem by adding a single quote in front. This quote won’t get displayed or printed but just tells excel to treat the entries as text. However if you press F2 and try and edit the values, you will see a single quotation mark in front.
But make sure you enter the quote before hitting enter i.e. if excel has already made it 20-Nov and now if you put a quote in front then it won’t change.
This can also come in handy when you have to enter more than 15 digits in a single cell and you want to keep the format as text. If you don’t enter a quote in front or change it to a text format, excel would display it in scientific notation like 4545121215241541514 would be shown as 4.54512E+18.
Share ThisPopularity: 10%
Selecting Multiple Worksheets
General December 3rd, 2007
If you want to select adjacent sheets then you can do that by holding down the shift key and clicking on the next sheet tab.For e.g. There are six worksheets - sheet 1 to sheet 6. And you want to select one to four then simply select the first sheet, hold down the shift key and click on the forth sheet.You will notice this will automatically select sheet 2 and 3 as well.
However if you want to select non adjacent sheets then hold down both the shift and control keys together and choose the desired sheet.
If you want to do a mouseless selection, just hold down the shift and control keys and press “Page up” or “page down”.
If you want to select all the worksheets at once then you can also right click on one of the sheet tab and choose “Select all sheets” option.
Share ThisPopularity: 5%
Selecting non-adjacent cells in excel
Clever Excel formulas December 3rd, 2007
We can select non-adjacent cells by holding down the control key and then simply choosing the desired ones by a single click of a mouse.
However this can be very frustrating at times, because if you have mistakenly released the control key and you are not through with your selection yet,then you will have to start all over again.
We can get around this problem by just pressing the (shift + F8) keys once and then highlighting the cells that you need,using the mouse.
When you are through with your selection, you can press the same keys again or just hit the “Esc” key.
Popularity: 14%
How to sum values in a filtered list???
General December 3rd, 2007
Once we have applied filter to our data using the excel’s “AutoFilter” option and we want to sum only the non-hidden range, then we can’t use the simple “sum” function as it adds both the hidden and visible cells. In that case we can use the excel’s subtotal function as it only sums the visible range.
The syntax of the Subtotal function is:
SUBTOTAL(function_num,range_reference1, range_reference2….)
We can perform 11 different functions in conjunction with subtotal.
Function_Num - Function
1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP
Suppose you have a list of 4 different products - A, B, C and D along with their quantity sold.
And you wish to add up only the quantity sold of product A. Then if you apply filter and sum the range using “Sum” function then it will give the total for all the products
Whereas if you use subtotal function you will only get it for product A as shown below:
Share ThisPopularity: 17%
Converting Text to Columns in Excel
General December 3rd, 2007
Whenever you have some text in one column and you wish to break it into several ones then instead of retyping the text in the next column, you can use the “Text to Columns” option under the “Data” tab. For e.g. Both the names and marks of the students are in the same column(say Column A) and you want to separate them in different columns as shown below:
The first step is to highlight the column that needs to be separated. Then click “Data” and choose “Text to Columns” option.
Then choose the delimited option.
Select the “space” check box, keeping others unchecked. So whatever text is after the space will be put into the next column.
As soon as you click the “Next” tab you will see the preview of your data. And you will notice that it has put marks into the next column.
Now just click the “Finish” button and it will be done.
Share ThisPopularity: 5%
Pivot table or countif function???
Clever Excel formulas November 29th, 2007
If we have a list of student names and we want to calculate the frequency of each name then we can use a countif function to obtain this. However the disadvantage of countif function is we first need to make a table with no repetition of names and only then we can apply countif.
The main advantage of using pivot table instead of countif function is, it will create a table itself and we don’t need to worry about it.
Moreover to make a pivot table, it is not necessary that we have two categorical variables. Even if we have one variable like “names” then we can drag “names” to the rows and “count of names” to the data region. So against each name it will give us the frequency.
Below is the step by step procedure:
Choose the “Pivot Table and PivotChart Report” from the “Data” drop down menu and click “Next”.
Select the range and then click “Next” tab.
Then click “Layout” tab and drag the variable and click “Ok”as shown in the diagram below.
So you have got exactly the same table as above using countif function but the best part is you did not manually enter the names.
Share ThisPopularity: 17%
Moving between multiple excel Workbooks
General November 29th, 2007
To switch between your open excel workbooks,you can use the “Window” drop-down menu as it lists all the workbooks that are currently open. Alternatively, you can use the shortcut keys(Alt+Tab) to move from one window to another.You need to hold down the Alt key as you repeatedly press the Tab key to switch from one window to another.
When the desired workbook window is selected, release the Alt key and the
actual workbook will be displayed.
However using this combination(Alt+Tab) will display the names of not just the excel
workbooks but all other applications that are currently open like word documents, notepad, internet explorer etc. If you only want to toggle between your open excel workbooks you can use (Ctrl+Tab) key combination instead You can also switch between excel sheets using the shortcut keys (Ctrl + F6).
Popularity: 7%
Delete Excel chart
Excel shortcuts November 29th, 2007
If you right click on the chart, you would not see a delete option. So to delete a
chart, just select the chart area with a single mouse click and press the Delete key.
Popularity: 5%
