03Feb2016

How to use ‘What-If Analysis’ tools in Excel

Microsoft Excel is equipped with “What-If Analysis” tools like scenario manager, Goal-seek and Data table. These tools allow you to use different data sets in one or more formulas to come up with different sets of results.

For example, scenario manager can be used to compare two different budgets at the same time using its “Summary” option. The goal-seek in excel is helpful in allowing to predict the input values for a desired output value. So, basically…

  • 3 Feb, 2016
  • Excel for Commerce
  • 0 Comments
  • Data table, Goal Seek, Scenario Manager, What-if analysis in excel,
Read more
21Jan2016

How to use Solver in Excel

In our day to day lives, at an individual level or at a business level, we come across questions like:

  • “How much should my personal expenses on a particular item be cut down so that my total expenses don’t reach the threshold?”
  • “To what extent should the company increase its advertising spend so that revenue is maximized? “
  • “How much should I invest in a particular fund so that I get a final amount of $100,000 at the end of 5 years?”

Excel…

  • 21 Jan, 2016
  • Excel for Commerce
  • 0 Comments
  • ExcelSolver, Solveraddin,
Read more
17Jul2015

Grouping Checkboxes in Excel

Grouping checkboxes will help to keep the checkbox values discrete. We may also need to group the checkboxes in order to move or resize them as per requirement. Grouping helps to take control on all checkboxes at a time.

 

Grouping checkboxes in Excel:

After inserting checkboxes onto the worksheet. Select all check boxes as shown in the image below.

grouping checkboxes in excel 3

If there are many checkboxes/objects to select then try Select…

  • 17 Jul, 2015
  • Excel for Commerce
  • 0 Comments
  • Excel Consultant, Excel Expert, grouping checkboxes in excel,
Read more
25Jun2015

Text to Columns in Excel

Text to Columns in Excel are used to split a single column of text into multiple columns. This feature is helpful when we import data from word, PDF or any other source. As the imported data may not be usable right away. We can split the text based on fixed width or split it at each comma, or at period or at any other character. For example, assume we have a list of data with Names and their Designation

  • 25 Jun, 2015
  • Excel for Commerce
  • 0 Comments
  • Excel Consultant, Excel Expert, Text to Columns,
Read more
16Jun2015

How to Browse and Select File and Folder in VBA

Browse and select file in VBA :

Sometimes, we may need to work with external files in Microsoft Excel. We might need a button in excel to open windows file dialog box so as to select the desired file. To achieve this we need to write the following code in VBA editor.

 

VBA Code Snippet:

browse and select file vba 1

 

There are two steps in the code snippet.

Step 1: Choosing the file

Here we are…

  • 16 Jun, 2015
  • Excel for Commerce
  • 0 Comments
  • Browse and Select File and Folder in VBA, Excel Consultant, Excel Expert,
Read more
21May2015

INSTRING function in VBA

Instring function in VBA returns the position of the first occurrence of a substring. This function can be used to find whether the search string (Sub string) is present in a string.

Syntax:  InStr( )

Start: It is the starting position for the search. If this parameter is omitted, then default search will begin at position 1.

String: It is the string to search within.

Substring: It is the substring that you want to find.

Compare: It is the type…

  • 21 May, 2015
  • Excel for Commerce
  • 0 Comments
  • Excel Consultant, Excel Expert, instring function,
Read more
07May2015

How to Analyze Large Data Sets in Excel

Ever wanted to use Excel to examine big data sets? This tutorial will show you how to analyze over 300,000 items at one time. And what better topic than baby names? Want to see how popular your name was in 1910? You can do that. Want to find the perfect name for your baby? Here’s your chance to do it with data.

There are professional data analysts out there who tackle “big data” with complex software, but it’s possible to…

  • 7 May, 2015
  • Excel for Commerce
  • 0 Comments
  • analyze large data sets, Excel Consultant, Excel Expert,
Read more
23Apr2015

Monte Carlo Simulation in Excel

What is Monte Carlo Simulation?

The name Monte Carlo simulation comes from the computer simulations performed during the 1930’s to know the probability that the chain reaction needed for an atom bomb to detonate successfully. The physicists who involved in this work were big fans of gambling hence the name Monte Carlo.

 

How to use?

It is developed to analyze the problem by running the simulation many times in order to develop a statistical data on how the model works. The resulting data…

  • 23 Apr, 2015
  • Excel for Commerce
  • 0 Comments
  • Excel Consultant, Excel Expert, monte carlo simulation,
Read more
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