Category Archives: Excel

Quick Navigation in Excel Worksheets

Go There in a Flash in Excel

You inherited the biggest spreadsheet on the planet and have to scroll and troll to ranges miles away from your current screen! Don’t despair, Excel has several tools to get you there quickly…

The Go To Dialog Box

You can jump to any area of your worksheet with this handy command. Using the Ribbon, Home tab, Editing group, Find & Select drop down arrow, and select Go To… Just type in a cell reference, click OK or press ENTER, and you are there!

OK, that might beat scrolling but not by much, so here’s the corresponding shortcut:

Press CTRL G or F5 function key to auto display the Go To Dialog Box and it is ready for your entry. It also remembers your previous cell location(s).

The Name Box

Want the quick mouse navigation trick? The Name Box is your ticket! Just click in the box and type the cell reference (such as T44456); press ENTER and you are there. (Unfortunately, there is no keyboard shortcut for the actual Name Box but you could use F5 to display the Go To box, type the cell reference and press ENTER).

keyboard angled 2 x 3
Where did I put that?

Range Names

The amazing Name Box is multi-talented. We saw we can highlight large ranges of cells and also jump to a faraway cell location just by typing the cell reference(s) in the Name Box. But what if you are short on sleep and can’t remember the cell reference(s)? No problem. There is a reason it is called the “Name Box”. All you have to do is find that cell once, select it and give it a name that makes sense to you, like Sales or Profit, etc.

Steps:

  • Navigate to desired location
  • Click on desired cell (or range of cells)
  • Click in the Name Box and type a name, i.e., Sales
  • Press ENTER

To return to that location any time, just click the drop-down arrow on the Name Box and select the name you gave it, and you are magically transported there!

Important things to know when naming a cell:

  • Range Names must start with a letter but can contain numbers.
  • You can use more than one word to name a range but it cannot contain spaces.
  • Capitalize the separate words, like TotalSales, or the underscore can be used to represent the space, i.e., Total_Sales.

Range Names are ABSOLUTE by default, meaning the name will always refer to the cell even if the content is moved. And, even more impressive, Range Names can also be used in formulas instead of cell references. That Excel is one smart cookie!

The New Start Screens

2013 Office applications sport a new color-coded start screen:

  • blue for Word,
  • green for Excel
  • orange for PowerPoint
  • green for Publisher
  • burgundy for Access

They all look and behave the same way as this example for Word. Launching the program displays a list of recent documents. A blank document is the default option or instead you can select a template, search online for templates by clicking the Category links or typing key words in the Search Box.

Click Open Other Documents to search for a document on your computer or in a OneDrive folder. The top right of the screen shows details of the OneDrive account that you are currently logged in to use and you can Switch User or access Account Settings with the drop down arrow next to the login name. This is accessible in the same area in all your Office programs, and inside of each of your files.

word-excel-powerpoint-shortcuts-tips-start-screen-1

Start screens help new users find their way around more easily, and experienced users may like having all of their options in one place at startup but not everyone is doing the Snoopy dance here. Turning off the Start Screen will display a blank document when you open the program (just like the old days)!

How to turn off the Start Screen

Word is open:

  1. Click File tab, then Options at the bottom of the category list on the left
  2. The General tab will display
  3. Scroll down to the Start up options section
  4. Remove the check mark from Show the Start screen when this application starts
  5. Click OK and you are good to go.

Test by closing Word and re-opening. The blank document awaits!

Restore Direct Response to Open and Save As

Several 2013 programs display the Backstage when you open or save a file. If you want to bypass it for direct access to the Open and Save As dialog boxes, you’ll be glad to know you can get that control back also. (This applies to Word, Excel, PowerPoint and Publisher).

  1. Back to File tab, Options
  2. Click the Save category at left
  3. Click in the checkbox so that the Backstage does not display
  4. Click OK

Test your Open or Save As commands and you have instant dialog box.

Make this process even faster

Press F12 function key for instant Save As (all Office programs except Publisher)

Use CTRL O for Open (all Windows programs)

If this was helpful to you, please leave a comment!