Excel Quick Navigation Tip
August 28, 2018
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.
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.
To fix those cells, so they're really blank, use the Text to Columns command:
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.
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:
To go back to one of the 4 locations:
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.
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)
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.
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.
dsdalg @ gmail.com
Last updated: August 27, 2018 3:57 PM