Posted on

MS Excel and ODBC/OLEDB for Database connectivity

Flat files like Excel workbooks have limitations when it comes to data storage. Though the new release of Excel has enough number of rows and columns to accommodate and analyze data, working with huge flat files in an Excel is a difficult job. Here comes the ‘database’, for our rescue.

A relational database management system (RDBMS) is an organized way of storing huge amounts of structured/columnar data. It also allows us to access the required records through SQL queries (SQL – Structured Query Language). MS Access, MS SQL server, Oracle, DB2 etc. are a few popular RDBMS.

This article is all about connecting Excel to MS Access & SQL Server using ODBC or OLEDB connection string. ODBC or OLEDB are the mediums to connect databases to any application.

Plugin spreadsheet and database together

 

Connecting to MS Access from Excel (2016)

The below procedure uses OLEDB connection string to establish the connection between the applications. To import data into an Excel workbook from MS access, there should be at least one table inside MS Access database. Save the created database in any location.

Step1: Open Excel Application -> Data tab -> Get Data -> From Database -> From MS Access Database.

Navigating to database option in excel

Step2: Then select the database file located on your drive and then double click the “Table” you want to import.

Importing database table into excel

Step3: You will get an option to edit the table in a “Power Query” editor as shown below.

Editing Power Database table in Power query editor

Step4: Click on “Click & Load to” option on the top left section of the ribbon. Then you will get a pop up to select the destination cell as shown below.

Selecting the excel range to load table data

Step5: Click ‘OK’ to get the data into the selected range of the excel worksheet. The data is dynamic, once MS Access table gets updated, same can be reflected into Excel workbook by clicking “Refresh All” option in the “Data Tab“.

Connecting to SQL Server Database from Excel

Connecting SQL has same type of data connectivity as MS Access, but the only difference is to enter the SQL server name in the field provided. Please refer below image.

Connecting to SQL server database from excel

Open Excel -> Data Tab -> Get Data -> From Database -> From SQL Server Database -> Enter SQL server name -> Enter Login Credentials -> click Next -> Select the database and table -> Click Next -> Select the cell/range to load the SQL table content -> Click OK

Connecting any database from Excel application follows the above procedure in establishing the connection. We can also use Visual Basic programming to automatically connect and refresh data from the database.

 

Check out our Tableau Pages

Posted on

How to create multiple dependent drop-downs using array formulas in Excel?

Drop-down lists are a very important part of any dashboard or data entry form. In this article, we’ll learn how to create multiple dependent drop-downs using array formulas. What I mean by multiple dependent drop-downs is: When selecting an item from the first drop-down, the following drop-downs would show only relevant items based on your selection.

Creating dependent drop-down lists in excel could be easy if done using VBA, but it becomes trickier to do it with excel formulas alone. For this, we can use ‘Array formulas’. An array is a collection of one or more items. Likewise, an array formula can return one or more items as result. Array formulas must be entered in a cell by pressing Ctrl + Shift + Enter. In the formula bar, you can see any array formula wrapped inside curly braces {}. For example, {SUM(A1:A5)}

We have sample data as follows:

Multiple drop downs sample data

Using the above data, we want to create dependent drop-downs like shown below. To create these drop-down lists, I will be using two sheets: “Sample Data” & “Temp”.

Excel drop down example

Creating a list with unique values for first drop-down

In Column A of ‘Sample Data’ , you can see there are repeated values. We need to eliminate them before we can show it in our first drop-down . There are many ways to make a list unique. One option that is often used is ‘Remove duplicates’ feature from ‘Data’ tab of Excel’s menu ribbon. But using that is a manual task and must be repeated, when new data would be added. Hence, it is not preferred. Another option to get the unique list would be to use array formulas. The below formula can be used to get the unique values from column A of ‘Sample Data’.

Write the below formula in the “A2” cell of “Temp” sheet and remember to press Ctrl + Shift + Enter! Drag the formula till “A11” cell of “Temp” sheet.

