Quick tip: Quickly navigate multiple Excel worksheets, much like a table of contents

I recently did an AXUG Session about using the real time integration to Excel. You can build all sorts of reports/metrics and have the data auto-refresh when opening the form.

Its great! And you can really fill up an Excel worksheet quickly...  But for those that really get into it, traversing a massive Excel sheet can get pretty daunting. The resolution to this for some people is to create other Excel files in a static location that all reference each other. That works, and is ok for some purposes, but not always. I would NOT recommend breaking out an Excel sheet into multiple linked Excel sheets just for ease of navigation sake. Once you hit the 4th or 5th linked Excel file and have more than one person trying to figure it out, good luck.

An alternative approach
A little known trick to quickly navigate multiple Excel worksheets, much like a table of contents, is to right click on the arrows in the lower left hand side of the application (to the left of the worksheet tabs). This will open a form called 'Activate'. 'Activating' a worksheet just means that it will be navigated to. Its not really activating or enabling anything. The terminology is a little odd but thats all its saying. This is basically a table of contents for your Excel file!

I had a worksheet with over 50 tabs and could navigate to a specific one in three clicks (right click on arrows, select worksheet, click ok). You might need to scroll to the worksheet if there are a lot but thats easier than the alternatives or a table of contents workbook tab.

Hope that helps!

Figure 1 - The many, long worded Excel worksheets

Figure 2 - Right click on the arrows to see all worksheets

Issue with Excel word being replaced (HSA converted to HAS automatically)

I was having an issue typing HSA in Excel and it being constantly 'corrected' to 'HAS'. I literally could not type HSA, which stands for health savings account (HSA).

You can correct this by two steps: 1) Create the word in the custom dictionary and 2) remove the autocorrect option for HSA/HAS combination.

Step 1 - Add HSA to the custom dictionary

1. In Excel
2. File > Options (Figure 1) > Proofing > 'Custom Dictionaries' 
3. Select the dictionary you'd like to add the word to (Figure 2)
4. Click 'Edit word list'
5. enter 'HSA' in the word field 
6. Click 'Add' 
7. Click 'Ok'

Step 2 - Autocorrect removal for HSA/HAS

1. In Excel
2. File > Options (Figure 1) > Proofing >  'AutoCorrect Options'
3. Type the word you are trying to type (HSA for me) in the 'Replace' field
4. Look for the replace/with combination you are hitting (HSA/HAS for me)
5. Highlight the record, click 'Delete', then click 'Ok'

Figure 1

Figure 2 

Figure 3