Amey (Exploring Excel)
Author Archives: Amey (Exploring Excel)

Macro Efficiency: Disabling Screen Updating

This is part 2 of the Macro Efficiency series where in we teach you how to code efficiently using Microsoft Excel VBA. This method which we are going to discuss today is recognized as one of the basic and the most important method for making the code efficient. The parameters which we are going to learn are regarding application.screenupdate. A small 2 line code will make your macro run faster. Sounds interesting ?

Create better looking Excel Chart #1

Excel Charts in dashboards are the key attention grabbing element. It means that they are first observed by the user. It is important that the chart which you have inserted is an attention grabbing chart i.e it should have some elements which would create a mesmerizing effect in the minds of viewer.
Now the question arises that what can be that element which is attention grabbing inside a simple chart. Check out the statement below and i am sure you would agree with it.

Turn Negative Numbers into ZERO

One of the simplest Excel Hack. The question was very simple. Sometime Excel Formula calculation fetches negative value and due to that negative value it affects your presentation. What if your statement does not accept negative value? , Your statement requires only 0 or a positive value as this may be parameters to judge performance. Hence here comes a handy technique which changes all the negative value to be displayed as 0 (Zero)

Calculation of ROI (Return on Investment)

ROI means return on Investment. Return on investment is important indicator which is used to measure the efficiency in business. Its calculation is similar to that of calculation of interest on Fixed Deposit. For Example if i want to invest $10000 in Fixed deposit for a period of a year @9% simple interest then i would get $900 as Interest. That amount which is received as interest is Return on investment of $10000. Entrepreneur is not interested on how much profit earned but he is interested on how much ROI he has received on investing certain sum in the business.

Easiest Way to create Funnel Chart !

Funnel Chart is one of the type of data visualization chart which helps to analyse data. Funnel chart can be used for sales data reporting or Departmental profits reporting (like i have done in this demo). The most important thing which is needed is that the data should be arranged in descending order i.e from Higher value to the lower value (check out the image below). This funnel chart which we are creating is a inside the cell. We don't have to insert any chart its just created by conditional formatting and formula. 

Get your Macros to work faster – Event Handling

In the series of Blog posts we are going to make you will find various tips and tricks regarding how to speed up calculation of macros in Excel VBA. This is the first among the other articles. 

Most of the time we make a great automation, great program and great software in Microsoft Excel. but we encounter a problem that all our automation is a bit slow. They take time to calculate and they are slow for each and every action. In a project of mine it once took around 3 minutes to run a simple sort macro. Lets learn the causes and the solution to this problem

Record a Simple Macro


You must have heard the term "Macro" in some or the other places. If you are using Photoshop or i would say that you are master of Photoshop then you must be aware regarding the term Macros. Macros basically means automation of the activities. If you are constantly reading my blog post some i have also added an article regarding an automation done with VBA Code. It relates with the macro recording. Basically what a macro recorder does is that it records all steps and presents in the code form in the VBA Editor.