How To Upload Odc To Table Excel

Sometimes Excel workbooks become quite big: The more worksheets there are, the more difficult it is to proceed the overview. A table of contents might help. In this article we'll explore 4 ways of creating tables of contents in an Excel workbook.

Let's say, we want to create a new worksheet with a list of all other worksheets. Furthermore, each list entry should accept a link to the corresponding worksheet and then that y'all can easily click on it and will be taken to this worksheet at once. Basically, at that place are 4 methods for creating such table of contents: Do it manually, use a complex formula, use a VBA macro or an Excel add-in.

Contents

Method 1: Create a tabular array of contents manually
Method 2: Use formulas for a tabular array of contents
Method 3: Employ a VBA macro
Method 4: Use an Excel add-in to create a table of contents
- Also interesting:

Method ane: Create a tabular array of contents manually

table of contents, index, manual, excel — Insert a table a contents manually

The starting time method is the most obvious one: Type (or copy and paste) each sheet name and add links to the cells. These are the necessary steps:

Create a new worksheet past correct clicking on whatever worksheet name and click on Insert Sheet (or press Shift + Alt + F1). Give a proper name, for example 'Contents'.
Outset by typing the first worksheet proper noun into prison cell B4 (or any cell you like…).
Add together the link to the cell: Right click on the jail cell and click on 'Hyperlink'. Select 'This document' as shown on the picture to a higher place and click on the canvass name you want to create the list entry for. Unremarkably cell A1 is fine as the jail cell reference. The window of adding a hyperlink on Windows looks a little bit dissimilar but offers the same options.
Echo steps 2 and 3 until all worksheets are in your tabular array of contents.
Do all the formatting as desired and then you lot are done.

Method two: Use formulas for a table of contents

named, range, table, contents, formula — Define a named range offset in order to insert a table of contents with formulas.

Thanks to Brian Canes, who posted this method as a annotate: There is actually a style of using formulas and named ranges. Follow these steps:

Click on "Define Name" in the heart of the Formulas ribbon.
Type the name "TabNames".
Copy and paste this code into the "Refers to:" field: =Get.WORKBOOK(1)&REPT(Now(),)
Confirm with OK.
Copy this formula into any prison cell. As information technology refers to the prison cell A1, information technology'll be the entry for the first worksheet in your workbook:

=IF(ROW(A1)>SHEETS(),REPT(At present(),),SUBSTITUTE(HYPERLINK("#'"&TRIM(Right(SUBSTITUTE(SUBSTITUTE(INDEX(TabNames,ROW(A1))," ",CHAR(255)),"]",REPT(" ",32)),32))&"'!A1",TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(Index(TabNames,ROW(A1))," ",CHAR(255)),"]",REPT(" ",32)),32))),CHAR(255)," "))

copy, formula, excel, named, range — Copy this formula down.

Copy this formula down until you get blank cells.

Please note:

All worksheets will be regarded, also hidden ones.
If you alter the society of worksheets, delete some or do whatever other changes: These changes will be immediately reflected in the table of contents. So y'all have to be careful if yous add comments or do any special formatting.
You take to salve the workbook as a macro enabled workbook in the format ".xlsm". The reason is that you lot utilise a formula as a named range which is only possible with macro-enabled workbooks.

Practise you want to boost your productivity in Excel?

Get the Professor Excel ribbon!

Add together more than 120 great features to Excel!

Method 3: Use a VBA macro

Every bit the beginning two methods works merely is quite troublesome – specially for large workbooks – nosotros'll take a look at a third method: A VBA macro. The macro is supposed to practice the same steps: Walk through all sheets, create a listing entry for each canvass and insert a hyperlink to each canvass.

Get to the programmer ribbon.
Click on "Editor".
Add together a new module.
Paste the following code.
Printing beginning on the top.

                Sub insertTableOfContents()     Dim PROFEXWorksheet As Worksheet     Dim tempWorksheetName, tempLink, nameOfTableOfContentsWorksheet As Cord     Dim i Equally Integer          'Add a new worksheet     Sheets.Add      'Rename the worksheet     nameOfTableOfContentsWorksheet = "TableOfContents"     ActiveSheet.Proper noun = nameOfTableOfContentsWorksheet      'Add the headline     Range("B3") = nameOfTableOfContentsWorksheet      'Initialize the counting variable i     i = 0               'Go through all worksheets as described     For Each PROFEXWorksheet In Worksheets             'Copy the current worksheet name             tempWorksheetName = PROFEXWorksheet.Name              'Create the link from the current worksheet and link it to cell A1             tempLink = "'" & tempWorksheetName & "'!R1C1"                          'Add the list entry             Sheets(nameOfTableOfContentsWorksheet).Cells(i + v, 2) = tempWorksheetName              'Select information technology for inserting the hyperlink             Sheets(nameOfTableOfContentsWorksheet).Cells(i + 5, ii).Select              'Insert the hyperlink             ActiveSheet.Hyperlinks.Add Anchor:=Option, Address:="", SubAddress:=tempLink              'Proceed with side by side entry and increase i therefore             i = i + i     Side by side Stop Sub

Method 4: Employ an Excel add-in to create a table of contents

table, of, contents, add-in, professor, excel, tools, table of contents, directory, index — Insert a customized table of contents with the Excel add together-in 'Professor Excel Tools'

In that location are some Excel add-ins for creating a table of contents. We – of class – recommend our own add together-in. It doesn't simply insert a table of contents but you tin can easily customize it:

You lot can customize the headline color.
You can choose if you desire to insert links to each worksheet.
You lot can define levels: For example by loftier level sheets by their name.
You can select if you want to insert an entry for the table of contents sheet itself.
You can choose if you want to skip hidden worksheets or include them as well.
You can define your preferred name for the tabular array of contents sheets.

Your last settings will be saved and then that it's very fast to update or create a table of contents. In that location is a free trial version with no sign-up available. Click hither to start the download.

This function is included in our Excel Add-In 'Professor Excel Tools'

(No sign-up, download starts directly)

Henrik Schiffner is a freelance business consultant and software programmer. He lives and works in Hamburg, Deutschland. Also being an Excel enthusiast he loves photography and sports.