Ms Excel Worksheet

1. Free Spreadsheet Templates
2. Ms Excel Worksheet Gridline Colors
3. Microsoft Excel Worksheets

In newer versions of Excel, each worksheet contains roughly a million rows and more than 16,000 columns, which necessitates an addressing scheme in order to keep track of where data is located. The horizontal rows are identified by numbers (1, 2, 3) and the vertical columns by letters of the alphabet (A, B, C). Microsoft Excel is one of the most versatile and useful programs in the Office suite. It doesn't matter if you need Excel templates for budgeting the next fiscal year, tracking your business inventory, planning out meals, or creating a fantasy football draft sheet, there are plenty of Microsoft Excel templates for you. Excel comes with the OS (operating system) you already have, if it is Microsoft. If you use Microsoft, it should be on your computer, just search for it in the Start Menu. If you are using a Mac, you will not find 'Microsoft Excel', but a program similar to it. If you are looking for a free Microsoft Excel® templates, below you will find a comprehensive list of excel spreadsheet templates and calculators created by Spreadsheet123.com. Vast majority of spreadsheet templates on this page are created with purpose to become a solution for practically any occasion in your life, whether your are moving home or placing kids to college, getting ready for.

Q. I have an Excel workbook with dozens of custom-named worksheets. Is it possible to create a list of those worksheet names in Excel without having to retype them?

A. Yes, you can create a list of your Excel workbook's worksheet names as follows. From the Formulas tab, select Defined Names, Define Name to launch the New Name dialog box pictured below.