IFERROR( INDEX( SampleData!$A$3:$A$11,MATCH( 0, COUNTIF( $A$1:A1,SampleData!$A$3:$A$11), 0) ) ,””)

  • COUNTIF function would return the number of occurrences of the in the sample data range
  • MATCH function will look-up for”0” in the output of COUNTIF and returns the row number which has the next unique brand name
  • INDEX function picks up & shows the text of the next unique brand name from the respective row number given by MATCH
  • If MATCH does find anymore unique names on the sample data range, then it’ll return #N/A error instead of row number, which would be handled by IFERROR function

Thus, unique list of values for column A of Sample Data would be obtained in “Temp” sheet.

First dynamic range drop-down

To populate the obtained unique values into a drop-down list, we’d use the data validation feature from ‘Data’ tab of Excel’s menu ribbon. Since we don’t know how many unique brand names could be there at any point in time, we cannot use a fixed range to populate the drop-down. We must use a dynamic range to populate the drop-down list.

In cell “A16” of “Sample Data” sheet, write the below formula in the Data Validation window and click OK as shown in the below Figure to get the dynamic drop-down list as shown below.

OFFSET(Temp!$A$2,,,COUNTIF(Temp!$A$2:$A$30,”?*”),)

  • COUNTIF function would return the number of rows with brand names
  • OFFSET function would select the number of rows as given by COUNTIF from A2 and populate the dynamic drop-down list

Dynamic range selection using data validation

 

Creating single drop down list

Second dependent dynamic range drop-down

Creating the second drop-down involves the same task as we did before. But, the second drop-down should populated on selecting an item from the first drop-down list. Thus, it should check the selected item in the first drop-down (currently in “A16” cell of “Sample Data” sheet) an get only the relevant items. We achieve that by using the “IF” function.

Compared to the previous formula, the change in this array formula is the input array for the INDEX function. In previous formulas, the input was fixed to SampleData!$A$3:$A$11 . Here, the input array in which the unique values must be found should be based on the selection made on the previous drop-down and hence, is dynamically obtained using OFFSET formula.

Write the below formula in cell “B2” of “Temp” sheet, press Ctrl+Shift+Enter & drag the formula till “B11”.

IFERROR(

INDEX(OFFSET(SampleData!$B$2,MATCH(SampleData!$A$16,SampleData!$A$3:$A$11,0),0,COUNTIF(SampleData!$A$3:$A$11,SampleData!$A$16),1),

IF(AND( ISERROR( MATCH( 0, COUNTIF( $B$1:B1, OFFSET( SampleData!$B$2, MATCH( SampleData!$A$16, SampleData!$A$3:$A$11, 0), 0, COUNTIF( SampleData!$A$3:$A$11, SampleData!$A$16), 1) ), 0) )=TRUE, $B1=”HeaderName”), 1,MATCH(0,COUNTIF($B$1:B1, OFFSET( SampleData!$B$2, MATCH( SampleData!$A$16, SampleData!$A$3:$A$11, 0), 0, COUNTIF( SampleData!$A$3:$A$11, SampleData!$A$16), 1) ), 0) )),””)

In Cell “B16” of “Sample Data” sheet, using data validation, enter the below formula and click OK to get the dependent dynamic second drop-down list. Eventually, the output would look as shown below.

OFFSET(Temp!$B$2,,,COUNTIF(Temp!$B$2:$B$30,”?*”),)

Creating dependent drop-down using data validation and array formulas

Multiple dependent dynamic range drop-downs

Any number of further dependent drop-downs we create would follow the same procedure, but for more than two drop-down selections, the above formulas would become very long & difficult to handle. Instead of IF condition, we can use logical AND concept to get the same output. So, we can use the below formula from third drop-down to all further ones.

Write the below formula in cell “C2” of “Temp” sheet to get the unique values for third drop-down.

