Contextures News

Excel Navigation - No Macros

Mar 15, 2016

Go to Next and Previous sheets without macros, troubleshooting, and more, in this week's Excel news. If there are topics that you'd like to see covered in future newsletters, please let me know.

 -  Debra -  ddalgleish @
     Visit my Excel website for many more tutorials and videos.

Sheet Navigation No Macros

In some large Excel workbooks, it's helpful to get to the previous sheet or the next sheet quickly. You can do that with worksheet buttons and macros, but sometimes you don't want to add those, especially if there aren't any other macros in the file.

I've created a new sample file that you can download, and it uses hyperlinks to go to the next and previous sheets. The hyperlinks are created with the HYPERLINK function. You just have to create a list of the sheets that you want to navigate to, and name that list. Then, copy the formulas to each sheet, and click the links to move from one sheet to another.

To get the sample file, go to the Excel Sample files page on my website. In the Functions section, look for FN0044 - Hyperlink Formulas for Worksheet Navigation

Troubleshoot Large Files

Last week I discovered that one of my sample files was way bigger than it should have been. Nothing looked unusual, so I used my Excel Tools add-in to create a list of the sheets, and show the used range on each sheet. That quickly showed the problem -- one sheet had 15.7 million cells in its used range! To fix it, I deleted those rows, and saved the file.

Your Feedback

Thanks for sending your comments and questions each week, after you read my newsletter. I love to hear from you!

Last week, I heard from Ron L, who reminded me of the handy "Day Zero" trick that you can use with the DATE function. If you use zero as the day number, Excel returns the last day of the previous month. Here's the example that Ron sent:

=IF(DAY(DATE(2016,3,0))=29,"LeapYear","Not LeapYear")

Excel Articles

Here are a couple of Excel articles I read recently, that you might find useful.

Excel Skills -- Brad Edgar has a great resource list, with 27 ways to improve your Excel skills. Is there somthing in that list that you haven't used yet? (Level - All)

Data Insights -- You can register to watch free online sessions, live-streaming from Microsoft's Data Insights Summit, on March 22-23. I couldn't make it to the conference, so this will be the next best thing. (Level - Intermediate/Advanced)

More Excel Articles -- Find links to more articles in the latest Excel Weekly Roundup on my Contextures blog.

Fun With Excel

It's March Break here, so the kids are out of school for a week, and I'll have some little visitors. I like to plan ahead, so I made a bingo game in Excel, that uses pictures instead of numbers. I'll print out a few cards, and we can play a few games if we need to relax for a few minutes.

If you're entertaining kids too, you can download the file from my Sample Excel Files page -- go to the Functions section, and look for FN0045 - Excel Bingo Card With Pictures.

I used the Webdings font to create the pictures, so I hope the kids recognize what all of them are! I left out some of the really ancient pictures, like the film projector, phone modem and fax machine shown below.

NOTE: If you have any problems with the links in the email, you can see this newsletter on my website -- copy this link and paste it into your browser:

Note: I am an affiliate for some of the products mentioned in this newsletter, and earn a commission on the sales.




Last updated: May 24, 2021 7:48 PM