Enter SheetNames into the Name field, enter the following formula into the Refers to field: =REPLACE(GET.WORKBOOK(1),1,FIND(']',GET.WORKBOOK(1)),'), and then select OK. This action will create a named formula that can then be used in conjunction with the INDEXfunction to produce a list of worksheet names. Continuing, let us assume you have 25 named worksheets. Next, select a cell where you want your list of names to appear (cell B2 in this example), and enter the numbers 1 through 25 (in cells A2 through A26 in this example, as pictured below). Then in cell B2 enter the formula =INDEX(SheetNames,A2), and then copy and paste the formula down 25 rows. These formulas will return a list of the names of your worksheet tabs in the same order as your worksheet tabs.

(Note: The workbook must be saved as an Excel Macro-Enabled Workbook file type in order to retain the Defined Name formula.)

Now that you have successfully added a table of contents to your workbook, I recommend you add hyperlinks as well, to aid navigation. This can be done by adding the formula =HYPERLINK('#'&B2&'!A1','Go To Sheet') in cell C2 (in the example below) and copying this formula down next to each of the worksheet names.

As a result, you will then be able to click each link to jump to the various worksheets listed in your table of contents. (As an alternative, you could instead select each name in the table of contents one at a time and press Ctrl+Kto apply a hyperlink directly to each worksheet name. The advantage of this alternative approach is that the separate column of hyperlinks (such as column C in the example above) is not necessary, but the disadvantage is that each hyperlink must be applied one at a time.)

Further, I recommend you insert a button on each worksheet that will return you to your table of contents, as follows:

1. Name your table of contents. Start by placing your cursor at the top of the table of contents and in the Name Box (located directly above column A), type TOC and press the Enter key. This named location will be the cell where the navigational button created in the steps below will lead.
2. Create a hyperlinked text box button. Anywhere in the workbook, insert a Text Box (from the Insert tab's Shapes menu), label it Return to the Table of Contents, and format the button to your desire. (In the example pictured below, I made the Text Box red, centered and bolded the text, applied a button effect, and applied a bottom right shadow.)
3. Apply a hyperlink to your text box button. Right-click the text box to select it, press Ctrl+K to launch the Insert Hyperlink dialog box, select Place in This Document (under the Link to menu), scroll down to Defined Names,select TOC, and press OK. These actions will create a clickable button that will return you to your table of contents.
4. Copy and paste the hyperlinked text box button throughout your worksheet. Right-click the text box button again to select it, left-click on the very edge of the text box button to ensure you have selected the entire text box button and not just the text within the text box, press Ctrl+C to copy the text box button, then navigate to each worksheet and paste the text box button on each of your worksheets to make navigating your large workbook easier. (You might want to paste several copies of this text box button on your larger worksheets for added navigation convenience.)

(Hint: To select a hyperlink-enabled text box without triggering the hyperlink effect, right-click the text box button and then left-click the very edge of the text box button — this action will allow you to resize or reposition the button as desired.) You can download an example Excel workbook containing a table of contents and navigational hyperlinks at carltoncollins.com/list.xlsm.

About the author

J. Carlton Collins (carlton@asaresearch.com) is a technology consultant, a conference presenter, and a JofA contributing editor.

Submit a question

Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.

Applies to:SQL Server (all supported versions) Azure SQL Database

There are several ways to import data from Excel files to SQL Server or to Azure SQL Database. Some methods let you import data in a single step directly from Excel files; other methods require you to export your Excel data as text (CSV file) before you can import it. This article summarizes the frequently used methods and provides links for more detailed information.

List of methods

You can use the following tools to import data from Excel:

If you want to import multiple worksheets from an Excel workbook, you typically have to run any of these tools once for each sheet.

A complete description of complex tools and services like SSIS or Azure Data Factory is beyond the scope of this list. To learn more about the solution that interests you, follow the provided links.

Important

For detailed info about connecting to Excel files, and about limitations and known issues for loading data from or to Excel files, see Load data from or to Excel with SQL Server Integration Services (SSIS).

If you don't have SQL Server installed, or you have SQL Server but don't have SQL Server Management Studio installed, see Download SQL Server Management Studio (SSMS).

SQL Server Import and Export Wizard

Import data directly from Excel files by stepping through the pages of the SQL Server Import and Export Wizard. Optionally, save the settings as a SQL Server Integration Services (SSIS) package that you can customize and reuse later.

1. In SQL Server Management Studio, connect to an instance of the SQL Server Database Engine.

2. Expand Databases.

3. Right-click a database.

4. Point to Tasks.

5. Click one of the following options.

• Import Data

• Export Data

For an example of using the wizard to import from Excel to SQL Server, see Get started with this simple example of the Import and Export Wizard.

To learn about other ways to launch the Import and Export wizard, see Start the SQL Server Import and Export Wizard.

SQL Server Integration Services (SSIS)

If you're familiar with SSIS and don't want to run the SQL Server Import and Export Wizard, create an SSIS package that uses the Excel Source and the SQL Server Destination in the data flow.

For more info about these SSIS components, see the following topics:

To start learning how to build SSIS packages, see the tutorial How to Create an ETL Package.

OPENROWSET and linked servers

Important

In Azure SQL Database, you cannot import directly from Excel. You must first export the data to a text (CSV) file. For examples, see Example.

Note

The ACE provider (formerly the Jet provider) that connects to Excel data sources is intended for interactive client-side use. If you use the ACE provider on SQL Server, especially in automated processes or processes running in parallel, you may see unexpected results.

Distributed queries

Import data directly into SQL Server from Excel files by using the Transact-SQL OPENROWSET or OPENDATASOURCE function. This usage is called a distributed query.

Important

In Azure SQL Database, you cannot import directly from Excel. You must first export the data to a test (CSV) file. For examples, see Example.

Before you can run a distributed query, you have to enable the ad hoc distributed queries server configuration option, as shown in the following example. For more info, see ad hoc distributed queries Server Configuration Option.

The following code sample uses OPENROWSET to import the data from the Excel Sheet1 worksheet into a new database table.

Here's the same example with OPENDATASOURCE.

To append the imported data to an existing table instead of creating a new table, use the INSERT INTO ... SELECT ... FROM ... syntax instead of the SELECT ... INTO ... FROM ... syntax used in the preceding examples.

To query the Excel data without importing it, just use the standard SELECT ... FROM ... syntax.

For more info about distributed queries, see the following topics:

• Distributed Queries (Distributed queries are still supported in SQL Server 2016, but the documentation for this feature has not been updated.)

Linked servers

You can also configure a persistent connection from SQL Server to the Excel file as a linked server. The following example imports the data from the Data worksheet on the existing Excel linked server EXCELLINK into a new SQL Server database table named Data_ls.

You can create a linked server from SQL Server Management Studio, or by running the system stored procedure sp_addlinkedserver, as shown in the following example.

For more info about linked servers, see the following topics:

For more examples and info about both linked servers and distributed queries, see the following topics:

Prerequisite - Save Excel data as text

To use the rest of the methods described on this page - the BULK INSERT statement, the BCP tool, or Azure Data Factory - first you have to export your Excel data to a text file.

In Excel, select File | Save As and then select Text (Tab-delimited) (*.txt) or CSV (Comma-delimited) (*.csv) as the destination file type.

If you want to export multiple worksheets from the workbook, select each sheet and then repeat this procedure. The Save as command exports only the active sheet.

Tip

For best results with data importing tools, save sheets that contain only the column headers and the rows of data. If the saved data contains page titles, blank lines, notes, and so forth, you may see unexpected results later when you import the data.

The Import Flat File Wizard

Import data saved as text files by stepping through the pages of the Import Flat File Wizard.

As described previously in the Prerequisite section, you have to export your Excel data as text before you can use the Import Flat File Wizard to import it.

For more info about the Import Flat File Wizard, see Import Flat File to SQL Wizard.

BULK INSERT command

BULK INSERT is a Transact-SQL command that you can run from SQL Server Management Studio. The following example loads the data from the Data.csv comma-delimited file into an existing database table.

As described previously in the Prerequisite section, you have to export your Excel data as text before you can use BULK INSERT to import it. BULK INSERT can't read Excel files directly. With the BULK INSERT command, you can import a CSV file that is stored locally or in Azure Blob storage.

For more info and examples for SQL Server and SQL Database, see the following topics:

BCP tool

BCP is a program that you run from the command prompt. The following example loads the data from the Data.csv comma-delimited file into the existing Data_bcp database table.

As described previously in the Prerequisite section, you have to export your Excel data as text before you can use BCP to import it. BCP can't read Excel files directly. Use to import into SQL Server or SQL Database from a test (CSV) file saved to local storage.

Important

For a text (CSV) file stored in Azure Blob storage, use BULK INSERT or OPENROWSET. For an examples, see Example.

For more info about BCP, see the following topics:

Copy Wizard (Azure Data Factory)

Import data saved as text files by stepping through the pages of the Azure Data Factory Copy Wizard.

As described previously in the Prerequisite section, you have to export your Excel data as text before you can use Azure Data Factory to import it. Data Factory can't read Excel files directly.

For more info about the Copy Wizard, see the following topics:

• Tutorial: Create a pipeline with Copy Activity using Data Factory Copy Wizard.

Azure Data Factory

If you're familiar with Azure Data Factory and don't want to run the Copy Wizard, create a pipeline with a Copy activity that copies from the text file to SQL Server or to Azure SQL Database.

As described previously in the Prerequisite section, you have to export your Excel data as text before you can use Azure Data Factory to import it. Data Factory can't read Excel files directly.

For more info about using these Data Factory sources and sinks, see the following topics:

To start learning how to copy data with Azure data factory, see the following topics:

Common errors

Microsoft.ACE.OLEDB.12.0' has not been registered

This error occurs because the OLEDB provider is not installed. Install it from Microsoft Access Database Engine 2010 Redistributable. Be sure to install the 64-bit version if Windows and SQL Server are both 64-bit.

The full error is:

Cannot create an instance of OLE DB provider 'Microsoft.ACE.OLEDB.12.0' for linked server '(null)'

This indicates that the Microsoft OLEDB has not been configured properly. Run the following Transact-SQL code to resolve this:

The full error is:

The 32-bit OLE DB provider 'Microsoft.ACE.OLEDB.12.0' cannot be loaded in-process on a 64-bit SQL Server

This occurs when a 32-bit version of the OLD DB provider is installed with a 64-bit SQL Server. To resolve this issue, uninstall the 32-bit version and install the 64-bit version of the OLE DB provider instead.

The full error is:

The OLE DB provider 'Microsoft.ACE.OLEDB.12.0' for linked server '(null)' reported an error. The provider did not give any information about the error

Cannot initialize the data source object of OLE DB provider 'Microsoft.ACE.OLEDB.12.0' for linked server '(null)'

Free Spreadsheet Templates

Both of these errors typically indicate a permissions issue between the SQL Server process and the file. Ensure that the account that is running the SQL Server service has full access permission to the file. We recommend against trying to import files from the desktop.

Ms Excel Worksheet Gridline Colors

The full errors are:

Microsoft Excel Worksheets

See Also