IFERROR(INDEX(SampleData!$C$3:$C$11,AGGREGATE(15,3,((SampleData!$A$16=SampleData!$A$3:$A$11)* (SampleData!$B$16=SampleData!$B$3:$B$11))/((SampleData!$A$16=SampleData!$A$3:$A$11)* (SampleData!$B$16=SampleData!$B$3:$B$11))*(ROW(SampleData!$B$3:$B$11) – ROW(SampleData!$B$2)), ROWS($B$1:$B1))),””)

  • AGGREGATE function is used here to get the smallest number in the range. The * operator performs logic AND operation.

In Cell “C16” of “Sample Data” sheet, using data validation, enter the below formula and click OK to get the dependent dynamic third drop-down list.

OFFSET(Temp!$C$2,,,COUNTIF(Temp!$C$2:$C$30,”?*”),)

The final output after we repeat the process for fourth and fifth drop-downs, would look as shown in the below Figure.

Multiple dependent drop down list using array formulas

Conclusion

Creating drop-down with array formulas is fun. I hope this article would help you in creating more sophisticated dashboards with multiple dependent dynamic drop-downs.

 

Check out our Tableau Pages

Posted on

Dropdowns in Excel Part 1

What is a dropdown?

I’m 100% sure most of you reading this post, know what a dropdown is. We interact with hundreds of websites and user forms every day. Dropdowns are such a beautiful user interface objects, that they make the lives of the user and the one who creates the form much happier. It reduces the pain of typing something in the box for the user and the pain of resolving inconsistencies in the data for the form creator. Let it be choosing the person title (Mr./Ms./Mrs.) to choosing the city you’re from to anything under sun. Let me start with the very basic things in this post, which many of you might already know. But I’ll slowly come to the very interesting ones in the subsequent blog posts.

How is dropdown useful in Excel?

Excel is unarguably one of the best data entry software and the most widely used across the fields and by a cheap viagra wide range of people.

From a user perspective, when I’m entering a number of rows of data, it’s difficult for me to type the text in a particular column every time I create a new row. Let’s say I am entering the transactions happening in a vegetable stall. Then I need to type the name of the vegetable every time a transaction happens, number of Kilograms the customer took, the amount he/she paid etc. And when I’m analyzing at the end of the month to understand the sales of each vegetable, I will have to add new vegetables to the dictionary,

  • Tomoto
  • Tamoto
  • Tamato
  • Tomat etc. 🙂

I don’t make these mistakes because I don’t know how to write a simple word “Tomato”; but while entering loads of data I tend to make small mistakes, which will trouble me a lot at the end. So, a dropdown is inarguably one of the most useful data entry elements in Excel.

How to create a dropdown list in Excel?

There are multiple ways in which you can create a dropdown list in Excel.

  • Data Validation
  • Form Control (Combo Box)
  • ActiveX Control (Combo Box)

But of these methods, Data Validation is the most used method, for its simplicity. Later on, I’ll explain the pros and cons of each method, so that you can choose the best method among these for you.

Creating a dropdown list in Excel through Data Validation

Before going to explain how to create a dropdown list, let me tell you what a Data Validation is? It controls the type of data or the values that users enter into a

cell. For example, you may want to enter only positive values in a cell, only ‘time’ in a column, or a text below the specified length. You can define all these conditions through Data Validation. Even dropdown list is a data validation where you’re constraining the user to use only those set of words in that cell. Again, in creating a dropdown list through data validation, there are different ways in doing it. Here are those…

Writing the complete list in the Source Field

1)  Select the Cell (or) set of Cells in which you want to see the dropdown.
2)  Go to “Data” Tab –> Data Validation –> Data Validation
3)  Select List in the “Allow box”
4)  Just enter the names you want to see in the dropdown list in the “Source” Box

Dropdown in excel through Data Vaidation

That’s it!!! Go to the cell for which you created validation. Click on the dropdown button, you’ll be able to see the names of the vegetables you’ve entered.

Excel Dropdown Names in the Source Field

Selecting a list of items in the same or other sheet

