Excel Help: 2011

Saturday, November 19, 2011

Extract live data from system in Excel by connecting your Excel file to the System database - Importing data from SQL in Excel

Suppose your organization records all the transactions in an SQL server database (or any other database) using an ERP system and you need data from this system on a regular basis for your routing activities.

For e.g. you work in HR department needing information about employees like current status, department, Designation, date of joining etc very frequently and this data is stored in SQL database.

You can use Import External data feature of Excel to connect your Excel file to this SQL server database and get updated data whenever you need it just by refreshing this data with few clicks.

Followings are the steps to import data from SQL server to Excel file.

Note: Before you start, please ensure that you have read access to this database and you have Server Name, Login name & Password

There are broadly two steps involved to achieve this…

1. Create Data Source Name (DSN)

2. Importing data into Excel File using this DSN

1. Creating Data Source Name (DSN)

Go to Control Panel --> Administrative Tools --> Data Sources (ODBC)
Click Add, Select SQL Server & Click Finish
Enter any Name for DSN and select server name and click next
Select the authentication mode and click next (check with the database administration team for the type of access provided to you)

Select “With Windows NT authentication using the Network login ID” if your windows login name has been granted the access
Select “With SQL Server Authentication using a login ID and password entered by the user” if you have the login name and password.

Select default database and click next
Click Finish

2. Importing data into Excel file using the DSN which we have just created

Open new Excel file and select Data --> From Other Sources (under “Get External Data”) --> From Microsoft Query

Select the DSN which we have just created (Note: You can use this same DSN for making n number of files)

Enter Password if it asks for and click OK

In next screen, Expand the Table name from which the data is required. Select the Columns which are required. Click Next.

If you want to filter any data then apply filters on this screen. Select the field on which filter need to be applies and select the filter type. Like Date of Joining after 1-1-2011 or Designation equal to “Grade 1” etc.

Select the sorting order if required. You can specify 3 sorting fields and select the type of sorting i.e. Ascending or Descending, click Next

Select “Return data to Excel” to finish the data. Use edit “Edit data using Microsoft Query” option for advanced query editing. (Basic MS SQL knowledge is required for advanced Query editing)
Click Finish

Select the cell where you need the data.

That’s it, you should get the data in the excel sheet. Now use Right click à Refresh option every time to get refreshed data….

Saturday, November 5, 2011

Making Pyramid Graph for Headcount Distribution Representation

Headcount Pyramid graphs are used to graphically represent the grade/designation wise distribution of the total headcount for entire organization or for specific department or for specific project. It is like a pie chart which shows the distribution of various elements of the total. However, for parameters like headcount, Pyramid graph is more effective as compared to a pie chart (lower level headcount is expected to be more than the upper level headcount, thus forming a pyramid). Refer to the screenshot below for a typical headcount pyramid graph…

Steps to make a Headcount Pyramid Graph

Make a table as shown below for grade wise headcount.

Add a column to the data table and apply formula in this column to insert the negative values of the headcount. . (in the given example, the formula in cell C3 will be “=-B3”). After adding this column, the data table should look like the table given below

Select the table and go to Insert --> Bars (under charts section) --> Cluster Bar Chart. It should insert graph as shown in the screen below

Select and delete gridlines of the graph
Now select Vertical category axis i.e. the grade appearing on the graph. And Click on format selection from Chart Tools --> Layout. Change the Axis labels from “Next to Axis” to “Low”. After doing this, grades label will get aligned to the left of the graph window.

Select any of the data series and click on format selection from Chart Tools --> Layout. Change the values of “Series overlap” to 100% and “Gap Width” to 0%

Remove Horizontal value axis and Legend (select axis press delete. Similarly select Legend and press delete)
Change the color of Bars, so that both the bars looks identical.
Again select Vertical category axis i.e. the grade appearing on the graph. And Click on format selection from Chart Tools --> Layout. Go to “Line Color” tab and select “No Line” to remove the line appearing at the center of the bars

Select “Headcount” data series and add data labels by selecting Chart tools --> Data Labels --> Outside end
Finally do some formatting changes as required. Like Font size, border color, shadow effects etc.

That’s it. The Headcount Pyramid is ready and it should look similar to the screen below.

Sunday, October 23, 2011

How to clear the “Recent Documents” list

In Excel 2007, by pressing the “Office Button”, the “File” menu is displayed. On the right side of this menu, it displays the recently opened document list.

This is a great feature. However, it may not be that useful when you are working on private files on a shared computer and you don’t want others to see the files which you have opened/edited recently.

The number of “Recent Documents” to be displayed can be changed from Advanced Option (set it to “0” for disabling this option). However, there is no option in Excel to delete this list.

To delete this list, one has to delete the registry entries of these files using Registry Editor.

Custom formula/function using VBA

Excel has lots of built-in functions which covers the requirements of almost all the standard calculations. However, these built-in functions are not sufficient. Many a times we come to a situation where we want to do some calculations which are not offered by excel built-in function.

Here I will show you how to create a custom function using VBA.

Enable Developer tab in Excel 2007

Developer tab is used for accessing macro/VBA related options and to add activeX controls like combo box, Command Buttons, Check Boxes etc into your file.

Followings are the steps to enable Developer ribbon

Simple method to make Pivot table

Pivot table is a very powerful and helpful tool available in excel for data processing. It helps in summarizing large amount of data with ease. It can be used to derive sum, averages, count from the data stored in a spreadsheet in various combination. For e.g. geography / product wise total sales revenue or Department/location wise headcount etc.

Steps to make graph in excel 2007/2010

Below mentioned are the steps to make simple graph in Excel 2007. The example displays how to make bar graph.

1. Make the base data table as shown in the screenshot belowUse Chart tools à Layout à Data Labels option to add data labels in the graph

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)