jump to navigation

Tips Excel: Customizing The End of Row and Column in Excel August 3, 2010

Posted by Admin in Tutorials, Tips & Tricks.
Tags: , , , , ,
add a comment

Hi Friends, I’m sorry It has been a long time not posting anything in this blog. Last few weeks was a very busy weeks. I need to finish my thesis writing and some works that will be in the deadline shortly. So I can post my new material today. It is the short one, but hopefully useful for us.

Have you ever seen Excel screen below? Which the limits of Row and Column are customized?

It is interesting, isn’t it? Even though this is a not difficult trick, not everyone know it. So in this post I will share how to do it. I hope It will make our Excel view is getting better and more interesting. The steps is quite simple:

  1. To make the Row limit (only until Row 13 in the picture) select Row 14 and then press Ctrl + Shift + down arrow. Row 14 to the end will be selected. After that, right click your mouse on the selected row and choose hide.
  2. To make the Column limit (only until Column E in the picture) select Column F and then press Ctrl + Shift + right arrow. Column F to the end will be selected. After that, right click your mouse on the selected column and choose hide.

It is finished now. It’s simple, isn’t it. Have a nice try. Hopefully useful.

How to Find Average Without Including 0 (zero) or Error Data in Excel March 4, 2010

Posted by Admin in Tutorials, Tips & Tricks.
Tags: , , , , , ,
add a comment

Few days ago, a friend of mine asked me how to find an average of a set of data without including 0 (zero) because it will be too small and out of purpose.

You can do it by using array formula. Usually you use =average(A1:A5) to find usual average. But this formula take 0 (zero) into calculation. To exclude 0 (zero) you can use this formula:

=average(if(A1:A5=0,””,A1:A5))

After finishing typing the formula, you can’t just press enter. But you need to press Ctrl+Shift+Enter it the same time because this is array formula. You can also use other function like sum, min, max, stdev, etc.

With the same logic you can use it to handle error data. When there is even one error data (#N/A, #DIV/0!, #REF,etc) in your data set. Any function you use like average, sum, max, min, etc, will result an error (#N/A, #DIV/0!, #REF, etc) as well. To avoid this you can use above tricks with a little change. Use this to find average and handling error:

=average(if(iserror(A1:A5),””,A1:A5)) don’t forget to press Ctrl+Shift+Enter

With that formula, error in the data will be ignored in the calculation.

Hopefully useful.

How to Copy a Sheet to Other File February 12, 2010

Posted by Admin in Tutorials, Tips & Tricks.
Tags: , , , ,
add a comment

Sometimes we have a complete file with data and formulas. Then we have other file with different data but the same structure. We want to copy a sheet with formula in the former file to the later, so we don’t need to rewrite the formula (because actually the formula will be the same), moreover if there are a lot of formula there. But the problem is, when we do that, the formulas is not linked to the later file as we want but still linked to the former one. Look at the example below.

In the former file:

=SUM(Dummy!A1:A5)

After we copy to the later file the formula will be:

=SUM(‘[Sales-Jakarta.xls]Dummy’!A1:A5)

So we are failed to get the same formula because the formula we get still linked to the former file (Sales-Jakarta.xls).

The tips to fix this problem is quite easy actually. After doing copy and paste operation, what you need to do is just doing Find and Replace operation (Click Ctrl+h). In Find what input type [Sales-Jakarta.xls] while in Replace with input just keep it empty. Click Replace All. Now all formulas you have is linked to the current file which is the later one. =SUM(Dummy!A1:A5).

Hopefully helpful.

*Actually you need to check whether you have more data of more formulas. If you have more formulas than the data you just need to copy the data from later file to the former (complete file with data and formula) and save as it. But if you have more data the above tips will work better.