In the aforementioned example, instead of entering the names in the source field, you can choose a list which is already entered in the same or different excel sheet.

The best advantage of this method is, if you remove the vegetable name ‘Carrot’ from the list and replace it with ‘Lady Finger’, it will automatically update the list without your intervention. The only problem with this method is, if you append an item to this list at the end, it won’t get reflected in the dropdown.

Selecting a List of Items from the Same or Other worksheet

Using Dynamic Defined Names

1)  Select the list you want to be shown in the dropdown
2)  In the Name Box which is beside the formula bar, enter the name and click ENTER
3)  That will name the selected table as written in the “Name Box”
4)  In the Source Field, write “=” followed by the Table Name you’ve selected

There it is! You’ve created a dropdown!! It’s as simple as that!

“Is that all about dropdowns?”

“No! Please wait a moment!”.  This is just the beginning and it’s going to get more interesting in the next posts in this series. I will be back with a post on Friday and till then adieu.

Posted on

How to Avoid Screen Updating in Excel VBA?

This article helps to handle the screen updates using macro. We can speed up our macro by turning off the screen updating while the macro runs, by adding the below line of code to the macro:
Sub Macro ()
Application.ScreenUpdating = False

Write the necessary code Here

——————-

——————-
Application.ScreenUpdating = True
End Sub.

The prior versions of excel 2000, it was not

required to turn ScreenUpdating back to

true. When the macro is finished, Excel would revert back to turning the screen updating to true.

But now whether you are writing macros in Excel 97, Excel 2000, Excel 2002, 2003, 2007 or 2010 always we have to turn the screen updating back to true with the below code:
Application.ScreenUpdating = True

Vani is a Business Associate with p2w2, a Spreadsheet Solutions company. p2w2 has expertise in Excel Modeling, Excel Dashboards, Profitability Analysis, Excel Invoicesand Excel Bid sheets. You can contact us by email: cs [at] p2w2.com or call us at 305.600.0950.

Posted on

How to do a Two-Way Lookup in Excel?

Excel spreadsheets support lookup functions that return a value from a table by looking up another value in the table. The vlookup and hlookup functions in Excel work as one-way lookups. This article helps you to perform a two way lookup.

The Pi

cture below shows a simple example:

The first table shows products, Price of the product and discount on number of products purchased. The second table shows name of the person, product purchased, number of products purchased and discount applicable.

A formula is used in column E of second table to get the discount percentage from the first table as shown in the picture below:

The formula in cell E15 looks up the values of cells C15(Product) and D15(# of Products) in the first table and returns the corresponding value from the table.

The formula in E15 is:

=IFERROR(INDEX($D$4:$F$12,MATCH($C15,$B$4:$B$12,0),MATCH($D15,$D$3:$F$3,0)),”-“)

Description of the formula:

The above formula uses the INDEX function, which consists of three arguments.

The first argument gives the entire range of ‘Discount% on # of products’ from first table($D$4:$F$12).

=IFERROR(INDEX($D$4:$F$12,MATCH($C15,$B$4:$B$12,0),MATCH($D15,$D$3:$F$3,0)),”-“)

The second argument is MATCH function. MATCH ($C15,$B$4:$B$12,0)

It matches the ‘product’ in cell C15 of second

table with the ‘products’ in cells B4 to B12 of first table and returns the ‘Product‘.

=IFERROR(INDEX($D$4:$F$12,MATCH($C15,$B$4:$B$12,0),MATCH($D15,$D$3:$F$3,0)),”-“)

The third argument is also a MATCH function. MATCH ($D15,$D$3:$F$3,0).

It matches the ‘# of products’ in cell D15 of second table with the ‘Discount% on # of products’

in cells D3 to F3 of first table and returns the ‘# of products’.

=IFERROR(INDEX($D$4:$F$12,MATCH($C15,$B$4:$B$12,0),MATCH($D15,$D$3:$F$3,0)),”-“).

In this way the above formula is used as a two way lookup formula to retrieve the value required.