How to make dynamic charts

In excel, one can create a GUI, where users can select values from the dropdown lists. Based on this selection the graphs below will be updated.

Such graphs & reports are very useful while making big dashboards in which the users requires different views of the report. For e.g. City wise report or Month wise report.

You can always make multiple graphs/reports for each values (for e.g. separate graphs for each location if you are making location wise report). However, doing this will take lot of time and efforts, especially when the list is too large.

In the example below, user can select values in cell “C1” and the graph below will get updated based on the selected value.

Steps to create dynamic chart

How to make data invisible from cells

To make data invisible in excel cells, select the cells and change the font color to cell’s fill color. (white for default white fill color).

How to add drop down list using Data validation

Data validations can be used to restrict users to enter invalid data in any cells. Alternatively data validations can also be used to create drop-down list to make GUI where the users can select values from the drop down and reports or graphs can be made available for selected value.

Convert text in proper case in VBA

Use StrConv function to get your text in proper case.

How to make waterfall graph in excel

Waterfall graph is a floating bar graph used to pictorially depict movement of any parameter or how various parts contribute to a whole parameter. For e.g. how the Headcount or profit have moved over last week, last month etc. OR various components of Net profit and so on. In the screenshot below, example 1 depicts the headcount movement w.r.t. last month and example 2 shows the various components of Net profit.

Example 1: (Headcount movement w.r.t. last month)

Example 2: Various component of P&L

Convert 2003 or older format files to 2007 format files

To convert 2003 or older format excel files (.xls) to 2007 format files (.xlsx) use Office button and select Convert option (highlighted in the screenshot below).

How to get windows login name in VBA

While developing macros, many a times we come to a situation where we want the windows login id of the user. Use Environ function to get windows login id. Syntax is as follows...

Environ ("username") --> Returns the windows login id of the user.

Post your questions on Excel-VBA (Macro)

Hello,

If you have any doubts in excel VBA, please post your questions on the Help Forum. I will try and answer your queries

Post your questions on Excel

Hello,

If you have any doubts in excel, please post your questions on Help Forum. I will try and answer your queries.