jump to navigation

Excel Trick: Chart with Automatic Colour Change February 10, 2020

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

The article below provide you with trick to make an automatic colour change graphic. Let say you have certain target, and you want the graph in the certain month or certain area that the target wasn’t achieved, coloured by red automatically.

Automatic Colour Chart

“Assalmualaikum…. I wanna ask.
I made a program (file) using a graph. Let say target of the data is 5, and the value of  are a=4, b=5, c=6, how to change the color of the graph automatically, if the value is less than the target (in) “a”= red, equal the target “b”= yellow, more than the target “c”= green. If a=6, b=4, c=5 how to change the color of the graph automatically.”

Few days ago, someone posted the above question in QA page, and I’ve promised to make it as new post in this blog. So here comes the explanation with example file to make it clearer.

The Case That Needed Automatic Colour Change

This time I will discuss an excel trick to make ordinary bar chart with extraordinary color. When a certain value is less than the target, than the bar color will change into red. If it matches the target than the color turn into yellow, and when it override the target, the color become green. The picture below will show you the illustration of the above case.

Automatic Colour Graph

Automatic Colour Graph

How to Make Automatic Chart Without Macro

There are two ways to make that kind of graph, using macro or without using macro. This time I will discuss the trick without using macro. The point of the trick is preparing the dummy data, as you can see from the picture above, next to the real data. There are 3 dummy data that represent less than the target data, equal, and more than the target (you can make more/less than 3 depend on the number of categorization of the value).

Formula for dummy data is =if(value>target,value,na()) for the dummy of more than target, and so does for equal and less than target, just replace > with = and <.

After dummy data is ready, the final step is inserting the graph it self. It is just an ordinary graph, but instead of inputting the real data as data source, get the dummy data. So there will be 3 series available in the graph. Just give each series the required color for given condition.

Example of Automatic Chart

Here is the example of the above trick.

New Version

https://www.mediafire.com/file/2fj0p92mj8an8sy/Automatic_Graph.xlsx

Old Version

http://www.mediafire.com/file/f4we269adkbd4gt/Template_Grafik_Warna_Otomatis.xls

http://www.ziddu.com/download/11132233/Template_Grafik_Warna_Otomatis.xls.html

Don’t forget, practices make perfect. Hopefully useful.

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.

Using Conditional Formatting and Validation to Make Your Excel Looks More Interesting July 16, 2010

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

Now, we are going to learn how to use conditional formatting and validation to make our Excel form and input template more interesting. One of my recent jobs in a consulting agency is preparing training and workshop material. The more interesting the material, the more interested the participant on the training. And it can prevent the participant from boredom as well, especially when there are so many materials to finish. Alhamdulillah, Excel has provided Conditional Formatting and Validation feature that we can utilize.

The above figure shows us a form to evaluate performance. When there is no input, the cells color will be white, but when it contains bad performance, for example 1 or 2 scale, the color change to red. When we fill it with good performance (3 or 4 scale) the cells color automatically will change to green. We can make such form using Conditional Formatting. If we pay attention on the way we can fill the cells, we will see that we can either, manually type the number or just choose from the drop down menu. To make that drop down menu we need to use Validation and choose “list” type.

What we need to do are:

  1. Select range C4:C8, and then choose Format – Conditional Formatting.
  2. We will try to make the cells color automatically change to green when we fill it with 3 or 4. In “Condition 1” change “between” option to “greater than or equal to”, and then fill it with 3. It means we want that the following format will be applied when the cells is filled with 3 or greater number. And then click Format – Pattern and choose Green, OK.
  3. After that we will make condition when the cells contain 1 or to the color will be red. First, click Add>>.
  4. After condition 2 appear, keep the “between” option and fill it with 1 and 2 (what happen if we choose “less then or equal to”? just try it). And then click Format – Pattern and choose red.
  5. At this step our Conditional Formatting is ready. Next, we are going to make Validation feature.
  6. First type 1 in any empty unused cells, and then type 2, 3, and 4 below it respectively.
  7. Select C4:C8 again, and then choose Data – Validation. In the “Allow” option choose “list”. The “source” input will appear, select range contained 1 to 4 from previous step, and click OK.
  8. And we have just finished the Validation.

Have a nice try to make your sheets more interesting. Hopefully helpful.

How to Open Excel 2007 File (.xlsx) (and Other Office, .docx, .pptx) in Excel of Office 2003 March 4, 2010

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

After reading Master Abimono post in www.xl-mania.com (sorry it is in Bahasa Indonesia), I found out that there are still many people who don’t know how to open excel 2007 file (.xlsx) (and other office, .docx, .pptx) in excel or office 2003. Actually, by default, we can’t use excel, word or power point 2003 to open excel, word of power point 2007 files. BUT alhamdulillah Microsoft still care on us, Office 2003 lovers. They provide us a compatibility pack, so we can still open Office 2007 using Office 2003. What you need to do is download that compatibility pack and install it. It doesn’t mean that you can use Office 2007 features, but at least you can open it, edit or save it.

So if you are still using Office 2003 just like me, but you can’t open Office 2007 file, hurry up, download the file directly from Microsoft website. Click here to direct you to microsoft office download link. Thanks to Master Abimono from www.xl-mania.com.

Ok, happy opening office 2007 file with your office 2003. Bravo office 2003!!! (I had terrible experience with office 2007, he…he… I can’t even put page number in my word file, it make me very upset with office 2007).

Hopefully helpful.

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.