Contextures

Contextures News 20180828

Excel Quick Navigation Tip

August 28, 2018

Go back to a previous location, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.

NOTE: You'll get the next newsletter on September 11th -- and then we'll go back to the weekly schedule.

Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.

Fix Blank Cells

I've been working on an Excel file where there are cells that look blank, but they aren't really blank. The list was imported from a database, and the cells have hidden content that cause problems with counting and navigating.

Counting: There are 5 cells selected in the screen shot below, and only 2 cells show text. In the status bar though, the count shows as 5.

Navigating: If I select the cell with "Comox", and double-click its bottom border, Excel takes me to the bottom of the list. If the cells were really blank, Excel would stop in the blank cell above Powell River.

cell looks blank

To fix those cells, so they're really blank, use the Text to Columns command:

  • Select all the blank cells
  • On the Excel Ribbon, click the Data tab
  • Click the Text to Columns command
  • In Step 1, select Delimited, then click the Finish button

To see more ways to fix the "not really blank" cells, go to my Contextures blog. That article also has the steps for showing the hidden contents of those cells - turn on the Lotus Transition Keys option.

Go Back to Previous Cell

When you're working on a big worksheet, you might jump around a bit, from section to section. If you want to keep track of a few cells, to go back to them quickly, you can store up to 4 temporary locations.

First, store a temporary location:

  1. Select a cell that you want to remember
  2. Click in the Name Box, at the left of the Formula Bar
  3. Press the Enter key, to temporarily store that cell's address.

store temporary location

To go back to one of the 4 locations:

  1. Press the F5 key, or Ctrl + G, to open the Go To box
  2. Double-click on any location in the list, to go to that location.

Read more about this trick on my Contextures blog, and remember -- these locations are temporary. They're only stored until you close the file, and only the last 4 are stored.

Excel Articles

Here are a few Excel articles that you might find useful or interesting.

ModelOff - The Excel questions and answers have been posted for last year's ModelOff competition. Even if you've never considered competing, it's interesting to see the solutions, and you'll probably find a new formula or two, to use in your own Excel projects. (Level - Int)

Power Query - Chandoo is hosting a free live YouTube session next week - Power Query for Accountants. He'll do a quick intro to PQ, then show how to get data from bank statements, and other accounting activities. (Level - Int)

Also see: My Excel Products || Excel Events || Previous Issues || Weekly Humour

New Laptop

Last week, I got a new laptop, which disrupts your life for a while, as you know. There are many programs to install, and thousands of files to copy, and countless settings to tweak. It's like moving into a new house, but without all the heavy lifting.

One of the first programs that I installed was Office 365, and I didn't believe Microsoft's optimistic estimate that it would be done in "just a moment". (Ha! It took about 30 minutes.)

Fortunately, the Canna Lily (not a lily) on our front porch is blooming now, so I can sit there to relax for a few minutes.

weekly photo

That's it for this week! If you have any comments or questions, send me an email.

NOTE: For the online version, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2018/20180828ctx.html
I'll also post any article updates or corrections there.

Debra Dalgleish
dsdalg @ gmail.com

Debra Dalgleish

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.

 

 

Get weekly Excel tips from Debra

 

pivot power free

 

 

 

 

 

Last updated: August 27, 2018 3:57 PM