17Apr2015

How to create a waterfall chart in Excel

A waterfall chart is also called as a cascade chart or a bridge chart. This type of chart is not a built-in chart option in excel. However, we can create one by using a modified stacked bar chart by carefully arranging the data.

Let’s take an example of net cash flow. Here, we are interested in finding which month the business did well and in which month it did not do well.

 

Step 1: Arranging data.

Assume that we have data for…

  • 17 Apr, 2015
  • Excel for Commerce
  • 0 Comments
  • Excel Consultant, Excel Expert, waterfall chart,
Read more
14Apr2015

Using Array Formulas in Excel

An Array formula works with an array. You may see array formulas referred to as “CSE formulas,” because you press CTRL+SHIFT+ENTER to enter them into your workbooks.

The following is a list of functions in Excel that use arrays:

  1. LINEST()
  2. MDETERM()
  3. MINVERSE()
  4. MMULT()
  5. SUM(IF())
  6. SUMPRODUCT()
  7. TRANSPOSE()
  8. TREND()

In general, array is a collection of items or values. An array formula is a formula that can perform multiple calculations on one or more of the items in an array. They can return either multiple results or a single result. An…

  • 14 Apr, 2015
  • Excel for Commerce
  • 0 Comments
  • array formulas, Excel Consultant, Excel Expert,
Read more
27Mar2015

Split Function in VBA

How to use SPLIT function in VBA

Split function in VBA is used to split a text string based on a delimiter. This delimiter can be a special character like “,”, ” -“, “&”  or even text delimiters like “and”, “or”, “the”, “sample” etc. For example, lets takes a data set with Employee name, Designation and sales figures combined in one string as shown in the image below. This data is not laid out properly. Instead of manually editing such…

  • 27 Mar, 2015
  • Excel for Commerce
  • 0 Comments
  • Excel Consultant, Excel Expert, split function,
Read more
19Mar2015

Bollinger Bands using Excel

Bollinger Band was developed by a technical trader John Bollinger in 1980. A band plotted two standard deviations away from a simple moving average is Bollinger Band. The price of the stock is banded by an upper and lower band along with a 21-day simple moving average. Bollinger Bands are a volatility indicator for the Stock prices.

How to interpret Bollinger Band?

Bollinger band is an effective strategy. Interpreting the Bollinger Band will help in finding the stock volatility to make…

  • 19 Mar, 2015
  • Excel for Commerce
  • 1 Comments
  • bollinger bands, bollinger bands template, investment tool, stock investment tool,
Read more
10Mar2015

Mail Merge in Excel

Mail merge is used to print letters or to send Email Messages to multiple addresses. For Example, If you have a letter that is to be sent to many clients then Mail merge can be very helpful. It is used to create multiple copies from a single template using a data source. This letter can be sent out to many recipients with a single click.

How to use Mail Merge in Excel:

 

Step 1: Create Database

First, we need to create a database…

  • 10 Mar, 2015
  • Excel for Commerce
  • 0 Comments
  • Excel Consultant, Excel Expert, mail merge,
Read more
02Mar2015

California Sales Template with Heat Map in Excel

Does your company have sales spread all over the counties in a State like California? Are you bored of having your sales data represented using regular in-built excel charts? Are you looking for  a more intuitive way  to represent your data?

Then this California Sales Template is for you. This template will help you in visualizing your sales by county, in the form of a Heat Map in Excel. We have included a heat map that uses variation in color density to represent sales data. This…

  • 2 Mar, 2015
  • Excel for Commerce
  • 1 Comments
  • california sales template, Excel Consultant, Excel Expert, heat map in excel, sales template,
Read more
25Feb2015

Difference between Activex and Form controls

There are two types of controls in Excel – ActiveX and Form controls. These controls can be accessed from Developer tab in Controls Section as shown in the image below.
difference between activex and form controls 1

1) Form controls are simple to use and are more integrated into Excel while ActiveX controls are loaded in excel by separate dlls (dynamic link library).

2) ActiveX controls are more flexible in designing. Customization is extensive in…

  • 25 Feb, 2015
  • Excel for Commerce
  • 0 Comments
  • difference between activex and form controls, Excel Consultant, Excel Expert,
Read more
03Feb2015

Dynamic Charts in Excel

Dynamic charts in excel are useful when we add new data to our existing data tables. For example, lets take 1st quarter sales data for Laptops, Mobiles and Tablets as shown in the image below. A chart for such a data table is generally static i.e. the chart does not reflect new data which gets added to the existing data table. This blog will help you to create a dynamic chart which reflects new data points as and when new…

  • 3 Feb, 2015
  • Excel for Commerce
  • 0 Comments
  • Dynamic Charts in Excel, Excel Consultant, Excel Expert,
Read more
29Jan2015

Get combobox selected value in Excel VBA

There are three methods to read the selected values from a combobox in VBA. Please go through the following blog to know how you can do it.

Method 1:

Consider, we have a dropdown list having days mentioned in it. The value selected in the dropdown is ‘Sunday’. Now, we will try to read the selected value from the dropdown list using Excel VBA.

get combobox selected value 1

 

  • 29 Jan, 2015
  • Excel for Commerce
  • 0 Comments
  • Combobox Selected value, Dropdown value,
Read more
06Jan2015

Microsoft Access – How to run a macro when database opens?

Microsoft Access allows the users perform a set of actions every time a database loads or opens by creating a macro called ‘AutoExec’. You can run a macro when database opens by naming it as ‘AutoExec’ or calling the ‘AutoExec’ macro.

As the name suggests, an ‘AutoExec’ macro is a macro that automatically executes the actions we specify when a database opens. If you name a macro as ‘AutoExec’, it executes on database load.

Create an AutoExec macro

  • 6 Jan, 2015
  • Excel for Commerce
  • 0 Comments
  • Access, AutoExec,
Read more