Converting from Text to Number

"How do I convert Numbers formatted as Text into Numbers?" Many times numbers will be formatted as text when there is an apostrophe in front. i.e. '50001. Though Excel is kind enough to place a green flag warning the user and giving the user the option to convert to numbers. This method might seem cumbersome if dealing with a large data set. An easy solution will be to 'tickle' the data. Simply, highlight your data or column then go to Data > Text to Columns and simply press on Finish on the first screen. As seem from the illustrations below numbers should now appear as numbers.

How do I color tabs in Excel?

Excel has a few features that can break the monotony of everyday use. For example you can color the tabs and insert pictures in the background.

"How do I change the color on tabs?"

How to unique a list

Many common tasks is the need to unique a list. An interesting way to do this is with MS Excel's advanced filter. Simply, highlight the list that is duplicated, go to Data > Advanced Filter > [click on] Copy to another location & unique records only & Copy to > OK

In the 'Copy To' section select the cell you want the uniqued list to appear. As you can see from the two graphs below this function does an effective job at removing duplicates within a list.

Good Luck!

Excel's workhorse: The If Statement

"How do I use the IF statement, and what if I have more than one condition?"

For those who do not know, an if statement is comprised of a condition, the result if that condition is true and the result if that condition is false.

=If(A1>10,"Bigger than ten","Smaller than ten")

Would return the words 'Bigger than ten' if Cell A1 were 11 for example and would return Smaller than ten, if Cell A1 were 8 for example.

But what if you have two or more conditions? A solution can be:

=If(A1>10,"Bigger than ten",If(A1>5,"Smaller than five", "Between ten and five")

You can do up to seven if statements.... but if you do now want to be constrained by 7 if statements, the AND function can be very useful:

=if(AND(A1<10,A1>5),"Smaller than 10 & bigger than 5","Not Smaller than 10 & bigger then 5")

The graph below shows a similar example for those who are financially inclined. If the Market value is below 120B and the Price to earnings ratio is less than 25, flag the stock as a buy, otherwise as a sell.

My First Blog

Hi I am starting this blog as a way for people to post questions on Excel. I work in finance and enjoy solving MS Excel questions...

Today's question of the day:

"How do I sort a column by text length?"

Solution:

The =Len(A1) function allows you to display the size of any particular cell. So you place this formula in a column next to your text with the A1 being replaced with the cell you want to measure. You can then sort by this column.

I will try to post as much as possible. Please leave questions.