Excel

Using a SQL Server SELECT Statement to Query an Excel Workbook

In this article we look at how you can query an Excel workbook as if it were a table in a SQL Server Database.

The SQL Server OPENROWSET function can be used to connect to a variety of data sources by means of a data provider: 

Creating And Working With Tables in Excel

You may think that an Excel Workbook already provides a table of data to you, and in a sense it does, an area consisting of Rows and Columns, with column and row headings and values.

It is, however, possible to create formal Tables from ranges of cells on an Excel worksheet.

Well, here are a few reasons:

Creating A Drop Down Lists For Users to Choose From In Excel

In the following example we can see a drop list that a user can choose from when populating a Country column value.

Although we can create External Data Sets in Excel that pull information from a SQL Server Database there are times when you want to check a value or lookup a value from an underlying SQL Server database directly from an Excel formula.

We will look at an example that enables us to lookup a customer’s total revenue value from an Excel Formula.

Make Rows the Columns and Make Columns the Rows In an Excel Worksheet

Have you ever found that you have a worksheet with a table of data on it, but you would really like the rows and columns to be switched round? In other words you would like to transpose the columns and rows.

Stripping Out Duplicate Data From an Excel Worksheet

We often find that after converging data from a number of sources we have duplicate records in our worksheets. In this article I show you how to strip out those duplicate rows using the Excel “Remove Duplicates” option.

De-duplicating A Single Excel Column

In the following example we have duplicate company names in the data on our worksheet.

Introduction to Microsoft Office Training Course

We have now introduced a new entry level Microsoft Office course “Introduction to Microsoft Office” aimed at those who are completely new to Microsoft Office or need a refresher of the basics:

Get More From Your Business Data

We are seeing an increasing number of our clients wanting to get more out of their databases and as a result we are seeing an increase in demand for business intelligence and data analysis courses.

Last week while teaching a PowerPivot course I was asked if it was possible to import data into a Power Pivot table from text files with non-standard delimiters.

The answer is – YES!!

ODBC Desktop Text File Driver

PowerPivot uses the Microsoft ODBC Desktop Database Drivers.

Are you getting the most out of Excel?

Excel is great at adding up numbers, but it can be used for so much more!!