Handy Excel Tips

This article shows how to get the count using more than one criteria. Additionally, it talks about the “conditional formatting” feature which highlights the important results and spares you the inconvenience of having to look at everything in detail.

Read the rest of this entry »

Popularity: 30%

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.

Read the rest of this entry »

Popularity: 42%

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 »

Popularity: 88%

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.

Popularity: 11%

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%

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.

Popularity: 23%